This is a screenshot of Task Manager for a database instance that is part of a synchronous database mirroring partnership in SQL Server 2008 R2. We are using synchronous database mirroring, with a Witness instance, so we have automatic failover. There are a number of mirrored, user databases that normally run as the Principal copy of the database on this instance.

What actually happened not too long before I took this screenshot, was that I kicked off the installation program for SQL Server 2008 R2 Cumulative Update 3 (CU3) on this instance. As designed, the CU3 setup program stopped the SQL Server Service on this instance, which caused the steep drop-off in physical memory usage shown in Task Manager. When this happened, we had an automatic failover of all of the user databases on that instance over to the mirror instance, which took about 10 seconds to complete. Since we have a failover partner in all of our connection strings and connection retry logic in the applications, the applications all came back up automatically.  I also have a SQL Agent job that runs on both the Principal and Mirror instances that automatically handles disabling and enabling the database level SQL Agent jobs (such as transaction log backups) on both sides of the mirror. I wrote about the details of how that works here.

After the SQL Server Cumulative Update finished installing, I then installed the pending Windows Updates, and allowed the server to reboot. In order to reduce the number of reboots, I always install the SQL Server update first. This is because the SQL Server update installation program does a check for any pending restarts before it begins.

Here is the complete rolling update procedure for a SQL Server synchronous database mirroring partnership:

1. Apply the SQL Server Cumulative Update to the Witness instance

2. Apply pending Windows Updates to the Witness instance, and restart the server if necessary

3. After the Witness instance is back up, apply the SQL Server Cumulative Update to the Mirror instance

4. Apply pending Windows Updates to the Mirror instance, and restart the server if necessary

5. Wait for the database mirror to become synchronized

6. Apply the SQL Server Cumulative Update to the Principal instance. This will trigger an automatic failover.

7. Apply pending Windows Updates to the Principal instance, and restart the server if necessary

8. Wait for the database mirror to become synchronized

9. Manually failover the user databases back to the regular Principal instance

If you follow this procedure, you will have two 10-15 second outages (depending on your workload). You can even do Steps 1 through 5 during normal business hours, since they will be completely transparent to the application users. This procedure is extremely easily, and it will give you the least amount of downtime possible to apply a SQL Server Service Pack, Cumulative Update, Windows Update, or any other server maintenance that requires that SQL Server be restarted.