Share via

SQL Server Availability Groups - Asynchronous Commit Without Data Loss

Jim Nguyen 61 Reputation points
2021-02-02T17:00:37.963+00:00

The basic documentation for availability groups where the secondary replica uses an asynchronous commit method states that there may be data loss which is further explained in the documentation. Is there a technique that can be leverage to guarantee no data loss for replicas using an asynchronous commit method? While the transactions don't need to be committed instantaneously, I just need to know that the transactions will eventually appear on the replica.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,850 questions
Accepted answer
  1. Sean Gallardy - MSFT 1,886 Reputation points Microsoft Employee
    2021-02-02T17:41:16.273+00:00

    Is there a technique that can be leverage to guarantee no data loss for replicas using an asynchronous commit method?

    No, there is no guarantee.

    While the transactions don't need to be committed instantaneously, I just need to know that the transactions will eventually appear on the replica.

    They will eventually make their way over. You can create something to check the DMVs if you wish, to compare and see. However, since an issue could strike at any time, there will be data loss as this is not a commit partner.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-02-03T03:58:38.287+00:00

    Hi @Estate Master ,

    > Is there a technique that can be leverage to guarantee no data loss for replicas using an asynchronous commit method?

    No, there is no guarantee. In asynchronous data commit mode, the primary replica send the transaction confirmation to the client without waiting for any of the secondary replicas to harden the log. The secondary replicas have data latency with primary replica. It will has the risk of possible data loss. Suggest you read the blog to get the detail information about understanding data synchronization in Asynchronous data commits mode VS Synchronous data commits mode.

    If the response is helpful, please click "Accept Answer", thank you.

    0 comments