SQL Server Always-on : taking database snapshot before resume data movement.

sakuraime 2,321 Reputation points
2021-08-14T02:56:44.183+00:00

Suppose I have 3 nodes AAG , 1 ASYNC.
after failover to the ASYNC node , the former sync mode need to be resume data movement , before resume the data movement , how can I protect the former primary database by taking a backup first ? I see from the internet which we need to use database snapshot ? any other method ?

and at the moment I will need to force failed over to an async node , how can I determine the amount of data loss (by seconds , or by how much MB/GB) , given that we loss contact of the original AG primary anymore .

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,886 questions
{count} votes

2 answers

Sort by: Most helpful
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-08-27T09:32:44.663+00:00

    Hi @sakuraime ,

    > I see from the internet which we need to use database snapshot ?

    Yes. Quote from MS document

    After a forced failover, all secondary databases are suspended. This includes the former primary databases, after the former primary replica comes back online and discovers that it is now a secondary replica. You must manually resume each suspended database individually on each secondary replica.

    When a secondary database is resumed, it initiates data synchronization with the corresponding primary database. The secondary database rolls back any log records that were never committed on the new primary database. Therefore, if you are concerned about possible data loss on the post-failover primary databases, you should attempt to create a database snapshot on the suspended databases on one of the synchronous-commit secondary databases.


  2. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-09-07T08:04:24.81+00:00

    Hi @sakuraime ,

    Create a database snapshot on synchronous-commit secondary databases in AG.

    To create a database snapshot

    Refer to Create a Database Snapshot (Transact-SQL)


    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

    0 comments No comments