SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 script for disable/enable mirroring
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deal11deal1
Starting Member

34 Posts

Posted - 01/08/2011 :  15:49:42  Show Profile  Reply with Quote
I have 2 jobs setup regarding database mirroring.
one job is enable-mirror, and other one is disable-mirror.
both are pretty straight forward jobs... they run on demand and off course enable and disable mirroring when we do patch upgrades and other stuff on the box.


job 1: alter database <databasename> set partner resume
alter database <databasename> set partner resume

( this job enable a database mirror is its disable.)


job 2: alter database DATABASENAME set partner suspend
alter database DATABASENAME set partner suspend
( this job when runs disable mirrored database).

what I need is a script in a SSIS package( it can only be a script too , i will config it in ssis packag thae later) instead of me writing the database name reads the database name from the sever (from a sys table) and looks if that database is mirrored , if it is.. than run the disable job.
if the database is disabled that run the enable job.

The logic for this I have come up so far is as follows.

used this article
http://msdn.microsoft.com/en-us/library/ms178655.aspx
the pseudo code is as follows

set @currentmirror = select status from dbmirroring table
if @currentmirror is null
sp_start_job @EnableJob
else
sp_start_job @disableJob

... does any one knows which table in system databse has the name of the databse where mirroring is taking place.

thanks in advance

tkizer
Almighty SQL Goddess

USA
36601 Posts

Posted - 01/08/2011 :  16:03:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
sys.databases

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

deal11deal1
Starting Member

34 Posts

Posted - 01/08/2011 :  17:32:37  Show Profile  Reply with Quote
any other insight?
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 01/08/2011 :  17:40:01  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
SELECT DB_NAME(database_ID) as DatabaseName, mirroring_role_desc
from sys.database_mirroring
Where mirroring_role = 1 ---1 = principal, 2 = mirror.

Continuation from your prior post?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=154907




Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page

deal11deal1
Starting Member

34 Posts

Posted - 01/08/2011 :  20:25:16  Show Profile  Reply with Quote
Thanks for help.. i will try it now.
Go to Top of Page

deal11deal1
Starting Member

34 Posts

Posted - 01/09/2011 :  13:57:30  Show Profile  Reply with Quote
SELECT DB_NAME(database_ID) as DatabaseName, mirroring_role_desc
from sys.database_mirroring
Where mirroring_role = 1 ---1 = principal, 2 = mirror.
in the above script how would i incorporate the disable mirrorring comand??
Go to Top of Page

deal11deal1
Starting Member

34 Posts

Posted - 01/09/2011 :  14:00:21  Show Profile  Reply with Quote
what I mean is

SELECT DB_NAME(database_ID) as DatabaseName, mirroring_role_desc
from sys.database_mirroring
Where mirroring_role = 1 ---1 = principal, 2 = mirror.

this code gets me the name of the databases that are mirrored on the server , now how would I
incorporate this following comand to run the job

alter database <databasename> set partner resume

Thanks in advance
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 01/09/2011 :  14:05:16  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
What have you tried in that respect? One way below...but there may be other ways ...do some research, trial and error..try to figure out basic SQL.

DECLARE @servername varchar(25)

SELECT @Servername = DB_NAME(database_ID)
FROM sys.database_mirroring
Where mirroring_role = 1

Declare @SQL varchar(1000)
Select @SQL = 'Alter Database ' + @ServerName + ' SET PARTNER RESUME'

exec sp_executeSQL @SQL






Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page

deal11deal1
Starting Member

34 Posts

Posted - 01/09/2011 :  18:12:36  Show Profile  Reply with Quote
when I run the above script it gives me a error

"Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'."
am I missing some thing.?
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 01/09/2011 :  18:22:20  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
Change the variable declaration to nvarchar instead of varchar.

DECLARE @servername nvarchar(25)


DECLARE @servername varchar(25)
Declare @SQL nvarchar(1000)

SELECT @Servername = DB_NAME(database_ID)
FROM sys.database_mirroring
Where mirroring_role = 1

Select @SQL = 'Alter Database ' + @ServerName + ' SET PARTNER RESUME'

exec sp_executeSQL @SQL



Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page

deal11deal1
Starting Member

34 Posts

Posted - 01/09/2011 :  19:53:05  Show Profile  Reply with Quote
I made a little change hoping to incorporate both resume and suspend databse comands in one script.
Does it sounds like a workable plan?

DECLARE @servername varchar(25)
Declare @SQL nvarchar(1000)

SELECT @Servername = DB_NAME(database_ID)
FROM sys.database_mirroring
IF mirroring_role = 1

Select @SQL = 'Alter Database ' + @ServerName + ' SET PARTNER RESUME'

Else

Select @SQL = 'Alter Database ' + @ServerName + ' SET PARTNER SUSPEND'

exec sp_executeSQL @SQL


Any thoughts?
Thanks in advance
Go to Top of Page

deal11deal1
Starting Member

34 Posts

Posted - 01/09/2011 :  21:47:48  Show Profile  Reply with Quote

DECLARE @servername varchar(25)
Declare @SQL nvarchar(1000)

SELECT @Servername = DB_NAME(database_ID)
FROM sys.database_mirroring
IF mirroring_role = 1

Select @SQL = 'Alter Database ' + @ServerName + ' SET PARTNER RESUME'

Else

Select @SQL = 'Alter Database ' + @ServerName + ' SET PARTNER SUSPEND'

exec sp_executeSQL @SQL



when I run the above mentioned script it gives me the error
Invalid column name 'mirroring_role'
I have check in the table and the coloum is there
Any help.
Go to Top of Page

shaggy
Posting Yak Master

India
245 Posts

Posted - 01/10/2011 :  02:01:24  Show Profile  Reply with Quote
DECLARE @servername varchar(25)
Declare @SQL nvarchar(1000),@mirroring_role nvarchar(1000)

SELECT @Servername = DB_NAME(database_ID) ,@mirroring_role = mirroring_role
FROM sys.database_mirroring


IF @mirroring_role = 1

Select @SQL = 'Alter Database ' + @ServerName + ' SET PARTNER RESUME'

Else

Select @SQL = 'Alter Database ' + @ServerName + ' SET PARTNER SUSPEND'

exec sp_executeSQL @SQL
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000