Running out of Identity Ranges

Question

Post reply

Running out of Identity Ranges

Otto Ramirez-404522

SSC Rookie

Points: 49
More actions
February 6, 2007 at 8:43 am

#92199

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

adamcrv Hall of Fame Points: 3056 More actions · Answer 1

adamcrv

Hall of Fame

Points: 3056

February 11, 2007 at 11:02 pm

#689076

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

Otto Ramirez-404522 SSC Rookie Points: 49 More actions · Answer 2

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

Andy Warren SSC Guru Points: 119747 More actions · Answer 3

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.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter