Forum Discussion
SQL Version Upgrade 2017 to 2019 in an unclustered read-scale availability group
I fail to see how FCI + a remote, readable AG secondary is meaningfully different from the first scenario in Upgrade Availability Group Replicas - SQL Server Always On | Microsoft Learn.
Basically, your proposed upgrade path follows the suggested upgrade path.
I would note the guidelines in the same article, particularly:
- During a version upgrade, readable secondaries can't be read after an upgrade of the readable secondary and before either the primary replica is failed over to an upgraded secondary, or the primary replica is upgraded.
- Backups can't occur on a database that is in the process of being upgraded. Prior to upgrading the secondary replicas, configure the automated backup preference to run backups only on the primary replica. During a version upgrade, no replicas are readable or available for backups. [...]
- Don't upgrade the primary replica instance before upgrading or updating any other secondary replica instance. An upgraded primary replica can no longer ship logs to any secondary replica whose SQL Server instance that hasn't yet been upgraded to the same version. When data movement to a secondary replica is suspended, no automatic failover can occur for that replica, and your availability databases are vulnerable to data loss. This also applies during a rolling upgrade where you manually fail over from an old primary to a new primary. As such, after you upgrade the old primary, you might need to resume synchronization.
On the backup point, I would also warn that your backup tooling may believe the log chain broken when you fail over to the upgraded FCI instance.
This could result new full backups of your databases starting at the next backup attempt.
I have experienced something similar with a rolling upgrade of a two-node AG.
Since the mechanics of FCI and AG are quite different, you may also be fine.
In summary:
- Update AG Readable Secondary - Will not be readable until step 3 finishes
- Update FCI Secondary
- Failover FCI - DB Startup may be delayed due to db_version upgrade
- Update final FCI instance
There's an argument for doing 1 and 2 in a different order/simultaneously.
In the eyes of the AG, the primary hasn't been upgraded until the FCI failover occurs.
Updating the Readable secondary as step 2 would reduce the time it is unavailable for reads.