1

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

4

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.

| improve this answer | |
  • 1
    Note that if your secondary is not readable, it is possible to avoid reconfiguring the client applications. docs.microsoft.com/en-us/sql/database-engine/… So the client reconfiguration doesn't absolutely have to happen during the downtime window. Also you may need additional configuration when switching to an AG Listener if you need Kerberos. The instances must use the same service account, and the SPNs must be registered manually if the computer accounts don't have rights to. – David Browne - Microsoft May 26 at 23:01
  • 1
    @DavidBrowne-Microsoft: Thanks that will be a case for my other project in future but for current one yes we do not need readable secondary or Kerberos. However we do use gMSA account and believe that will not be an issue – BeginnerDBA May 26 at 23:20
  • @Handy : Thanks much for the detailed answer. One general question to your first point as enabling clustering. As my current windows server needs to be used, do you think adding 2 nodes to cluster , because currently these are not part of cluster, will bring any outage/break or downtime? even though we do have that window available but trying to understand if that window increases converting them to part of cluster – BeginnerDBA May 26 at 23:22
  • As noted here, enabling the failover clustering features and setting up the cluster shouldn't require an outage. Be very careful when adding cluster nodes to your new cluster to uncheck the option "Add eligible storage to cluster". This is ticked by default, it is not needed for a cluster supporting AGs and if left checked this will cause an outage and take the disks offline on your nodes. – HandyD May 27 at 4:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.