Re: a different identiy columns in replication question

From: Hilary Cotter (hilaryk_at_att.net)
Date: 07/18/04 

Date: Sun, 18 Jul 2004 08:09:13 -0400

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
> queued benifit the cause?
>
>
>
> "Hilary Cotter" <hilaryk@att.net> wrote in message
> news:u3h6VFHbEHA.1656@TK2MSFTNGP09.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
> > > 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...
> > > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Updatable Subscription in SQL 7.0
    ... I would use pure bi-directional transactional replication. ... you were not using immediate updating. ... > "Hilary Cotter" wrote in message ...
    (microsoft.public.sqlserver.replication)
  • Re: Two Way Transactional
    ... "Hilary Cotter" wrote: ... > the not for replication switch on the identity colummns. ... >> identity on serverA with a seed of B using odds and the identity on ... >> transactional replication? ...
    (microsoft.public.sqlserver.replication)
  • Re: Synchronising In-House and Web Data
    ... Since transactional replication is transaction based, ... Director of Text Mining and Database Strategy ... The CSV files we send to the web-server are in the ...
    (microsoft.public.sqlserver.replication)
  • Re: bi-directional vs updatable subscriptions
    ... Bi-directional replication strictly speaking is where you have data ... replication, bi-directional transactional replication, snapshot replication ... best used when the majority of the DML originates on the publisher. ...
    (microsoft.public.sqlserver.replication)
  • Re: Replication Architecture/Design advise
    ... bi-directional transactional replication to be ... publisher goes offline for an extended time period. ... > Does this mean that the standby server can update the primary? ...
    (microsoft.public.sqlserver.replication)