Before we dive in, this is a longer post but if you’re staring down a Windows Server 2016 end of support deadline with AGs in your environment, it’s worth every minute. I hope this post can help answer some questions that pop up when choosing a migration approach for something like this.

Setting The Scene

At the time of writing this, I am in the process of migrating over 20 production (A total of 60 including their development and testing counterparts) SQL Server 2019 Always On Availability Group environments that are running on Windows Server 2016 to SQL Server 2022 (AG as well) running on Windows Server 2025. All of this is on-prem. There is more of an urgent rush around this due to Windows Server 2016 going out of support on Jan 12, 2027. As of this moment that gives us roughly 6-ish months to get the project complete. As I’m sure you can relate, migrating this many critical servers that all contribute towards business revenue dictate a lot of coordination and planning to ensure that migrations are efficient, safe, and repeatable. Not to mention, when you throw CDC and Replication in the mix, a whole other element of fun gets thrown in to your migration runbook.

Choosing A Migration Approach For Production

With all of these production servers being business critical in nature, downtime needing to be kept to a minimum, and with all of these being in an AG (2 replica AG for all of these), choosing Distributed Availability Groups (DAGs) was the way to go that made the most sense. There were several reasons that influenced this decision:

1. Large jump in operating system versions
   • With the jump being Windows Server 2016 to Windows Server 2025, simply adding a new node to the cluster then the AG and then failing over to that newer version replica was not a realistic option. Microsoft only allows one OS version jump at a time in a mixed version cluster, so going 2016 to 2025 that way means running three full rolling upgrades per cluster. No thanks.
2. Maintaining high availability after cutover
   • With many of these databases being multi-terabyte in size, it was an attractive option to be able to just fail over to a fully configured AG and the databases already be synchronized.
3. Simplicity of initial synchronization
   • You could argue that we could have configured log shipping from source to the two destination AG replicas, but with the use of 3rd party backups, using a DAG allowed us to temporarily switch to native to get the databases synced and then put our 3rd party backup tool back in place post DAG setup and synchronization. Third party log backups break the restore chain you need during the manual seeding window, so backups get switched to native until the databases are joined to the forwarder AG, then the third party tool goes back on and from that point the AG log stream keeps everything synced regardless of who takes backups. Not to mention, with log shipping, you have to be mindful of out-of-band log backups while you have log shipping going. If something messes up your backup chain, recovery can be a pain to continue synchronization. Once the DAG is set up and the databases are synced, it doesn’t matter what is taking backups. Databases will keep syncing between AGs.
4. Fast cutover
   • Continuing to play devil’s advocate, log shipping can have quick cutovers for a migration as well, but there’s more moving parts to that. You have to set the source databases to read only, take a final log backup, apply it to the primary database on the new side’s primary replica, bring that one online, apply that same final log backup to the new side’s secondary replica(s) but leave that one in no recovery, then join to the AG (amongst other touchpoints post failover). With a DAG, it’s just demoting the global primary AG to secondary, failing over the DAG, and then hit the post failover touchpoints (I’ll get into this in more detail later). I recognize there are tradeoffs to both migration methods, and anything can be argued for one or the other, but this is what made logistical sense for this setup.
5. Replication persistence
   • You can get this done if you decide to go with log shipping as well as most of the touchpoints related to replication are in the prep work (it’s mostly metadata focused), but replication can be prepped in a way so that a re-snap is not necessary and replication will just work post failover. This is extremely useful for transactional replication topologies that are living in your AG.
6. Change Data Capture (CDC)
   • All of this lives within the user database and will be handled with the synchronization and failover of the DAG. CDC jobs still need to be created on the new replicas and synced properly between replicas in the AG, but can be done and prepped ahead of time with scripting.

What Is A Distributed Availability Group?

There are tons of relevant blog posts out there as well as documentation and tutorials that explain what DAGs are as well as instructions how to set them up and their use cases. So, with that in mind, I’m not going to go into a technical tutorial or deep dive from that aspect and will leave it to you to do that research. However, at a high level, Distributed AGs allow you to extend your source Availability Group by forwarding transactions from one AG to another AG. From a migration standpoint, I think DAGs are an ingenious technology. They allow you to fail over and upgrade to a new version of SQL and operating system and immediately have high availability ready to roll. The DAG itself is defined purely in SQL Server and spans two independent clusters. There’s no cluster resource for the DAG like there is for a normal AG.

Misconceptions And Unknowns

There are a lot of misconceptions and unknowns around DAGs that people (and AI) get wrong, and the unknowns that you may be asking yourself are the most important questions that need to be answered. So, it’s my goal here to try and list out and flag as many general questions and answers that you (and I) have that I can answer from experience, so you’re not relying on the “AI gave me another wrong answer” during your set up and cutover experience. I’m going to list these out in “Q and A” format:

Q: Can I use automatic seeding to synchronize my DAG when upgrading SQL?

A: So, in a migration scenario where you’re upgrading from one SQL version to a higher SQL version, Microsoft’s documentation says automatic seeding is not supported and that seeding mode must be set to MANUAL. The docs go as far as saying that if you don’t disable auto seeding, “your migration will fail” with error 946 in the error log. That has not been my experience, and not just on this 2019 to 2022 project. In general, every time I’ve set everything to auto seed in a cross version DAG, the databases seed over from the global primary AG to the primary replica of the forwarder AG just fine and land in a Synchronizing/Restoring state. They sit that way until you fail over the DAG. At that point they go through their internal upgrade process, become available, and then start auto seeding out to the rest of the forwarder secondary replicas on their own, even though they never touched those secondaries before the failover.

Funny enough, this is the same exact behavior you get with manual seeding. If you manually restore your databases to the primary and secondary replicas of the forwarder AG and join them, they’ll sit in Synchronizing/Restoring on all forwarder replicas until you fail over the DAG. The docs even describe this degraded but syncing state a couple paragraphs after telling you the migration will fail. So take that for what it’s worth.

One thing worth knowing here since it trips people up: auto seeding for a DAG requires the instance name on the global primary to match the instance name on the forwarder. All of my environments are default instances so this was never a factor for me. If your instance names don’t match, you’re using manual seeding whether you like it or not.

Also, while databases were sitting in that Synchronizing/Restoring state, I kept an eye on log reuse waits on the global primary and never saw anything flagged waiting on Availability_Replica. So the lower version side wasn’t hurting while the forwarder sat in that pre-failover state.

Q: Why are my databases in a Synchronizing/Restoring state when I have a cross version DAG?

A: Whether you use automatic seeding, or manual seeding, your databases will come up in a Synchronizing/Restoring state on the forwarder primary replica (forwarder primary only if using automatic seeding) and secondary replica(s). The reason your databases go into this state is because they are waiting to go through their internal upgrade process. After you fail over, they become available and seed over to your secondary replica(s) if you have auto seeding set up.

Q: Should I use automatic or manual seeding to initially synchronize my databases when using a DAG?

A: I would recommend using manual seeding and restore your databases to the forwarder AG primary and secondary replica(s) before joining them to your forwarder AG. You’re setting yourself up for better success post DAG failover. You’ll immediately have HA set up, and you don’t have to wait for databases to seed over to all secondary replicas.

Q: What are the steps to properly fail over a DAG?

A: You’ll want to first see if you can stop all applications from writing to your databases. Then follow these steps:

On the global primary, set everything to synchronous commit

ALTER AVAILABILITY GROUP [DistributedAGName]
MODIFY
AVAILABILITY GROUP ON
'GlobalPrimaryAG' WITH
(AVAILABILITY_MODE = SYNCHRONOUS_COMMIT),
'ForwarderAG' WITH
(AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

On the forwarder primary, set everything to synchronous commit

ALTER AVAILABILITY GROUP [DistributedAGName]
MODIFY
AVAILABILITY GROUP ON
'GlobalPrimaryAG' WITH
(AVAILABILITY_MODE = SYNCHRONOUS_COMMIT),
'ForwarderAG' WITH
(AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

Then check to see LSN status by running this on both global primary and forwarder primary

SELECT ag.name AS [AG Name],
       ar.replica_server_name AS [replica],
       DB_NAME(drs.database_id) AS database_name,
       drs.synchronization_state_desc,
       drs.last_hardened_lsn
FROM sys.availability_groups AS ag
JOIN sys.availability_replicas AS ar
  ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_database_replica_states AS drs
  ON drs.replica_id = ar.replica_id
 AND drs.group_id = ag.group_id

Don’t proceed until synchronization_state_desc shows SYNCHRONIZED and the last_hardened_lsn matches per database between the global primary and the forwarder. If it doesn’t, keep re-running it until it does.

On the global primary, demote the AG to secondary

ALTER AVAILABILITY GROUP [DistributedAGName]
SET (ROLE = SECONDARY);

Special Note: This step is easy to miss but crucial. Think of it as your replacement for setting databases read_only before a standalone cutover. Once you run this, the global primary AG becomes a secondary in the DAG and nothing can write to it.

Now run your final last_hardened_lsn comparison between both sides. If they match, you’re failing over with zero data loss.

SELECT ag.name AS [AG Name],
       ar.replica_server_name AS [replica],
       DB_NAME(drs.database_id) AS database_name,
       drs.synchronization_state_desc,
       drs.last_hardened_lsn
FROM sys.availability_groups AS ag
JOIN sys.availability_replicas AS ar
  ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_database_replica_states AS drs
  ON drs.replica_id = ar.replica_id
 AND drs.group_id = ag.group_id

On the forwarder primary, failover the DAG

ALTER AVAILABILITY GROUP [DistributedAGName]
FORCE_FAILOVER_ALLOW_DATA_LOSS;

Special Note: You must use force_failover_allow_data_loss. This is because you’re failing over an AG that lives on a completely separate cluster than the other, so there is no way to make sure that upon a manual failover of a DAG that in-flight transactions are consistent between two separate AGs on different clusters. Not to mention that this is the only supported failover type for a distributed AG.

One more note: yes, SQL Server 2022 added the REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT setting for distributed AGs, which is designed to guarantee no data loss on a DAG failover without babysitting LSNs. But it has to be supported on both sides of the DAG, and in a cross version migration your global primary is still on the older version that doesn’t support it. So for a migration like this, you’re doing it the classic way laid out above.

Handling Replication In A DAG Migration

For my environment, we use replication in quite a few of these AGs. So, In order to make the cutover as seamless as possible when it comes to a replication standpoint, the major work is in the prep. Now, the replication topology is as follows:

• Transactional replication
• Distributors are remote (note that the distributor has to be greater than or equal to the publisher in version)
• Everything is push replication
• Distributors are already on SQL 2022

I should make a note here that if you have replication in your environment that you need to migrate, you should do this prep work AFTER you set up the new AG you’re migrating to but BEFORE you set up the DAG. It avoids issues when running these queries later on. Here’s the steps I take to configure replication metadata when prepping for the DAG migration:

Step 1: Add Your New Replicas As Publishers On The Distributor

-- Run on the distributor, doing it once per replica 
-- in your target AG (Replica1, Replica2)
EXECUTE sys.sp_adddistpublisher
    @publisher = 'Replica1',
    @distribution_db = 'distribution',
    @working_directory = '\\MyReplShare\WorkingDirectory',
    @security_mode = 1;  -- or 0 with @login/@password matching the original publisher

Step 2: Configure The Remote Distributor On Your New Replicas

-- Run on each replica in your target AG
-- (Replica1, Replica2)
EXECUTE sys.sp_adddistributor
    @distributor = 'RemoteDistributorServerName',
    @password = '<same distributor password>';

Step 3: Add The Subscriber Servers As Linked Servers On New Replicas

-- Run on each target AG replica (Replica1, Replica2)
-- once per subscriber server
EXECUTE sys.sp_addlinkedserver @server = 'MySubscriber';

This makes it nice, because if you’re re-using the existing AG listener from the old AG, the redirected publisher setup will persist fine post DAG failover. Once you drop and re-create the listener on the new AG, replication will continue to flow.

The Conceptual Cutover

So, for my scenario, the general production cutover process would look like this (reminder, I’ve already laid the ground work with replication here):

1. Stop app and end user writes (if necessary)
2. Validate global primary and forwarder AG replica synchronization status by setting everything to synchronous commit and validating LSN matching (this mitigates data loss)
3. Demote the global primary AG to be secondary
4. Failover the DAG
5. Drop the AG listener on the 2019 AG
6. Drop the temporary listener on the 2022 AG
7. Clean up AD and DNS of the 2019 AG listener so we can re-create on the 2022 side
8. Re-create the listener
9. At this point databases are up, available for transactions and have HA and replication is flowing
10. Drop the Distributed AG on both the new global primary and the now forwarder AG
11. Wire up CDC with capture jobs and cleanup jobs
12. Done

The longest part of this whole cutover is the listener touchpoints in AD and DNS. The CDC is handled via a PowerShell script I’ve created that automatically hits all the touchpoints to ensure CDC gets set up properly.

Also make note that when you’re using a DAG as your migration method to go from a lower version of SQL to a higher version, there is no failback. Once you cut over, the databases have gone through their upgrade process and can’t sync back to the lower version side. Your only emergency bail-out is pointing connections back at the old AG, and that means eating the loss of anything written to the new side since the failover. So treat the DAG failover as the point of no return and make sure your validation happens before it.

Making This Process Repeatable

With all of these touchpoints, steps, and considerations laid out above, I wanted to make this as easy and repeatable as possible. So, I’ve created PowerShell scripts that do all of the above automatically for us. This includes:

• DAG Setup And Synchronization
• DAG Failover
• CDC Wireup

Downtime And The Big Picture

With this approach, databases are up and available for transactions within a few seconds. The entire cutover process (setting up CDC and other touchpoints for maintenance) takes a couple minutes. A big part of this is the automation through PowerShell. The less we have to click around the better.

This also allows us to complete complex migrations for business critical systems way more efficiently than ever before. We have no concerns at this point hitting all of these legacy boxes for upgrades and completing months before Windows Server 2016 goes out of support.