locked
Disaster recovery in SQL 2005 - which one is better ? RRS feed

  • Question

  • Hi All,

    i am in a slight confusion of implementing a DR solution for one of my banking client. They have a citrix application which depends on a sql server. they cannot have a down time more than 15 mins for that application...They want the DR database to come up in less than 15 mins and i am in a slight confusion which DR solution to go with...

    I had ruled out Clustering as the client is not willing to bear the cost of it. I also ruled out database mirroring as we failed to get the required performance benchmark with synchronous mirroring with RTT of 167 ms

    I am in confusion whether to go with log shipping or replication. if i am going with replication which should i go for.

    Could anybody please help me to get a better solution ?

    My environment is Windows 2003+ SQL 2005 Standard edition

    Regards
    Nimesh
    Wednesday, January 27, 2010 1:09 PM

Answers

  • Hi All,

    i am in a slight confusion of implementing a DR solution for one of my banking client. They have a citrix application which depends on a sql server. they cannot have a down time more than 15 mins for that application...They want the DR database to come up in less than 15 mins and i am in a slight confusion which DR solution to go with...

    I had ruled out Clustering as the client is not willing to bear the cost of it. I also ruled out database mirroring as we failed to get the required performance benchmark with synchronous mirroring with RTT of 167 ms

    I am in confusion whether to go with log shipping or replication. if i am going with replication which should i go for.

    Could anybody please help me to get a better solution ?

    My environment is Windows 2003+ SQL 2005 Standard edition

    Regards
    Nimesh

    Hmm , log shipping restores log backups on standby on a regular schedule and if primary server failed then there maybe a loss of data from the last time a log backup happend , does your application tolerate that ?

     Also bringing the standby server online needs manual intervention and this can vary so not sure if you can handle in 15 minutes. You may need to copy left over log backups and restore them manually.

    Replication supposed to deliver data in near real-time to subscribers if configured as continuous distribution but in you case you will have to publish all tables so that subscriber gets everything as publisher (production).

    Transactional replication provides the lowest latency and is usually used for high availability. however , if DB Mirroring failed your benchmark then you need to test replication as well as tables will be scanned for transactions marked for replication and may present an activity.

    Also , better to use remote distributor in that case , a cost you should consider. Very important too is that replication maintenance is way more complicated than Db mirroring or Log shipping

    You can read also here an overview and then maybe feedback more about your situation

    http://msdn.microsoft.com/en-us/library/ms151244%28SQL.90%29.aspx

    HTH

    Please mark as answer if you think this answers your questions
    Wednesday, January 27, 2010 10:59 PM

All replies

  • Hi All,

    i am in a slight confusion of implementing a DR solution for one of my banking client. They have a citrix application which depends on a sql server. they cannot have a down time more than 15 mins for that application...They want the DR database to come up in less than 15 mins and i am in a slight confusion which DR solution to go with...

    I had ruled out Clustering as the client is not willing to bear the cost of it. I also ruled out database mirroring as we failed to get the required performance benchmark with synchronous mirroring with RTT of 167 ms

    I am in confusion whether to go with log shipping or replication. if i am going with replication which should i go for.

    Could anybody please help me to get a better solution ?

    My environment is Windows 2003+ SQL 2005 Standard edition

    Regards
    Nimesh

    Hmm , log shipping restores log backups on standby on a regular schedule and if primary server failed then there maybe a loss of data from the last time a log backup happend , does your application tolerate that ?

     Also bringing the standby server online needs manual intervention and this can vary so not sure if you can handle in 15 minutes. You may need to copy left over log backups and restore them manually.

    Replication supposed to deliver data in near real-time to subscribers if configured as continuous distribution but in you case you will have to publish all tables so that subscriber gets everything as publisher (production).

    Transactional replication provides the lowest latency and is usually used for high availability. however , if DB Mirroring failed your benchmark then you need to test replication as well as tables will be scanned for transactions marked for replication and may present an activity.

    Also , better to use remote distributor in that case , a cost you should consider. Very important too is that replication maintenance is way more complicated than Db mirroring or Log shipping

    You can read also here an overview and then maybe feedback more about your situation

    http://msdn.microsoft.com/en-us/library/ms151244%28SQL.90%29.aspx

    HTH

    Please mark as answer if you think this answers your questions
    Wednesday, January 27, 2010 10:59 PM
  • Nimesh,

    1. I agree, log shipping requires considerable amount of manual intervention and I doubt if it can be handled within the stipulated timeframe of 15 minutes.

    2. Replication is a complicated solution and requires a big effort in terms of it's maintenance.

    So, what are we left with?

    Have you tried Database Mirroring with Asynchronous mode? This reduces a significant amount of performance bottleneck and is a much simpler solution to implement. However, in order to ensure DB mirroring works in a seamless manner with minimal data loss, ensure that the latency of your network is not more than 10-15 ms.

    Let me know how you go about it?

    Thanks,

     

     

     

     

    Thursday, February 4, 2010 9:56 AM
  • Nimesh,

    1. I agree, log shipping requires considerable amount of manual intervention and I doubt if it can be handled within the stipulated timeframe of 15 minutes.

    2. Replication is a complicated solution and requires a big effort in terms of it's maintenance.

    So, what are we left with?

    Have you tried Database Mirroring with Asynchronous mode? This reduces a significant amount of performance bottleneck and is a much simpler solution to implement. However, in order to ensure DB mirroring works in a seamless manner with minimal data loss, ensure that the latency of your network is not more than 10-15 ms.

    Let me know how you go about it?

    Thanks,

     

     

     

     


    BUT Asynchronous mode needs Enterprise Edition. If he has it then he should go for it

    THX

    Please mark as answer if you think this answers your questions
    Thursday, February 4, 2010 10:30 AM
  • You could consider a multi-site cluster using a host based replication solution such as SteelEye DataKeeper.  The costs involved with this solution is the cost of Windows Enterprise Edition, however, SQL Standard Edition will support a 2-node cluster, so Enterprise SQL is not required.  And then the cost of the replication software, which is actually very affordable.

    I just did a webinar that discusses SQL Server multi-site clusters for disaster recovery which you can view here...

    https://www2.gotomeeting.com/register/378084954

    I also wrote a detailed SQL Server multi-site cluster configuration step-by-step article here...

    http://clusteringformeremortals.com/2009/10/07/step-by-step-configuring-a-2-node-multi-site-cluster-on-windows-server-2008-r2-%e2%80%93-part-3/

    Go luck and let me know if you would like to talk offline.
    David A. Bermingham, Director of Product Management, SteelEye Technology
    Saturday, February 6, 2010 4:37 AM