Re: General SQL Server 2005 transactional replication question

Log shipping has the problems of not really being scalable for large
databases (although I know an online bank who uses it extensively and is
happy with it), and having a larger exposure to data loss than other
options.

--
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:EDF99D5B-0A29-46E1-BDB5-7B542B04DEDF@xxxxxxxxxxxxxxxx
Hilary,

It's certainly something I can discuss but I have a feeling it'll get
blocked.
Did you have any other thoughts on my last post?

Thanks,
Kev.

"Hilary Cotter" wrote:

A unique index is like a primary key with the exception that it tolerates
a
single null and you can have multiple unique indexes on a table.

I would evaluate whether you can change your unique indexes into real
PKs.

--
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:B3424403-9546-4654-87A8-2D0CDCE702E8@xxxxxxxxxxxxxxxx
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

  • SQL Mobile Replication subsciption and Hard Reset
    ... Database is stored on an internal flah memory. ... my replication doesn't allow multiple subcription per partition. ... Is there a way to preserve subscription after device hard reset? ...
    (microsoft.public.sqlserver.replication)
  • RE: Orphaned subscription
    ... database or at the Subscriber on the subscription database. ... This procedure should be used only if other methods of removing replication ... Removes transactional replication publishing objects. ...
    (microsoft.public.sqlserver.replication)
  • RE: Orphaned subscription
    ... database or at the Subscriber on the subscription database. ... This procedure should be used only if other methods of removing replication ... Removes transactional replication publishing objects. ...
    (microsoft.public.sqlserver.replication)
  • Re: updating subscriber error for transaction replication
    ... Hilary Cotter, Thanks! ... Actually the subscription is still there. ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: looking for more info on merge repl problem
    ... create database test ... -- Adding the merge subscription ... Looking for a book on SQL Server replication? ... > However - I know of no problems with having different owner names on the> Publisher or Subscriber, or non dbo owner names on both your Publihser and> Subscriber. ...
    (microsoft.public.sqlserver.replication)