SQL Server – How to Migrate from Database Mirroring to Availability Groups

availability-groupshigh-availabilitysql serversql-server-2016sql-server-2017

Need some practical/general guidance on moving from database mirroring to AOAG

Current setup – 2 standalone servers with database mirroring configured as async.

  1. Server A: Windows 2016 running SQL Server 2017, no cluster, simple standalone server as Primary
  2. Server B: Windows 2016 running SQL Server 2017, no cluster, simple standalone server as Mirror
  3. File share server will be provided in different DC

Distance between the servers is about 150 miles.

We need to use the above servers only and convert database mirroring to AG's with automatic failover option to provide HA as a bonus because with the above setup it's mostly a DR solution.

  1. Clusterless AG came to my find but it seems HA wont be available as automatic failover might not work. Am I missing something here or is it possible?

  2. We can have an outage up to 8 hours to convert db mirroring to AG if needed.

How should I plan or if any resources available to do this? I tried searching for lot of articles but could not find any, please suggest

Best Answer

As a general rule, this migration should be simple. AGs use the database mirroring framework and endpoints for transmitting log blocks, so the transition is quite straightforward.

One thing to note is you currently run your mirrors async but you've stated you want to run your AG sync to provide HA. This shouldn't be a problem, but you need to check your latency between the servers to ensure you're not creating a bottleneck that is going to hamper write performance.

Synchronous commit AGs require all transactions to be hardened to the log on the secondary prior to committing them on the primary, so if your latency across the 150 miles between servers is too high, this can cause substantial performance issues.

Clusterless AG came to my find but it seems HA wont be available as automatic failover might not work. Am I missing something here or is it possible?

No, you are correct. Clusterless AGs, or read-scale availability groups, are not an HA option. There is no automatic failover in a read-scale availability group. See this link here which states:

This is not a high-availability setup. There is no infrastructure to monitor and coordinate failure detection and automatic failover. Without a cluster, SQL Server can't provide the low recovery time objective (RTO) that an automated high-availability solution provides. If you need high-availability capabilities, use a cluster manager (Windows Server Failover Cluster on Windows or Pacemaker on Linux).

The read-scale availability group can provide disaster recovery capability. When the read-only replicas are in synchronous-commit mode, they provide a recovery point objective (RPO) of zero.

To your second point-

We can have an outage up to 8 hours to convert db mirroring to AG if needed.

You could do the following:

  1. Enable clustering on Server A and Server B.
  2. Configure the Availability Group with both SQL instances on Server A and Server B as replicas
  3. Stop log backups on both servers
  4. Remove mirroring from DBs on Server A/B
  5. Add databases to AG on Server A
  6. Join databases to AG on Server B - This should be fairly seamless as the DBs on Server B should in recovery since mirroring was disabled. As long as no log backups have occurred, you should be able to sync the DBs in the AG without backup/restore or seeding.
  7. Update App connections to use the listener name.

Some of these steps require the servers or SQL Server instances to be restarted, so it will be multiple outages but each is shorter than your allowed 8 hours.

This is a very simple overview, and the process needs to be tested and any problems identified and resolved prior to running through this in production. If you have a non-production environment, start there and script every step so you can replay the actions in production accurately.

If you don't have a non-production environment, ask your sysadmins to clone your servers in an isolated network where you can practise the change. If you're running physical servers, then testing without a non-production environment is going to be difficult, but you could build a similar environment in Azure or AWS to dry-run the process.

Here is a useful link on configuring clusters for AGs. Its a bit older but the information is still valid. If you're not confident with this process, especially if you do not have a non-production environment available, it may be better to engage a third-party contractor/consultant/expert who can plan and execute this for you.

Related Question