General SQL Server 2005 transactional replication question

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.

 

I would consider using queued updating subscribers for this purpose. You can
have identity columns taken care of and fail back is relatively simple -
just run the queue reader agent. All triggers shoulld be ser to NFR and the
triggered changes replicated as per normal. Peer-to-peer wouldn't enable
change management (schema changes) so easily so I'd still use queued
updating subscribers instead. Only extra consideration is BLOB datatypes
which would prohibit this method.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

 

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.

 

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?

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.

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?

Many thanks.

 

Kev,
your questions really apply to Hilary's recommendation (my answer applied to
queued updating subscribers which I prefer n this scenario) so I'll leave
this one to him.
Rgds,
Paul Ibison

 

Hi Paul,

Could you tell me your reasons for preferring queued updating subscriptions?

Regards,
Kev.

 

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.

 

It's out of the box, deals with schema changes, handles identity ranges
automatically and you can easily find what is waiting at the subscriber to
be sent back to the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

 

Paul,
Is it mandatory that you need primary key constraints on all replicated
tables in queued updating subscribers? Is a unique index good enough?
Also, on which server would you place the various agents - queue reader,
distributor etc?

Thanks,
Kev.

 

answers inline.

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.

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

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

 

Immediate handles identity ranges transparently, with queued you have the
option to use automatic identity ranges which if not well designed can be
highly problematic.

 

Kev,
yes - PK is mandatory. Queue reader will be on subscriber. Distribution
agent depends on push or pull and pros and cons of each approach. To offload
some processing usage onto the subscriber I'd use pull as this server is
unlikely to be used until failover. Also need to set automatic identity
range management on each relevant article. Main caveat is for BLOBS - you
won't be able to replicate back from the subscriber to the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

 

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.

 

The thing is that Kev wants to be able to make changes to the subscriber
while the publisher is being repaired, so immediate updating wouldn't work.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

 

Yikes, I just realized that with sql 2005 queued has automatic identity
range management enabled by default. Sorry about that.

 

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.

 

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,

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.

 

In that case I'd go for log shipping. Once the primary server is back up and
going, you could restore the database from the standby server or use
DataCompare to sync up the databases. Higher latency than replication but it
looks like you have little choice. SQL Server 2005 will offer mirroring
which you can use as a lower latency version of log shipping.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

 

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.