locked
SQL Disaster Recovery RRS feed

  • Question

  • We are running a front-end web-base software that communicates with SQL Server 2005. We need to have a disaster recovery solution in case of interruption of service. Could we accomplish this with placing similar hardware in a remote location using the built-in mirroring with SQL Server 2005 ?

    I am trying to do this as simply as possible. I have read several whitepapers/internet resources on SQL Clustering, Mirroring and Log Shipping. I just want to have a server to fail-over to in case of primary loss of service.

    Thursday, July 7, 2011 4:49 PM

All replies

  • Each technology has it advantages and disadvantages. Some are geared more towards HA (log shippng) and other more towards DR (log shipping), but the scale is sliding. There is no one technology which is the best. You have to decide this based on your requirements, witht he knowledge of each of the solutions architecture and based on that its advantages and disadvantages. Since you read papers on each technolify already, you should be able to weith each technologie's advantages and disadvantages against each other to see what is better for your business. Cluster, for instance has a sgated disk, so if the data goes south (for whatever reason), then cluster doesn't help you. The other technolgies are less transparent to the application and reqwuires more work and knowledge by the DBA (http://www.karaszi.com/SQLServer/info_moving_database.asp), but you do have several separate sets of data and with log shipping you can even lag behind to possible cater for the "oops, I dropped the Orders table"" situation.
    Tibor Karaszi, SQL Server MVP | web | blog
    Thursday, July 7, 2011 5:20 PM
  • In addition to what Tibor has already mentioned, it is important to first define your recoverability requirements. What are your recovery point objectives (RPO) and recovery time objectives(RTO)? Defining these objectives would be your guide on what specific technology to use. Have a look at this for definitions of these terms and how they apply to your disaster recovery requirements

    http://bassplayerdoc.blogspot.com/2010/12/disaster-recovery-is-more-than-just.html


    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn
    Friday, July 8, 2011 1:20 AM
  • Thank you both for your responses.  Let me give you a little more information on our current configuration and our goals for HADR. We are currently running a proprietary web-facing application (Windows Server 2003 - IIS). We have hundreds of users. Most run a thick-client, others use the website directly, to enter data about our business. That front-end then stores the data into several SQL databases.(Windows Server 2003 - SQL 2005). The goal is to setup a second server, in a different geographic location, in case of loss of service(for any reason). My thought is to setup one server running both IIS and SQL 2005. Copy everything over from the primary and setup database mirroring to sync the data. We could then perform a manual failover for maintenance scenarios or forced failover if the primary goes down.

    My main question is will this work? Suggestions are welcomed and appreciated.

    Friday, July 8, 2011 2:34 PM
  • Hi Davis27,

    Yes, this will indeed work. However, as I've mentioned in my previous post, it is important to define your RPO and RTO. For example, if your application went down, how much downtime can you afford? This is called your recovery time objective. If you have an RTO value of one hour, this means that you need to get your application back online - both database and web application - in less than or equal to one hour after it went down. Second, how much data can you afford to lose? This is called your recovery point objective. If you have an RPO of one hour, this means that you can afford to lose one hour's worth of data from the last known good state, which means an hour before the application went down. Notice that in both definitions, I have not defined a backup strategy nor a technology that will address these. It is because of the fact that these requirements have to be defined first even before you start thinking about which technology to use.

    http://bassplayerdoc.blogspot.com/2010/12/disaster-recovery-is-more-than-just.html


    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn
    Friday, July 8, 2011 4:56 PM
  • I read your blog post. Thank you for the link. As with most companies we would like to have downtime approaching zero. Having previously discussed downtime with upper management, I feel confident the a one hour value will be sufficient for both RTO/RPO. But my hope is with this particular solution there will very little data loss and downtime *should* be just a few minutes after discovery.
    Friday, July 8, 2011 5:53 PM
  • Hi Davis27,

    Bear in mind that as RPO/RTO approaches zero, your cost exponentially increases. Mention dollar values to upper management and equate that with RPO/RTO values approaching zero and they will start asking for other options :-)

    If you feel confident about your RPO/RTO requirements, a combination of daily FULL + LOG backups every 30 minutes with synchronous database mirroring would be OK for your databases and maybe a ROBOCOPY of your web application files every 30 minutes to a remote web server. If your web applications have COM+ components, then, maybe an NLB solution would be more appropriate and simply redirect all web traffic to the production web server. Your means of failing over your web servers will depend on how you would configure high availability for your web applications

    Hope this helps


    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn
    Friday, July 8, 2011 9:05 PM