Merge Replication identity field issues

Question

One of our clients is using our software with merge replication of a database on a SQL Server 2008 R2 machine. There are two production environments in separate geographical locations only one of which is live at any one time, so basically one and live one on standby. Only the live database is updated by teh applications. Every couple of months they failover between the datacentres and the standby environment become the live centre. There is an instance of SQL Server 2008 in each datacentre and merge replication is used to keep them in sync. This was all working ok until the beginning of the year when we started getting replication errors with some lTID columns in various tables that have the Identity property set.

The errors were like this one:

The insert failed. It conflicted with an identity range check constraint in database 'GateMain', replicated table 'dbo.tGateCalcsLog', column 'lTID'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.

Then after the last failover we noticed we had an issue with the lTID values in one specific table. Our application relies on the lTID value always having incremented in order such that the highest lTID value is always the newest entry in the table. We've found that due to how the identity ranges are being managed by replication that when the system is failed over that the lTID range of the now live database server may have a range of values that are lower than those already present in the table. Is there a way to manage this in merge replication so we can guarantee that the next identity value allocated to the lTID column in greater than any lTID currently in the table? Or do we need to use a different type of replication or possibly mirroring?

Answer 1

You could turn off automatic identity management and allocate your ranges manually. Another option could be forcing a range re-allocation by issuing sp_msrefresh_publisher_idrange and specifying the range boundaries.

However, both options would not guarantee that the lTID column is populated with ever-increasing values: you will always have at least one range for the publisher and one range for the subscriber and if users are inserting at both sites you can't have ever-increasing IDs.

This is by design and there's no way around it.

If your goal is keeping the identity values generated at the subscribers ever-increasing, that's a whole different story and it can be achieved with one of the above options.

Answer 2

If you set a starting range for replicated tables for any databases involved in replication, I think it will do the trick, just make sure the ranges you choose has enough IDs for records you insert so the ranges won't conflict any time soon.

For example, use a starting point of 1 for table a in database 1 1000000 for database 2 and so on.

All records inserted in db1 will have a value between 1 to 1000000, and all inserted in db 2 will have a range of 1000000+

You may also want to check out Replicate Identity Columns in Books Online.

Answer 3

If I understand your question correctly, your issue is with the Identity columns.

What is the Identity increment between the publisher and subscriber? I would suggest you set it up as shown below:

For e.g.

On publisher: Set Identity increment to 2, Identity seed to 1 ==> ODD

On subscriber: Set Identity increment to 2, Identity seed to 2 ==> Even

--- Below query will give the Current Identity Value.

SELECT  QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +  QUOTENAME(t.name) AS TableName, 
        c.name AS ColumnName,
        IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) AS CurrentIdentityValue,
        IDENT_INCR (SCHEMA_NAME(t.schema_id) + '.' + t.name) as Identity_increment
    FROM    sys.columns AS c 
        INNER JOIN  sys.tables AS t ON t.[object_id] = c.[object_id]
        WHERE   c.is_identity = 1 
  -- write here the table name if you want to filter for specific table
    and t.name like '%TABLE_NAME%'

This way when you failover, you wont be having Identity Insert failed problem.

Answer 4

Have you thought about using transaction log shipping instead of merge replication? This will provide a "constant" data flow (~15 minutes) between the two databases. It's a bit more resource intensive to the SQL pipeline, but if the systems are running with good specs, you shouldn't have an issue.