How to add a new table in SQL Server primary 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 LEARNINGPROCTEST01 was established on the database Learningproc, and the objects to be published were the tables student and course, which were subscribed to on the database test on the other server. Now as demand changes, a table needs to be added TestTable

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.

EXECsp_changepublication@publication = 'RPL_TTT',@property = 'allow_anonymous' ,@value = 'false'GOEXECsp_changepublication@publication = 'RPL_TTT',@property = 'immediate_sync' ,@value = 'false'GO 

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

3: On the release "learningProcTest01", 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. Just change the false to True, execute the code on the line, do not enable the problem is not very large.

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