Sql-server – Can you combine database mirroring with AlwaysOn Availability Groups

availability-groupsmirroringsql-server-2012

I assume the answer is "no" but the SQL Server Management Studio 2012 GUI implies that you might be able to. In other words, if you right-click on a database that is an AlwaysOn Availability Group primary replica, you are given the option to mirror it (Tasks –> Mirror…).

Tasks Mirror

Is that just a SSMS GUI 'feature'..? I've experimented by trying to mirror the database to a different instance (i.e. an instance that is not an availability replica) and the GUI lets me fill in all the necessary details and takes me right up to the point where you choose your mirroring type (High Performance or High Safety) and click Start Mirroring. An error message appears saying that the database is already enabled for mirroring (presumably because it is in an AlwaysOn availability group).

enter image description here

Probably I am the only person daft enough to attempt this but I'd welcome a confirmation that indeed it is impossible to seperately mirror an AlwaysOn availability group database. (I can't find any confirmation either way when Googling).

By the way, (just by way of background), in case you wonder I don't just set up another AlwaysOn Availability Group secondary replica (instead of attempt database mirroring), the issue is that my intended mirror instance is another instance on the same Windows Server Failover Cluster (as my AlwaysOn Availability Groups primary/secondary instance). It transpires that you can’t have more than one availability replica (per availability group) on a single WSFC cluster server node. This is the precise error message:

SQL Server instance 2 is installed on the same computer as server instance 1, which is already selected for this availability group. Each Windows Server Failover Clustering (WSFC) node can host only one availability replica per availablity group. Specify a server instance on a different WSFC node.

Best Answer

You are correct, it is impossible to mirror a database that is part of an Always On Availability group. You can consider logshipping.

Ref:

https://blogs.msdn.microsoft.com/sqlalwayson/2010/12/08/alwayson-availability-group-and-database-mirroring/

"Does AlwaysOn Availability Group replace Database Mirroring going forward?” The short answer is we recommend that you migrate from the mirroring configuration or even mirroring and log shipping configuration to using Availability Group. Database Mirroring will still be available in the Denali release but will be phased out over subsequent releases. Log Shipping will continue to be available in future releases.

For the error you are getting read this document especially section: Windows System Requirements and Recommendations.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/prereqs-restrictions-recommendations-always-on-availability#PrerequisitesForAGs

Related Question