Auto identity in republisher scenario

Hi to everyone,
Is auto identity range supported in publisher-subscriber scenario?
In example:
Server1 publish database1 with article Table1 with auto identity range.
Server1 reserve itself the range 1-100.
Server2 subscribe to Server1 and get and identity range 101-200.
Server2 is also a republisher for database1 so it allows Client1 to
subscribe and replicate with it.
Which identity range does Client1 get?
I suppose Client1 will get a 201-300 range from Server2, which will
eventually conflict if the same range is assigned to other clients directly
from Server1.
Am I right or am I wrong?
Kind regards,
Corrado
You are correct that it will eventually conflict. Auto-identity ranges is
something that I've played with and then never used. There are just too
many potential problems with them at this point. You can wind up with range
skipping and run out of values long before you would have. I've also had
cases where you generated duplicate values.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||are you using merge replication? it is unclear from your post.
Server 1 will have a check constraint on the identity column allowing a
range of identity values from 101-200. Server 2 from 201-300. Client 1 will
have a range from 320-330.
The ranges will be maintained between server 2 and client 1 with a different
set of ranges and thresholds than between Server 1 and Server 2.
There are some caveats with automatic identity range management. First you
must use a representative data type that will be good for your range of
values - ie smallint will probably be too small for most applications.
Secondly pick a range which is larger than your largest batch insert. The
automatic range adjustment is run after a transaction is distruted for
transactional publications or covergence is achieved if you are using merge.
Third most dba's will set the maxiumn ranges they expect their replication
solution to use in the life time of thier probject - sort of like set it and
forget it.
"Corrado Labinaz" <corradolab@.virgilio.it> wrote in message
news:uT2xsySMEHA.3668@.TK2MSFTNGP11.phx.gbl...
> Hi to everyone,
> Is auto identity range supported in publisher-subscriber scenario?
> In example:
> Server1 publish database1 with article Table1 with auto identity range.
> Server1 reserve itself the range 1-100.
> Server2 subscribe to Server1 and get and identity range 101-200.
> Server2 is also a republisher for database1 so it allows Client1 to
> subscribe and replicate with it.
> Which identity range does Client1 get?
> I suppose Client1 will get a 201-300 range from Server2, which will
> eventually conflict if the same range is assigned to other clients
directly
> from Server1.
> Am I right or am I wrong?
> Kind regards,
> Corrado
>