Never ever set up a Database Mirroring session across different SQL Server Editions (SKU)

Never ever set up a Database Mirroring session across different SQL Server Editions (SKU)

  • Comments 3

Even though it might work, never attempt to do that. As the documentation clearly states: it is unsupported. In topic Prerequisites, Restrictions, and Recommendations for Database Mirroring, Books Online shows a Prerequisites section where it mentions the following:

Prerequisites

Before you can set up database mirroring, you must perform the following:

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

  • Make sure that the two partners, that is the principal server and mirror server, are running the same edition of Microsoft SQL Server. Database mirroring partners are supported only by SQL Server 2005 Standard and later versions and by SQL Server 2005 Enterprise Edition and later versions. Asynchronous database mirroring (high-performance mode) is supported only by SQL Server 2005 Enterprise Edition and later versions.

If you use the UI to try to set up a Database Mirroring session where the editions of the two partner servers (principal & mirror) don’t match, it won’t let you do that. One of the two following warnings would be shown and that would block the wizard until the requirement is met. The first one is shown when the Principal is Standard and the Mirror is Enterprise/Developer/Evaluation, and the second is shown when the opposite happens:

This mirroring configuration is not supported. Because the principal server instance, {0}, is Standard Edition, the mirror server instance must also be Standard Edition.

This mirroring configuration is not supported. Because the principal server instance, {0}, is not Standard Edition, the mirror server instance cannot be Standard Edition.

Still, nothing prevents you from setting up a Database Mirroring session using TSQL. None of the two partners will complain, the session will be started and would work as if nothing. Then, why isn’t such configuration supported?

Imagine that while your Principal copy is being maintained by the instance which runs on Enterprise (and the Mirror partner runs on Standard), you enable Change Data Capture, Transparent Data Encryption, Page or Row compression, or create a partitioning function, to name a few. Any problem with that? Not for now, but… what if you failover the principal to its mirror partner? Then you are in trouble. At that point, the mirror (which is running Standard edition) will try to startup the database to recover it and, guess what, the engine will realize that the database contains functionality only supported by the Enterprise edition and will not be able to open it up.

Unfortunately, nothing has been implemented since the release of Database Mirroring that would enforce this requirements from the engine itself. Still in SQL11 this glitch is present. Fortunately, AlwaysOn has indeed learnt from it and it is solved in there.

Leave a Comment
  • Please add 5 and 5 and type the answer here:
  • Post
  • Hmmm, never is a strong word.  If I'm upgrading from SQL Server 2008 Standard Edition to a new box with SQL Server 2008R2 Enterprise Edition, and I want to use mirroring to reduce downtime during the upgrade, what's the problem?  I'm actually angry that the UI doesn't allow me to do it.

  • Hi Brent,

    I hadn't thought of such scenario before, but probably the Program Manager for Database Mirroring in the Storage Engine team did. That would explain why this is not enforced from the engine itself, only from the UI. For those who would require configuring DBM for upgrade scenarios like the one you propose, they would have to use the TSQL-based approach.

    Because DBM has been deprecated, with the release of AlwaysOn, I won't even file a DCR against this issue to change the UI so that you could somehow accept the "risk" associated with having a DBM configured with mixed editions, for the UI to let you continue.

    Thanks,

    Nacho

    PS: And thanks for reminding me that I should never ever use the word never so easily. :-)

  • Very detailed and clear explanations, which I also encountered the same situations, thanks for sharing this information

Page 1 of 1 (3 items)