Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from Mar 20 '13 at 7:13

This question came from our site for professional and enthusiast programmers.

4 Answers 4

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.

share|improve this answer

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 as below:

For e.g.

On publisher : Keep Identity increment to 1 ==> ODD

On subscriber: Keep Identity increment to 2 ==> Even

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

SELECT  QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +  QUOTENAME( AS TableName, AS ColumnName,
        IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + AS CurrentIdentityValue,
        IDENT_INCR (SCHEMA_NAME(t.schema_id) + '.' + 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 like '%TABLE_NAME%'

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

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
Thanks for the reply. Our client doesn't want the possibility of losing 15 minutes worth of transactions as its critical data so don't think we can suggest that option. – user21502 Mar 20 '13 at 11:41

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.