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.)
- 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.