January 7, 2011 at 9:20 am
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.
what I need to do is to create a job or a ssis package that does this dynamically meaning scan the sys database and if mirroring is enable (disable it ) or if its disable enable it.
right now the jobs look like this
enable -> alter database <databasename> set partner resume
alter database <databasename> set partner resume
and
disable -> alter database DATABASENAME set partner suspend
alter database DATABASENAME set partner suspend
Any help is helpful.
Thanks in advance.
January 7, 2011 at 9:33 am
Would this help?
http://msdn.microsoft.com/en-us/library/ms178655.aspx
You could scan this for the status of a mirrored pair.
January 7, 2011 at 9:40 am
Thanks for the reply... yes it does help so if the all the rows are null in the table except database_id that means mirror is disable so the job would enable it, and off course the other way around.
But my problem is since I am a junior DBA I don't know how to implement it.?
Thanks in advance.
January 7, 2011 at 9:58 am
Also , if i can just run these commands dynamically , what I mean by is .... since we have 20 database on the server and to run a job for each database ( and we have 5 server with about the same amount of database for each)
if I get the name of the databases on a excel sheet.. the package get the name of the databases from the list and disables them.
i can do two different jobs ( one for enable and one for the disable).
January 7, 2011 at 10:01 am
Sorry, query the table in an SSIS task. Based on the value, then you would execute the proper code.
set @currentmirror = select status from dbmirroring table
if @currentmirror is null
sp_start_job @EnableJob
else
sp_start_job @disableJob
That pseudocode make sense?
January 7, 2011 at 10:45 am
Thanks for your reply.
Dont want to sound too much of a rookie but how would I proceed after that ...
lets say based on your code the value is not null ( mirroring) now how would I link the disable job after the above mentioned code. I see ur r calling that job in the script.. can you please explain steps to me, it will honestly save my job.
also how would I get the name of the database from a excel sheet.
Many thanks.
January 7, 2011 at 11:06 am
I figure out the way how to load data from the excel file ( in my case that will be the name of the databases) in to ssis package.
Now how would I make my job put that database name in the script instead of my writing in there?
Thanks in Advance.
January 7, 2011 at 1:02 pm
qur7 (1/7/2011)
Thanks for your reply.Dont want to sound too much of a rookie but how would I proceed after that ...
lets say based on your code the value is not null ( mirroring) now how would I link the disable job after the above mentioned code. I see ur r calling that job in the script.. can you please explain steps to me, it will honestly save my job.
also how would I get the name of the database from a excel sheet.
Many thanks.
In SSIS, you can link tasks together. Add two tasks, one before, and one after, all your controls.
January 7, 2011 at 1:03 pm
qur7 (1/7/2011)
I figure out the way how to load data from the excel file ( in my case that will be the name of the databases) in to ssis package.Now how would I make my job put that database name in the script instead of my writing in there?
Thanks in Advance.
This I can't give you a great answer for as I'm not an SSIS person. I would start a thread in the SSIS forum to ask this (http://www.sqlservercentral.com/Forums/Forum364-1.aspx)
However if this is really saving your job, it isn't. You should be clear this is beyond you a bit. If you cannot get the answer from an online forum, and there is an issue you will more likely be fired than if you admit you are struggling, but looking for help.
January 7, 2011 at 1:04 pm
Thanks for the Reply....
I will give it a try ... keep u with the updates too.
thanks for the help.
January 7, 2011 at 2:02 pm
Hi,
my boss just on the fly changed the requirements a little bit ;-). so here I am again.
the SSIS package doesn't need a input from a file.
step 1: the ssis package first read the name of the database from the systable,
step 2: than see if the database is config as mirror.... if it is than disable it.
or.
if the database is disabled than enable it.
Now I believe the 1 and and 2 step are the same as what we have discussed before. how would i read the name of the databases from system and incorporate it with the script.
January 8, 2011 at 9:33 am
Sounds like you know what to do.
If you get stuck in SSIS, post a note in that forum, and someone can likely help you.
January 8, 2011 at 10:24 am
Seems a familiar request : http://www.sqlservercentral.com/Forums/Topic1044104-148-1.aspx
Someone ( your boss ) is kind of focused on really wanting SSIS 😉
If you build a package that does the flip/flop (switch) operation for the mirroring server pair, it may seem fairly easy to provide the mirror pair info in an input parameters file.
However, you might as well deploy a job on each of the sqlserver instances of a mirrored pair that does that functionality for you.
With dbmirroring you have to do your admin tasks double anyways.
This would just be another setup step in your mirror install checklist.
If the intention is to have it more scriptable, you may as well do it in SQLCMD or even in powershell using SQLSMO.
edited: added following
Keep in mind your most valuable info is in
Select db_name(database_id) as dbName
, *
from sys.database_mirroring
Where mirroring_guid is not null -- show only mirrored databases
order by dbName;
Especially pay attention to these columns to figure out partnership and avoid human errors (by providing incorrect info).
mirroring_partner_name
mirroring_partner_instance
mirroring_witness_name
btw Did you know we have a reference job script at SSC ? :w00t:
Mirroring Job Switcher :http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31919/
Certainly worth the test.
As a powershell ref you could start with : http://blogs.msdn.com/b/sethus/archive/2008/07/07/configure-database-mirroring-using-sql-2008-power-shell.aspx
I don't think SSIS(2005) has objects in its toolsbox to handle mirroring specifically. So you would need the script components.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 11, 2011 at 5:13 am
Just so that im clear, you now have the required code to check for an enabled or disabled mirror, right?
As far as database names and server names are concerned, I would either manually populate (as presumably the database(s) and server name(s) dont chnage on a daily basis) a table is a small database (call it 'Mirror_Control').
You then have another table for mirror status, this can be updated for each server, for each database. Basically youd have one table called 'Databases' which would contain Database names and server names (or you could seperate out to two tables) and a table called 'Mirror State' (which will show the mirror state for each mirrored database on each server (once populated)).
From the server where the 'Mirror_Control' database is, create a linked server to all the servers that you wish to alter mirror states on.
Then in code set a variable string to execute a remote query to a linked server to get the mirror state and use that to poulate the 'Mirror State' table. Once this is done all you need to do is build your exec string to either enable or disable the mirrors. This should be done inside a while loop or cursor.
So it would look like (the below is NOT code):
For each row in 'Mirror State'
set string = {linked server} {code to enable/disable mirror for current 'Mirror State' row}
execute string
I have skiped a few things like having row id's for your tables etc but thats the logic. There is the possibility that you wont be able to remotely execute the code on the linked server, so you may need the below to allow remote execution (store the sp on your licked servers and pass a string into its variable) but the basic concept of this will work.
I would do it all and test in t-sql, then convert to sp's to store in your 'Mirror_Control' database and then use ssis to call the sp's.
Hope this helps. 🙂
USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_execute_remote_sql] Script Date: 01/11/2011 12:13:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_execute_remote_sql]
@sql_string VARCHAR(500)
AS
SET FMTONLY OFF
SET NOCOUNT ON
EXEC(@sql_string)
RETURN 0
GO
On a seperate not, this is pretty advanced stuff and you may wish to heed Steve's advice; tell your boss that you are struggling but you have the plans blah blah and need help.
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply