Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 script for disable/enable mirroring

Author  Topic 

deal11deal1
Starting Member

34 Posts

Posted - 2011-01-08 : 15:49:42
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

38200 Posts

Posted - 2011-01-08 : 16:03:10
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 - 2011-01-08 : 17:32:37
any other insight?
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 17:40:01
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 - 2011-01-08 : 20:25:16
Thanks for help.. i will try it now.
Go to Top of Page

deal11deal1
Starting Member

34 Posts

Posted - 2011-01-09 : 13:57:30
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 - 2011-01-09 : 14:00:21
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
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-09 : 14:05:16
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 - 2011-01-09 : 18:12:36
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
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-09 : 18:22:20
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 - 2011-01-09 : 19:53:05
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 - 2011-01-09 : 21:47:48

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

248 Posts

Posted - 2011-01-10 : 02:01:24
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
   

- Advertisement -