identity problem

I have multiple tables with identity column replicated (merge
replication) from Server A to Server B. During the initial setup, I
specified that sql agent manages the identity columns automatically and
runs in continuous mode. But a while, I got identity error and I had to
stop the agent, run sp_adjustpublisherientityrange and restart the
agent. After more research, I found out that this is a SQL bug and what
Microsoft recommend is exactly what I did, they also said the agent
should be scheduled to run every minute or so. However, in my case, this
is unacceptable because client may still run out of ID before the agent
restart and it causes the application error out. So here's what I did
1) build a insert trigger on every articles to check the range and call
sp_adjustpublisheridentityhrange is necessary, but this solution doesn't
work because looks like sp_adjustpublisheridentity has no effect until
the identity column hits its limit
2) build a insert trigger on every articles to check and re-adjust the
check constraint. This somewhat working on the publisher side, however,
how to deal with in at the subscriber side? I know there's sp_help and I
can get the constraint from system tables, but they are more for human
eyes than for programs, just pain to parse and I just don't like putting
too much inside a trigger
any recommendations?
Thanks
Eric Li
SQL DBA
MCDBA
Eric
it is possible to create identity ranges which never overlap, in which case
you'll just need to give a large range to each subscriber and not need to
adjust it. Have a look at this article by Michael Hotek:
http://www.mssqlserver.com/replicati...h_identity.asp
Regards,
Paul Ibison
|||there are two approaches to this problem
1) follow the KB's advice
http://support.microsoft.com/default...&Product=sql2k
2) set your ranges to values where they never will be exceeded during the
lifetime of your replication solution.
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:ufUvuhoEEHA.580@.TK2MSFTNGP11.phx.gbl...
> I have multiple tables with identity column replicated (merge
> replication) from Server A to Server B. During the initial setup, I
> specified that sql agent manages the identity columns automatically and
> runs in continuous mode. But a while, I got identity error and I had to
> stop the agent, run sp_adjustpublisherientityrange and restart the
> agent. After more research, I found out that this is a SQL bug and what
> Microsoft recommend is exactly what I did, they also said the agent
> should be scheduled to run every minute or so. However, in my case, this
> is unacceptable because client may still run out of ID before the agent
> restart and it causes the application error out. So here's what I did
> 1) build a insert trigger on every articles to check the range and call
> sp_adjustpublisheridentityhrange is necessary, but this solution doesn't
> work because looks like sp_adjustpublisheridentity has no effect until
> the identity column hits its limit
> 2) build a insert trigger on every articles to check and re-adjust the
> check constraint. This somewhat working on the publisher side, however,
> how to deal with in at the subscriber side? I know there's sp_help and I
> can get the constraint from system tables, but they are more for human
> eyes than for programs, just pain to parse and I just don't like putting
> too much inside a trigger
> any recommendations?
> Thanks
> --
> Eric Li
> SQL DBA
> MCDBA