identity ranges on republished restore of db

Hi folks,
We are having issues with restores of our production databases into our
test environments.
Our production environment is SQL server 2000 sp3 with merge
replication with 3 publications using automatic identity range
management.
The problem we're having is that when we try to restore a production
backup into a test environment and republish the database - inserts on
the server are failing due to the identity ranges being full. We run
the sp_adjustpublisheridentityrange stored procedure but this produces
inconsistent results. Sometimes it fixes the problem and allows inserts
to proceed but other times it doesnt
What we see happening is that it might fix the first table the range is
full on but when we try an insert on another table we get the range
full message again.
Its causing us major grief for an app we have thats trying to merge
duplicate customers on our system. The merge process involves lots of
inserts server side as we make back up copies of the customers before
we try merging them etc.
i guess what we're after is a consistent way to set/reset and make sure
identity ranges are ok across the database once we republish it.
Any help would be greatly appreciated
Thanks,
Michael
This should be working. What you should do is run your merge agents at a
greater frequency. You might also want to change your subscriber ranges to
match the largest increment which might ever occur on your subscriber
between merge agent runs. So if your merge agent runs once a week, and you
do 1000 inserts on the subscriber at a max in this interval, tune your
subscriber range accordingly.
To address your problem
Run dbcc checkident('tablename')
Get the current value
then issue a select max(identititycolumn) from tablename and get the current
value in your table.
reseed for this value
run dbcc checkident('tablename',reseed, maxvalue)
The alter your check constraint
to do this issue sp_help tablename
look for the check constraint called something like
repl_identity_range_pub_84EB4FA5_28B9_4AD2_82CC_27 B1CE554D0D
Alter this constrainst for your new range.
Keep in mind when you blow a range, subsequent inserts fail, but the
identity value is incremented.
Also adjust the MSrepl_identity_range table in your distribution database
for this new range.
The merge agent polls sysmergearticles on your subscriber with each run to
see if any of them have automatic identity range management support. It
should adjust the identity ranges automatically with each run. I am not
exactly sure why this is not working for you.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<micks_address@.yahoo.co.uk> wrote in message
news:1117018628.625701.77440@.g47g2000cwa.googlegro ups.com...
> Hi folks,
> We are having issues with restores of our production databases into our
> test environments.
> Our production environment is SQL server 2000 sp3 with merge
> replication with 3 publications using automatic identity range
> management.
> The problem we're having is that when we try to restore a production
> backup into a test environment and republish the database - inserts on
> the server are failing due to the identity ranges being full. We run
> the sp_adjustpublisheridentityrange stored procedure but this produces
> inconsistent results. Sometimes it fixes the problem and allows inserts
> to proceed but other times it doesnt
> What we see happening is that it might fix the first table the range is
> full on but when we try an insert on another table we get the range
> full message again.
> Its causing us major grief for an app we have thats trying to merge
> duplicate customers on our system. The merge process involves lots of
> inserts server side as we make back up copies of the customers before
> we try merging them etc.
> i guess what we're after is a consistent way to set/reset and make sure
> identity ranges are ok across the database once we republish it.
> Any help would be greatly appreciated
> Thanks,
> Michael
>
|||Hi Hilary,
Thanks very much for the quick reply. We have a script which checks the
current ident for each table - - then compares this value to the max
column value in each table. If the max column value is above the seed
value it sets the seed value to be the max column value plus 1 doing a
run dbcc checkident('tablename',reseed, maxvalue+1)
do we also need to update the constraint on the table? and if so what
do you set the constraint values to be?
For example one of our tables is called ApplProduct
The max value in the identity column is 1881002
The current identity value is 12427472
The current range constraint on the table is
([ApplProd_ID] > 11490000 and [ApplProd_ID] < 13490000)
After we run our script to produce the new seed value is produces the
DBCC command below:
DBCC CHECKIDENT ('ApplProduct', RESEED, 18810003)
We dont alter the constraint... i would have thought the
adjustpublisheridentityrang=ADe sp might do that? If we need to alter
the constraint what should the new value be?
Again your help is much appreciated
Thanks,
Michael
|||You only need to update the constraint IF non replication processes will be
inserting the rows, i.e. if you or your app want to insert the rows on the
subscriber.
First off I would reseed your table to DBCC CHECKIDENT ('ApplProduct',
RESEED, 1881002)
Basically from what I see you have inserted or attempted to insert
12427472 -1881002 rows which the constraint has kicked back a mere 10546470
rows.
I'd alter the constraint for this range as well
([ApplProd_ID] > 1881000 and [ApplProd_ID] < 3000000)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<micks_address@.yahoo.co.uk> wrote in message
news:1117023368.665062.255950@.g47g2000cwa.googlegr oups.com...
Hi Hilary,
Thanks very much for the quick reply. We have a script which checks the
current ident for each table - - then compares this value to the max
column value in each table. If the max column value is above the seed
value it sets the seed value to be the max column value plus 1 doing a
run dbcc checkident('tablename',reseed, maxvalue+1)
do we also need to update the constraint on the table? and if so what
do you set the constraint values to be?
For example one of our tables is called ApplProduct
The max value in the identity column is 1,881,002
The current identity value is 12,427,472
The current range constraint on the table is
([ApplProd_ID] > 11490000 and [ApplProd_ID] < 13490000)
After we run our script to produce the new seed value is produces the
DBCC command below:
DBCC CHECKIDENT ('ApplProduct', RESEED, 18810003)
We dont alter the constraint... i would have thought the
adjustpublisheridentityrangXe sp might do that? If we need to alter
the constraint what should the new value be?
Again your help is much appreciated
Thanks,
Michael
|||Our insertions are happening on the server Hilary - does this make any
difference? Our inserts all use SCOPE IDENTITY to get their values so i
would have thought we'd be ok i.e. we'd be inserting in a replication
friendly way?
Thanks,
Michael
|||Hi folks,
It looks like we need to do a lot more around identity ranges for bulk
server side inserts. From our investigations it looks like our initial
server ranges on some tables are almost full. The laptop(subscriber)
ranges all seem to sit above the server range at the moment. The server
has a much larger range than any of the subscribers - 2 million plus
(to handle out initial migration and nightly imports from another
database). One solution might be to reset our server range on all the
tables to above the current max subscriber range. That would give us a
fresh start as it were in a new range and hopefully the level of server
side inserts wont be as large to blow the range again for some time
If we leave the ranges as is then we're likely to have to build some
sort of range checking into our inserts to check when the server range
is near its max and re adjust accordingly.
apart from leaving gaps in our indenties will reseting them upset
anything else in the db?
Cheers,
Michael
|||I'm having a problem understanding how you are using the scope_identity
property.
What I said holds for both inserts occurring at the publisher or subscriber.
Just make sure you adjust the publisher range accordingly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<micks_address@.yahoo.co.uk> wrote in message
news:1117032974.032687.146510@.g44g2000cwa.googlegr oups.com...
> Our insertions are happening on the server Hilary - does this make any
> difference? Our inserts all use SCOPE IDENTITY to get their values so i
> would have thought we'd be ok i.e. we'd be inserting in a replication
> friendly way?
> Thanks,
> Michael
>
|||Possibly. One thing that worked for one client of mine was not to use
automatic identity range management and just travel to the publisher or
subscriber, issue a dbcc checkident('tablename',reseed,12312396490)
on an as needed basis.
We used large ranges and monitored to see when we were getting close to
being full.
What made this work for us is that we knew we weren't going to grow, and had
a good handle on where data was going to be inserted and how much.
So, it was basically the set it and forget it philosophy, although it
required some monitoring. Naturally after we got everything working and all
the bugs shaken out, management added 10 more subscribers.
Automatic Identity Range Management is a maintenance free way, scalable way
of partitioning and efficiently using identity ranges. With planning you
should be able to have it parcel out the identity range chunks you need. You
should by now have a handle on what the representative subscriber and
publisher ranges should be.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<micks_address@.yahoo.co.uk> wrote in message
news:1117100177.816930.108770@.z14g2000cwz.googlegr oups.com...
> Hi folks,
> It looks like we need to do a lot more around identity ranges for bulk
> server side inserts. From our investigations it looks like our initial
> server ranges on some tables are almost full. The laptop(subscriber)
> ranges all seem to sit above the server range at the moment. The server
> has a much larger range than any of the subscribers - 2 million plus
> (to handle out initial migration and nightly imports from another
> database). One solution might be to reset our server range on all the
> tables to above the current max subscriber range. That would give us a
> fresh start as it were in a new range and hopefully the level of server
> side inserts wont be as large to blow the range again for some time
> If we leave the ranges as is then we're likely to have to build some
> sort of range checking into our inserts to check when the server range
> is near its max and re adjust accordingly.
> apart from leaving gaps in our indenties will reseting them upset
> anything else in the db?
> Cheers,
> Michael
>
|||I've been following along this thread, as it sounds similar to an issue I'm
ranting about above ... one question in this thread: how should the check
constraint be altered?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Oj8D31gYFHA.796@.TK2MSFTNGP09.phx.gbl...
> Possibly. One thing that worked for one client of mine was not to use
> automatic identity range management and just travel to the publisher or
> subscriber, issue a dbcc checkident('tablename',reseed,12312396490)
> on an as needed basis.
> We used large ranges and monitored to see when we were getting close to
> being full.
> What made this work for us is that we knew we weren't going to grow, and
> had
> a good handle on where data was going to be inserted and how much.
> So, it was basically the set it and forget it philosophy, although it
> required some monitoring. Naturally after we got everything working and
> all
> the bugs shaken out, management added 10 more subscribers.
> Automatic Identity Range Management is a maintenance free way, scalable
> way
> of partitioning and efficiently using identity ranges. With planning you
> should be able to have it parcel out the identity range chunks you need.
> You
> should by now have a handle on what the representative subscriber and
> publisher ranges should be.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> <micks_address@.yahoo.co.uk> wrote in message
> news:1117100177.816930.108770@.z14g2000cwz.googlegr oups.com...
>