October 27, 2022 at 3:20 pm
Hello Everyone,
I have Availability Group, one primary replica and one secondary replica. SQL Server version is 2012. It is configured for automatic failover and is in synchronous mode. I already googled but found different answers, so I want to know the correct procedure. How to do patching step by step.
October 27, 2022 at 6:06 pm
The flow chart at the following MS link identifies where to find all the steps based on your configuration.
The main article that came from is at the following.
You just need to science out what steps are appropriate for your configuration.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 27, 2022 at 8:08 pm
The simple outline is:
If you don't have to fail back - then it is only a single outage that is minimally invasive.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 28, 2022 at 6:22 am
You are in Synchronous mode with SQL 2012 so how have you done the patching so far. I hope this is not a new install as SQL 2012 is 10 years old and out of support.
The advice I have seen is to put the AG into Async mode for the duration of the patching, so that work can continue on the primary while you patch the secondary. If you do not do this, then when SQL is stopped during patching or the server rebooted, no work can continue on the primary until the secondary is available again.
It is also worth reviewing the justification for running in synchronous mode. It always results in worse performance than async mode, as you are hostage to the extra overhead of writing to two servers and any network glitches between them.
Likewise review the justification for automatic failover. Any fail over will cause disruption to your workload, and you will always get false positives with auto fail over so you get more disruption than needed. Again hoping this is an old install, have you done any analysis of the fail overs you have had over the years.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 28, 2022 at 8:27 am
SQL 2012 is 10 years old but I graduated this year and joined as entry level.
October 28, 2022 at 8:41 pm
The advice I have seen is to put the AG into Async mode for the duration of the patching, so that work can continue on the primary while you patch the secondary. If you do not do this, then when SQL is stopped during patching or the server rebooted, no work can continue on the primary until the secondary is available again.
There really is no need to switch modes - when you restart the secondary, the primary replica will temporarily shift into asynchronous commit mode until the secondary reconnects. Once it has reconnected it will resume synchronous commit mode (after the send and redo queues have caught up - bringing the databases into a synchronized state).
It is also worth reviewing the justification for running in synchronous mode. It always results in worse performance than async mode, as you are hostage to the extra overhead of writing to two servers and any network glitches between them.
This also is not true - it is entirely dependent on the network between the hosts. The performance on the primary is dependent on the transactions being hardened on the secondary before they can be hardened on the primary and that will happen very quickly with a properly configured cluster. In the event that either the send or redo queues fall behind - the session timeout would be hit and SQL Server will temporarily shift to asynchronous-commit mode.
The entire purpose of synchronous commit mode is to enable automatic failover to meet HA requirements with no data loss. If you do not have synchronous mode enabled - then any issues with the primary replica would cause an outage until a manual failover can be performed. And if you are configured in asynchronous commit mode then you increase the possibility of incurring data loss if something happens on the primary replica.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 31, 2022 at 6:23 am
Thanks Jeffrey, that was helpful
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply