SQL Server Always-On Availability Groups Patching

Question

My SQL server farm has been neglected with patching OS level & SQL server level (because they are critical systems, outages are hard to come by).

An option is to patch the secondary nodes of our AOAG clusters up to the latest patch one month, then the next month the business agree to schedule a fail over out of hours.. I can then patch the new secondary (old primary). This will mean the nodes wont be on the same patch level for a month.. is this “a no no”?

Answer 1

This is an unsupported configuration as per the docs

Mixing versions of SQL Server instances in the same AG is not supported outside of a rolling upgrade and should not exist in that state for extended periods of time as the upgrade should take place quickly. The other option for upgrading SQL Server 2016 and later is through the use of a distributed availability group.

What does this mean practically? It might be completely fine - there may be zero compatibility issues and it could go swimmingly. It also might not. Microsoft has not tested the configuration you are running if you choose to mix versions between instances. At that point for me personally, the risks far outweigh the advantages.

It's also worth noting that using the rolling upgrade process as defined in the link I posted reduces downtime to a minimal figure anyway. If that's still not good enough, why not build two new servers and a new AG, and migrate to them, rather than patching? It's a lot more work but you should be able to minimise downtime even further.

Answer 2

Patch your secondary server, on the day of your down time, but finish before your down time.

Failover to it as scheduled.

Patch the primary server, as soon as you complete the failover and are stable.

Both servers should be built the same, so it really should not matter which one is primary. But if you care, fail back at a different down time window.

Or better yet, add a listener to your AG, have the application point to the Listener (not all applications can do this), you can patch the servers one after the other every month, and the only down time is the first time the application re-points to the listener.

Answer 3

An option is to patch the secondary node of AOAG clusters up to the latest patch, then the next month the business agree to schedule a fail over out of hours.. I can then patch the new secondary (old primary).

When you plan for patch update you must plan it for all replicas in AG to keep solution really Always On, it's one of core advantage of Availability Groups that server maintenance can be done without downtime.

In your approach your breaking the term of Always-on business continuity, for example, when you update secondary replica, leave primary replica without updating. For some-reason you wanted to fail-over to the secondary, that moment on-wards it may not fail-back over to the original primary again - one of the reason that the databases always upgraded to newer version but never be downgraded, there must be some workaround (scripting out) to downgrade the DB version, in this case when you cannot fail-back over to primary replica it's not Always On, thus, it's recommended to schedule patch update for all replicas together with recommended order..

Although, there is no down-time, the better time to perform patch updated is during less overload on the servers. Before proceeding, you may want to consider (if not configured already) Node and fail-share majority quorum configuration at WSFC as it's recommended when there are even number of nodes in WSFC, so the file-share witness maintain a vote to keep healthy quorum and cluster resources healthy (listeners) especially when secondary node is offline which normal during patch update.

Following query would be helpful to see the synchronization health (it's essential before and after doing patch update), some info cannot be available in the availability group dashboard but you can get it though DMVs (as follows):

select  db.name, 
        db.database_id, 
        ag.name as GroupName, 
        state_desc, 
        recovery_model_desc, 
        log_reuse_wait_desc,
        AGDB.truncation_lsn,
        Rep.replica_server_name,
        rep.endpoint_url,
        DBRepStats.is_primary_replica,
        DBRepStats.synchronization_health_desc,
        DBRepStats.database_state_desc,
        (redo_queue_size / 1024.0) as redo_queue_size_MB,
        last_redone_time,
        last_redone_lsn,
        DBRepStats.end_of_log_lsn,
        DBRepStats.last_sent_lsn,
        DBRepStats.last_sent_time,
        DBRepStats.last_received_lsn,
        DBRepStats.last_received_time,
        DBRepStats.last_hardened_lsn,
        DBRepStats.last_hardened_time
from sys.databases as db
    left outer join sys.availability_databases_cluster as AGDB on db.group_database_id = AGDB.group_database_id
    left outer join sys.dm_hadr_database_replica_states as DBRepStats on db.group_database_id = DBRepStats.group_database_id
    left outer join sys.availability_replicas as Rep on DBRepStats.group_id = Rep.group_id and DBRepStats.replica_id = Rep.replica_id 
    left outer join sys.availability_groups as AG on DBRepStats.group_id = AG.group_id
where db.database_id > 4