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
Best Answer
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