none
Disaster Recovery replication topology RRS feed

  • Question

  • I've been trying to figure out the best way to set up replication for our Disaster Recovery. Currently we have five production databases replicating through five distributors to one data warehouse. We're looking to recreate the same set of servers with replicated data for our Disaster Recovery, but I can't figure out the best approach. We are using Server 2003, SQL 2005, and transactional replication. Between our production and Disaster Recovery, we are sharing a 45Mb/sec tunnel.

    Here are the scenarios I've come up with:

    Scenario 1: Replicate the production databases to our Disaster Recovery databases using the original publications and distributors. Then using our Disaster Recovery distributors, create new publications from the replicated databases and replicate again to our Disaster Recovery Data Warehouse. With this method I've encountered a couple problems already. First, our two busiest databases can not keep up over the WAN after the snapshot is delivered (i.e. latency continues to grow). and second, so far I haven't been able to replicate a replicated database, and I'm not even sure it is possible.

    Scenario 2: Use the original production databases and configure our Disaster Recovery distributors to replicate to both our Disaster Recovery databases and our Disaster Recovery Data Warehouse. This means adding a second distributor for each original publisher, and through research, it looks like you can not have two distributors from one publisher, even if separate publications are created.

    Hopefully somebody is able to follow what I'm trying to describe, and maybe have some hints or a better idea than what I've been able to come up with.

    Tuesday, September 14, 2010 9:42 PM

Answers

  • This is the wrong technology to do this with. You should be using log shipping, database mirroring or even a combination of log shipping and database mirroring as outlined in this article.

    http://sqlcat.com/whitepapers/archive/2008/09/02/sql-server-replication-providing-high-availability-using-database-mirroring.aspx

    That been said you can use a combination of log shipping and replication if you use the sync with backup option when dumping your tlogs.

    Basically you will ship the tlogs from each of your 5 main publishers to your DR site, and then ship the distributor tlog dumps as well.

    Now on failover you will restore everything the final log with the keep_replication switch, rename the servers with their original names, use sp_replrestart  and then use the continue on data consistency profile to replicate to the same subscriber(s). The subscriber could also be log shipped to the DR site and then if you do the rename it will continue where you left off.

    Now with a 45 Mg pipe I think this will be difficult to do with only that available bandwidth as I am sure you will be sharing that with other applications.

    Note also that your exposure to data loss with such a scenario will be completely unpredictable, so you have to think long and hard as to whether you want to go down this path.

    I also urge you to practice this in a virtual environment so you can see all the steps involved as it is very complicated.

    The last thing you want to do is follow 30 to 40 steps in a high pressure scenario when you have to failover.

    When it comes to DR simplicity is key to a successful DR implementation.

     


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    • Marked as answer by Thomas Bane Friday, September 17, 2010 8:51 PM
    Wednesday, September 15, 2010 12:17 PM
    Moderator

All replies

  • Can you please explain the meaning of this statement:

     Currently we have five production databases replicating through five distributors to one data warehouse.

    Tuesday, September 14, 2010 9:54 PM
  • would be helpful if you can explain the replication toplogy in terms of how many publications you have along with the number of susbcriptions for each publication
    Tuesday, September 14, 2010 9:55 PM
  • Sorry if I wasn't clear.  Hopefully this will help.

    Our current production system consists of five database servers (which have to be kept separate due to regulations), each with just one production database.  This production database is replicated through a separate distributor server (one distributor per database) to one data warehouse.  Our data warehouse is a separate server that keeps replicated copies of all five of our production databases.  This data warehouse is used for reporting and data archiving.

    We're hoping to recreate the above setup for our Disaster Recovery system (five database servers, five distributors, and one data warehouse).  Ideally each of these databases would always have current data, through replication, if failover was required. 

    Tuesday, September 14, 2010 10:33 PM
  • Currently each of our five production database servers hold just one database.  A publication was created for each of these databases that goes through a separate distributor (one for each database).  They all have the same subscriber (our Data Warehouse), and this is currently the only subscriber for each.

    We are hoping to replicate both our production databases and our Data Warehouse databases to our Disaster Recovery servers.  But I'm not sure the best way to go about doing this.

    Tuesday, September 14, 2010 10:39 PM
  • This is the wrong technology to do this with. You should be using log shipping, database mirroring or even a combination of log shipping and database mirroring as outlined in this article.

    http://sqlcat.com/whitepapers/archive/2008/09/02/sql-server-replication-providing-high-availability-using-database-mirroring.aspx

    That been said you can use a combination of log shipping and replication if you use the sync with backup option when dumping your tlogs.

    Basically you will ship the tlogs from each of your 5 main publishers to your DR site, and then ship the distributor tlog dumps as well.

    Now on failover you will restore everything the final log with the keep_replication switch, rename the servers with their original names, use sp_replrestart  and then use the continue on data consistency profile to replicate to the same subscriber(s). The subscriber could also be log shipped to the DR site and then if you do the rename it will continue where you left off.

    Now with a 45 Mg pipe I think this will be difficult to do with only that available bandwidth as I am sure you will be sharing that with other applications.

    Note also that your exposure to data loss with such a scenario will be completely unpredictable, so you have to think long and hard as to whether you want to go down this path.

    I also urge you to practice this in a virtual environment so you can see all the steps involved as it is very complicated.

    The last thing you want to do is follow 30 to 40 steps in a high pressure scenario when you have to failover.

    When it comes to DR simplicity is key to a successful DR implementation.

     


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    • Marked as answer by Thomas Bane Friday, September 17, 2010 8:51 PM
    Wednesday, September 15, 2010 12:17 PM
    Moderator
  • Thanks for your input Hilary.  I will be investigating means other than SQL replication to implement my Disaster Recovery.
    Friday, September 17, 2010 8:53 PM