When replicating data to a target SQL Server database that has identity columns, triggers, and cascade and check constraints, consider the following:

• For columns that have an identity column, Replicat sets the IDENTITY_INSERT ON for the table, which may reduce delivery performance.

• For tables that have triggers or cascade constraints, execution of the trigger or cascade operation may result in a Replicat error if the Replicat is configured to deliver the same data that a trigger will insert or cascade constraint will update or delete.

  For example, TableA on the source has a trigger that inserts a record into TableB. The Extract is configured to capture records for both TableA and TableB. On the target, the Replicat will first insert a record for TableA, then the trigger for TableA fires and inserts into TableB, followed by the Replicat attempting to insert the same record into TableB, which will result in a Replicat error.

• Check any foreign key constraints are also enforced, which may reduce delivery performance.

• For tables on the target database that have triggers, set the SET XACT_ABORT parameter to off. This ensures that execution of the trigger operation does not result in missing transactions.

To overcome these situations, there are several options that can be implemented based on the replication use case.

• For unidirectional implementations where a Replicat is the only process writing data to the target tables, consider the following options for Identity columns, triggers and constraints on the target tables.

  • Disable or drop the Identity property, triggers and constraints on the target tables.

  • Modify the identity property, triggers and constraints and set the NOT FOR REPLICATION option on for each and ensure that the Microsoft ODBC driver is at least version 17.8.1.

• For multi-directional implementations where both a Replicat and application write data to the target tables, and triggers and constraints are enabled, modify the Identity property, triggers and constraints and set the NOT FOR REPLICATION option on for each and ensure that the Microsoft ODBC driver is at least version 17.8.1.

  Additionally, to use IDENTITY columns in a multi-directional replication configuration, define the IDENTITY columns to have an increment value equal to the number of servers in the configuration, with a different seed value for each one.

  For example, a three-database configuration would be as follows:

  Database1 set the seed value at 0 with an increment of 3.

  Database2 set the seed value at 1 with an increment of 3.

  Database3 set the seed value at 2 with an increment of 3.