Add a table without re-initializing the snapshot after SQL Server synchronizes

Source: Internet
Author: User

        In an existing transactional replication, the time-length needs to add tables, indexes, and these changes are not synchronized to the from library. If you take the default settings, you need to reinitialize the snapshot each time, reapply the snapshot from the library and the unsynchronized synchronization commands, which is obviously not a way to practice online. Another option is to create a new publish subscription with new schema changes, but it can cause maintenance difficulties and increase the chance of errors.         You can set immediate_sync and allow_anonymous to create a new table without having to reinitialize the snapshot.         First look at the meaning of the two parameters:       immediate_sync: Specifies whether synchronization files are created for the publication each time the Snapshot Agent runs. The immediate_synchronization data type is nvarchar (5) and the default value is FALSE. True indicates that synchronization files are created or recreated each time the Snapshot Agent is run. If the Snapshot Agent completes before the subscription is created, the Subscriber can obtain the synchronization files immediately. The new subscription Gets the latest synchronization file generated by the most recent execution of the Snapshot Agent. Independent_agent must be true in order for Immediate_synchronization to be true. If False, the synchronization file is created only if there is a new subscription. When adding new items to an existing publication incrementally, you must call sp_addsubscription for each subscription. Subscribers cannot receive synchronization files after subscription until the Snapshot Agent is started and completed.        allow_anonymous: Specifies whether anonymous subscriptions can be created for a given publication. The allow_anonymous data type is nvarchar (5) and the default value is FALSE. If true, the immediate_synchronization must also be set to true. False indicates that anonymous subscriptions are not allowed to be created for the publication.         from the definition, when Immediate_sync is false, new items (tables, stored procedures, and so on) can be published incrementally, and allow_anonymous must be false. Immediate_sync to be false. Case, create a database Masterdb, create a table t: 
The subscription is named Repl_master, and the subscription database Slavedb. View the settings for both properties of an existing database, which are now set to 1
 Use msterdbsp_helppublication; or: Select  from dbo. syspublications

modify these two parameters to 0: UseMsterdb;GoEXECsp_changepublication@publication = 'Repl_master',@property = 'allow_anonymous' ,@value = 'false'GOEXECsp_changepublication@publication = 'Repl_master',@property = 'immediate_sync' ,@value = 'false'GO

  Create a new table and publish it  create  table  T1 (id int , NAME varchar  ( Span style= "color: #800000; Font-weight:bold; " >10 ), constraint  pk_t1 primary  Span style= "color: #0000ff;" >key   (ID)  insert  into  t1 1 ,  2 , 2 ), ( 3 , 3 )

Tick the new table in the project that publishes the properties:

Start the agent in Replication Monitor and see

Log generation Status:

View from the library, the table T1 is already synchronized.

Deleting the synchronization is simple, just delete the item in the published project, but the table remains in the subscription database and requires a manual drop

Add a table without re-initializing the snapshot after SQL Server synchronizes

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.