SQL "Jobs" Mirroiring - SQL Server Forums

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

SQL "Jobs" Mirroiring

Author

Topic

GMacfarlane
Starting Member

1 Post

Posted - 2012-12-18 : 07:28:53

Good day to all,

Being relatively new to MS SQL - I would like to apologize first of all if my question is not relevant

Scenario

2 SQL Servers (SQL 2008 R2) are set-up (no witness) and certain mission critical databases are set-up for mirroring

I would like to be able to do the following:

When a "Job" is created on SRV-1 it will automatically be created or "mirrored" onto SRV-2. There are times where it's forgotten to create the job on the mirror server

Is there a "best practice" or a guide on how to do this?

Any help would be appreciated

Gordon Macfarlane
System Administrator
Faxbox Ltd
The Digital Depot,
Thomas Street
Dublin 8,
Dublin, Ireland
Site: www.rte.fr

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-12-18 : 18:07:15

I don't know of a way to put a trigger type event into place that would automate the processing that you want. What we do is script the jobs out on one server and then run the scripts on other "similar" servers. The best that I can think of to automate this would be to create a job that run periodically on your "master" server to see if any new jobs exist and then programatically script it out and run it on your "slave" server(s).
The msdb..sysjobs table would have the information needed to determine if a job had been created or modified.

=================================================
Hear the sledges with the bells - silver bells!
What a world of merriment their melody foretells!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-12-18 : 18:23:26

I follow the same process Bustaz describes. One thing I add to my job steps are tests to check if the database is mirrored or otherwise offline before proceeding. If a database is mirrored is it not accessible, so things like backups and DBCC checks will fail on the mirror server. That way the job will always run on each server, but won't do anything against mirrored databases.

It's better to keep the job scripts in source control rather than scripting them on the fly from a server and then deploying them. That way you know you using the correct version and it's consistent on all servers. It's VERY EASY to generate a script from prod and run it on the wrong server, or tweak something in it, that FUBARs it on another server (yep, I just did this yesterday :)

You might also was to look at master servers http://msdn.microsoft.com/en-us/library/ms180992.aspx or central management servers: http://msdn.microsoft.com/en-us/library/bb895144.aspx

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-12-18 : 18:52:10

In Re: "It's better to keep the job scripts in source control rather than scripting them on the fly"
Amen!

=================================================
Hear the sledges with the bells - silver bells!
What a world of merriment their melody foretells!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources