General SQL Server 2005 transactional replication question

Discussion in 'microsoft.public.sqlserver.replication' started by Kev, Feb 17, 2007.

  1. Kev

    Kev Guest

    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
    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.
    Kev, Feb 17, 2007
    1. Advertisements

  2. Kev

    Paul Ibison Guest

    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.
    Paul Ibison SQL Server MVP,
    Paul Ibison, Feb 17, 2007
    1. Advertisements

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

    I would not recommend using queued, it is best used when the majority of the
    DML originates on the publisher.
    Hilary Cotter, Feb 18, 2007
  4. Kev

    Kev Guest

    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, Feb 19, 2007
  5. Kev

    Paul Ibison Guest

    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.
    Paul Ibison
    Paul Ibison, Feb 19, 2007
  6. Kev

    Kev Guest

    Hi Paul,

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

    Kev, Feb 19, 2007
  7. Kev

    Kev Guest


    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?

    Kev, Feb 19, 2007
  8. Kev

    Paul Ibison Guest

    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.
    Paul Ibison SQL Server MVP,
    Paul Ibison, Feb 19, 2007
  9. Kev

    Kev Guest

    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?

    Kev, Feb 20, 2007
  10. 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?
    Hilary Cotter, Feb 20, 2007
  11. 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.
    Hilary Cotter, Feb 20, 2007
  12. Kev

    Paul Ibison Guest

    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.
    Paul Ibison SQL Server MVP,
    Paul Ibison, Feb 20, 2007
  13. 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, Feb 20, 2007
  14. Kev

    Paul Ibison Guest

    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.
    Paul Ibison SQL Server MVP,
    Paul Ibison, Feb 20, 2007
  15. Yikes, I just realized that with sql 2005 queued has automatic identity
    range management enabled by default. Sorry about that.
    Hilary Cotter, Feb 20, 2007
  16. Kev

    Kev Guest

    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.

    Kev, Feb 20, 2007
  17. 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, Feb 20, 2007
  18. Kev

    Kev Guest


    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?

    Kev, Feb 20, 2007
  19. Kev

    Paul Ibison Guest

    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.
    Paul Ibison SQL Server MVP,
    Paul Ibison, Feb 20, 2007
  20. 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
    Hilary Cotter, Feb 20, 2007
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.