I recently had to apply SQL Server 2008 SP2 to some of our SQL Servers, three of which are involved in a database mirroring configuration (as the principle, mirror, and monitor servers). Or so I thought. When I looked a bit deeper, I realized I mis-read my spreadsheet and the servers using mirroring were running SQL 2008 R2, and thus the service pack did not apply to them.

But be that as it may, I had already sent out a tweet to #sqlhelp asking for the correct order to apply the service pack to the principle, the mirror, and the monitoring servers. I didn’t find any good info on this with Google, so I thought I’d document this here, even though I didn’t actually use this information. (And also because I trust the source of this info – he wrote the book on SQL mirroring.)

According to Robert L. Davies (blog | Twitter), this is how to apply SQL service packs to a mirroring configuration:

• Remove the witness server from mirroring
• Apply the service pack to the mirror server
• Fail the mirrored databases over to the mirror server
• Apply the service pack to the former principle server (now a mirror)
• Fail the mirrored databases back to the former principle server (optional)
• Apply the service pack to the witness server
• Add the witness server back to the mirroring configuration

Robert also told me that the witness server can have the service pack applied at any time – the key is to remove it while upgrading the other servers.

If you don’t currently use it, I highly recommend using Twitter for any important / time-critical SQL questions you may have that you can’t find the answers to anywhere. Use the #sqlhelp hash tag. Brent Ozar wrote a great post about how to use this fantastic community service. You usually can get an answer from someone in minutes.