Replication Reinitialization from backup

Saran 26 Reputation points
2024-03-26T12:16:08.5233333+00:00

I have Reinitialization transactional replication of 950 GB database from backup. It is working fine.

I left allow_anonymous & immediate_sync 1 i.e true. I plan to keep it as true.

Is that fine or do I need to change to false. I have tried to put false, it is making lot of blocking in the published database.

Please advise.

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,715 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. MikeyQiao-MSFT 485 Reputation points Microsoft Vendor
    2024-03-27T06:53:12.49+00:00

    Hi @Saran

    This is Description of the tow Property:

    allow_anonymous：

    **True：**Anonymous subscriptions can be created for the given publication, and immediate_sync must also be true. Cannot be changed for peer-to-peer publications.

    **False：**Anonymous subscriptions cannot be created for the given publication. Cannot be changed for peer-to-peer publications.

    immediate_sync：

    **True：**Synchronization files for the publication are created or re-created each time the Snapshot Agent runs. Subscribers are able to receive the synchronization files immediately after the subscription if the Snapshot Agent has been completed once before the subscription. New subscriptions get the newest synchronization files generated by the most recent execution of the Snapshot Agent. independent_agent must also be true.

    **False：**Synchronization files are created only if there are new subscriptions. Subscribers cannot receive the synchronization files after the subscription until the Snapshot Agent is started and completes.

    Impact of immediate sync：

    When immediate sync is on, all changes in the log are tracked immediately after the initial snapshot is generated even if there are no subscriptions. Logged changes are used when a customer is using backup to add a new peer node. After the backup is restored, the peer is synched with any other changes occurring after the backup was taken. Since the commands are tracked in the distribution database, the synchronization logic can look at the last backed up LSN and use this as a starting point, knowing that the command is available if the backup was taken within the max retention period. (The default value for the min retention period is 0 hrs and max retention period is 24 hrs.)

    When immediate sync is off, changes are kept at least the min retention period and cleaned up immediately for all the transactions that are already replicated. If immediate sync is off and configured with the default retention period, it is likely that the required changes after the backup was taken were cleaned up and the new peer node will not be initialized properly. The only option left is to quiesce the topology. Setting immediate sync to on provides greater flexibility and is the recommended setting for P2P replication.

    Refer to this.

    About the issue "it is making lot of blocking in the published database." when have tried to put false.

    It is possible that you switched immediate_sync from true to false, which caused the issue.The previous Snapshot files can no longer be used to reinit subscribers. A new snapshot was required to be generated.

    Refer to this artcile.

    If you want to monitor transaction replication between different databases, you can refer to this article.

    Best regards,

    Mikey Qiao

     

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments
  2. Saran 26 Reputation points
    2024-03-28T12:25:24.13+00:00

    Thanks, My question is very simple - Can I keep allow_anonymous & immediate_sync 1 i.e true or ON.

    Will it affect anything?