Data at the Publisher and Subscriber Do Not Match

Data at the Publisher and Subscriber is considered non-convergent (in other words the data does not match) if:

• There are a different number of rows at the Subscriber than at the Publisher, and the publication is not filtered. If the publication is filtered, it might be expected that the number of rows differs.
• The data in one or more rows is different in content at the Publisher and Subscriber.

Explanation

Data at the Publisher and Subscriber can be non-convergent for a number of reasons:

• Data is updated at a Subscriber that should be treated as read-only. The subscription database should be treated as read-only unless you are using merge replication, transactional replication with updatable subscriptions, or peer-to-peer transactional replication.
• Triggers are used at the Subscriber. Triggers can modify data at the Subscriber and also prevent the data from being updated if the trigger issues a ROLLBACK.
• Scripts are executed by replication at the Subscriber but not at the Publisher.
• Replication of stored procedure execution for a transactional publication produces different results at the Subscriber.
• Constraint violations or other issues prevent rows from being inserted, updated, or deleted at the Subscriber.

User Action

The following actions describe how to determine if data is non-convergent and how to bring it into convergence:

1. Determine if data is non-convergent using validation or the tablediff utility:
   • If the Distribution Agent or Merge Agent is able to run, determine whether data is missing by running binary checksum validation. You can also use row count validation, but this method does not reveal differences in the contents of the data. For more information, see Validating Replicated Data.
   • If the Distribution Agent or Merge Agent cannot run, determine whether the data is non-convergent by running the tablediff utility. For information about using this utility on replicated tables, see How to: Compare Replicated Tables for Differences (Replication Programming).
2. If the data is non-convergent, you can use the tablediff utility to generate a Transact-SQL script to bring the data into convergence. For more information, see tablediff Utility.

Addressing the Cause of the Non-Convergence

The following actions address the causes listed in the "Explanation" section:

• Data is updated at the Subscriber outside of replication:

  • If you want to allow users to insert, update, and delete data at the Subscriber, use merge replication, transactional replication with updatable subscriptions, or peer-to-peer transactional replication. For more information, see Merge Replication Overview and Publication Types for Transactional Replication.

  • If you want to prevent users from inserting, updating, and deleting data at the Subscriber, create a trigger for each table that contains the word ROLLBACK and uses the NOT FOR REPLICATION option (which prevents the trigger from firing when a replication agent performs an operation). For example:

    USE AdventureWorks
    GO
    CREATE TRIGGER prevent_user_dml
    ON Person.Address
    FOR INSERT, UPDATE, DELETE
    NOT FOR REPLICATION
    AS
    ROLLBACK
    

    For more information, see CREATE TRIGGER (Transact-SQL) and Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION.

• Triggers are used at the Subscriber. Triggers at the Subscriber must be managed properly so that they do not cause non-convergence or other issues:
  • Triggers should only cause data changes at a Subscriber if you use merge replication, transactional replication with updatable subscriptions, or peer-to-peer transactional replication. For more information, see Merge Replication Overview and Publication Types for Transactional Replication.
  • In many cases, triggers should use the NOT FOR REPLICATION option. Consider a trigger that inserts data into a tracking table: when the user inserts the row originally, it is appropriate for the trigger to fire and enter a row into the tracking table, but the trigger should not fire when that data is replicated to the Subscriber, because it would result in an unnecessary row being inserted in the tracking table.
    If a trigger includes a ROLLBACK statement and the trigger does not use the NOT FOR REPLICATION option, rows that were replicated to a Subscriber might not be applied.
  • For transactional replication, there are additional considerations regarding the XACT_ABORT setting and using COMMIT and ROLLBACK statements in a trigger. For more information, see the "Triggers" section of Considerations for Transactional Replication.
• Scripts are executed by replication at the Subscriber but not at the Publisher.
  The @pre_snapshot_script and @post_snapshot_script parameters of sp_addpublication and sp_addmergepublication allow you to specify scripts to run before and after the snapshot is applied. For more information, see Executing Scripts Before and After the Snapshot Is Applied. The stored procedure sp_addscriptexec allows you to execute a script during the synchronization process. For more information, see How to: Execute Scripts During Synchronization (Replication Transact-SQL Programming).
  These scripts are typically used for administrative tasks, such as adding logins at the Subscriber. If the scripts are used to update data at a Subscriber that should be treated as read-only, the administrator must ensure that non-convergence does not result.
• Replication of stored procedure execution for transactional publication produces different results at the Subscriber.
  If you replicate the execution of a stored procedure, the procedure definition is replicated to the Subscriber when the subscription is initialized; when the procedure is executed at the Publisher, replication executes the corresponding procedure at the Subscriber. For more information, see Publishing Stored Procedure Execution in Transactional Replication.
  If the stored procedure performs a different action at the Subscriber or acts on different data than at the Publisher, non-convergence can occur. Consider a procedure that performs a calculation and then inserts data based on this calculation. If the Subscriber is filtered such that the calculation at the Subscriber is based on different data, the result inserted at the Subscriber could be different or the insert might not occur at all.
• Constraint violations or other issues prevent rows from being inserted, updated, or deleted at the Subscriber.
  For transactional replication, constraint violations are treated as errors; by default they cause the Distribution Agent to stop synchronizing if they are encountered (for information about skipping these errors, see Skipping Errors in Transactional Replication). For merge replication, constraint violations are treated as conflicts; they are logged, but they do not cause the Merge Agent to stop synchronizing. For both types of replication, constraint violations can lead to non-convergence if an insert, update, or delete that succeeded at one node does not succeed at another.
  When a table is published, the default schema options specify that foreign key constraints and check constraints should be created in the subscription database with the NOT FOR REPLICATION option set. If your application requires different settings for constraints, change the schema options. For more information, see How to: Specify Schema Options (SQL Server Management Studio) and How to: Specify Schema Options (Replication Transact-SQL Programming).

See Also

Concepts

Troubleshooting Replication

Help and Information

Getting SQL Server 2005 Assistance