Disaster Recovery option RRS feed

  • Question

  • We are considering disaster recovery option for our SQL 2000 and SQL 2008.  Our goal is to achieve less than 1 minute data loss in case of disaster.  It will be fine to have a system that needs manual failover in case of disaster.  We will have 2 sites with 50 miles apart.  Each site has SQL server.

    What option do I have in addition to use SAN disk replication?


    Thursday, June 18, 2009 6:53 PM

All replies

  • This sounds like an excellent homework question.

    How is the number of sites and the distance between them relevant?  Are you trying to keep copies of up-to-the-minute data in both sites?
    Aaron Alton |
    MCITP: Database Administrator
    Friday, June 19, 2009 12:57 AM
  • Also, do your plan involve getting to the data from an earlier point in time? How much earlier? How quick do you need to accomplish this?

    In the end, there are so many questions around this, that only you can answer so you really need to understand the available technologies to do your job properly.
    Tibor Karaszi, SQL Server MVP
    Friday, June 19, 2009 7:35 AM
  • One minute RPO for a remote site is doable, but tight.  You will need a very high-speed, low latency connection.  You are pretty much stuck with a synchronous solution to get data loss down that tight.

    You can do this with storage-based data synchronization and geo-clustering. 

    You can do this with database mirroring.

    Log shipping is out since the RPO is just too low.

    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP
    Friday, June 19, 2009 2:30 PM
  • Depending on load, you might even consider transactional replication.

    Friday, June 19, 2009 4:22 PM
  • No. No. No. No.

    Transactional replication pushes a copy of the data, not the database.  Very big difference.  I have seen many places rely on replication for DR until they actually tried to bring the database "live" and were missing key database components such as triggers, DRI, and stored procs.
    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP
    Wednesday, June 24, 2009 5:38 PM