SQL Server Transactional Replication is a powerful tool that can address many challenges in your environment. Setting up Replication is not difficult, but it can be time-consuming, depending on the amount of data you need to replicate. This is why some people dread having to add tables to Replication once it has been set up and is running in Production. The assumption is sometimes that you will need to re-initialize and generate a full new snapshot, and many people do not have the time for that. However, this is a misconception that can be easily handled with 2 simple statements. Adding new tables may just be simpler than you thought!
Before making any changes to your Publication, run these two statements:
exec sp_changepublication
@publication = N'PublicationName',
@property = N'allow_anonymous',
@value = 'false';
exec sp_changepublication
@publication= N'PublicationName',
@property = N'immediate_sync',
@value = 'false';The immediate_sync property is set to True by default, and relates to how long transactions are kept after being delivered, according to your retention settings. When set to True, every time a new article is added, the Snapshot Agent will generate a snapshot for all articles within that Publication. Setting this property to False will ensure that a Snapshot will be generated for only the new article you are adding.
The allow_anonymous property is also set to True by default, and determines whether anonymous subscriptions can be created for the publication. When this property is set to True, immediate_sync must also be set to True.
After executing these statements, you can now go to your Publication and add the new article as you normally would. Once you have added the new article, right-click on the Publication and choose “View Snapshot Agent Status”. Click the Start button to start the Snapshot Agent. When the Snapshot is complete, you will see this message:
Once you have verified that your new article is being replicated, you can change these settings back to the default by running these commands:
exec sp_changepublication
@publication= N'PublicationName',
@property = N'immediate_sync',
@value = 'true';
exec sp_changepublication
@publication = N'PublicationName',
@property = N'allow_anonymous',
@value = 'true';And that’s it! You have just added a new article with little time and overhead. This process will enable you to add new articles as needed, with no downtime and no waiting for full Snapshots to be generated.