Merge Replication identity field issues 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?
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.