SQL Server Performance

redirecting transactional Replication to a mirrored database

Discussion in 'SQL Server 2005 Replication' started by mistys77, Dec 9, 2008.

  1. mistys77 New Member

    Hopefully I have posted this in the right forum.
    We have transactional replication setup between a box in the U.S and a box in the UK as a push subscription from the U.S. The database in question is approx 100GB in size and growing fast.
    I'd like to setup a DR environment in case the UK box goes down. I was thinking of setting up a mirrored database on the subscribed database and in the event the database goes down the mirrored database could take over.
    My question is, from within replication, can the publisher be redirected to point at the mirrored database rather than the orginal database easiley, without having to setup a new subscriber, snapshot etc. If this is possible, can you see any pitfalls?
    Thanks for your help.
  2. ndinakar Member

    You cannot "Redirect" replication subscriber server like that. You need to re-setup replication to the new mirror server. Since you already have the latest data from the principal, you dont need to re-snapshot the data, you can just re setup replication to the new server with very little downtime.
  3. mistys77 New Member

    Thanks.Just to be sure....
    1) Replication is setup to point to my subscriber
    2) A mirrored database set up as high protection from my subscriber
    Now the scenario….
    My subscriber database fails, I now manually flip over to my mirrored db. I ask for a new subscription to be setup to point to my mirrored db.
    Now whilst’s a new subscription is being setup, considering the time difference between the U.S and UK and how quickly someone can setup a new subscription, transactions will have been happening on the published database.
    How will the new subscription be in synch with the publication if a new snapshot is not applied? Will SQL Server know that transactions are missing from the subscriber and somehow replicate the missing transactions that had been processed before the new subscription was created?
    Thanks for your help.
  4. ndinakar Member

    If your subscriber goes down, you can have your reports continue to use the mirror, while you restore the most recent full back up onto the mirror server (perhaps with a different name), bring prod down, take differential backup, restore on mirror, resetup replication and bring prod online.
  5. satya Moderator

    Just another point to watch here is the licensing when using the mirror server for reporting purpose, in any case I have seen the following recommendations in one of the MSDN blog:1) Sync with Mirror means mirror became a principal due to fail over2) No need to change the subscription properties when there is a fail over. 3) In the initial subscription time/phase, we connect directly to the Publisher supplied in the subscription properties to find the publisher version so that we invoke the right merge replication mode.
    4) Direct connections are not allowed to the Mirror
  6. mistys77 New Member

    Thanks for that, but dont really understand what this means? Also its transactional replication not merge replication, unless it doesn't matter for the above solution?

Share This Page