How to add a new table in SQL SERVER transactional replication How to not initialize the entire snapshot

In SQL Server replication (Replication), it is possible that the need for a new table or some tables to an existing replication (publish subscription) may occur due to changes in business requirements, which should be normal and common. However, in the existing replication (publish subscription) to add new tables/articles, often need to re-initialize the entire snapshot, although it is simple, but often in the actual application of a number of problems, for example, publish a subscription table is more, the amount of data is relatively large, then the reinitialization of the snapshot often takes a long time, affect the normal operation of the system. In addition, this will increase the load on the server and affect the network bandwidth.

Can you create a new table/article without initializing the entire snapshot, but simply initializing the new table? Of course, here is a small case to tell you how to do it:

A publication named RPL_TTT was established on the database TTT, and the objects to be published are the tables Wipnotoreed and Wipnotostarch, which are subscribed to on the database test on the other server. Now as demand changes, a table needs to be added Wipnotoloosen

We can follow the following three steps, as follows:

1: Use sp_helppublication to view information about the publication, as shown below, and you will see that both the ' immediate_sync ' and ' allow_anonymous ' properties are enabled by default. We first need to disable these two properties.

allow_anonymous Indicates whether anonymous subscriptions are allowed for publications.

Immediate_sync Indicates whether synchronization files are created or recreated each time the Snapshot Agent runs.

 Use TTT;

Go

sp_helppublication;

Or

 Use TTT;

Go

exec ' rpl_ttt ';

 on Database

 Use TTT;

Go

EXEC sp_changepublication

' rpl_ttt ',

' allow_anonymous ' ,

@value' false '

GO

EXEC sp_changepublication

' rpl_ttt ',

' Immediate_sync ' ,

@value' false '

GO

2: Find "RPL_TTT" in the local release, then add the table Wipnotoloosen in the "project", of course you can also use the sp_addarticle command. As shown below:

3: On the release "Rpl_ttt", right click on "View Snapshot Agent Status", select "Start" in the popup window, you will find that the snapshot will only initialize one table instead of the entire three tables.

After the command is completed, you will find that the table's subscription_status has changed to 2, as shown below

As for the completion, both the ' immediate_sync ' and ' allow_anonymous ' properties are very simple and easy if you want to start. Not enabled is not a big problem.

How to add a new table in SQL SERVER transactional replication How to not initialize the entire snapshot