Our SQL always-on automatic failover for UMS is currently configured for asynchronous. This was based on a recommendation from our vendor that helped us with our installation. Our DBA’s dont’ like this configuration and this isn’t considered standard practice for our organization because it requires manual intervention. Does anyone know of any reason why we can’t switch our DB failover configuration to use synchronous mode? This would make our DBA’s very happy. Please advise.
Basic recommendation — Is to go with your company standards defined by your DBAs.
Availability Modes
The availability mode is a property of each availability replica. The availability mode determines whether the primary replica waits to commit transactions on a database until a given secondary replica has written the transaction log records to disk (hardened the log). AOAG’s supports two availability modes—asynchronous-commit mode and synchronous-commit mode.
· Asynchronous-commit mode: An availability replica that uses this availability mode is known as asynchronous-commit replica. Under asynchronous-commit mode, the primary replica commits transactions without waiting for acknowledgement that an asynchronous-commit secondary replica has hardened the log. Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag the primary databases, making some data loss possible.
· Synchronous-commit mode: An availability replica that uses this availability mode is known as a synchronous-commit replica. Under synchronous-commit mode, before committing transactions, a synchronous-commit primary replica waits for a synchronous-commit secondary replica to acknowledge that it has finished hardening the log. Synchronous-commit mode ensures that once a given secondary database is synchronized with the primary database, committed transactions are fully protected. This protection comes at the cost of increased transaction latency.
Ok thanks!
Continue reading and comment on the thread ‘Which SQL Always on fialover modes are supported’. Not a member? Join Here!
Learn more, search the IGEL Knowledge Base
Ask a question or comment on the above message thread?
Join or log in to the IGEL Community to ask us anything and meet other IGEL customers, partners, and EUC enthusiasts.Submit a question, or Join Today!