Transitioning AlwaysOn Availability Group to Synchronous Commit Mode
-
Hello, I have a SQL Server with an AlwaysOn configuration consisting of three replicas operating in asynchronous commit mode. I have received a request to switch to synchronous commit mode, but I am concerned about potential data loss risks.
Currently, the database is in "Synchronizing" status. My question is: What will be the status of the Availability Group (AG) if I proceed with the switch to synchronous commit mode?
Thank you in advance for your help!
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
If I have understood the question correctly, switching to synchronous commit mode should result in all Availability Groups showing a synchronized status, and the failover readiness state should indicate NO DATA LOSS.
However, before making this change, it is important to align with the business to identify an appropriate maintenance window or a quiet period on the server. Begin monitoring the replication lag between the primary and secondary replicas immediately after the change. If the secondary replica begins to lag to a point where the business perceives the data as stale or inconsistent with the primary, that becomes your tipping point. At that stage, you will need to investigate the root cause of the lag on the secondary replica.
If the performance impact is unacceptable, you can revert to asynchronous mode. There is no immediate risk in enabling synchronous commit mode—it does not break anything—but it must be done with caution, close observation, and with a clear rollback plan.
Thanks,
Abhinav -
The question is:
"What will be the status of the Availability Group (AG) if I proceed with the switch to synchronous commit mode?"
By status of the AG I believe you are asking about the status of each database on the secondaries. The status will be "synchronized" on all of the secondaries, same as the status is for the Primary.
Your concern about potential data loss in a synchronized commit secondary environment should be very low, lower than in an asynchronous commit environment.
HOWEVER
There are some things to be aware of when switching to "synchronized commits" on multiple secondaries.
A transaction will NOT be considered "committed" until all secondaries have "committed" or received the transaction and ACKnowledged the transaction. This could significantly slow down transaction processing.
If you have an OLTP system, it is recommended to leave any read-only secondaries in an asynchronous commit mode.
Have one secondary, not readable, which basically means its ready for failover (HA solution), this usually reduces the commit issues since it is only accepting changes from the primary.
If you are going to (or already have moved to) synchronous commits you need to have good monitoring. You will find that sometimes these databases will move to an asynchronous commit status, usually determined by the SQL engine and usually because of a delay in committing transactions to one or more secondaries.
Yes you read that right. SQL will change the commit mode if it feels its warranted. Usually when the secondaries are "unreachable".
While AGs may seem like a "set it and forget it" feature of SQL server replication. It is not. There are many more things that can go wrong with AG's and when you add multiple secondaries, its compounding the possible problems you can encounter.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply