a different identiy columns in replication question

sql2k sp3
Ive got a little bit of a Replication background but never
with "immediate updating with queued updating for
failover" like Im testing now. In fact Ive never even done
just "immediate updating". I seen lots of horror stories
here about identity columns causing replication problems
for people and was expecting to get them during my testing
this week but I havent. Im curious as to why and thought
I'd ask. Heres what I've done in testing:
1; Made one big Publication of all my tables.
2; Did a Backup/ Restore to the Subscriber.
3; Took the actions as outlined in KB 320499.
4; Took the actions as outlined in KB 320773.
Everything is up and running at this point. Replication
runs fine in both directions. Identity columns on both the
Pub and Sub are in place. I am NOT using the "Yes(Not for
Replication)" option on either box nor have I modified the
ranges on either box. This is why I thought I'd have
problems. I thought Id need to place different ranges on
them and use the "Not for Replication" option on them. But
I didn't and am having no problems. Why? Not that Im
complaining. I even did a failover test by turinng off the
Publisher and switching to Queued Updating. I did inserts
while it was in that mode and still had no problems.
Again Im not upset by my success. But just dont get why
others have the problems Ive read about and I dont? There
is something about my settings that is correct I am
curious to find out what it is.
TIA, ChrisR
Chris,
the errors people have reported come from a variety of causes. Often it is
incorrect range management - either manually or on behalf of SQL Server.
Sometimes the problems have been in using the standby server when the
internal identity value of a column hasn't been updated. In some cases
upating the identity value is not possible even through DBCC CHECKIDENT.
In your scenario as I understand it, there is no allowance for the publisher
and subscriber being allocated the same identity value. This may not be a
problem for you as yet, but if someone on the subscriber attempts to insert
a record and network connectivity is temporarily down, it'll go into the
queue and when the queue reader starts, there could be conflicts. To avoid
this you can have SQL Server allocate an identity range for you or you can
manually create the range. The latter is quite straightforward if you have
realtively few subscribers. EG if you had one subscriber, the publisher
could have a seed of 1 and increment of 2 (odd nos), while the subscriber
has a seed of 2 and increment of 2 (even nos).
HTH,
Paul Ibison
|||This may not be a
>problem for you as yet, but if someone on the subscriber
attempts to insert
>a record and network connectivity is temporarily down,
it'll go into the
>queue and when the queue reader starts, there could be
conflicts.
This is the scenario I did in testing. Turned off the
Publisher, inserted into the Subscriber. When the Pub was
back up there was no issues. You are saying there could be
conflict as I beleive you. Do you know what the
circumstances are that would amke this happen?
Thanks

>--Original Message--
>Chris,
>the errors people have reported come from a variety of
causes. Often it is
>incorrect range management - either manually or on behalf
of SQL Server.
>Sometimes the problems have been in using the standby
server when the
>internal identity value of a column hasn't been updated.
In some cases
>upating the identity value is not possible even through
DBCC CHECKIDENT.
>In your scenario as I understand it, there is no
allowance for the publisher
>and subscriber being allocated the same identity value.
This may not be a
>problem for you as yet, but if someone on the subscriber
attempts to insert
>a record and network connectivity is temporarily down,
it'll go into the
>queue and when the queue reader starts, there could be
conflicts. To avoid
>this you can have SQL Server allocate an identity range
for you or you can
>manually create the range. The latter is quite
straightforward if you have
>realtively few subscribers. EG if you had one subscriber,
the publisher
>could have a seed of 1 and increment of 2 (odd nos),
while the subscriber
>has a seed of 2 and increment of 2 (even nos).
>HTH,
>Paul Ibison
>
>.
>
|||Chris,
to test this you can force failover, or more easily set up an alternative
test system with just a queue. Stop the queue reader agent and distribution
agent. Insert a record into the publisher and subscriber, and the 2 new
records will have the same identity value.
When starting the queue reader there will be a conflict registered which is
viewable i the conflict viewer.
This is not an error like in some of the other posts, but it is a problem of
lost data whch can be avoided by partitioning the identity range.
HTH,
Paul Ibison
|||With immediate updating you are guananteed not to have identity range
problems.
The reason is that any update that happens on the subscriber is first
applied on the publisher where the publisher's identity range rules.
The problem of course is your publisher/subscriber must be well connected
and the publisher must always be online. If so, updates on your subscriber
are rolled back. If the link between the publisher and subscribers goes
down, updates can still occur on the publisher.
With queued, when your publisher is offline, all updates happen on the
subcsriber, so again, no identity problems as no updates happen on the
publisher.
As queued is an asynchronous process when the publisher comes back on line
unless you revert back to immediate you can have identity range problems
unless you are using automatic identity range management.
Automatic Identity Range Management is basically trouble free. You run into
problems with it when you have a range of lets say 100, and a batch update
that updates 1000 rows (or really anything over the 100 range). The procs
which do the automatic range management don't have time to work during the
batch and you get the problem.
So pick a range which is large. Many dba's pick very large ranges which they
know will not be blown for the lifetime of their replication solution. This
option is called set it and forget it. It works very well.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:2ef4e01c46b7b$643c0120$a301280a@.phx.gbl...
> sql2k sp3
> Ive got a little bit of a Replication background but never
> with "immediate updating with queued updating for
> failover" like Im testing now. In fact Ive never even done
> just "immediate updating". I seen lots of horror stories
> here about identity columns causing replication problems
> for people and was expecting to get them during my testing
> this week but I havent. Im curious as to why and thought
> I'd ask. Heres what I've done in testing:
> 1; Made one big Publication of all my tables.
> 2; Did a Backup/ Restore to the Subscriber.
> 3; Took the actions as outlined in KB 320499.
> 4; Took the actions as outlined in KB 320773.
> Everything is up and running at this point. Replication
> runs fine in both directions. Identity columns on both the
> Pub and Sub are in place. I am NOT using the "Yes(Not for
> Replication)" option on either box nor have I modified the
> ranges on either box. This is why I thought I'd have
> problems. I thought Id need to place different ranges on
> them and use the "Not for Replication" option on them. But
> I didn't and am having no problems. Why? Not that Im
> complaining. I even did a failover test by turinng off the
> Publisher and switching to Queued Updating. I did inserts
> while it was in that mode and still had no problems.
> Again Im not upset by my success. But just dont get why
> others have the problems Ive read about and I dont? There
> is something about my settings that is correct I am
> curious to find out what it is.
> TIA, ChrisR
|||Thanks Hillary and Paul. I just realized from reading your responses that Im
not totally positive if this box will be used just for fail over if the
Publisher goes down or not. Im not sure, but I dont think the two of them
will ever be used at the same time and the Subscriber will be written to
only if the Pub is off line. If this is the case, I don't think I will have
the identity range problem will I? Come to think of it, should I switch the
whole plan over just to Queued Updating if this is the case? Would I benifit
from that in any way?
Thanks alot you guys for your help.
ChrisR
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:#4F46$$aEHA.3420@.TK2MSFTNGP12.phx.gbl...
> With immediate updating you are guananteed not to have identity range
> problems.
> The reason is that any update that happens on the subscriber is first
> applied on the publisher where the publisher's identity range rules.
> The problem of course is your publisher/subscriber must be well connected
> and the publisher must always be online. If so, updates on your subscriber
> are rolled back. If the link between the publisher and subscribers goes
> down, updates can still occur on the publisher.
> With queued, when your publisher is offline, all updates happen on the
> subcsriber, so again, no identity problems as no updates happen on the
> publisher.
> As queued is an asynchronous process when the publisher comes back on line
> unless you revert back to immediate you can have identity range problems
> unless you are using automatic identity range management.
> Automatic Identity Range Management is basically trouble free. You run
into
> problems with it when you have a range of lets say 100, and a batch update
> that updates 1000 rows (or really anything over the 100 range). The procs
> which do the automatic range management don't have time to work during the
> batch and you get the problem.
> So pick a range which is large. Many dba's pick very large ranges which
they
> know will not be blown for the lifetime of their replication solution.
This
> option is called set it and forget it. It works very well.
>
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:2ef4e01c46b7b$643c0120$a301280a@.phx.gbl...
>
|||queued and bi-directional transactional replication are options. If you
expect schema changes I would use queued as opposed to bi-directional
transactional,
Queued will add a guid column to all tables you are replicating however.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <chris@.noemail.com> wrote in message
news:OQ4yT8BbEHA.3524@.TK2MSFTNGP12.phx.gbl...
> Thanks Hillary and Paul. I just realized from reading your responses that
Im
> not totally positive if this box will be used just for fail over if the
> Publisher goes down or not. Im not sure, but I dont think the two of them
> will ever be used at the same time and the Subscriber will be written to
> only if the Pub is off line. If this is the case, I don't think I will
have
> the identity range problem will I? Come to think of it, should I switch
the
> whole plan over just to Queued Updating if this is the case? Would I
benifit[vbcol=seagreen]
> from that in any way?
> Thanks alot you guys for your help.
> ChrisR
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:#4F46$$aEHA.3420@.TK2MSFTNGP12.phx.gbl...
connected[vbcol=seagreen]
subscriber[vbcol=seagreen]
line[vbcol=seagreen]
> into
update[vbcol=seagreen]
procs[vbcol=seagreen]
the
> they
> This
>
|||Ive done a bit of schema changes on replicated tables in the past. How does
queued benifit the cause?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u3h6VFHbEHA.1656@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> queued and bi-directional transactional replication are options. If you
> expect schema changes I would use queued as opposed to bi-directional
> transactional,
> Queued will add a guid column to all tables you are replicating however.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:OQ4yT8BbEHA.3524@.TK2MSFTNGP12.phx.gbl...
that[vbcol=seagreen]
> Im
them[vbcol=seagreen]
> have
> the
> benifit
> connected
> subscriber
goes[vbcol=seagreen]
> line
problems[vbcol=seagreen]
> update
> procs
> the
which
>
|||with bi-directional transactional replication you have to drop both
publications, make changes on both sides and rebuild. You can't use
sp_repladdcolumn or sp_repldropcolumn when you are doing bi-directional
transactional replication.
You can use these stored procedures when you are using transactional
replication with queued updating subscribers.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <chris@.noemail.com> wrote in message
news:O$rxpQJbEHA.1732@.TK2MSFTNGP09.phx.gbl...
> Ive done a bit of schema changes on replicated tables in the past. How
does[vbcol=seagreen]
> queued benifit the cause?
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:u3h6VFHbEHA.1656@.TK2MSFTNGP09.phx.gbl...
> that
the[vbcol=seagreen]
> them
to[vbcol=seagreen]
switch[vbcol=seagreen]
range[vbcol=seagreen]
first[vbcol=seagreen]
> goes
the[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
> problems
run[vbcol=seagreen]
during[vbcol=seagreen]
> which
solution.
>
|||Thanks Hilary. Ive used sp_repladdcolumn in the past in transactional repl
but it wasnt bi-directional. I assumed I could use it now as well. Good to
know. I'll find out all the requirements this weeks and will now be more
informed on which road to take. Thanks again.
CR
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:e$1Ix$LbEHA.2216@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> with bi-directional transactional replication you have to drop both
> publications, make changes on both sides and rebuild. You can't use
> sp_repladdcolumn or sp_repldropcolumn when you are doing bi-directional
> transactional replication.
> You can use these stored procedures when you are using transactional
> replication with queued updating subscribers.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:O$rxpQJbEHA.1732@.TK2MSFTNGP09.phx.gbl...
> does
you[vbcol=seagreen]
however.[vbcol=seagreen]
> the
written[vbcol=seagreen]
> to
will[vbcol=seagreen]
> switch
> range
> first
rules.[vbcol=seagreen]
> the
> the
> on
> run
batch[vbcol=seagreen]
The
> during
> solution.
>