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.