SQL Server 2008 synchronous replication creates new tables/functions/stored procedures (without reinitialization of snapshots)

SQL Server 2008 synchronous replication creates new tables/functions/stored procedures (without reinitialization of snapshots)

One, in the production environment already has the transaction replication (the replication type is the transactional publication), needs the existing published database to add the table, the view, the stored procedure and so on, these changes are not synchronized to from the library. If you have to apply to the slave library, there are two ways to do this:

1. If you use the default settings, you need to reinitialize the snapshot each time, reapply the snapshot from the library, and the unsynchronized synchronization commands, which are poorly performed on the database pressure or performance or DBA maintainability in the production environment.

2, new schema changes to create a new publication subscription, but will cause maintenance difficulties, increase the chance of error.

3, you can set immediate_sync and allow_anonymous to achieve the premise of not re-initializing the snapshot, in the original publication subscription to add table/function/stored procedures.

Immediate_sync: Specifies whether to create a synchronization file 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.

# #注意: When Immediate_sync is false from the definition, new items (tables, stored procedures, and so on) can be published incrementally, and Allow_anonymous must be false,immediate_sync to be false.

# #同时如果删除发布项目的中已发布的表/function/stored procedure/field the corresponding Table/function/stored procedure/field in the subscription library is still retained, such as to be removed must be manually operated

Second, according to the above definition to test

1, the sample Library is platform1126 (publication database), the subscription database is TDB1

# #注意: Here's how to create a copy operation for a transactional publication (all tables in a transactional publication require a primary key column.) ), you can self-Baidu, or refer to the following links: http://blog.csdn.net/hliq5399/article/details/51678774

2. Create a transactional publication with the publication name: Pl_tdb, on the publication database (platform1126), view the current default properties for Immediate_sync, Allow_anonymous, and both properties.

650) this.width=650; "title=" t01.jpg "src=" https://s1.51cto.com/wyfs02/M00/9C/D6/wKioL1l23YuT_ Fjeaachmmm-hnq052.jpg-wh_500x0-wm_3-wmp_4-s_947267168.jpg "alt=" Wkiol1l23yut_fjeaachmmm-hnq052.jpg-wh_50 "/>

use platform1126sp_helppublication; or: Select immediate_sync  ,allow_anonymous from dbo .syspublications The default result is:immediate_sync      allow_anonymous1                   1# Modify Immediate_sync, allow_anonymous parameter is False or 0:use platform1126; goexec sp_changepublication@publication =  ' Pl_tdb ',  #publication为发布名称 @property  =   ' allow_anonymous ', @value  =  ' false ' goexec sp_changepublication@publication =  ' pl _tdb ', @property  =  ' immediate_sync '  , @value  =  ' false '    go# Recheck its settings results select immediate_sync ,allow_anonymous from dbo .syspublications The default result is: immediate_sync    allow_anonymous0                   0 
 

3. In the platform1126 publication database, re-add the newly released item Advertise table

650) this.width=650; "title=" t03.jpg "src=" https://s5.51cto.com/wyfs02/M02/9C/D6/ Wkiol1l23asgismjaabz3qssrcg256.jpg-wh_500x0-wm_3-wmp_4-s_2104857226.jpg "alt=" Wkiol1l23asgismjaabz3qssrcg256.jpg-wh_50 "/>

4. Start Replication Monitor and start the Snapshot Agent and view the details

650) this.width=650; "title=" t04.jpg "style=" Float:none "src=" https://s3.51cto.com/wyfs02/M00/9C/D7/ Wkiol1l23brghwojaabxj0z0vqa322.jpg-wh_500x0-wm_3-wmp_4-s_1618807883.jpg "alt=" Wkiol1l23brghwojaabxj0z0vqa322.jpg-wh_50 "/>

650) this.width=650; "title=" t05.jpg "style=" Float:none "src=" https://s3.51cto.com/wyfs02/M01/9C/D7/ Wkiom1l23bvrvus_aaf5sqoqcci686.jpg-wh_500x0-wm_3-wmp_4-s_1349504122.jpg "alt=" WKiom1l23bvRvUs_ Aaf5sqoqcci686.jpg-wh_50 "/>

5, check the subscription library TDB1, whether the advertise table has been synchronized

650) this.width=650; "title=" t06.jpg "src=" https://s3.51cto.com/wyfs02/M01/9C/D7/ Wkiol1l23dxjjrq3aaaxro0erc8442.jpg-wh_500x0-wm_3-wmp_4-s_4242912202.jpg "alt=" Wkiol1l23dxjjrq3aaaxro0erc8442.jpg-wh_50 "/>

# #注: New functions/stored procedures like above, new fields are executed directly in the publishing library

This article is from the "10,000-hour Law" blog, be sure to keep this source http://daisywei.blog.51cto.com/7837970/1950760

SQL Server 2008 replication creates new tables/functions/stored procedures (without reinitialization of snapshots)