Identity Range not working for Merge Replication in SQL Server 2005 - by Lizet

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


21
0
Sign in
to vote
ID 330476 Comments
Status Closed Workarounds
Type Bug Repros 13
Opened 2/28/2008 7:22:11 AM
Access Restriction Public

Description

the problem we were having with the identity range in merge replication:@@identity not working after SQL Server 2005 upgrade The problem continued until today, this post is to explain what we figured out.

The error message that describes this problem reads as follows:

[548] The insert failed. It conflicted with an identity range check constraint in database 'DatabaseName', replicated table 'dbo.TableName', column 'ColumnNameId'. 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.



The identity range adjustment happens after every insert in the given article. The code responsible for the identity check adjustment is on the system trigger for the published article:
MSmerge_isn_GUID where GUID is the GUID for the given article.
...
if is_member('db_owner') = 1
begin
-- select the range values from the MSmerge_identity_range table
-- this can be hardcoded if performance is a problem
declare @range_begin numeric(38,0)
declare @range_end numeric(38,0)
declare @next_range_begin numeric(38,0)
declare @next_range_end numeric(38,0)

select @range_begin = range_begin,
@range_end = range_end,
@next_range_begin = next_range_begin,
@next_range_end = next_range_end
from dbo.MSmerge_identity_range where artid='BAEF9398-B1B1-4A68-90A4-602E3383F74A' and subid='0F9826DB-50FB-4F73-844D-AE3A111B4E1C' and is_pub_range=0

if @range_begin is not null and @range_end is not NULL and @next_range_begin is not null and @next_range_end is not NULL
begin
if IDENT_CURRENT('[dbo].[TableName]') = @range_end
begin
DBCC CHECKIDENT ('[dbo].[TableName]', RESEED, @next_range_begin) with no_infomsgs
end
else if IDENT_CURRENT('[dbo].[TableName]') >= @next_range_end
begin
exec sys.sp_MSrefresh_publisher_idrange '[dbo].[TableName]', '0F9826DB-50FB-4F73-844D-AE3A111B4E1C', 'BAEF9398-B1B1-4A68-90A4-602E3383F74A', 2, 1
if @@error<>0 or @retcode<>0
goto FAILURE
end
end
end
...


As you might have noticed already, if the insertion is made by a user that is not member of the db_owner database role, the identity adjustment won't happen. I believe this is a bug, not a feature. It forces the users that are allowed to do insertions to be owners of the database, or they will have the run out of identities error quite often and a manual adjustment will be required.
Sign in to post a comment.
Posted by spaghettidba on 3/4/2013 at 10:46 AM
Same problem here, SQL 2008 R2.
When something would deserve a fix, it always gets "Won't fix".
Do you want this product to turn into Oracle?
It's very annoying.
Posted by Kiwi_Mark on 12/3/2012 at 7:33 PM
Same problem using SQL 2008 R2. Can somebody please fix this - only 18 votes over 4years ... but how many DBA's out there are trying to figure this out or just given up and have a script running to do the manual sp_adjustpublisheridentityrange?
Posted by Paulie0498 on 2/1/2012 at 4:04 AM
Could you please explain why this is "by design" as I cannot fathom why you would decide to make this a feature. Our inserts to the table come from a webpage. It is unacceptable to make the user db_owner. Does this mean I am forced to handle identities on all of my articles manually?

Could you also offer suggestions as to what those of us with this problem should do? Simply saying it is "by design" doesn't help those of us struggling with this situation.
Posted by Jean-Yves [MSFT] on 8/4/2011 at 5:30 PM
Hi Lizet,

We've looked into this. Although we would like to improve the product this way we will not have the means to do it in a foreseeable future.
That said your feedback is very valuable to us, we keep track of the issue.


Best regards
Jean-Yves Devant
Program Manager (Replication/CDC/CT)
SQL Server Engine Team
Posted by Jean-Yves [MSFT] on 8/4/2011 at 5:30 PM
Hi Lizet,

We've looked into this. Although we would like to improve the product this way we will not have the means to do it in a foreseeable future.
That said your feedback is very valuable to us, we keep track of the issue.


Best regards
Jean-Yves Devant
Program Manager (Replication/CDC/CT)
SQL Server Engine Team
Posted by Karen Wallace on 1/19/2010 at 4:22 PM
I'm planning to work around this bug by creating a stored procedure that monitors the identity values of published tables and kicks the distributor via sp_adjustpublisheridentityrange if they're at the threshold defined in the automatic identity range, and I'll run the check once a minute. I'd like to hear from Microsoft on whether this sounds like it will solve the problem (presuming that I've set the identity intervals large enough that they won't run out within 60 seconds).
Posted by Dee_SQL on 9/30/2009 at 6:56 PM
We are expriencing the same problem. What was the solution or workaround to this problem?
Posted by Lizet on 3/6/2008 at 11:05 PM
I should also point out that we never had this problem when the topology used SQL Server 2000 Ent edition. the automatic range adjustment "did" happen. We upgraded our servers to 2005 though and it seems we will have to do extra maintenance for this problem now when it worked fine before.
Posted by Lizet on 3/6/2008 at 11:02 PM
We can provide traces of insertions at the publisher that are not made by the merge agent. On this traces you can see the identity range is not updated at the publisher and the insertion fails (when user is not db_owner). The code shown above is never executed.
If any of the mere agents run after this failed insertion the range is still not adjusted as our merge agents are not supposed to do insertions on those tables at the publisher. Our insertions come from another system mainly. The subscribers mostly perform updates on the tables affectes by this problem, not insertions.

If this is a feature and it is "by design" there is a flaw in the design as it only accounts for insertions made by the merge agents and by db_owners.

The design should have taken into account the insertions that are not done by the merge agents.
Posted by Lizet on 3/6/2008 at 10:41 PM
Could you please read my comment before closing this issue and marking it as "by design"? I am referring to the identity range at the published database not at the subscriber.
We have a production database that is published with over 30 pull subscribers, the merge sync agents for each subscriber runs at least twice a day, and still the insertions at the publisher fail when the identity is bigger than the threshold and there is not automatic identity management at the publisher if the insertions are performed by other entities that are not merge agents, ie stored procedures that transfer data or SSIS packages...
Posted by Lizet on 3/6/2008 at 10:28 PM
This is fine for the subscriber, but the inserts fail at the publisher.

The publisher is the one that does not automatically update the identity range if the insertion is not made by a member of the db_owner database role.
In a merge topology the publisher can have inserts that are not created by the merge replication, this is inserts that do not come from one of the subscribers. In our case, these inserts are made by stored procedures that transfer data from another database on demand.
Posted by Lizet on 3/6/2008 at 10:28 PM
This is fine for the subscriber, but the inserts fail at the publisher.

The publisher is the one that does not automatically update the identity range if the insertion is not made by a member of the db_owner database role.
In a merge topology the publisher can have inserts that are not created by the merge replication, this is inserts that do not come from one of the subscribers. In our case, these inserts are made by stored procedures that transfer data from another database on demand.
Posted by Microsoft on 3/6/2008 at 10:21 PM
In order to get a new range for subscriber, the app must run sync. This is by design
Posted by Microsoft on 3/6/2008 at 9:45 PM
We are investigating. Thanks