Re: General SQL Server 2005 transactional replication question

Tech-Archive recommends: Fix windows errors by optimizing your registry

It looks like from the info you've both provided that I may not be able to
use transactional replication after all. The main problem is that the
database makes little use of primary key constraints (just uses unique
indexes) and I would have to add them to all replicated tables which is
probably a no goer from a retesting standpoint at present, although I think
it will definitely need to be scheduled later on.
With this issue in mind:
Is there anything else I can use or any "workaround" that you know of?
Presumably peer-to-peer and bi-directional replication would require the same
primary key changes? From what I've read it seems to be the transactional
publication part that requires the primary key constraints rather than the
subscription side per se, so this would rule out things like using
transactional replication to the secondary and then using log shipping back
to the primary.
The system has a large number of real-time updates and inserts, so I think
that snapshot and merge replication are out for starters.
Log shipping would be a fall back position.

Thanks,
Kev.

"Hilary Cotter" wrote:

There is a lot involved.

1) create the publication on the publisher and create the subscription,
script it out and add the loopback_detection=true parameter to the
sp_addsnapshot proc.
2) drop the publication but save the modified script
3) backup the database on the publisher and apply it on the subscriber (or
initialize subscription from backup if using sql 2005)
4) make the seed 1 and the increment two on all the identity columns on the
publisher, make the seed 0 and the increment 2 on all the identity columns
on the subscriber.
5) change all identity columns, triggers, and constraints to NFR.
6) run dbcc checkident to make sure the next value to be assigned on the
publisher is odd and on the subscriber is even.
7) generate the replication procs, edit them to make sure the identity
column is never updated.
8) Create your publication and subscription using a no-sync subscription,
edit the script for the subscriber and run it on the subscriber.
--
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:B1302F53-C6E5-498A-8E7B-F9F05F58D4F2@xxxxxxxxxxxxxxxx
Hilary,

In addition to my earlier reply to your reply:
Does bidirectional replication necessarily involve a lot of scripting -
ie:
enabling each article for replication in each database? Or is there a
short
cut via wizards or higher level constructs?
Is it possible to add additional read-only subscriptions?

Thanks,
Kev.

"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: Cannot Update Identity Column
    ... > Looking for a SQL Server replication book? ... The publication pushes a subscription to a database on the same ... The articles are setup to "Keep the existing table ...
    (microsoft.public.sqlserver.replication)
  • Re: Drop replication question
    ... would need to drop subscription and publication. ... failed due to replication being active. ... the time it takes to recreate the publication and subscription isn't an issue ...
    (microsoft.public.sqlserver.replication)
  • Re: General SQL Server 2005 transactional replication question
    ... "Hilary Cotter" wrote: ... Looking for a SQL Server replication book? ... publication part that requires the primary key constraints rather than the ... create the publication on the publisher and create the subscription, ...
    (microsoft.public.sqlserver.replication)
  • Need help - desperate
    ... transactional replication in SQL server 2005. ... Distributor database is in the same server as the publisher database. ... There was no publication to be dropped. ...
    (microsoft.public.sqlserver.replication)
  • Re: Need help - desperate
    ... I executed the command and recreated the publication and subscription. ... Looking for a SQL Server replication book? ... that there were no jobs running with this database name. ...
    (microsoft.public.sqlserver.replication)