Share via

  • Article

Data Is Not Being Delivered to Subscribers

If it appears that data is not being delivered to Subscribers, there are two broad reasons:

  • The data is not being applied due to filtering, an agent issue, or another replication error.

  • The data is being deleted at the Subscriber after it has been applied.

Explanation

There are a number of possible causes for data not being delivered to Subscribers:

  • The table is filtered, and there are no changes to deliver to a given Subscriber.

  • One or more agents are not running or are failing with an error.

  • A transactional subscription was initialized without a snapshot, and changes have occurred on the Publisher since the publication was created.

  • Replication of stored procedure execution for a transactional publication produces different results at the Subscriber.

  • The INSERT stored procedure used by a transactional article includes a condition that is not met.

  • Data is deleted by a user, a replication script, or another application.

  • Data is deleted by a trigger, or a trigger includes a ROLLBACK statement.

User Action

Before attempting to diagnose why data is not being delivered to Subscribers, we recommend that you use validation or the tablediff utility to verify that rows are missing:

  • 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 data is missing by running the tablediff utility. For information about using this utility on replicated tables, see How to: Compare Replicated Tables for Differences (Replication Programming).

Addressing the Cause of the Missing Data

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

  • The table is filtered, and there are no changes to deliver to a given Subscriber.

    It is possible that the rows missing at the Subscriber were not replicated because they do not meet the filtering criteria for the publication. All types of replication support static filters, and merge replication also supports parameterized filters and join filters. For more information, see Filtering Published Data. If one or more articles in the publication are filtered, execute the following procedures, and verify the value of the filter clause:

    Use the filter clause to determine whether any of the missing rows meet the filtering criteria. For example, you could execute the filter clause against the table at the Publisher and determine whether the data returned matches the data at the Subscriber.

  • One or more agents are not running or are failing with an error:

  • A transactional subscription was initialized without a snapshot, and changes have occurred on the Publisher since the publication was created:

    • If you enable a publication to be initialized from a backup, changes to published tables are tracked in the publication database log as soon as the publication is created. When a subscription is initialized, pending changes are delivered to the Subscriber as long as they are still available in the distribution database.

    • Unlike initializing from a backup, if you initialize a subscription using the replication support only option, you or your application must ensure that the data and schema are properly synchronized at the time you add the subscription. If, for example, there is activity on the Publisher between the time data and schema are copied to the Subscriber and the time at which the subscription is added, changes resulting from this activity might not be replicated to the Subscriber.

    For more information, see Initializing a Transactional Subscription Without a Snapshot.

  • Replication of stored procedure execution for a 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.

  • The INSERT stored procedure used by a transactional article includes a condition that is not met.

    By default, transactional replication uses a set of stored procedures to propagate changes to Subscribers. You can also customize these procedures to include business logic required by your application. For more information, see Specifying How Changes Are Propagated for Transactional Articles. If the INSERT stored procedure includes a condition in its logic that is not met, the insert does not occur. Consider a procedure that is customized to check for a certain value in a table (Table A) at the Subscriber before allowing an insert into another table (Table B). If the value is not available in Table A because of an error or because data has not yet been replicated to this table, the expected row is missing from Table B.

  • Data is being deleted by a user, a replication script, or another application:

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

    • If you want to prevent users from 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 AdventureWorks2008R2;
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.

    • Replication allows you to execute scripts before and after the snapshot is applied and during synchronization. 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 delete data at a Subscriber that should be treated as read-only, the administrator must ensure that non-convergence does not result.

  • Data is being deleted by a trigger, or a trigger includes a ROLLBACK statement.

    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. 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.

See Also

Concepts

Troubleshooting (Replication)