February 6, 2007 at 8:43 am
Hello
I have the following problem:
- I have a transactional replication between a publisher and a few suscribers,
- The servers are SQL 2003.
- I have Identities columns in some of the tables
- I'm using the Automatic Identity Range Handling
The problems happens when the publisher or one of the suscriber goes down, (it happens relativly often and we can't do anything against it), so when the connection is restablished, the merge agent assign a new identity range to the publisher or
the suscriber(the one that went down).
Everytime a server goes down, it "eats" a idetity range, doesnt matter how many idenities have been used, and i am running out of identity ranges.
I want to keep using the Automatic Identity Range Handling to manage the replication activity because
changing it to manual would be really hard for us.
Im new at this but I see two ways to solution it:
1) avoid that the marge agent assign a new identity range when a server goes down
2) let the merge agent assign a new identity range, and the reestablish the identity to the heigest id value (using CHECKIDENTITY()), but i think i would have to do some extra things to make the publisher to be sincronized with the suscribers (maybe modify a table on the publisher or something)
Could someone please tell me what is the easier way to solution it, if there is another easier way and how to implement it, or the other two?
thanks for your attention
February 9, 2007 at 8:00 am
This was removed by the editor as SPAM
February 11, 2007 at 11:02 pm
this seems like a very weird problem to me..can you provide any other information about it.
is the issue occurring during the subscription process?
when are the servers disconnecting?
------------------------------
Life is far too important to be taken seriously
February 12, 2007 at 7:29 am
thanks for you answer
Well, our power supply somtime fails, we have a ups that last 10 minutes but somtimes it last more time to electricity to come back, thats one of the reasons why our servers goes down.
When a server start again, the publisher assign to it a new identity range, that is the normal way that it works. But we want to know if we can change the next identity range that will be assigned.
I know that checkidentity change the next identity that will be assigned on any table, and sp_adjustpublisheridentityrange will give new ranges to any server of the replication that need it, but no one changes the next identity range that will be assigned to a manually disered number and thats what I want to do
February 12, 2007 at 1:43 pm
I dont do a lot of merge repl, but I know for sure that restarting a server using transactional with updating subscribers does not request a new range. Doesn't seem like that behavior would make sense?
You could probably alter sp_adjustpublisheridentityrange to do what you want, but you'd have to check after service packs to make sure your changes not overwritten. You could also just switch to smaller ranges for now to buy some time.
February 12, 2007 at 4:20 pm
yeah, it doesnt make sense to me either, but I thougt that it was that it did, i'll check this stuff out
yeap, i will make smallers ranges, for the moment, then let see what happend...
Thanks for you answer
February 15, 2007 at 1:47 am
agreed
smaller ranges will fix ur issue for now (in so much as you dont have to worry about running out of them.)
let us know how you go and if you find anything else out...ill keep racking my brains for something else...
------------------------------
Life is far too important to be taken seriously
February 15, 2007 at 7:37 am
ok thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply