Fixing a screwup

After synching a handful of subscribers, I reviewed the data that came back
and realized I forgot to set auto range identity on two tables that required
it. So I dumped the pub, dumped the subscriptions, manually assigned the
identity values to consecutive identities, reset the identity columns to
"not for replication", re-created the publication, then let the subscribers
resynch.
Before anyone goes berserk on manually assigning an identity value, note
that at this time, the identity values on those two tables (at the "end of
the food chain" so to speak) are not being used or keyed to other tables,
but may in the future. Whether a record is 1 or 15 or whatever is
irrelevant, but I want to simply ensure uniqueness for future use.
So, my question is, have I overlooked anything or are there other hidden
potential issues?
Earl,
I'm not too sure what you did. It seems as though you set up a nosync
initialization? Or was it a normal initialization but without automatic
range management?
In one place I worked they would often forget to set the automatic range
management on articles. Rather than reinitializing, which was simply not
possible, what they'd do is reseed the identity value at the subscriber.
There were only 3 subscribers, so there was never a posibility of range
overlaps. The main difference to automatic range management was the absence
of check constraints and no possibility of getting a new range from
MSrepl_identity_range but as the difference between seeds was so large it
would be 200 years or so before a clash occurs.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul, it was a normal initialization but without automatic range management.
It's an easy thing to forget, especially if you've had to go through a lot
of contortions massaging the data into shape. The subscribers are PocketPCs,
so it would've been a real PIA to reseed those values. The identity values
back in the pub (once synched) were like this:
1
2
3
...
1
2
3
...
1
2
3
Seemed easier to drop the pub and the subscriptions, then do a manual reseed
of the pub tables (had I not caught it immediately and thus been dealing
with a large number of records, I'm not sure how I would've handled the
situation):
1
2
3
4
5
6
7
8
9
...
Which brings to mind a followup question: If my subscribers had the same
name as their pubs then I could've just created a new publication AND
subscription and just moved on? Thanks for your thoughts!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e04BD$vdFHA.892@.tk2msftngp13.phx.gbl...
> Earl,
> I'm not too sure what you did. It seems as though you set up a nosync
> initialization? Or was it a normal initialization but without automatic
> range management?
> In one place I worked they would often forget to set the automatic range
> management on articles. Rather than reinitializing, which was simply not
> possible, what they'd do is reseed the identity value at the subscriber.
> There were only 3 subscribers, so there was never a posibility of range
> overlaps. The main difference to automatic range management was the
> absence of check constraints and no possibility of getting a new range
> from MSrepl_identity_range but as the difference between seeds was so
> large it would be 200 years or so before a clash occurs.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Earl,
"Which brings to mind a followup question: If my subscribers had the same
name as their pubs then I could've just created a new publication AND
subscription and just moved on? Thanks for your thoughts!"
- sorry - I'm probably being a bit thick, but I don't follow this. How can
the subscriber have the same computer name as the publisher, and in what way
would this simplify things?
Rgds,
Paul Ibison (www.replicationanswers.com)
|||Hehe ... merge publication on a PocketPC, subscribers assign their own
subscription name, which I usually just keep as the same name across the
board. I have an input form in my app where the users can enter all of their
replication settings. In this case, once I had reseeded the tables, I
could've just created a new pub and set up auto range management at the same
time. Instead of the users synching to the old pub, they would enter the new
pub and subscription names, tap the synch button (which would create a new
db onboard and download the new pub) and keep on trucking. Perhaps it is me
that is "thick" on this one because that may or may not have worked (maybe
I'll try that next time).
Sorry for not including more about how my app works -- I'm primarily a
developer and not a DBA (although since I spend so much time with SLQ
Server, I decided long ago to go ahead and try to get the MCDBA cert).
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ehq9zp0dFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Earl,
> "Which brings to mind a followup question: If my subscribers had the same
> name as their pubs then I could've just created a new publication AND
> subscription and just moved on? Thanks for your thoughts!"
> - sorry - I'm probably being a bit thick, but I don't follow this. How can
> the subscriber have the same computer name as the publisher, and in what
> way would this simplify things?
> Rgds,
> Paul Ibison (www.replicationanswers.com)
>