August 23, 2005 at 9:22 am
At one time, we were replicating our production database to a single subscriber, with identity columns marked "not for replication", and value ranges assigned for each system, with the subscriber using the higher range. We stopped the replication several months ago, but have an occasional problem with the current identity value running into higher pre-existing values from our replication period.
I finally wrote a query to find, and correct, tables where the current identity value was lower than the maximum value in the table, so it shouldn't happen again. However, now I have to explain to a superior or two WHY it happened, which is beyond my limited replication expertise.
Several development databases, restored from production backups, have apparently auto-corrected the current identity value for some tables, while other development databases develop an error, which must be corrected manually.
Is there a mechanism within SQL Server which detects (and corrects) this situation?
If so, why does it sometimes not work?
August 24, 2005 at 5:16 am
This is a bug. Basically once upon a time you were using Automatic Identity Range Management. AIRM uses different identity seeds on the subscriber and implements a check constraint there. If you drop replication these remain.
You have to manually fix these tables. I did report this bug to Microsoft.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
August 24, 2005 at 11:03 am
Thanks for your response.
I searched msdn, but couldn't find anything which sounded like this, except for 321879, which CAN'T be the cause, because none of our SQL Servers have shut done unexpectedly during the past two months.
Do you know an article number?
Has the issue been resolved, or is it outstanding?
My question about differences between databases involves the past couple of months, and we are are usually current on Service Packs and hot-fixes, both SQL Server 2K Enterprise and Server 2K3. My superiors want to know whysome databases appear to "reseed" automatically and correctly, while some do not, causing a run-time "duplicate value" message. The replication was stopped about 5 months ago, and our development databases were restored from a Production database backup just over 2 months ago.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply