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.

Database mirroring rambling follow-up

In my last post containing a number of comments on database mirroring I left some points for follow-up.

First of all, when it comes to edition requirements it stays the same for mirroring in SQL Server 2012 as it was in previous versions. However, you have remember that for high performance (asynchronous) mirroring you have to use Enterprise Edition. Confirmed by official license documentation.

Second, you have to remember that if you have mirroring set up between different database engine versions the principal has to have earlier version than the mirror. The reason for this is backward incompatibility of database versions (don’t confuse it with compatibility model) which does not allow to use a database from newer server with an older one. If you establish such mirroring, you will not be able to do failover for the same reason – after failover in this scenario a principal is of newer database version and incompatible with a mirror. The mirroring will enter paused state and will not be resumable until mirror’s version matches the principal.

I noticed that in case of SQL Server 2012 CTP0 (build 11.0.1750) and RTM (11.0.2100) – the respective database version numbers are 705 and 706, soI could mirror from CTP0 to RTM, but then in order to have a failover possibility an upgrade was required for CTP0. I had in mind building a more comprehensive list of database versions, since all information I could come up with can be summarized in a following table:

SQL Server version

Internal database version

SQL Server 2012 RTM


SQL Server 2012 CTP0


SQL Server 2008 R2


SQL Server 2008


SQL Server 2005 SP2 + vardecimal enabled


SQL Server 2005


SQL Server 2000


SQL Server 7.0


 Third point was – I was wondering about FileTable in context of FILESTREAM incompatibility with mirroring. You can’t have both – it’s either mirroring or FILESTREAM. And after short research I found out that a FileTable is actually an extension to FILESTREAM which is designed to work with AlwaysOn. But basic assumptions remain the same and FILESTREAM is exclusive with mirroring.

Database mirroring pseudorandom ramblings

I liked database mirroring since it appeared in SQL Server 2005. In fact, it’s the only HA technique I feel comfortable with (apart from it’s obvious limitations). It’s neither as blunt as log shipping, as random as replication nor as complex as clustering. It either works or it doesn’t – and you know it straight away after you set it up – you don’t have to wait for snapshot to be generated, sent over the network and applied on a subscriber or for a backup to be copied and restored on your secondary.

The incentive for this post was one by Glenn Berry, which held one answer to my mirroring-related questions. The database mirroring is considered deprecated in SQL Server 2012; it’s going to be available in 2012 and the next version, but then it will be removed from SQL Server and replaced by AlwaysOn Availability Groups. Given the releases of new major versions of SQL Server my totally wild guess it would be like… 2018? Later? All in all, it’s not the major point of concern right now, but something worth remembering and planning when time comes.

What gives me second thoughts is the second part of deprecation note saying “If your edition of SQL Server does not support AlwaysOn Availability Groups, use log shipping”. What I understand is – combining it with new SQL Server licensing model – you can get replication and log shipping in Standard Edition, clustering and AlwaysOn in Enterprise Edition, but what about mirroring? I would like it to remain in Standard Edition, but to be 100% sure I asked on MSDN forums and I’m waiting for an answer. On the other hand, if it were to go to Enterprise Edition, I would expect dramatic costs increase due to per-physical-core licensing model, which would not be very welcome by most organizations.

When it comes to facts about database mirroring apart from standard source of knowledge (Books On-Line) I also recommend the following:

  1. The forementioned post and it’s sequel by Glenn Berry – a detailed introduction and complete checklist of points to be considered while implementing database mirroring.
  2. Pro SQL Server 2008 Mirroring – a book by Robert Davis and Ken Simmons, which apparently is not only about mirroring, but also about other high availability techniques as well.
  3. Blog posts by Robert Davis tagged with database mirroring – lots of additional information.
  4. An MCM video followed by a demo presented by Paul Randal – to give an insight how the mirroring works internally.
  5. Microsoft KB article 2001270 on things to consider while implementing mirroring, mentioning exact Performance Objects to watch out for in case of performance issues.

Now few words why I like mirroring in SQL Server:

  1. It works.
  2. It allows you to do automatic client redirection in case of failure (provided you have a high safety scenario with a witness) – see an example of implementation, works with SQL Native Client, ADO.NET and SQL Server JDBC driver.
  3. You can run it on Standard Edition and you don’t need extra hardware for that (unlike clustering).
  4. In high safety mode, you can have zero data loss (unless your mirroring is broken).
  5. You can have it working even if you are not in a domain, or you don’t have trust relationship between domains in which mirroring partners are – I have recently presented an example during local SQL Server user group meeting based on Books On-Line example available here. It’s completely opposite to simple scenario in one domain when it comes to implementation, but I recommend trying that one out.

For the other side of the coin – things to remember when implementing database mirroring:

  1. There was only one Redeemer! 🙂 you can have only one mirror server – this limitation is overcome in 2012’s AlwaysOn, where you can have 4 replicas, but it has it’s costs.
  2. Mirror is not easily accessible but you can use snapshots to query it.
  3. Server-level objects are not transferred with mirroring – you have to take care of logins, Database Mail profiles, SQL Server Agent jobs or Maintenance plans yourself. See Books On-Line article on jobs and logins.
  4. Mirroring requires full recovery model so you have to be absolutely sure about the transaction log of mirrored databases. Index maintenance, for example, can kill mirroring performance if you have 100GB of log records related to index reorganize sent to the mirror (and it will kill you twice – first the network, then the mirror server).
  5. In SQL Server 2008 and 2008 R2 FILESTREAM and mirroring don’t work together. You can’t have both in one database. To be checked – SQL Server 2012’s FileTables and mirroring.
  6. The failover is not instant – see Paul Randal’s post and Books On-Line. In short – it depends on the length of your REDO queue, so if you’re not monitoring it, then please do, just for the sake of not being surprised in the event of failover.
  7. The failures not detected automatically – there are four cases here (again, I’m quoting Paul Randal):
    • SQL Server unavailability – detected in a second
    • Machine unavailability – default is 10 seconds, you may increase it using ALTER DATABASE SET PARTNER TIMEOUT
    • Unavailable log drive – it takes 20 seconds for a warning, and next 20 seconds (total 40 seconds) for database to be taken offline
    • Page corruption – might not even be detected 😦
  8. It’s not the witness who performs the failover, but the mirror! The mirror and the witness agree that the principal cannot be contacted and the mirror switches to a principal. If you don’t believe, set three Profiler sessions on all three mirroring participants and watch out for ALTER DATABASE statements when failover occurs.

Wrapping it all up, database mirroring is a really useful high availability technique (especially when combined with other methods – be it clustering or replication). It is easy to implement, does not have extra hardware requirements. Though it’s marked as deprecated in SQL Server 2012, it is going to be available and useful for at least couple of years, but it has it’s downsides which should be carefully considered when planning.