Synchronizing Data
Synchronizing data refers to the process of data being propagated between Publisher and Subscribers after the initial snapshot has been applied at the Subscriber. When a subscription is synchronized, different processes occur depending on the type of replication you are using and whether the subscription has been marked for reinitialization.
For snapshot replication, synchronize means to reapply the snapshot at the Subscriber so that schema and data at the subscription database is consistent with the publication database. For transactional replication, synchronizing data means that data updates, inserts, deletes, and other modifications are distributed between Publisher and Subscribers. For merge replication, synchronization means that data updates made at multiple sites are merged, conflicts (if any) are detected and resolved, and data eventually converges to the same values.
The Distribution Agent and the Merge Agent move changes to data that occur at the Publisher or at Subscribers. For consistency, Microsoft® SQL Server™ 2000 replication uses the term synchronize to refer to when one of these replication agent runs.
Snapshot Replication Synchronization
When a subscription to a snapshot publication is synchronized, the Distribution Agent (using distrib.exe or the Distribution ActiveX® Control) runs and the most recent snapshot will be applied at the Subscriber. If modifications to data have been made, a new snapshot will need to be generated before the new data can be applied to the Subscriber.
Transactional Replication Synchronization
When a subscription to a transactional publication is synchronized, the Distribution Agent (using distrib.exe or the Distribution ActiveX Control) runs and UPDATE, INSERT and DELETE statements that have been logged at the Distributor are propagated to the Subscriber.
If the subscription has been marked for reinitialization, the Snapshot Agent and Distribution Agent must run so that a new snapshot is generated and propagated to Subscribers.
Merge Replication Synchronization
Synchronization occurs when Publishers and Subscribers in a merge replication topology reconnect using the Merge Agent (replmerg.exe or the Merge ActiveX Control) and updates are propagated between sites, and if necessary, conflicts detected and resolved. At the time of synchronization, the Merge Agent sends all changed data to the other sites. Data flows from the originator of the change to the sites that need to be updated or synchronized.
At the destination database, updates propagated from other sites are merged with existing values according to extensible and flexible conflict detection and resolution. A Merge Agent evaluates the arriving and current data values, and any conflicts between new and old values are resolved automatically based on the default resolver (a resolver you specified when creating the publication or a custom resolver).
Changed data values are replicated to other sites and converged with changes made at those sites only when synchronization occurs. Synchronizations can occur minutes, days, or even weeks apart. Data is converged and all sites eventually end up with the same data values. However, if conflicts were detected and resolved, it means that work that was committed by some users was altered or undone to resolve the conflict according to your defined policies.
Synchronizing Schema Changes
Microsoft® SQL Server™ 2000 supports limited schema changes to an existing publication database. You can add columns to and drop columns from a published table without dropping and re-creating the publications and subscriptions referencing that table.
Replication of schema changes is supported for snapshot replication, transactional replication, and merge replication. Column additions and deletions are implemented at the table level and propagated to all Subscribers that receive data from that table.
For more information, see Schema Changes on Publication Databases.
On Demand Script Execution
On demand script execution allows you to post a SQL script, and then during the distribution or merge process, the script can be executed at all Subscribers to a specific publication.
On demand script execution is available for snapshot replication, transactional replication, and merge replication.
To specify a script to run for all Subscribers to a merge publication, execute sp_addreplmerge_script. The next time the Merge Agent runs, the script will execute at each Subscriber.
To specify a script to run for all Subscribers to a snapshot or transactional publication, execute sp_addscriptexec. The next time the Distribution Agent runs, the script will execute at each Subscriber.
The following parameters need to be specified when executing either sp_addscriptexec or sp_addreplmerge_script.
| Parameter | Data Type | Description |
| @publication | sysname | Specifies a valid publication. Required. No default. |
| @scriptfile | nvarchar(8000) | Specifies the UNC path where the SQL script is located. Required. No default. |
On demand script execution copies the script to the replication working directory and then uses osql.exe to apply the script at the Subscriber. If there is a failure when applying the script for snapshot or transactional publications, the Distribution Agent will stop. The sp_addscriptexec system stored procedure has an additional parameter, @SkipError, to specify whether the Distribution Agent should stop if an error is encountered (@SkipError = 0) or if the error should be logged and the Distribution Agent should continue (@SkipError = 1).
To synchronize a push or pull subscription