SQL Server AlwaysOn Restart service on secondary

Question

I would like to restart the sql service on my secondary, my question is this...

If My Primary & Secondary are both set with Availability Mode: Synchronous; Failover Mode: Automatic, do I have to switch my Failover Mode to "Manual" before restarting the sql service on the secondary node?

Thanks In Advance,
Tony

Answer 1

Hi,

before we start let me clarify that everything you do is on your responsibility and if you are not sure, then first test the entire procedure in development environment, and go to the production only when you understand 100% of what you do and better to do it first time with someone who have the experience and knowledge!

In any case, ALWAYS REMEMBER TO MAKE SURE THAT YOU HAVE FULL BACKUP OF THE SYSTEM.

Now we can move to the theory :-)

On Synchronous-Commit Mode with Automatic Failover, If the secondary replica goes down, then the primary will try to connect the secondary and there will be a latency until the primary's session-timeout period is exceeded! In this case the primary replica will temporarily shift into asynchronous-commit mode for that specific secondary replica.

For an automatic failover to that secondary, both primary and secondary must be configured with the Synchronous commit. Since primary shifted into asynchronous-commit it will stop the automatic failover to that secondary (which make sense since it is down).

When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode.

Ask yourself:

(1) Do you really want to make your user wait for the session-timeout period when the secondary go down?

(2) What if automatic failover happens before the primary shifted into asynchronous-commit? In this case it might try to failover to the secondary.

Here are the basic steps that usually should be applied (Again, always remember that everything you do is on your responsibility!)

(1) Change the failover mode from Automatic to Manual - yes this might be important as mentioned above.

(2) Connect to the secondary replica in SSMS -> Expand Always On High Availability -> Availability Databases -> Select the database -> Suspend data movement

(3) Restart the secondary replica

(4) Once the secondary replica comes online, check the SQL Server error logs for any errors and warnings, verify SQL Services are online, and check the database

(5) resume data movement from the secondary replica database.

Check the following documents for more information:

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups?view=sql-server-ver15#SyncCommitWithAuto

Upgrading Always On Availability Group Replica Instances
https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/upgrading-always-on-availability-group-replica-instances?view=sql-server-ver15

Answer 2

Adding that the REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT configuration will need to be zero during the operation if you want to allow writes to the primary when the secondary is down in a 2-node AG.