How To Apply Service Packs To SQL Servers Involved In Mirroring

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.

Share

3 thoughts on “How To Apply Service Packs To SQL Servers Involved In Mirroring

  1. hi,
    I have sql server 2008 R2 server with RTM with mirror configured in it… now i would like to install sp2 on Principle & Mirror servers.. is it possible to apply SP2 without failover to principle server…downtime is ok with me.

    Regards,
    Praveen

  2. Yes, you can do this. You’ll need to disable mirroring first for all the databases that you don’t want to failover. Apply the service pack to the principle server. This will cause downtime, but you said you are ok with that. Then apply the service pack to the mirror server. (This is important. The principle and mirror servers should be identical, so you’ll want to put the SP on both.) If you have a monitoring server, you might as well apply the service pack to that machine at this time as well. Then re-enable mirroring.

Leave a Reply

Your email address will not be published. Required fields are marked *

I am a real person and can prove it by doing this math problem: (required) Time limit is exhausted. Please reload CAPTCHA.