Running out of Identity Ranges

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Replication (2005)

Running out of Identity Ranges

Author

Topic

ottorm
Starting Member

5 Posts

Posted - 2007-02-06 : 12:08:34

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 13:13:05

What datatype for "IDENTITY" are you using?

Peter Larsson
Helsingborg, Sweden

ottorm
Starting Member

5 Posts

Posted - 2007-02-06 : 14:54:09

quote:
Originally posted by Peso

What datatype for "IDENTITY" are you using?

Peter Larsson
Helsingborg, Sweden

i'm using the positive values of an int, it would be 2 billion id's, i will modify the size of the ranges to fit more ranges

I was wondering if there is a way to modify the value that the publisher will give to the next server that will need a range?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources