August 22, 2017 at 6:26 am
Building a AG group for an ETL DWH, I am of the mind set for ETL type updates that Synchronous is the way to go, for the multitude of small updates to that fact we are looking for performance (and you can always rerun the process for an ETL). If I am wrong be happy to listen to arguments or people who have gone down this road.
August 22, 2017 at 8:17 am
In AlwaysOn Availability Groups, the difference between synchronous commit and asynchronous commit is how the transaction is committed to the secondary(ies) replica(s). In synchronous commit, the transaction is applied to primary and secondary(ies), and the ACK waits until the transaction is completed on the secondary(ies) before finishing. In Asynchronous commit, the transaction is applied to the primary and the ACK is done at that point...then the transaction is applied to the secondary(ies) afterwards. So, asynchronous is "faster" for processing on the primary but the trade off is "risk" that the transaction may not be applied to the secondary(ies) if a catastrophic event occurs before the transaction is applied to the secondary(ies).
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 22, 2017 at 8:19 am
Microsoft states this better than I can: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply