patching SQL Server with Availability Group and Secondary Replica

Question

Scenario: SQL Server 2014 CU6. 1 AG with 2 databases (compatibility level 100). Failover Cluster with 3 nodes on Windows Server 2012 R2. Primary Availability Replica on node 1 and node 2. Secondary Availability Replica on node 3 (no votes). Async commit, manual failover, readable secondary. Node 3 is used to run reports on these 2 databases (out of 8 databases running outside AG on node 1 and 2).

Problem: Looking to patch these from SQL 2014 to SP1 and latest CU. Not my build and no experience with AG or FC so far. My "test" environment is also used for development, so no room for mistakes.

Questions:

What's the best order to patch the 3 nodes ?

Do I first have to remove the databases from AG on the node I'm patching ?

Do I need to take the Secondary out when patching it ?

No experience on this, and I can't find clear answers for my scenario (maybe not supported?) in the docs: https://msdn.microsoft.com/en-us/library/dn178483(v=sql.120).aspx

Answer 1

The scenario is called out and supported on the link you've provided.

Availability Group with One Remote Secondary Replica

If you have deployed an availability group only for disaster recovery, you may need to fail over the availability group to an asynchronous-commit secondary replica. Such configuration is illustrated by the following figure:

Availability Group Upgrade in DR Scenario

In this situation, you must fail over the availability group to the asynchronous-commit secondary replica during the rolling upgrade/update. To prevent data loss, change the commit mode to synchronous commit and wait for the secondary replica to be synchronized before you fail over the availability group. Therefore, the rolling upgrade/update process may look as follows:

1.Upgrade/update the remote server

2.Change the commit mode to synchronous commit

3.Wait until synchronization state is SYNCHRONIZED

4.Fail over the availability group to the remote site

5.Upgrade/update the local (primary site) server

6.Fail over the availability group to the primary site

7.Change the commit mode to asynchronous commit

Answer 2

The documentation was updated since, and I feel the below is a perfect answer to my scenario:

1. Remove automatic failover on all synchronous-commit replicas

2. Upgrade all remote secondary replica instances running asynchronous-commit secondary replicas

3. Upgrade the all local replica secondary instances that are not currently running the primary replica

4. Manually fail over the AG to a local synchronous-commit secondary replica

5. Upgrade or update the local replica instance that formerly hosted the primary replica

6. Configure automatic failover partners as desired

If necessary, you can perform an extra manual failover to return the AG to its original configuration.

Answer 3

Making the secondary async is important as when it dissappears, the primary wont be be able to harden transaction on secondary and will wait 30 seconds or however long the heartbeat is. Make Async so that it allows commits in those first 30 seconds.

Answer 4

The documentation! doesn't seem to require the secondary be in ASYNCH mode, however. It only says set to "MANUAL" failover. Under the "NOTE" section.

"Upgrading a synchronous-commit replica and taking it offline will not delay transactions on the primary. Once the secondary replica is disconnected, transactions are committed on the primary without waiting for logs to harden on the secondary replica."