June 23, 2014 at 8:03 am
I have developed an application in which I am using merge replication to keep a database on the primary and secondary SQL servers in sync. This setup is used because the primary SQL server is hosted remotely from the site where the application is used, so a secondary server hosted locally is kept as a warm standby in case of comms failure with the primary server.
Since the system went live, I have occasionally seen the following error when inserting into a table with an identity column (on the primary server, i.e. the publisher in the replication setup):
Msg 548, Level 16, State 2, Line 1
The insert failed. It conflicted with an identity range check constraint in database 'dbname', replicated table 'dbo.tablename', column 'ID'. 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.
I have established that this happens when an insert is performed by a user without db_owner rights, which I understand is because the MSMerge_ins trigger on the table can't update the identity range if it isn't running as db_owner. Unfortunately in this case I can't trap the error in code and execute sp_adjustpublisheridentityrange if necessary, because such a user can't execute that stored procedure either.
A morning spent searching the web shows this is a fairly well-known problem with SQL Server replication, but there seems no consensus on the best way to get around the problem.
I am considering giving up with automatic identity management and simply configuring the identity columns to use odd numbers on the primary and even numbers on the secondary server, but is there a better way around the problem (preferably one that avoids all the pain of reconfiguring the identity columns on a live server!)?
Thanks,
David
June 23, 2014 at 8:23 am
Not that I know of. However, because it is well-known, most new implementations will take this into account and design the tables in such a manner that the identity column is wholly irrelevant to the application, and only gets used by queries for things like ORDER BY statements within ROW_NUMBER() functions, or other similar things that just require a way to identify a record uniquely without using data columns. Of course, hindsight is 20-20. In the long run, the sooner this problem goes away, the less costly it will be to maintain.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply