#Microsoft SQL Server: Database #Replication (Mirroring) deprecated and replaced by #SQL Availability Groups

By | February 5, 2018

It looks like the Microsoft SQL Database Mirroring a feature I used a lot and for which posted a step by step tutorial that proved to be very popular was deprecated by Microsoft.

In a news article on Microsoft site the alternative is presented “Basic Availability Groups (Always On Availability Groups)”.

I will summarize in the following what is changed and what is important to know if you used mirroring until now:

  • Always On Basic Availability Groups provide a high availability solution for SQL Server 2016 and SQL Server 2017 Standard Edition.
    It is a replication solution targeted for Standard Edition the same as mirroring
  • Basic availability group supports a failover environment for a single database.
    This is one of the limitations, only one database instance can be configured with failover
  • It is created and managed much like traditional (advanced) Always On Availability Groups (SQL Server) with Enterprise Edition.
    So as in case of mirroring the same “configuration setup” is consistent between Standard edition and Enterprise Edition setup, thus making it easy to migrate to the more expensive licensing if it is the case.
  • Basic availability groups enable a primary database to maintain a single replica. This replica can use either synchronous-commit mode or asynchronous-commit mode.#
    This is in fact a huge bonus. Mirroring was allowed for Standard Edition only in synchronous-commit mode. The fact that we can have asynchronous-commit mode using Standard Edition license is a huge cost save. I had cases of clients that were forced to upgrade to Enterprise edition due to the bad connection between sites that made synchronous-commit mode unstable and slow.
  • The secondary replica remains inactive unless there is a need to failover. This failover reverses the primary and secondary role assignments, causing the secondary replica to become the primary active database.
    Same behavior as mirroring
  • Basic availability groups can operate in a hybrid environment that spans on-premises and Microsoft Azure.
    This is new, it means that we can host our replica in Azure cloud.
  • Now the following limitations are advertised as things that are missing from Basic Availability Groups compared to Advanced Availability Groups that comes with Enterprise edition

  • Limit of two replicas (primary and secondary).
    As expected
  • No read access on secondary replica.
    Same as in case of mirroring
  • No backups on secondary replica.
    Same as in case of mirroring
  • No integrity checks on secondary replicas.
  • No support for replicas hosted on servers running a version of SQL Server prior to SQL Server 2016 Community Technology Preview 3 (CTP3).
    An obvious compatibility issues here, expected.
  • Support for one availability database.
    This was already mentioned and is the same as in case of old mirroring.
  • Basic availability groups cannot be upgraded to advanced availability groups. The group must be dropped and re-added to a group that contains servers running only SQL Server 2016 Enterprise Edition.
    This may be a concern in case of migration. So even if the configurations look the same a migration to the Advance Availability Groups is not possible. I think this is the biggest limitation.
  • Basic availability groups are only supported for Standard Edition servers.
    Nothing new same as in case of mirroring
  • Basic availability groups can not be part of a distributed availability group.
    This is something expected for Standard Edition
  • There is another limitation but for administrators this time. There is no GUI setup at this point, the only alternative being the use of transact-sql to make the setup.

    There is still no tutorial on how to clearly and simple do this setup but when I will have the occasion to put this in a test environment I will definitely provide one.

    Contribute to this site maintenance !

    This is a self hosted site, on own hardware and Internet connection. The old, down to earth way 🙂. If you think that you found something useful here please contribute. Choose the form below (default 1 EUR) or donate using Bitcoin (default 0.0001 BTC) using the QR code. Thank you !

    €1.00

    Leave a Reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.