Feb 132018
 

SQL Server Mirroring has had a rough time the past few years. It was deprecated in SQL Server 2016, and now it’s more or less being replaced by Basic Availability Groups, (which I love to refer to as “BAGS”). Database Mirroring hasn’t gone the way of the Dodo yet though. Perhaps it won’t be quite so popular in new deployments anymore, but there’s plenty of existing ones out there. For this reason I don’t expect Microsoft to remove the feature for at least a few more years.

A few weeks ago I had a reminder of one of the finer points of the requirements for mirroring: mirrored servers need to be running not only the same version of SQL Server, but the same edition as well.

I hadn’t thought of this in a while, but it makes sense. Asynchronous Database Mirroring (also known as “High-Performance Mode”) is only available in Enterprise Edition, while Standard Edition only supports “High Safety Mode”, which is synchronous. If the primary server in a mirroring topology was Enterprise Edition, but the mirror server was Standard Edition, how would that work? What if the Enterprise Edition server had features enabled that don’t exist in Standard Edition? One way might be to disable features where conflicts occur, but that would probably lead to more issues as well as confusion. The simple solution is “just don’t let that happen” and that is what Microsoft opted for here. If you try to configure mirroring between instances running different editions of SQL Server, you won’t get very far before you see this:

Microsoft makes no secret of this in their documentation. The second listed prerequisite is “The two partners, that is the principal server and mirror server, must be running the same edition of SQL Server. The witness, if any, can run on any edition of SQL Server that supports database mirroring.”

So to sum things up, you may never have to touch a mirrored database again, but if you do, remember that editions matter!

UPDATE: While editions must match, it should be noted that versions of SQL Server need not match, and this exception can be particularly useful for using mirroring to perform upgrades, as mentioned in this blog post by Glenn Berry. However once you failover a mirroring session to a newer version of SQL server, failback to the older version is no longer possible. This is perhaps why Microsoft states in their documentation that versions must match – they will need to for any kind of long-term arrangement.

  2 Responses to “Mirroring? Make Sure Editions Match!”

  1. You are absolutely correct that you cannot establish a new mirroring partnership between different Editions of SQL Server. It is actually possible to do an Edition Upgrade of an existing mirroring partnership by upgrading the mirror instance first, then failing over, and upgrading the original principal instance, as I discuss here: https://www.sqlskills.com/blogs/glenn/how-to-perform-a-rolling-edition-upgrade-while-using-database-mirroring/

    • You are absolutely right, fact I used to do a presentation on that very topic. I was trying to stay more true to the MS documentation, because their very first prerequisite is:

      “For a mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server.”

      Perhaps they don’t consider the upgrade case to be a true “mirroring session?” I think I’ll update the post and make note of this. Thanks!

Leave a Reply to Bob Pusateri Cancel reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)