• Adam Thurgar

Synching SQL Agent jobs


I was asked the other day how to make sure that all the SQL Server Agent jobs on a production server are the same on the DR server. This could apply to a standalone DR server or servers in an AG. I had a few suggestions. But first I needed to create two standard jobs. The two jobs were jobs to enable and disable the SQL Server Agent jobs as required, especially in an AG. The issue here is that you don't want jobs running on both servers, so as you switch from primary to secondary you need to ensure that the jobs are enabled on the primary server. I found the following link to scripts that can do this for you.

https://clinthuijbers.wordpress.com/2012/12/06/sql-agent-disable-all-jobs-script/

Once you have these jobs created and tested, then you need to make sure that your jobs are synch'd between you servers. The easiest way is to backup and restore msdb. To restore msdb as a database you need to stop the SQL Server Agent Service and then just restore it like a normal database and then start the service again. The good things about this is that all your operators and alerts etc are also synch'd.

Now if you need to tailor some jobs this may not be the best solution.

Another way would be to backup msdb database on the secondary server and save this backup. Then delete all the jobs on the secondary server. Script out all jobs (drop and create) on the primary server, save the script. Copy and paste the script on the secondary server query window - make any modifications you require (e.g. file paths etc), save the modified script then run it.

Once you have all the jobs on your secondary server, remember to disable/enable the jobs as required.


4 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t