Always on(SQL server 2016) is configured between four standalone servers Server1,Server2,Server3,Server4. These servers contains one availability Group(AG01) which contains 3 databases. How to apply SQL server patch for these servers? Do we need to apply patch for all servers at same day? These servers are production servers.
The process to upgrade/patch Always On Availability Groups is documented extensively in the official SQL Server docs. Typically this is done as a rolling upgrade, where you move from secondaries to the primary SQL Server. Depending on your setup you would do the following:
- Patch remote secondaries
- Patch local secondaries
- Perform a failover of the primary
- Patch former primary
You don't typically do them all the same time, you roll through the servers. When I patch our servers, I normally do the secondaries one day and then the following, I'll perform the failover and finish patching the former primary.
No, you don't need to apply the updates at the same time. A cautious approach is to upgrade a secondary, then fail over to it and run it as the primary for a few days to ensure that no new issues were introduced. Once you're confident there are no new issues, you can proceed with upgrading the other nodes.
Service packs and cumulative updates do not change anything in user databases, so there are no concerns about not being able to fail over to a node that does not have the update.