Database mirroring rolling upgrade

I was testing database mirroring rolling upgrade in my lab when upgrading from SQL Server 2012 RC0 to RTM shortly after RTM was available. One of the side results was proven incompatibility of internal database versions between RC0 and RTM of SQL Server 2012 (705 and 706, respectively). In this post I would like to focus on the main activity that was performed.

Imagine you have database mirroring with automatic failover implemented in your environment and you want to perform an upgrade of server instance while minimizing possible downtime. It does not seem quite challenging if you follow the right procedure:

  1. Disable witness.
  2. Perform maintenance on the mirror.
  3. Fail over.
  4. Since the principal is now mirror, perform maintenance on it.
  5. Fail over again.
  6. Enable witness.

If you look at it closely, you’ll notice close resemblance to failover cluster rolling upgrade, when you upgrade passive nodes one by one. In this case, steps 1 and 6 are there to ensure that no unexpected failover occurs that would disrupt the progress of upgrade.

Keep in mind that as always with failover in database mirroring it’s quick but there always is a downtime, which length is determined by several factors:

  • length of REDO queue on the mirror;
  • processing power of the mirror server and the remaining workload it has to deal with;
  • SQL Server edition on the mirror.

Whereas first two points are quite straightforward – failover requires roll-forward to complete and it’s when REDO queue is processed – last one is connected to them indirectly becuase roll-forward process uses one thread in following situations:

  • Standard Edition is installed or
  • Enterprise Edition is installed and the instance has up to 4 processor cores available

If you have Enterprise Edition installed and more than 4 cores are available for SQL Server, roll-forward uses one thread for every 4 cores the server utilizes.

In addition, you should remember to protect your data, which means not only doing standard your backup routine prior to maintenance, but switching from high-performance mirroring to high-safety mode. This will help you ensure that all transactions on the principal have been delivered to the mirror minimizing the risk of data loss. You should also check your database(s) for consistency before and after failovers.

This constitutes a detailed action plan like this (S1 is the principal, S2 is the mirror):

  1. Run DBCC CHECKDB on all databases on S1.
  2. Perform full backup of all databases on S1.
  3. If running mirroring in high-performance mode, switch to high-safety.
  4. Remove witness from mirroring session.
  5. Perform upgrade on S2.
  6. Fail over all databases to S2.
  7. Run DBCC CHECKDB on all databases on S2.
  8. Perform upgrade on S1.
  9. Fail over all database to S1.
  10. Run DBCC CHECKDB on all database on S1.
  11. Perform full backup of all databases on S1.
  12. If mirroring was initally set to high- performance mode, switch mirroring modes.
  13. Upgrade witness (actually you can do it any time).
  14. Add witness to mirroring session.

I have done upgrade from 2012 RC0 to RTM this way and it took me about 60 minutes on my lab PC at home. The application using those database servers is System Center Configuration Manager 2012 RC0 and I had no perceivable downtime during upgrade.

The thing I have to verify is – in case of upgrade from SQL Server 2008 R2 to 2012 is it possible to have a witness on 2008 R2 with partners on 2012? If anyone knows, please share it with me.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s