Using SQL Agent Job Categories to Automate SQL Agent Job Enabling with Database Mirroring

Microsoft’s Kevin Cox (from SQLCAT) and I co-authored a blog post titled Using SQL Agent Job Categories to Automate SQL Agent Job Enabling with Database Mirroring that describes a simple technique that I use at NewsGator to automate the process of enabling and disabling database level SQL Agent jobs based on the mirroring status of individual user databases.

We have been using SQL Server database mirroring at NewsGator since mid-2006. Until about December 2009, we normally ran asynchronous mirroring (since we did not have enough I/O capacity on the mirror side to run synchronous mirroring adequately). When it came time to do server maintenance, we would patch the mirror “instance” first, and then switch to synchronous mirroring and failover all of the user databases. Then we would switch back to asynchronous mirroring while we patched the original principal “instance”. After that was done, we would switch back to synchronous mirroring and failover back to the regular principal “instance”, and finally switch back to asynchronous mirroring for normal operations.

All of this switching around made a rolling maintenance operation relatively complicated. Having to manually enable and disable SQL Agent jobs on both sides of the mirror during the maintenance operation made it even more complicated. I would use checklists to cover each step involved that often had over 100 steps.

Now, we use synchronous mirroring with a witness, so we have automatic failover for all of our mirrored databases. As long as your applications have a failover partner in their connection strings, you can failover databases to your heart’s content without too much impact (as long as the applications can deal with reconnecting and handling in-flight transactions). After a brief outage (usually 5-20 seconds in my environment), your applications come back up and work normally after a failover, with no DBA intervention required. The problem of handling the SQL Agent jobs remained though, so I decided to come up with a very easy way to handle it, as described in blog post on SQLCAT.

This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s