Re: General SQL Server 2005 transactional replication question

answers inline.

--
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



"Kev" <Kev@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5FD151BC-FDC4-4AC7-A463-742BDE363F43@xxxxxxxxxxxxxxxx
Thanks for your replies.

1) If the primary is down and I'm now running off the secondary
(subscriber), presumably all my changes to that database are relayed to
the
distribution database. If the primary is still down do they just
accumulate
in the distribution database until it comes back up? At that point are
they
then automatically delivered to the primary, or is there some manual
process
involved? What difference does usingnot using queued make in this process?

Exactly, when the primary is down, they will be queued in the distribution
database on the subscriber. With queued updating triggers are involved and
it will not be as scalable as with bi-directional transactional replication.

2) For the setup described, do I need to configure both servers as
distributors? I don't have an additional standalone windows server
available
to put a distribution database on.

Yes, you don't need a standalone distributor but it is recommended if your
publisher(s) is under high load.

3) The posts I referred to were discussing how NFR didn't work properly
when
using updating subscribers - ie: when inserting a row into the subscriber
database, apparently two rows (one from replication, one from trigger
firing)
were inserted into the primary. It sounds like from your answers that this
is
not the case?

Yes, with NFR it is not the case. Can you post links to the posts you are
referrring to here?


Many thanks.

"Hilary Cotter" wrote:

I would use bi-directional transaction replication with push subscribers.
With the NFR property on your triggers it will work correctly.
Peer-to-peer
does offer the feature where you can replicate ddl. With pure
bi-directional
replication you will have to drop the publications before making schema
changes.

I would not recommend using queued, it is best used when the majority of
the
DML originates on the publisher.

--
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



"Kev" <Kev@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2FDF2BB2-1861-4400-B616-82C0273A9E80@xxxxxxxxxxxxxxxx
I want to implement transactional replication for failover purposes. I
need
to replicate data from a primary database to a secondary one that will
be
used if the primary goes down. In the event of a primary failure I need
to
switch the applications to run off the secondary. When the primary is
back
up
I then need to update it with all the changes made to the secondary
while
the
primary was down, switch the replication direction, and switch the
applications back to the primary again.

I'd like some advise on how best to set this up. For instance:
1) How do I go about delivering secondary updates back to the repaired
primary?
2) Should I use push or pull subscription?
3) The database uses a number of triggers. How should I ensure that
replication works correctly for these - is it just a matter of using
the
NOT
FOR REPLICATION option? I've read a couple of posts indicating that
this
may
not work properly in the case of updateable subscriptions.
4)Does peer-to-peer replication offer any advantages over the
traditional
publication-subscription in this scenario?

Any assistance much appreciated. Apologies for the scope of the
question,
but I'm new to sql server replication and would like some expert advice
to
get me started.





.

Relevant Pages

  • Re: General SQL Server 2005 transactional replication question
    ... (subscriber), presumably all my changes to that database are relayed to the ... in the distribution database until it comes back up? ... database, apparently two rows (one from replication, one from trigger firing) ...
    (microsoft.public.sqlserver.replication)
  • Re: Backup Question with Replication enabled
    ... Basically the subscriber database contains a marker in it of the last ... transaction replicated to it from the distribution database. ... So when you restore the database the commands the marker says ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Index Tuning Wizard and Transactional Replication
    ... Looking for a SQL Server replication book? ... "Marcio G Macedo" wrote in message ... > Running the Index Tuning Wizard against a database that is the Publisher ...
    (microsoft.public.sqlserver.replication)
  • Re: replication server crashed
    ... Job history and job information is stored in the msdb database including ... information about the replication jobs. ... the distribution database and the publication database. ... the subscriber to the publisher, or will ALL data from the subscriber be ...
    (microsoft.public.sqlserver.replication)
  • Re: identity ranges
    ... distribution database, I changed it in the database to be replicated. ... >> Looking for a SQL Server replication book? ... >>> dbcc checkident ...
    (microsoft.public.sqlserver.replication)
Loading