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...
>
Showing posts with label ranges. Show all posts
Showing posts with label ranges. Show all posts
Monday, March 19, 2012
Identity ranges different for different pubs
Silly question that came to mind as I was driving back from a poker game:
What would happen if one publication sets an identity range at say, 100,000
and another publication (in the same database) has the identity range set
for 50,000 on the same table?
Earl,
when you create the second publication, the identity range for the table is
inherited from the first one.
If you're having these sort to thoughts, does it mean you won or lost
?
Rgds,
Paul Ibison, SQL MVP
|||Hehe. I won... now if I can just cobble together enough wins to get back to
the WSOP this year ...
On a more serious note, I'm in the final stages of rolling out an app and
when the mind is freewheeling, I tend to speculate on what might go wrong
long-term. It seems like some issues of replication could lead to walking
into a dead-end if it were not anticipated early on.
Thanks Paul.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eGNf8mBcFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Earl,
> when you create the second publication, the identity range for the table
> is inherited from the first one.
> If you're having these sort to thoughts, does it mean you won or lost?
> Rgds,
> Paul Ibison, SQL MVP
>
What would happen if one publication sets an identity range at say, 100,000
and another publication (in the same database) has the identity range set
for 50,000 on the same table?
Earl,
when you create the second publication, the identity range for the table is
inherited from the first one.
If you're having these sort to thoughts, does it mean you won or lost
?Rgds,
Paul Ibison, SQL MVP
|||Hehe. I won... now if I can just cobble together enough wins to get back to
the WSOP this year ...
On a more serious note, I'm in the final stages of rolling out an app and
when the mind is freewheeling, I tend to speculate on what might go wrong
long-term. It seems like some issues of replication could lead to walking
into a dead-end if it were not anticipated early on.
Thanks Paul.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eGNf8mBcFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Earl,
> when you create the second publication, the identity range for the table
> is inherited from the first one.
> If you're having these sort to thoughts, does it mean you won or lost
?> Rgds,
> Paul Ibison, SQL MVP
>
Identity Ranges
I'm using Merge replication on a database that was designed using integer identity columns for primary keys. When I create a publisher it's great because Sql Server will create rowguid columns for me on most of the tables; actually all but one table.
The problem comes when I try and use identity ranges for the subscribers. As a test I set up a range on one table, it only allowed for 10 in the range with an 80% threshold. I wanted to see what would happen when say my publisher db inserts 11 new rows. Well, I found out that after the 8th new row it wouldn't insert anymore as the range was exceeded and it gave me an error message saying
The identity range managed by replication is full and must be updated by a replication agent.
The question is; which agent needs to run in order for the new range to be assigned to the publisher? I have seen some people talk about an sp_ that can be run, but in a production environment I wont this to be automatic.
My altenative to ranged identies is using guid uniqueidentifiers. See my other post on this!!
regards
GrahamI assume this is SQL Server 2000.
To answer your questions:
Maybe the table on which merge is not adding the rowguid col is because the table already has a rowguid column?
And since you have 80% threashold, it is failing after the 8th row and I assume you have pub_idrange and range values each set to 10.
You can get by this situation by increasing the numbers, so that the probablity of them running out of numbers is less. Say like 10,000 or 100,000.
And the agent to be run when the id range is full is the merge agent. Running the merge agent will refresh the ranges on publisher and subscriber.
On the publisher, you can also run the sp_adjustpublisheridentityrange to refresh the range and that way you dont need to run the merge agent.
But typically in a production scenario, it is recommended that:
1. You have a decent sized value for the publisher and subscriber id range values
2. Merge agent to run frequently. That way the ranges will be refreshed (if needed) when merge agent runs.
identity ranges
We restored out database from a backup. we are using merge replication with
ranged identity supor t on some tables. The restore screwed up the
identities. I found an earlier post on how to fix this. Her's what I've
done.
I ran dbcc checkident(tablenaem) and it returns:
Checking identity information: current identity value '161801', current
column value '176509'.
I then reseed it with
dbcc checkident('Material',reseed)
it then returns
Checking identity information: current identity value '176509', current
column value '176509'.
I then run
sp_help 'material'
the constraint is as follows:
([MaterialID] > 161800 and [MaterialID] < 161900)
I then alter my constraint and rerun
sp_help 'material'
it returns
([MaterialID] > 176509 and [MaterialID] < 176709)
I then run
dbcc checkident('Material')
it returns
Checking identity information: current identity value '176509', current
column value '176509'.
all looks good so far
I then go to msrepl_identity_range
it contains
55879466 176700 100 20 2147483647 80 176700
which is correct.
at this point if I create a publication and add the material table as an
article and look at identity range next starting value is 161900 which is
incorrect.
If I create a snapshot and merge, the
dbcc checkident(tablenaem)
returns the original result
'161801', current column value '176509'.
I've narrowed it down to the case that If I recreate all of the above minus
the merge and run
sp_adjustpublisheridentityrange 'Winship_AWSM'
the identity s reset just as with the merge.
What am I missing?
There must be one more place I need to change the seed value. Since the
publication wizard still showed theold value even when all of the storepd
procedures and tables showed the correct info.
Thanks
did you modify MSrepl_identity_range on the Publisher in the distribution
database?
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
"mgarner1980" <mgarner@.kbsi.com> wrote in message
news:uGNT01ZgFHA.3940@.tk2msftngp13.phx.gbl...
> We restored out database from a backup. we are using merge replication
with
> ranged identity supor t on some tables. The restore screwed up the
> identities. I found an earlier post on how to fix this. Her's what I've
> done.
> I ran dbcc checkident(tablenaem) and it returns:
> Checking identity information: current identity value '161801', current
> column value '176509'.
> I then reseed it with
> dbcc checkident('Material',reseed)
> it then returns
> Checking identity information: current identity value '176509', current
> column value '176509'.
> I then run
> sp_help 'material'
> the constraint is as follows:
> ([MaterialID] > 161800 and [MaterialID] < 161900)
> I then alter my constraint and rerun
> sp_help 'material'
> it returns
> ([MaterialID] > 176509 and [MaterialID] < 176709)
> I then run
> dbcc checkident('Material')
> it returns
> Checking identity information: current identity value '176509', current
> column value '176509'.
> all looks good so far
> I then go to msrepl_identity_range
> it contains
> 55879466 176700 100 20 2147483647 80 176700
> which is correct.
> at this point if I create a publication and add the material table as an
> article and look at identity range next starting value is 161900 which
is
> incorrect.
> If I create a snapshot and merge, the
> dbcc checkident(tablenaem)
> returns the original result
> '161801', current column value '176509'.
> I've narrowed it down to the case that If I recreate all of the above
minus
> the merge and run
> sp_adjustpublisheridentityrange 'Winship_AWSM'
> the identity s reset just as with the merge.
> What am I missing?
> There must be one more place I need to change the seed value. Since the
> publication wizard still showed theold value even when all of the storepd
> procedures and tables showed the correct info.
> Thanks
>
>
|||yes, the publisher is the distributor as well. the merge is between
sqlserverce (pocketpc's) and sqlserver2000.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ue5Cy6ZgFHA.1412@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> did you modify MSrepl_identity_range on the Publisher in the distribution
> database?
> --
> 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
> "mgarner1980" <mgarner@.kbsi.com> wrote in message
> news:uGNT01ZgFHA.3940@.tk2msftngp13.phx.gbl...
> with
an[vbcol=seagreen]
> is
> minus
storepd
>
|||I'm sorry...I misunderstood your question. I did not change itin the
distribution database, I changed it in the database to be replicated. I've
now updated the distribution database and it's working.
Thanks!
"mgarner1980" <mgarner@.kbsi.com> wrote in message
news:uXqDYJagFHA.3940@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> yes, the publisher is the distributor as well. the merge is between
> sqlserverce (pocketpc's) and sqlserver2000.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ue5Cy6ZgFHA.1412@.TK2MSFTNGP09.phx.gbl...
distribution[vbcol=seagreen]
I've[vbcol=seagreen]
current[vbcol=seagreen]
current[vbcol=seagreen]
current[vbcol=seagreen]
> an
which[vbcol=seagreen]
the
> storepd
>
ranged identity supor t on some tables. The restore screwed up the
identities. I found an earlier post on how to fix this. Her's what I've
done.
I ran dbcc checkident(tablenaem) and it returns:
Checking identity information: current identity value '161801', current
column value '176509'.
I then reseed it with
dbcc checkident('Material',reseed)
it then returns
Checking identity information: current identity value '176509', current
column value '176509'.
I then run
sp_help 'material'
the constraint is as follows:
([MaterialID] > 161800 and [MaterialID] < 161900)
I then alter my constraint and rerun
sp_help 'material'
it returns
([MaterialID] > 176509 and [MaterialID] < 176709)
I then run
dbcc checkident('Material')
it returns
Checking identity information: current identity value '176509', current
column value '176509'.
all looks good so far
I then go to msrepl_identity_range
it contains
55879466 176700 100 20 2147483647 80 176700
which is correct.
at this point if I create a publication and add the material table as an
article and look at identity range next starting value is 161900 which is
incorrect.
If I create a snapshot and merge, the
dbcc checkident(tablenaem)
returns the original result
'161801', current column value '176509'.
I've narrowed it down to the case that If I recreate all of the above minus
the merge and run
sp_adjustpublisheridentityrange 'Winship_AWSM'
the identity s reset just as with the merge.
What am I missing?
There must be one more place I need to change the seed value. Since the
publication wizard still showed theold value even when all of the storepd
procedures and tables showed the correct info.
Thanks
did you modify MSrepl_identity_range on the Publisher in the distribution
database?
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
"mgarner1980" <mgarner@.kbsi.com> wrote in message
news:uGNT01ZgFHA.3940@.tk2msftngp13.phx.gbl...
> We restored out database from a backup. we are using merge replication
with
> ranged identity supor t on some tables. The restore screwed up the
> identities. I found an earlier post on how to fix this. Her's what I've
> done.
> I ran dbcc checkident(tablenaem) and it returns:
> Checking identity information: current identity value '161801', current
> column value '176509'.
> I then reseed it with
> dbcc checkident('Material',reseed)
> it then returns
> Checking identity information: current identity value '176509', current
> column value '176509'.
> I then run
> sp_help 'material'
> the constraint is as follows:
> ([MaterialID] > 161800 and [MaterialID] < 161900)
> I then alter my constraint and rerun
> sp_help 'material'
> it returns
> ([MaterialID] > 176509 and [MaterialID] < 176709)
> I then run
> dbcc checkident('Material')
> it returns
> Checking identity information: current identity value '176509', current
> column value '176509'.
> all looks good so far
> I then go to msrepl_identity_range
> it contains
> 55879466 176700 100 20 2147483647 80 176700
> which is correct.
> at this point if I create a publication and add the material table as an
> article and look at identity range next starting value is 161900 which
is
> incorrect.
> If I create a snapshot and merge, the
> dbcc checkident(tablenaem)
> returns the original result
> '161801', current column value '176509'.
> I've narrowed it down to the case that If I recreate all of the above
minus
> the merge and run
> sp_adjustpublisheridentityrange 'Winship_AWSM'
> the identity s reset just as with the merge.
> What am I missing?
> There must be one more place I need to change the seed value. Since the
> publication wizard still showed theold value even when all of the storepd
> procedures and tables showed the correct info.
> Thanks
>
>
|||yes, the publisher is the distributor as well. the merge is between
sqlserverce (pocketpc's) and sqlserver2000.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ue5Cy6ZgFHA.1412@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> did you modify MSrepl_identity_range on the Publisher in the distribution
> database?
> --
> 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
> "mgarner1980" <mgarner@.kbsi.com> wrote in message
> news:uGNT01ZgFHA.3940@.tk2msftngp13.phx.gbl...
> with
an[vbcol=seagreen]
> is
> minus
storepd
>
|||I'm sorry...I misunderstood your question. I did not change itin the
distribution database, I changed it in the database to be replicated. I've
now updated the distribution database and it's working.
Thanks!
"mgarner1980" <mgarner@.kbsi.com> wrote in message
news:uXqDYJagFHA.3940@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> yes, the publisher is the distributor as well. the merge is between
> sqlserverce (pocketpc's) and sqlserver2000.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ue5Cy6ZgFHA.1412@.TK2MSFTNGP09.phx.gbl...
distribution[vbcol=seagreen]
I've[vbcol=seagreen]
current[vbcol=seagreen]
current[vbcol=seagreen]
current[vbcol=seagreen]
> an
which[vbcol=seagreen]
the
> storepd
>
Identity ranges
Using merge replication, I've decided to change over to identity ranges
(versus uniqueidentifiers). At the publisher, I'm setting a range on the
tables that would be consistent with the records expected not to be exceeded
in the database. Since these values are for an entire state of consumers, I
set the publisher range to 500,000 for that consumer table.
Three questions:
1. Does the publisher range sound excessive and would I be better off using
the auto-range to up that value or should I err on the high side initially?
2. On the subscriber side (all PPC), the normal range of records that would
be pushed would probably be 3000-5000 and the actual changes made would
probably only amount to about 500 per day per PPC. What would be a safe
range on the subscriber side?
3. Does it really matter how WIDE the range is set?
You really need to consider how long the PPC can go without a sync. Can
they stay disconnected for 5 days, 10, longer? Do some calculating and make
sure that not only the range is wide enough, but that the threshold is low
enough so they receive a new range to cover the next worst case senario.
One thing you can guarantee is that the user are not forced to sync
everyday...they probably will not.
"Earl" wrote:
> Using merge replication, I've decided to change over to identity ranges
> (versus uniqueidentifiers). At the publisher, I'm setting a range on the
> tables that would be consistent with the records expected not to be exceeded
> in the database. Since these values are for an entire state of consumers, I
> set the publisher range to 500,000 for that consumer table.
> Three questions:
> 1. Does the publisher range sound excessive and would I be better off using
> the auto-range to up that value or should I err on the high side initially?
> 2. On the subscriber side (all PPC), the normal range of records that would
> be pushed would probably be 3000-5000 and the actual changes made would
> probably only amount to about 500 per day per PPC. What would be a safe
> range on the subscriber side?
> 3. Does it really matter how WIDE the range is set?
>
>
|||Thanks Brian, those are good thoughts. A couple of followup questions: What
would the criteria be to ensure that the "range is wide enough"? I didn't
really understand about the threshold being "low enough".
"Brian Reuter" <BrianReuter@.discussions.microsoft.com> wrote in message
news:885F4F00-0793-478F-B363-7FCCF97C97C9@.microsoft.com...[vbcol=seagreen]
> You really need to consider how long the PPC can go without a sync. Can
> they stay disconnected for 5 days, 10, longer? Do some calculating and
> make
> sure that not only the range is wide enough, but that the threshold is low
> enough so they receive a new range to cover the next worst case senario.
> One thing you can guarantee is that the user are not forced to sync
> everyday...they probably will not.
>
>
> "Earl" wrote:
|||IMHO best way is to give range of 10.000.000 or even hundred million
and forget about ranges, tresholds etc. forever.
Max identity is something like 9223372036854775807, so why to be
worried about ranges.
Is there any drawback with this approach?
Pagus
On Fri, 12 Nov 2004 10:13:23 -0500, "Earl"
<brikshoe@.newsgroups.nospam> wrote:
[vbcol=seagreen]
>Thanks Brian, those are good thoughts. A couple of followup questions: What
>would the criteria be to ensure that the "range is wide enough"? I didn't
>really understand about the threshold being "low enough".
>"Brian Reuter" <BrianReuter@.discussions.microsoft.com> wrote in message
>news:885F4F00-0793-478F-B363-7FCCF97C97C9@.microsoft.com...
|||FAO Earl - Pagus is talking about BigInts (8 bytes) and
not Ints (4 bytes), where the range is +/-2billion or so.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I was thinking that range wouldn't work :=)
I want that replication book, can I get that as an ebook too (I didnt' see
anything on the site)?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:5e3f01c4c999$7e3be410$a601280a@.phx.gbl...
> FAO Earl - Pagus is talking about BigInts (8 bytes) and
> not Ints (4 bytes), where the range is +/-2billion or so.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Earl,
the book is now available - I've read a draft and it looks v.good.
Hilary mentioned something about internet updates, but I don't recall
hearing about it as an ebook. Hopefully he'll see this thread and reply to
you.
Rgds,
Paul Ibison, SQL Server MVP
|||No, ebook. The merge volume may be released as an ebook, but its future is
yet undecided.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Ot8sppbyEHA.2568@.TK2MSFTNGP11.phx.gbl...
> Earl,
> the book is now available - I've read a draft and it looks v.good.
> Hilary mentioned something about internet updates, but I don't recall
> hearing about it as an ebook. Hopefully he'll see this thread and reply to
> you.
> Rgds,
> Paul Ibison, SQL Server MVP
>
(versus uniqueidentifiers). At the publisher, I'm setting a range on the
tables that would be consistent with the records expected not to be exceeded
in the database. Since these values are for an entire state of consumers, I
set the publisher range to 500,000 for that consumer table.
Three questions:
1. Does the publisher range sound excessive and would I be better off using
the auto-range to up that value or should I err on the high side initially?
2. On the subscriber side (all PPC), the normal range of records that would
be pushed would probably be 3000-5000 and the actual changes made would
probably only amount to about 500 per day per PPC. What would be a safe
range on the subscriber side?
3. Does it really matter how WIDE the range is set?
You really need to consider how long the PPC can go without a sync. Can
they stay disconnected for 5 days, 10, longer? Do some calculating and make
sure that not only the range is wide enough, but that the threshold is low
enough so they receive a new range to cover the next worst case senario.
One thing you can guarantee is that the user are not forced to sync
everyday...they probably will not.
"Earl" wrote:
> Using merge replication, I've decided to change over to identity ranges
> (versus uniqueidentifiers). At the publisher, I'm setting a range on the
> tables that would be consistent with the records expected not to be exceeded
> in the database. Since these values are for an entire state of consumers, I
> set the publisher range to 500,000 for that consumer table.
> Three questions:
> 1. Does the publisher range sound excessive and would I be better off using
> the auto-range to up that value or should I err on the high side initially?
> 2. On the subscriber side (all PPC), the normal range of records that would
> be pushed would probably be 3000-5000 and the actual changes made would
> probably only amount to about 500 per day per PPC. What would be a safe
> range on the subscriber side?
> 3. Does it really matter how WIDE the range is set?
>
>
|||Thanks Brian, those are good thoughts. A couple of followup questions: What
would the criteria be to ensure that the "range is wide enough"? I didn't
really understand about the threshold being "low enough".
"Brian Reuter" <BrianReuter@.discussions.microsoft.com> wrote in message
news:885F4F00-0793-478F-B363-7FCCF97C97C9@.microsoft.com...[vbcol=seagreen]
> You really need to consider how long the PPC can go without a sync. Can
> they stay disconnected for 5 days, 10, longer? Do some calculating and
> make
> sure that not only the range is wide enough, but that the threshold is low
> enough so they receive a new range to cover the next worst case senario.
> One thing you can guarantee is that the user are not forced to sync
> everyday...they probably will not.
>
>
> "Earl" wrote:
|||IMHO best way is to give range of 10.000.000 or even hundred million
and forget about ranges, tresholds etc. forever.
Max identity is something like 9223372036854775807, so why to be
worried about ranges.
Is there any drawback with this approach?
Pagus
On Fri, 12 Nov 2004 10:13:23 -0500, "Earl"
<brikshoe@.newsgroups.nospam> wrote:
[vbcol=seagreen]
>Thanks Brian, those are good thoughts. A couple of followup questions: What
>would the criteria be to ensure that the "range is wide enough"? I didn't
>really understand about the threshold being "low enough".
>"Brian Reuter" <BrianReuter@.discussions.microsoft.com> wrote in message
>news:885F4F00-0793-478F-B363-7FCCF97C97C9@.microsoft.com...
|||FAO Earl - Pagus is talking about BigInts (8 bytes) and
not Ints (4 bytes), where the range is +/-2billion or so.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I was thinking that range wouldn't work :=)
I want that replication book, can I get that as an ebook too (I didnt' see
anything on the site)?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:5e3f01c4c999$7e3be410$a601280a@.phx.gbl...
> FAO Earl - Pagus is talking about BigInts (8 bytes) and
> not Ints (4 bytes), where the range is +/-2billion or so.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Earl,
the book is now available - I've read a draft and it looks v.good.
Hilary mentioned something about internet updates, but I don't recall
hearing about it as an ebook. Hopefully he'll see this thread and reply to
you.
Rgds,
Paul Ibison, SQL Server MVP
|||No, ebook. The merge volume may be released as an ebook, but its future is
yet undecided.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Ot8sppbyEHA.2568@.TK2MSFTNGP11.phx.gbl...
> Earl,
> the book is now available - I've read a draft and it looks v.good.
> Hilary mentioned something about internet updates, but I don't recall
> hearing about it as an ebook. Hopefully he'll see this thread and reply to
> you.
> Rgds,
> Paul Ibison, SQL Server MVP
>
Identity range when rows already exist
I've created a merge publication with automatic range management. Insert
fails because the ranges assigned have already been used. How do I specify
that I want the new identity ranges to start above those which have already
been used?
I created this publication by backing up my production database and
restoring it to my test database. Then I created the publication on my test
database by manually editing the auto-generated script for creating the
publication on the production database. I don't know if this is the reason
things don't work out as I want them to.
I think you would be best to drop this publication and its subscriptions and
recreate from start.
If you are a masochist you can do the following.
Look in your distributor for a table called MSrepl_identity_range. The
highest range is the range which is deployed to one of your subscribers. You
can bump this value up to give yourself a cushion.
For instance if the highest range is 10000, bump it up to 20000, which will
be the next value assigned.
Now go to your problem subscriber and fix the table there. Use dbcc
checkident('tablename') to determine what the current range is, and then
reseed to the value you found on your publisher's distribution database
MSrepl_identity_range table.
Now issue a sp_help 'problemTableName' to get the name of the check
constraint used to restrict the range of possible values acceptable for this
table. script out the check constraint and recreate it with a set of values
which matches the range you assigned with the checkident reseed statement.
If you are really feeling like punishing yourself you might want to read
http://www.simple-talk.com/2005/07/05/replication/
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
"Daniel" <daXniel_kriXstensXen_@.hotmail.com (remove the Xs)> wrote in
message news:92B2698A-DE7C-485C-9381-78295E49A335@.microsoft.com...
> I've created a merge publication with automatic range management. Insert
> fails because the ranges assigned have already been used. How do I specify
> that I want the new identity ranges to start above those which have
already
> been used?
> I created this publication by backing up my production database and
> restoring it to my test database. Then I created the publication on my
test
> database by manually editing the auto-generated script for creating the
> publication on the production database. I don't know if this is the reason
> things don't work out as I want them to.
|||"Hilary Cotter" wrote:
> I think you would be best to drop this publication and its subscriptions and
> recreate from start.
I already did that. Perhaps the problem was that I created the publication
using the script generated by EM. For each merge article it did:
exec sp_addmergearticle ... @.article = [tableName] ...
go
To solve the problem, for all merge articles for which I use automatic range
management I added:
declare @.NewID int
Select @.newID = max(ID)+1 FROM [tableName]
DBCC CHECKIDENT([tableName],RESEED,@.newID)
exec sp_addmergearticle @.article = [tableName] ...
go
That is, I reseed the identity for each table before adding it to the
publication. It seems to work.
>If you are really feeling like punishing yourself you might want to read
> http://www.simple-talk.com/2005/07/05/replication/
Thanks I did that. You got all these great articles scattered all over the
net. But your book about merge replication is due any week now, right? It
would be nice to have the information gathered in one place
fails because the ranges assigned have already been used. How do I specify
that I want the new identity ranges to start above those which have already
been used?
I created this publication by backing up my production database and
restoring it to my test database. Then I created the publication on my test
database by manually editing the auto-generated script for creating the
publication on the production database. I don't know if this is the reason
things don't work out as I want them to.
I think you would be best to drop this publication and its subscriptions and
recreate from start.
If you are a masochist you can do the following.
Look in your distributor for a table called MSrepl_identity_range. The
highest range is the range which is deployed to one of your subscribers. You
can bump this value up to give yourself a cushion.
For instance if the highest range is 10000, bump it up to 20000, which will
be the next value assigned.
Now go to your problem subscriber and fix the table there. Use dbcc
checkident('tablename') to determine what the current range is, and then
reseed to the value you found on your publisher's distribution database
MSrepl_identity_range table.
Now issue a sp_help 'problemTableName' to get the name of the check
constraint used to restrict the range of possible values acceptable for this
table. script out the check constraint and recreate it with a set of values
which matches the range you assigned with the checkident reseed statement.
If you are really feeling like punishing yourself you might want to read
http://www.simple-talk.com/2005/07/05/replication/
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
"Daniel" <daXniel_kriXstensXen_@.hotmail.com (remove the Xs)> wrote in
message news:92B2698A-DE7C-485C-9381-78295E49A335@.microsoft.com...
> I've created a merge publication with automatic range management. Insert
> fails because the ranges assigned have already been used. How do I specify
> that I want the new identity ranges to start above those which have
already
> been used?
> I created this publication by backing up my production database and
> restoring it to my test database. Then I created the publication on my
test
> database by manually editing the auto-generated script for creating the
> publication on the production database. I don't know if this is the reason
> things don't work out as I want them to.
|||"Hilary Cotter" wrote:
> I think you would be best to drop this publication and its subscriptions and
> recreate from start.
I already did that. Perhaps the problem was that I created the publication
using the script generated by EM. For each merge article it did:
exec sp_addmergearticle ... @.article = [tableName] ...
go
To solve the problem, for all merge articles for which I use automatic range
management I added:
declare @.NewID int
Select @.newID = max(ID)+1 FROM [tableName]
DBCC CHECKIDENT([tableName],RESEED,@.newID)
exec sp_addmergearticle @.article = [tableName] ...
go
That is, I reseed the identity for each table before adding it to the
publication. It seems to work.
>If you are really feeling like punishing yourself you might want to read
> http://www.simple-talk.com/2005/07/05/replication/
Thanks I did that. You got all these great articles scattered all over the
net. But your book about merge replication is due any week now, right? It
would be nice to have the information gathered in one place
identity range check constraint
SQL 2005 merge replication.
I took the defaults on an article for publication - auto identity
management and the default ranges. If I attempt to insert >2000 rows
(the size of the 2 ranges assigned), I recieve the following error:
The insert failed. It conflicted with an identity range check
constraint in database 'DHD_73', replicated table
'dbo.tblAlaska_Facility_Manager', column 'facilityID'. If the identity
column is automatically managed by replication, update the range as
follows: for the Publisher, execute sp_adjustpublisheridentityrange;
for the Subscriber, run the Distribution Agent or the Merge Agent.
Does this mean I cannot do an insert of more than 2000 rows at a time
to this particular article?
TIA,
john g.
Sorry, I forgot to add, I am doing the inserts on the publisher
jg
jgmein...@.gmail.com wrote:
> SQL 2005 merge replication.
> I took the defaults on an article for publication - auto identity
> management and the default ranges. If I attempt to insert >2000 rows
> (the size of the 2 ranges assigned), I recieve the following error:
> The insert failed. It conflicted with an identity range check
> constraint in database 'DHD_73', replicated table
> 'dbo.tblAlaska_Facility_Manager', column 'facilityID'. If the identity
> column is automatically managed by replication, update the range as
> follows: for the Publisher, execute sp_adjustpublisheridentityrange;
> for the Subscriber, run the Distribution Agent or the Merge Agent.
> Does this mean I cannot do an insert of more than 2000 rows at a time
> to this particular article?
> TIA,
> john g.
|||You can batch up the inserts, or increase the size of the assigned rabge,
otherwise you're stuck.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Check the constraint to see what the range is. You can insert up to this
value and then run a sync. This should update the range.
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
<jgmeinder@.gmail.com> wrote in message
news:1167937361.253647.109410@.51g2000cwl.googlegro ups.com...
> SQL 2005 merge replication.
> I took the defaults on an article for publication - auto identity
> management and the default ranges. If I attempt to insert >2000 rows
> (the size of the 2 ranges assigned), I recieve the following error:
> The insert failed. It conflicted with an identity range check
> constraint in database 'DHD_73', replicated table
> 'dbo.tblAlaska_Facility_Manager', column 'facilityID'. If the identity
> column is automatically managed by replication, update the range as
> follows: for the Publisher, execute sp_adjustpublisheridentityrange;
> for the Subscriber, run the Distribution Agent or the Merge Agent.
> Does this mean I cannot do an insert of more than 2000 rows at a time
> to this particular article?
> TIA,
> john g.
>
I took the defaults on an article for publication - auto identity
management and the default ranges. If I attempt to insert >2000 rows
(the size of the 2 ranges assigned), I recieve the following error:
The insert failed. It conflicted with an identity range check
constraint in database 'DHD_73', replicated table
'dbo.tblAlaska_Facility_Manager', column 'facilityID'. If the identity
column is automatically managed by replication, update the range as
follows: for the Publisher, execute sp_adjustpublisheridentityrange;
for the Subscriber, run the Distribution Agent or the Merge Agent.
Does this mean I cannot do an insert of more than 2000 rows at a time
to this particular article?
TIA,
john g.
Sorry, I forgot to add, I am doing the inserts on the publisher
jg
jgmein...@.gmail.com wrote:
> SQL 2005 merge replication.
> I took the defaults on an article for publication - auto identity
> management and the default ranges. If I attempt to insert >2000 rows
> (the size of the 2 ranges assigned), I recieve the following error:
> The insert failed. It conflicted with an identity range check
> constraint in database 'DHD_73', replicated table
> 'dbo.tblAlaska_Facility_Manager', column 'facilityID'. If the identity
> column is automatically managed by replication, update the range as
> follows: for the Publisher, execute sp_adjustpublisheridentityrange;
> for the Subscriber, run the Distribution Agent or the Merge Agent.
> Does this mean I cannot do an insert of more than 2000 rows at a time
> to this particular article?
> TIA,
> john g.
|||You can batch up the inserts, or increase the size of the assigned rabge,
otherwise you're stuck.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Check the constraint to see what the range is. You can insert up to this
value and then run a sync. This should update the range.
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
<jgmeinder@.gmail.com> wrote in message
news:1167937361.253647.109410@.51g2000cwl.googlegro ups.com...
> SQL 2005 merge replication.
> I took the defaults on an article for publication - auto identity
> management and the default ranges. If I attempt to insert >2000 rows
> (the size of the 2 ranges assigned), I recieve the following error:
> The insert failed. It conflicted with an identity range check
> constraint in database 'DHD_73', replicated table
> 'dbo.tblAlaska_Facility_Manager', column 'facilityID'. If the identity
> column is automatically managed by replication, update the range as
> follows: for the Publisher, execute sp_adjustpublisheridentityrange;
> for the Subscriber, run the Distribution Agent or the Merge Agent.
> Does this mean I cannot do an insert of more than 2000 rows at a time
> to this particular article?
> TIA,
> john g.
>
Identity Range
Hi,
It has been a while that I have been struggling with an issue of Identity
Range. I have manually assigned Identity Ranges to the table. Inspite of
assigning a good range of 1000 records after adding only 100 records the
table says that it is out of identity range. Why is that?
Please help.
Thanks
issue a dbcc checkident('MytableName') and see what it says. Chances are
that you have blown the range somehow.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Microsoft Newsgroup" <joebloggs@.abc.org> wrote in message
news:%23wEyU369GHA.4268@.TK2MSFTNGP02.phx.gbl...
> Hi,
> It has been a while that I have been struggling with an issue of Identity
> Range. I have manually assigned Identity Ranges to the table. Inspite of
> assigning a good range of 1000 records after adding only 100 records the
> table says that it is out of identity range. Why is that?
> Please help.
> Thanks
>
|||I am wondering how did a user blow up the range without entering any data...
here is the result from the dbcc
Checking identity information: current identity value '230030', current
column value '310054'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OJCF3K79GHA.4468@.TK2MSFTNGP05.phx.gbl...
> issue a dbcc checkident('MytableName') and see what it says. Chances are
> that you have blown the range somehow.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Microsoft Newsgroup" <joebloggs@.abc.org> wrote in message
> news:%23wEyU369GHA.4268@.TK2MSFTNGP02.phx.gbl...
>
It has been a while that I have been struggling with an issue of Identity
Range. I have manually assigned Identity Ranges to the table. Inspite of
assigning a good range of 1000 records after adding only 100 records the
table says that it is out of identity range. Why is that?
Please help.
Thanks
issue a dbcc checkident('MytableName') and see what it says. Chances are
that you have blown the range somehow.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Microsoft Newsgroup" <joebloggs@.abc.org> wrote in message
news:%23wEyU369GHA.4268@.TK2MSFTNGP02.phx.gbl...
> Hi,
> It has been a while that I have been struggling with an issue of Identity
> Range. I have manually assigned Identity Ranges to the table. Inspite of
> assigning a good range of 1000 records after adding only 100 records the
> table says that it is out of identity range. Why is that?
> Please help.
> Thanks
>
|||I am wondering how did a user blow up the range without entering any data...
here is the result from the dbcc
Checking identity information: current identity value '230030', current
column value '310054'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OJCF3K79GHA.4468@.TK2MSFTNGP05.phx.gbl...
> issue a dbcc checkident('MytableName') and see what it says. Chances are
> that you have blown the range somehow.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Microsoft Newsgroup" <joebloggs@.abc.org> wrote in message
> news:%23wEyU369GHA.4268@.TK2MSFTNGP02.phx.gbl...
>
Wednesday, March 7, 2012
Identity columns after failover
Hi,
I was doing some testing of Identity columns with 'Not For Replication'
and failovers. I wanted to test Identity column value ranges. Here is some
back ground info. Both the Publisher and Subscriber have Identity column with
the 'Not For Replication' clause. Both have the same (default) value range
(seed) and increment of 1. On the Publisher, I can insert or add new rows of
data with the new Identity column value being replicated to the standby
successfully. When I break replication and make my subscriber the primary
(and only) server, the first time I insert a new row I get the 'duplicate
key' error. I get this regardless of what tool or application I use. It also
does seem to make a difference if I insert the new row immediately after the
failover or 20 minutes later. However, on the second and all other attempts
to insert a new row it works with no errors what so ever. This is true
regardless if I've set up replication to replicate back to the original
publisher (now the subscriber) or not.
So, my question is, why is this happening? What am I over looking on
failover that gives me the duplicate key error?
Thanks for your replies.
JD
JD,
you'll either need to enable automatic range management with an appropriate
seed, or manually reseed the subscriber. Replication is doing the identity
insert for you, but if you use dbcc checkident you'll see that the seed
value isn't changing, so the first value added after failover has the value
of 1 which was already taken.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thank you for the answer.
Joe
"Paul Ibison" wrote:
> JD,
> you'll either need to enable automatic range management with an appropriate
> seed, or manually reseed the subscriber. Replication is doing the identity
> insert for you, but if you use dbcc checkident you'll see that the seed
> value isn't changing, so the first value added after failover has the value
> of 1 which was already taken.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||One more quick question Paul,
Besides the DBCC CHECKIDENT to check and reseed the column, is there a
Stored Procedure that can be used? I heard there was but I haven't found any
supporting documentation.
Thanks again,
JD
"JD" wrote:
[vbcol=seagreen]
> Paul,
> Thank you for the answer.
> Joe
> "Paul Ibison" wrote:
|||To reseed this is the only way. There are things like truncate table
which'll reseed as a side effect, but practically dbcc checkident is the
only way.
Rgds,
Paul Ibison
"JD" <John316@.online.nospam> wrote in message
news:1134F064-2FD4-4DBF-BD18-20ABD6830CAB@.microsoft.com...[vbcol=seagreen]
> One more quick question Paul,
> Besides the DBCC CHECKIDENT to check and reseed the column, is there a
> Stored Procedure that can be used? I heard there was but I haven't found
> any
> supporting documentation.
> Thanks again,
> JD
> "JD" wrote:
|||Once again, Thank you Paul.
"Paul Ibison" wrote:
> To reseed this is the only way. There are things like truncate table
> which'll reseed as a side effect, but practically dbcc checkident is the
> only way.
> Rgds,
> Paul Ibison
>
> "JD" <John316@.online.nospam> wrote in message
> news:1134F064-2FD4-4DBF-BD18-20ABD6830CAB@.microsoft.com...
>
>
I was doing some testing of Identity columns with 'Not For Replication'
and failovers. I wanted to test Identity column value ranges. Here is some
back ground info. Both the Publisher and Subscriber have Identity column with
the 'Not For Replication' clause. Both have the same (default) value range
(seed) and increment of 1. On the Publisher, I can insert or add new rows of
data with the new Identity column value being replicated to the standby
successfully. When I break replication and make my subscriber the primary
(and only) server, the first time I insert a new row I get the 'duplicate
key' error. I get this regardless of what tool or application I use. It also
does seem to make a difference if I insert the new row immediately after the
failover or 20 minutes later. However, on the second and all other attempts
to insert a new row it works with no errors what so ever. This is true
regardless if I've set up replication to replicate back to the original
publisher (now the subscriber) or not.
So, my question is, why is this happening? What am I over looking on
failover that gives me the duplicate key error?
Thanks for your replies.
JD
JD,
you'll either need to enable automatic range management with an appropriate
seed, or manually reseed the subscriber. Replication is doing the identity
insert for you, but if you use dbcc checkident you'll see that the seed
value isn't changing, so the first value added after failover has the value
of 1 which was already taken.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thank you for the answer.
Joe
"Paul Ibison" wrote:
> JD,
> you'll either need to enable automatic range management with an appropriate
> seed, or manually reseed the subscriber. Replication is doing the identity
> insert for you, but if you use dbcc checkident you'll see that the seed
> value isn't changing, so the first value added after failover has the value
> of 1 which was already taken.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||One more quick question Paul,
Besides the DBCC CHECKIDENT to check and reseed the column, is there a
Stored Procedure that can be used? I heard there was but I haven't found any
supporting documentation.
Thanks again,
JD
"JD" wrote:
[vbcol=seagreen]
> Paul,
> Thank you for the answer.
> Joe
> "Paul Ibison" wrote:
|||To reseed this is the only way. There are things like truncate table
which'll reseed as a side effect, but practically dbcc checkident is the
only way.
Rgds,
Paul Ibison
"JD" <John316@.online.nospam> wrote in message
news:1134F064-2FD4-4DBF-BD18-20ABD6830CAB@.microsoft.com...[vbcol=seagreen]
> One more quick question Paul,
> Besides the DBCC CHECKIDENT to check and reseed the column, is there a
> Stored Procedure that can be used? I heard there was but I haven't found
> any
> supporting documentation.
> Thanks again,
> JD
> "JD" wrote:
|||Once again, Thank you Paul.
"Paul Ibison" wrote:
> To reseed this is the only way. There are things like truncate table
> which'll reseed as a side effect, but practically dbcc checkident is the
> only way.
> Rgds,
> Paul Ibison
>
> "JD" <John316@.online.nospam> wrote in message
> news:1134F064-2FD4-4DBF-BD18-20ABD6830CAB@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)