About this article: AI-assisted writing, reviewed against the cited sources. This is source research, not a lab validation of the procedures. Check the stated versions and your own recovery requirements before making changes.
Two native methods shrink a SQL Server migration outage to the final log handover and the steps after it. Log shipping works on Standard edition and is simple, but you carry the last log backups across by hand. A distributed availability group keeps the target closer and can prove that nothing was lost, but it needs Enterprise edition and an availability group with a listener on each side. If the target runs a newer major version, either method becomes a one-way door after the newer target is recovered or takes over; returning business writes then needs a separate data-reconciliation plan.
The commands below were not run in a lab. They come from Microsoft's documentation, and are reference material, not a ready-to-run procedure, until a qualified engineer has checked them on a copy of the database. Server, database and path names are examples.
Interpretations are editorial; no lab testing was performed.
Why it matters
The copy of the data is rarely what hurts. The outage is made of the steps after the application stops: the final log, the recovery, and everything that lives outside the database and did not come with it. Microsoft's log shipping upgrade guidance is blunt on this. It says that, since log shipping is rarely configured for high availability now and newer options are more robust, failing over generally doesn't minimise downtime, because system database objects aren't synchronised and clients can struggle to find the promoted server.
We read that as a warning about an unprepared failover, not about log shipping migrations in general. A migration can prepare for both problems in advance, and most of this article is about doing that.
How it works
Log shipping
Log shipping is three repeating operations: back up the log on the source, copy the file to the target, and restore it there. SQL Server Agent jobs do each step. It never fails over automatically. The target is therefore always behind by at least the unshipped log, and the last step of the migration closes that gap by hand.
The source database must use the full or bulk-logged recovery model. Switching to simple recovery stops log shipping.
The target waits in one of two states. Standby allows limited read-only access between restores. However, standby is only offered when both servers are the same version; a higher-version secondary must use no recovery. Microsoft also warns that RESTORE WITH STANDBY isn't supported for a database that needs upgrading. A standby secondary moved to a newer version cannot keep restoring, and log shipping has to be set up again.
Distributed availability groups
A distributed availability group spans two separate availability groups. Each side keeps its own cluster, and the distributed group exists only inside SQL Server. The clusters can be in different domains and run different Windows Server versions, which is why it suits a move between sites.
Two terms matter. The global primary is the one writable copy. The forwarder is the primary replica of the second group, which receives the log and passes it to its own secondaries.
The second group cannot accept writes until you manually fail over the distributed group. Automatic failover is not supported.
The limits that choose for you
| Limit | Log shipping | Distributed availability group |
|---|---|---|
| Editions | SQL Server 2022: Enterprise, Standard, Web. SQL Server 2025: Enterprise, Standard | Enterprise only, in both the 2022 and 2025 tables |
| Oldest source | Set by what the target can restore; check its release documentation | SQL Server 2016; groups from 2012 or 2014 can't take part |
| Mixed major versions | Target may be higher, in no recovery mode | SQL Server 2017 or later; the side holding the writable primary must be the same or lower |
| Needs on each side | A share and Agent jobs | An availability group with a listener. A failover cluster instance can use its network name instead |
| Check for no loss | Matching last_lsn after the tail-log restore (our check, below) |
Matching last_hardened_lsn on both sides (Microsoft's check) |
| Read the target before cutover | Same version only, using standby | Not while the source is a lower version |
One Azure limit does apply to virtual machines. A distributed group isn't supported on top of a listener configured with a distributed network name.
What to choose and why
These are our recommendations, based on the limits above.
Choose log shipping if you run Standard edition, have a standalone instance, or have no availability group today. Building an availability group on the source only for the move is a production change to a system you are about to retire.
Choose a distributed group if the source already has an availability group on Enterprise edition, the database is large and busy, and you want a checked no-loss handover.
Do not change the version in the same step without a reason. A same-version move has a way back. A version change does not, as the next sections show.
Do the arithmetic before you start
Microsoft works one example for automatic seeding: 5 TB at a sustained 125 MB per second on a 1 Gbps link between sites 1,000 miles apart takes just over 11 hours, and says real seeding is slower.
Plan the log as well. During automatic seeding the source log keeps growing and cannot be truncated until seeding of that database finishes. Check the log volume against your log generation rate multiplied by the seeding time.
Then write down an outage budget, in this form:
outage = application stop + final log backup + copy + restore + recovery + login and job checks + application start + smoke test
Only a rehearsal gives real numbers for those terms. Log shipping's upgrade step at recovery is described as small, with one documented exception: full-text indexes in a SQL Server 2005 database can take hours. Measure it on your own database.
Move these before you seed
The database does not bring its surroundings. Microsoft keeps a list of what lives outside a user database, including credentials, linked servers, Agent jobs and proxies, logins, server-level permissions, startup procedures and server-level triggers.
Handle these three first.
Encryption keys. A database using transparent data encryption needs its certificate or asymmetric key installed in master on the destination. Keep both the certificate file and its private key file.
SQL logins. Database users are matched to logins by security identifier, so a SQL login recreated by hand gets a new identifier and an orphaned user. Microsoft publishes sp_help_revlogin, which scripts logins with their hashed passwords and original identifiers. If the domain changes, replace the domain name in the output. Treat that output as a secret.
Agent jobs. The account, proxies and service-account rights a job relies on must exist on the destination. Create the jobs disabled so they cannot run against a database that is still restoring.
Path A: log shipping
Set up log shipping with SQL Server Management Studio or the stored procedures, using the configuration procedure. Two decisions matter for a migration:
- Stop every other log backup. Restores must have all the necessary log backups available. A routine log backup from another tool lands somewhere the restore job will not look, and leaves a gap. A copy-only log backup does not affect the sequence, if another tool must run.
- Rehearse on a separate copy. A higher-version target cannot be read before cutover. Restore a copy-only full backup under another name, recover it, and test the application there.
Watch it on the target. A copy of the status for each server is kept locally:
SELECT secondary_database, last_copied_file, last_restored_file,
last_restored_date, last_restored_latency
FROM msdb.dbo.log_shipping_monitor_secondary;
last_restored_latency is the minutes between creating a log backup and restoring it. Cut over only when it has been low and steady for several days.
Plan the final handover
The DBA's procedure must preserve a complete log chain, identify the exact tail backup and establish exclusive ownership of writes. Microsoft's log-shipping failover guidance describes copying outstanding backups, applying them in order, capturing and applying the tail, and recovering the destination. The source tail-backup option can leave the source in RESTORING and unavailable, so this is an outage decision, not an observation step.
Before approving that procedure, record the backup, copy and restore job states, any configured restore delay, the last successfully restored file, and the location of every outstanding log backup. Do not infer completeness from a low latency value alone. The plan must prevent routine jobs from racing the handover and specify how they resume if the migration is stopped.
Recovery on a higher-version instance can upgrade the database. The source must therefore remain recoverable independently, and any target writes require a separate return plan. Microsoft's known issue involving memory-optimised tables and the tail/recovery sequence also needs review against the actual release and patch level; an instance restart affects more than this database.
The following read-only comparison is a starting point for identifying the final backup, not a complete proof of the restore chain. Compare the last log backup on the source with the last restore on the target. Each server records a last_lsn for each backup set, and restorehistory links each restore to one:
-- On the source
SELECT TOP (1) last_lsn, backup_finish_date
FROM msdb.dbo.backupset
WHERE database_name = N'AppDb' AND type = 'L'
ORDER BY backup_finish_date DESC;
-- On the target
SELECT TOP (1) bs.last_lsn, rh.restore_date
FROM msdb.dbo.restorehistory AS rh
JOIN msdb.dbo.backupset AS bs ON rh.backup_set_id = bs.backup_set_id
WHERE rh.destination_database_name = N'AppDb'
ORDER BY rh.restore_date DESC;
Match the identified tail backup to its successful restore, including database identity, backup-set identity, recovery fork and chain. Different LSNs require investigation; they do not uniquely prove a missing file, because the query may have selected a different backup or restore event. Matching numbers alone do not establish complete application consistency.
Path B: distributed availability group
A distributed-group handover can lose data if its version-specific synchronisation conditions are not met. Use the Microsoft procedure for the installed releases and validate it on an isolated pair before scheduling production.
Build a second availability group on the target, with its listener, then create the distributed group on the source and join it on the target. Stay in asynchronous mode until you are ready to fail over, on both sides. Four details catch people:
LISTENER_URLuses the mirroring endpoint port, such as 5022, not the listener's client port. With a load balancer, add a rule for that port as well.- Endpoints must listen on all addresses. Binding one to a single network stops the group working properly.
- Without a shared domain you need certificates, and each primary must hold the other's.
- The second group must use the same mirroring endpoint, or replication stops after a local failover.
Seeding across versions. If the target is a higher version, automatic seeding isn't supported. Leaving it on fails the migration with error 946. Set SEEDING_MODE = MANUAL, restore a full and a log backup from the global primary with NORECOVERY on the forwarder, then join the database there. Do the same on any secondary replica of the second group, joining it to ag2 instead:
The database-join operation targets different groups on the forwarder and its local secondaries. Have the DBA record those roles explicitly in the seeding plan; a command copied to the wrong server can change the wrong replication relationship.
Until failover, the target group shows as Partially Healthy and its secondary replicas as Synchronizing/In Recovery, even in synchronous mode. That is documented behaviour. Do not spend your outage window chasing it.
The handover
Microsoft documents two procedures, and they end differently, so follow one list and do not mix them. The newer one uses REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT, which distributed groups support only from SQL Server 2022 and which is "designed to guarantee no data loss". The older one applies whenever that setting is not in use. Both lists use these four statements.
The reviewable plan should contain these decisions, with evidence captured from both sides:
| Decision | Required evidence | Why it matters |
|---|---|---|
| Which procedure applies? | Exact SQL Server releases and whether the required-synchronised-secondary setting is supported and selected | The 2022-and-later path and the older path have different conditions and recovery branches |
| Are all writers controlled? | Application, integration and job ownership, with a recorded freeze boundary | An unaccounted writer can move the state after a comparison |
| Is synchronous protection established? | Local-group and distributed-group modes, healthy replica state and database-level synchronisation | Changing a mode does not prove that every database has caught up |
| Is the expected log hardened? | Version-appropriate comparison of each database on both servers | A NULL, stale or mismatched value is not clearance to promote |
| What changes when the original primary steps down? | Named roles and a tested stopping/recovery decision | Microsoft warns that the newer procedure cannot simply fail back at that intermediate point |
| What is the intended client destination? | Listener and routing plan for the replacement group | The distributed group has no listener of its own |
Microsoft's procedure for SQL Server 2022 and later can use REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT, a setting designed to guarantee no data loss when its documented prerequisites are satisfied. Requiring a secondary acknowledgement changes commit behaviour and can affect production latency. The plan must record when that requirement is enabled and how it is adjusted on the former primary after the roles change.
The older procedure requires its own synchronisation and hardened-log checks before and after the global primary steps down. Its recovery branch must not be copied into the newer procedure. The forced-failover operation has an explicit data-loss capability; its name is a reason to require the full evidence, not a shortcut around a failed comparison.
A rehearsal should include the case where one database fails a check. Record which server remains authoritative, whether either side is writable, and which operations are still safe. This article does not supply a forced-failover command or an executable cutover sequence because neither has been validated here.
Finishing, on either list. If distance causes latency, you can return to asynchronous commit. After a move to a higher version, Microsoft's two endings are to remove the original group from the distributed group, which completes the process, or to upgrade it to match and keep the group.
Applications then need a route to the new group. A distributed group has no listener of its own. Microsoft's options are to rename or re-create the listener, or to point applications at the second group's listener.
If clients cross subnets, read the MultiSubnetFailover and RegisterAllProvidersIP guidance. Without them, a cross-subnet change could take 15 minutes or longer to reach clients, depending on DNS settings.
Checks before users return
| Check | How | Pass |
|---|---|---|
| Orphaned users | Query sys.database_principals against sys.server_principals in each database |
No rows, or fixed with ALTER USER ... WITH LOGIN. Microsoft's query finds SQL logins only; after a domain change Windows users are orphaned too, so check them separately |
| Owner and options | SELECT name, SUSER_SNAME(owner_sid), is_trustworthy_on, is_db_chaining_on FROM sys.databases; |
Matches the source. The login that ran the restore becomes owner, and TRUSTWORTHY and DB_CHAINING may need enabling. Re-create any explicit Service Broker routes held in msdb |
| Compatibility level | SELECT name, compatibility_level FROM sys.databases; |
Unchanged, or raised to the instance minimum. A restored database keeps its level if the new instance allows it |
| Integrity | DBCC CHECKDB, adding WITH DATA_PURITY once if upgraded from an earlier version |
No errors. Schedule by size; it may not fit the window |
| Statistics | sp_updatestats, recommended when restoring from an earlier version to SQL Server 2022; the page does not mention a 2025 target |
Completed |
| Jobs and backups | Enable jobs; take a new full backup on the target | First log backup succeeds |
| Application | Sign-in, one write, one report, using real service accounts | Passed by the application owner |
Risks and limits
- A version upgrade is one-way. No backup can be restored to an earlier version. After a distributed failover to a higher version, fail-back isn't possible and updates don't replicate to the old group. The distributed failover itself can close the old-version failback path; target writes add a separate reconciliation problem.
- Rollback at the same version. Before the target is recovered, you can bring the source back with
RESTORE DATABASE AppDb WITH RECOVERY. The source then writes log the target has not seen. The pages we opened do not say whether shipping can resume, so budget a retry as needing a new seed and try it in rehearsal. A distributed group can fail back with the same forced command, and it loses data unless the synchronisation steps come first. For log shipping, after the target is recovered and in use, Microsoft's route is to configure log shipping in reverse, with the old primary as the initialised secondary. Set that up before opening to users. - Inconsistent wording in the source. Microsoft's overview allows mixed major versions from 2017, then says in its Windows Server section that they must match. We followed the version and migration sections. Test your exact pair.
- SQL Server 2025 encryption default. Log shipping's linked servers default to
EncryptMandatory, so changes may be needed when a 2025 instance joins. - Not covered: replication publishers, cross-platform moves, Managed Instance link and failover cluster instance detail. How far back a given target version can restore directly must be checked against its release documentation.
Sources
Opened 2026-11-10.
- About log shipping, Configure log shipping, Fail over to a log shipping secondary, Change roles, Monitor log shipping, Upgrading log shipping log_shipping_monitor_secondary.
- Distributed availability groups, Configure distributed availability groups, Automatic seeding, Listeners and client connectivity
- ALTER AVAILABILITY GROUP, sys.dm_hadr_database_replica_states
- Tail-log backups, Apply transaction log backups, Copy-only backups, RESTORE statements, RESTORE arguments, backupset, restorehistory
- Manage metadata on another server, Move a TDE-protected database, Transfer logins and passwords, Troubleshoot orphaned users
- SQL Server 2022 editions, SQL Server 2025 editions, Compatibility level, DBCC CHECKDB
This article was originally published on .