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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.