Shulou(Shulou.com)06/01 Report--

New tables / functions / stored procedures added after SQLserver 2008 synchronous replication is created (no reinitialization of snapshots)

First, in the existing transaction replication in the production environment (the replication type is transaction release), it is necessary to add new tables, views, stored procedures, etc., to the published database, and these changes will not be synchronized to the slave database. If you must apply to the slave library, there are two ways:

1. If you use the default settings, you need to reinitialize snapshots every time, reapply snapshots and unexecuted synchronization commands from the library, which performs poorly on database pressure or performance or DBA maintainability in a production environment.

2. Create a new publish subscription for the newly added schema changes, but it will cause maintenance difficulties and increase the probability of errors.

3. You can add tables / functions / stored procedures to the original publish and subscribe without reinitializing the snapshot by setting immediate_sync and allow_anonymous.

Immediate_sync: specifies whether to create synchronization files for the publication each time the Snapshot Agent is run.

The data type of immediate_synchronization is nvarchar (5) and the default value is FALSE. If True, the synchronization file is created or recreated each time the Snapshot Agent is run. If the snapshot agent completes before the subscription is created, the subscriber can get the synchronization file immediately. The new subscription gets the latest synchronization file generated by the last execution of the snapshot agent. Independent_agent must be true so that immediate_synchronization is true. If False, the synchronization file is created only if there is a new subscription. When you incrementally add a new article to an existing publication, you must call sp_addsubscription for each subscription. The subscriber 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 data type of allow_anonymous is nvarchar (5) and the default value is FALSE. If True, immediate_synchronization must also be set to True. If False, anonymous subscriptions to the publication are not allowed.

# # Note: it can be seen from the definition that when immediate_sync is false, new projects (tables, stored procedures, etc.) can be published incrementally, and allow_anonymous must be false,immediate_sync in order to be false.

# # at the same time, if you delete the published table / function / stored procedure / field subscription library of the published project, the corresponding table / function / stored procedure / field subscription library is still retained, if you want to delete it, you must do it manually

Second, test according to the above definition

1. The sample library is platform1126 (publication database), and the subscription database is TDB1

# # Note: here how to create a replication operation for a transactional publication (all tables in a transactional publication require primary key columns.) , you can use Baidu on your own, or refer to the following link: http://blog.csdn.net/hliq5399/article/details/51678774

2. Create a transactional publication named pl_tdb, and view the current default attributes of immediate_sync and allow_anonymous on the publication database (platform1126).

USE platform1126sp_helppublication; or: select immediate_sync, allow_anonymous from dbo .syspublications check its default result is: immediate_sync allow_anonymous1 changes immediate_sync, allow_anonymous parameter to false or 0:use platform1126 GOEXEC sp_changepublication@publication = 'pl_tdb', # publication is the release name @ property =' allow_anonymous',@value = 'false'GOEXEC sp_changepublication@publication =' pl_tdb',@property = 'immediate_sync', @ value =' false'GO # re-check its setting result select immediate_sync, allow_anonymous from dbo. Syspublications check its default result: immediate_sync allow_anonymous0 0

3. Re-add the Advertise table of the new release project in the platform1126 publication database

4. Start replication Monitor, start Snapshot Agent, and view the details

5. Check the subscription library TDB1 to see if the Advertise table has been synchronized.

# # Note: the new function / stored procedure is similar to the above operation, and the new fields can be directly executed in the publishing library.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.