Side-by-side SQL Server Always-On cluster upgrade using Log Shipping

 Log Shipping 1st Run:

1. Transfer SQL Server logins from the old SQL Server 2012&2016 to the new SQL Server 2022 primary and secondary servers.
2. Migrate SQL Server Agent jobs from the old SQL Server 2012&2016 to the new SQL Server 2022 primary and secondary servers.
3. Transfer Linked Servers (if any) from the old SQL Server 2012$&2016 to the new SQL Server 2022 primary and secondary servers.
4. Disable existing Transaction-Log Backup jobs on the old SQL Server 2012&2016 to prevent interference with Log Shipping.
5. Establish a shared folder as the log shipping backup file destination path, ensuring it has read-write permissions for the SQL service account and is accessible by the new SQL Server 2022 servers.
6. Configure Log Shipping from the primary AG replica of the old SQL Server 2012&2016 to both the primary and secondary AG replicas of the new SQL Server 2022:
1. Perform a compressed full database backup on the primary AG replica of the old SQL Server 2012&2016.
2. Copy the backup file to both the primary and secondary AG replicas of the new SQL Server 2022.
3. Restore the backups on the new SQL Server 2022 servers using the WITH NORECOVERY option.
4. Enable Log Shipping on the databases in the primary AG replica of the old SQL Server 2012/&2016.
5. Configure the log shipping jobs, selecting the created shared folder as the backup path.
6. Add both the primary and secondary AG replicas of the new SQL Server 2022 as the Log Shipping destination servers.
7. Verify the Log Shipping status using SQL Server 2022 standard reports --> Transactional Log Shipping Status.
8. Repeat the above steps for each application database.

Bring SQL Server 2022 Database Online for Application Testing:

1. Disable Log Shipping on the old SQL Server 2012&2016.
2. Re-enable existing log backup jobs on the old SQL Server 2012&2016 that were disabled during the first run of Log Shipping.
3. Bring databases online in the primary replica of the new SQL Server 2022 (RESTORE LOG <db> WITH RECOVERY).
4. Configure Always-On Availability Group between the primary and secondary replicas of the new SQL Server 2022.

Log Shipping 2nd Run:

1. Disable existing Transaction-Log Backup jobs on the old SQL Server 2012&2016 to prevent interference with Log Shipping.
2. Remove the Always-On Availability Group in the new SQL 2022 servers.
3. Configure Log Shipping from the primary AG replica of the old SQL Server 2012&2016 to both the primary and secondary AG replicas of the new SQL Server 2022:
1. Perform a compressed full database backup on the primary AG replica of the old SQL Server 2012&2016.
2. Copy the backup file to both the primary and secondary AG replicas of the new SQL Server 2022.
3. Restore the backups on the new SQL Server 2022 servers using the WITH NORECOVERY option.
4. Enable Log Shipping on the databases in the primary AG replica of the old SQL Server 2012&2016.
5. Configure the log shipping jobs, selecting the created shared folder as the backup path.
6. Add both the primary and secondary AG replicas of the new SQL Server 2022 as the Log Shipping destination servers.
7. Verify the Log Shipping status using SQL Server 2022 standard reports --> Transactional Log Shipping Status.
8. Repeat the above steps for each application database.

Switch-over:

1. Terminate all client connections to the old SQL Server 2012&2016 by disabling SQL logins.
2. Execute LS-Backup jobs on the old SQL Server 2012&2016.
3. Execute LS-Copy and LS-Restore jobs on the new SQL Server 2022 servers.
4. Disable Log Shipping on the old SQL Server 2012&2016.
5. Bring databases online in the primary replica of the new SQL Server 2022 (RESTORE LOG <db> WITH RECOVERY).
6. Configure Always-On Availability Group between the primary and secondary replicas of the new SQL Server 2022.
7. Estimated switch-over time: 15 minutes.