How to Add Tables to an Existing Publication and Syncing Without Reinitializing the Subscription in SQL Server Replication
Problem:
When I add a new table to the existing publication, I had to reinitialize the subscription. This creates a snapshot for all the articles present in the subscription instead of only the newly added tables. This is a problem if the size of the existing tables is huge.
I want to create a snapshot only for the new tables and not all the existing tables present in the subscription.
Solution:
-
The below publication property must be false to be able to achieve our requirement.
a.immediate_sync
= false
b.allow_anonymous
= falseYou can check these properties either in the publisher database or in the distribution database. Execute the below query to check the same.
If either of the property has a value as true (1), then change the respective publication property to false. Execute the below script in the publisher database.
- You need to add the tables to the publication. You can do it either through the UI or by executing the below script in the publisher database.
You can verify that the table is added to the publication using the script below.
- After adding a new table to the publication, add a Subscription by executing the below script.
Execute the below script in the publisher database to verify that subscription to the new table is present. The subscription_status
property will be there as 1 as we just added the subscription but it is not yet actively synchronizing.
- Start the snapshot agent to generate the snapshot of the newly added table.
You can check the snapshot folder to verify the snapshot is generated only for the new table added to the publication.
- If the distribution agent job is not running, start the job. If it is already running, you can check the distribution agentβs job history. If the snapshot agent is completed, the distribution agent will automatically synchronize the new table.
- You can verify the newly added table and data are present in the subscriber server. Compare the row counts of the newly added table between the publisher and subscriber server to confirm.
Thanks for reading this far and I hope this article is helpful for you.
You may also like other articles on SQL replication below.
https://blog.rajanand.org/sql-server-replication-queries
https://blog.rajanand.org/how-to-find-which-command-has-failed-in-sql-server-replication