High Availability with zero RPO

  • U J

    SSCrazy

    Points: 2420

    Hi Experts,

    I want to achieve High Availability on SQL server 2005 on our standby servers with zero RPO(Recovery Point Objective ) and according to my knowledge following are the solutions -

    1)replication

    2)mirroring &

    3)clustering

    but each one has its own advantages & disadvanges..

    So I just want your expert opinion about these options if anyone has already applied on their setup.

    Thanks in advance. 🙂

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Replication is not a HA technique.

    By 0 RPO, do you mean absolutely 0 downtime? That's not possible. It's possible to get it to a couple seconds in favourable circumstances, but not 0.

    How much budget is there? What are you protecting against?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • U J

    SSCrazy

    Points: 2420

    Thanks Gail for sharing the info.

    Budget is not much so customer dont want to go for clustering...

    The goals to achieve are -

    1) secondary server (standby) should be always in sync with primary(production) with say few seconds lag.

    2) production server should not hamper in case of any disastor at standby server

    3) Performance of production server should not be impacted

    so according to me,

    Replication is the best suited ... but still I m confused ....

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Replication is not a high availability technique. While it can create a copy of a database, it's a pain to switch apps over, it may not be a complete copy and it's a real absolute pain to switch back.

    Read up on database mirroring, it's probably the one you want.

    Just bear in mind, in general, the less you're willing to spend on HA, the less you get in terms of easy of failover/failback, latency, etc.

    p.s. Considering that you have the SQL 2005 certs, you should know about mirroring.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Bru Medishetty

    SSC-Insane

    Points: 23606

    As Gail has pointed out, though Replication is listed / treated as one of the HA features, it would be cumbersome to implement it as a true DR or HA solution. It would still be one of the better features for having multiple copies of data.

    Replication replicates only published objects (chosen as a part of the publication) but any new tables and other objects added in the database would need to be added in the existing publications or create newer one which would be messy and tedious not to mention the switch over / redirecting the application (already mentioned by Gail).

    I would definitely go with Database Mirroring as the choice for achieving HA.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Bru Medishetty (2/5/2010)


    It would still be one of the better features for having multiple copies of data.

    Sure, it's fine for secondary reporting systems. It's just bad for hot failover servers, especially if you're looking for 0 failover time

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Bru Medishetty

    SSC-Insane

    Points: 23606

    GilaMonster (2/5/2010)


    Bru Medishetty (2/5/2010)


    It would still be one of the better features for having multiple copies of data.

    Sure, it's fine for secondary reporting systems. It's just bad for hot failover servers, especially if you're looking for 0 failover time

    I am in complete agreement with you Gail on Replication not being a HA / DR solution.

    At each job I implemented replication, it was more of an additional database to move read only users away from the OLTP system or setting up a Data Warehouse which needed several different tables from multiple databases consolidated for DSS system..


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • GTR

    SSCertifiable

    Points: 7977

    If cost is not constraint then Clustering is the best option for high availability, you have two options in clustering Active\Active and Active\Passive.

    Active\Active: In any point in time both nodes are active meaning SQL services are running on both nodes. If any one nodes fails then all the services will failover to other node, so you have two instances running on same node now, downtime is in seconds may be 5 secs. Now you have time to fixed other node, and failback\move services back to original node.

    Active\Passive: In any point in time only one node is active that is SQL services are running on only one node other node acts as stand by or passive. If active node fails all services will failover to passive node, which is active node now.

    Remember clustering is good only for OS level, If disk fail you can't do anything, however you can manage disk failures at SAN level.

    As Gail and Bru mentioned Replication is good for object level, you can do it in database level, but it's pain in the butt. Database mirroring and Log shipping can be considered as high availability solution only if you have cost constraint, all depends criticality of the business.

    For clustering we don't need DBA intervention at all in most cases if node fails, thats not same with database Mirroring and Log Shipping.

    Enjoy!

    EnjoY!
  • Bru Medishetty

    SSC-Insane

    Points: 23606

    I guess the OP had mentioned Clustering is not an option due to budget constraints.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • U J

    SSCrazy

    Points: 2420

    Hi Gail/Bru

    Thanks For the nice reply....

    Very first even I have suggested customer to go for mirroring but customer can't afford 3rd server (which we can use as a witness) then I suggested for Synchronous mirroring but I guess in synchronous mirroring there could be performance impact on production server as it has to wait till the transaction completes on secondary server.Customer is also concerned about the performance impact on production :doze:

    so in short custmore not agreeing to invest more for witness server and also wants a performance on production server :crazy:

  • Bru Medishetty

    SSC-Insane

    Points: 23606

    As you have mentioned your client cannot afford for a 3rd server in setting up Mirroring, have you thought about the option of Log Shipping? Again it would not be that synchronous as DB Mirroring, but it would be a better option than Replication.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • Lynn Pettis

    SSC Guru

    Points: 442312

    Tushar-355587 (2/9/2010)


    Hi Gail/Bru

    Thanks For the nice reply....

    Very first even I have suggested customer to go for mirroring but customer can't afford 3rd server (which we can use as a witness) then I suggested for Synchronous mirroring but I guess in synchronous mirroring there could be performance impact on production server as it has to wait till the transaction completes on secondary server.Customer is also concerned about the performance impact on production :doze:

    so in short custmore not agreeing to invest more for witness server and also wants a performance on production server :crazy:

    The third server doesn't need to be dedicated. You could use another existing server, like a file server, by simply installing a copy of SQL Server Express for the sole purpose of serving as a witness server. It doesn't even have to host any databases.

  • Gail Shaw

    SSC Guru

    Points: 1004474

    You can do asynchronous mirroring without a witness, but it doesn't have the same level of data protection as synchronous, manual failover is required and there's a chance of data loss during the failover

    HA is one of those areas where you get what you pay for. The less you're willing to spend, the less protection you can get.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Bru Medishetty

    SSC-Insane

    Points: 23606

    I would suggest you follow Lynn's / Gail's suggestion on mirroring, I forgot to mention that.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • Trey Staker

    SSCarpal Tunnel

    Points: 4736

    Gail's suggestion about using a sql express instance works really well. I've implemented it like that and had zero problems over a 2 year period of time that I managed it.

    If this is sql server 2008 and you're using FILESTREAM you can't do mirroring but can still do log shipping.

    ---------------------------------------------------------------------
    Use Full Links:
    KB Article from Microsoft on how to ask a question on a Forum

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply