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 articlehttp://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 |
|
deal11deal1
Starting Member
34 Posts |
Posted - 2011-01-08 : 17:32:37
|
any other insight? |
|
|
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_mirroringWhere 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. |
|
|
deal11deal1
Starting Member
34 Posts |
Posted - 2011-01-08 : 20:25:16
|
Thanks for help.. i will try it now. |
|
|
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_mirroringWhere mirroring_role = 1 ---1 = principal, 2 = mirror. in the above script how would i incorporate the disable mirrorring comand?? |
|
|
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_mirroringWhere 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 jobalter database <databasename> set partner resumeThanks in advance |
|
|
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_mirroringWhere mirroring_role = 1Declare @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. |
|
|
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.? |
|
|
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_mirroringWhere mirroring_role = 1Select @SQL = 'Alter Database ' + @ServerName + ' SET PARTNER RESUME'exec sp_executeSQL @SQL Poor planning on your part does not constitute an emergency on my part. |
|
|
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_mirroringIF mirroring_role = 1Select @SQL = 'Alter Database ' + @ServerName + ' SET PARTNER RESUME'ElseSelect @SQL = 'Alter Database ' + @ServerName + ' SET PARTNER SUSPEND'exec sp_executeSQL @SQLAny thoughts?Thanks in advance |
|
|
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_mirroringIF mirroring_role = 1Select @SQL = 'Alter Database ' + @ServerName + ' SET PARTNER RESUME'ElseSelect @SQL = 'Alter Database ' + @ServerName + ' SET PARTNER SUSPEND'exec sp_executeSQL @SQLwhen 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 thereAny help. |
|
|
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_roleFROM sys.database_mirroring IF @mirroring_role = 1Select @SQL = 'Alter Database ' + @ServerName + ' SET PARTNER RESUME'ElseSelect @SQL = 'Alter Database ' + @ServerName + ' SET PARTNER SUSPEND'exec sp_executeSQL @SQL |
|
|
|