November 4, 2002 at 9:59 am
I have a queued transactional replication setup between two SQL Server 2000 SP2 servers. Since records may be inserted either at the publisher or the subscriber, I'm using automatically managed identity ranges for the tables that have identity fields. In this mode SQL Server is supposed to automatically assign and maintain an identity range for the publisher and each subscriber. Let's say I choose range size of 1000 for both the publisher and the subscriber. When I set up the replication initally, the publisher gets the 0-1000 range, and the subscriber gets the 1001-2000 range. Let's say I insert two records at the subscriber which get 1001 and 1002 for their identity field. These records get replicated to the publisher. Everything works fine so far. I run into problems if I have to drop and recreate the publication for some reason (i.e. to add another table to the publication or modify structure of some table in the publication). What happens is the publisher and subscriber get the same identity ranges as before, and when I try to insert a record at the subscriber it tries to use the same value for the identity field (1001), which of course fails since a record with that value already exists.
It would seem that SQL Server is supposed to manage the ranges in a way that would not cause such problems. Has anyone experienced this before? How did you solve it? The only workaround I see is to manually set the identity seed value on the subscriber after republishing. Of course, this is cumbersome in an environment with many tables and subscribers.
I wrote a script to reproduce the problem. It's too big to include here, but let me know if you'd like to see it.
Please let me know any thoughts you might have!
Thanks,
Alek
Edited by - alekb on 11/04/2002 12:39:33 PM
November 4, 2002 at 11:55 am
I had a similar but different problem with merge replication. I have a central publisher and a couple dozen subscribers. I tried letting replication manage the identity ranges, but found that every time I dropped/reinitialized the subscriptions it would assign new identity ranges to all databases. This resulted in a lot of wasted ids over the course of time, and I could see that it might eventually eat up all available id ranges.
Now that I think about it, I may also have had the same problem you're having -- not properly resetting the ID seed. In fact, I still have that problem ocassionally from normal replication, and I'm manually setting the ranges myself (well, my code is). The merge agent inserts some new records from a different server, hence with IDs outside the current server's range, but fails to reset the ID seed afterward.
I now reset all tables' IDs after each replication using some simple SQL and a subscribers table which contains a subscriber number for each server that's participating in replication. I could post that code if you are interested. Of course, this method prevents you from using anomynous subscribers.
Jay
Jay Madren
November 4, 2002 at 12:56 pm
quote:
I now reset all tables' IDs after each replication using some simple SQL and a subscribers table which contains a subscriber number for each server that's participating in replication. I could post that code if you are interested. Of course, this method prevents you from using anomynous subscribers.
Thanks, I'd be interested to see how you implemented it. If I understood you correctly, you have a script that you run on subscribers just after you initialize the subscriptions, that sets the seed according to what you have defined in some table?
I don't have any anonymous subscribers, so that should not be an issue.
Alek
November 4, 2002 at 2:02 pm
quote:
Thanks, I'd be interested to see how you implemented it. If I understood you correctly, you have a script that you run on subscribers just after you initialize the subscriptions, that sets the seed according to what you have defined in some table?
More accurately, the script sets the seed to the highest existing ID in the range defined for that subscriber. I calculate the ID range using the subscriber's number which is defined in a table that I copy to all subscribers. This table consists of, among other things, the subscriber number and the SQL Server name (which is always the computer name for my subscibers, hence the column is called ComputerName).
Below is an excerpt of the script. I actually have several more tables, and two other tables with different ranges. But to keep it short for this post I reduced it down to two tables. All of this can be adjusted to suit your needs. Of course, you have to be sure that the actual identity range defined for each subscriber's tables matches this calculation.
Declare @SubNum int,
@ID int,
@IDbeg int,
@IDend int
--Get the subscriber number
Select @SubNum = Subscriber from Subscribers where ComputerName = @@ServerName
--Calculate subscriber's Identity range
Set @IDbeg = 10000001 + (@SubNum * 1000000)
Set @IDend = 11000000 + (@SubNum * 1000000)
--Reset Identity values
Select @ID = max(ContactID) from Contact where ContactID < @IDend
If @ID < @IDbeg or @ID is null
Select @ID = @IDbeg
print ''
print 'Contact'
dbcc checkident('Contact',RESEED,@ID)
Select @ID = max(JournalID) from Journal where JournalID < @IDend
If @ID < @IDbeg or @ID is null
Select @ID = @IDbeg
print ''
print 'Journal'
dbcc checkident('Journal',RESEED,@ID)
--and so on for all tables with an identity range
The print statements are there to identify which table is being processed, as the message from checkident doesn't do that.
Let me know if any of this is unclear.
Jay
Jay Madren
November 4, 2002 at 2:33 pm
Yeah, it makes sense what you are doing here... I'm guessing you still have the 'Auto Range Management' option on for these tables (with range size of 1000000 or whatever). If this is true, then I guess you have to initialize the subscriptions to all the subscribers and then adjust subscriber numbers in the Subscribers table to match the ranges SQL Server chooses. And then, run this script. I guess there no way to query SQL server for the ranges... maybe query system tables for constraint conditions, but this is ugly. Doesn't it seem like SQL Server should be doing a little better job here?
Well, thanks a lot for your input, looks like I'll be implementing something very similar to what you have.
Alek
November 4, 2002 at 3:23 pm
Actually, I am NOT using 'Auto Range Management' because of the problems I mentioned. I assigned the ranges to each subscriber manually (well, sort of - I built a macro in my text editor to do the same calculation). This also allows me to easily match the subscribers table to the ranges defined for each subscriber.
But if you wanted to continue to use 'Auto Range Management' then you could look at the system table MSrepl_identity_range as a possible way to get the current range values.
Jay
Jay Madren
November 4, 2002 at 3:49 pm
That's why your range is huge - to avoid having subscribers step on each others' toes... or how do you assign a new (maybe non-consecutive) range to a subscriber that is running out of its current range?
November 4, 2002 at 4:24 pm
Yes, it's a huge range. If I ever have to assign a new range, I'd do it during one of the reinitializations by recreating the tables. But I don't think that will ever be required in this situation.
Good luck in getting your's working smoothly.
Jay
Jay Madren
November 5, 2002 at 11:10 am
I don't know if this is useful to you, or you may be too far into production...but if you only have 2 systems, you could use positive identity values for one system and negative for the other (i.e. seed=-1 and increment=-1)
-Dan
-Dan
November 5, 2002 at 1:56 pm
I have three servers at this point (a publisher and two subscribers), but I need to be able to support more subscribers in the future.
Dan, I could still use something like you are suggesting by using odd/even for subscribers, http://www.mssqlserver.com/replication/bp_replication_with_identity.asp describes the technique nicely. I'll most likely end up doing a variation on this theme. However, this might become an administration headache. Seems like SQL Server 2000 made an attempt to provide a solution with their 'Auto range management', but came up short like Microsoft often does.
Alek
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply