Post reply

High avalibility - what is best ?

  • January 16, 2012 at 8:31 pm

    #251411

    Hi,

    I am in a situation where I need to build a brand new environment which is to require high avalibilty. I have money to spend so that is not a real show stopper - with our being silly of course -

    They system or systems will be running sql 2008 r2 on windows 2008 r2 boxes with lots of grunt etc. My main limitation is that there is no shared storage between the two data centers I am to use.

    Typically each instance will have a number of databases running. I am thinking in this situation my best bet would be to use mirroring with a witness. I have darkfiber between my two sites so I am not worried about latency. My biggest worry is what if my sql instance has say 10 or 20 application databases running. Is mirroring going to work or is there a better solution. I need zero data lose with automated failover.

    Appreciate all ideas ...

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • January 16, 2012 at 9:04 pm

    #1434387

    There is no issue for configuring mirroring on all your databases.

    this article may help you.

    http://sqlcat.com/sqlcat/b/technicalnotes/archive/2010/02/10/mirroring-a-large-number-of-databases-in-a-single-sql-server-instance.aspx

  • January 16, 2012 at 9:29 pm

    #1434392

    Good article, thanks.

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • January 16, 2012 at 11:04 pm

    #1434407

    welcome!!:-)

  • January 17, 2012 at 8:29 am

    #1434651

    I've posted this before, but you can do geo-clustering without shared storage provided you purchase 3rd party disk mirroring software.

    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/[/url]

  • January 17, 2012 at 12:00 pm

    #1434776

    While budget is not a constraint, I would always recommend defining your objectives - RPO, RTO and SLAs[/url]. This needs to be the foundation of your high availability and disaster recovery plan.

    Zero data loss is misleading. You cannot have a zero-data-loss situation. This is why even Microsoft and other third-party vendors include a statement that "you can possibly have data loss" in your design architecture

    "Helping people and organizations grow and develop their full potential as God has planned for them"
    Twitter | Blog | LinkedIn

  • January 17, 2012 at 2:32 pm

    #1434886

    there are limitations with DB mirroring that you have to be aware of before you get started down that road.

    1. DB mirroring is NOT application agnostic. (meaning the application must be able to support a connection string that can contain a primary and failover instance name) Some application it is as simple as changing a config file, some applications it is a show stopper.

    2. Distributed transactions are not supported very well in DB mirroring.

    3. Applications that dynamically create databases are not supported.

    4. app code cannot reside on your DB server OR, if it must, it has to reside on both sides and be 'intelligent' enough to know where it lives.

    5. if your company has policies in place to change app user passwords on a regular basis and those app users are SQL logins, be prepared for periodic downtime to sync those logins after the change.

  • January 17, 2012 at 3:40 pm

    #1434909

    This is why I'd be tempted to implement a faux geo cluster. It's near zero loss and removes the administrative overhead. I currently have a project to implement several of these clusters and have completed POC

  • January 17, 2012 at 5:30 pm

    #1434928

    Thanks for all the replies.

    When I say zero data loss I understand you can never garuntee this but can do what ever nessasary to minimize data loss, with the standard caveats around that.

    I like the idea of geo-clustering with 3rd party software and a decent backup strategy. I just need to convince the Storage team that this is a viable option to look at.

    As fopr the apps, none dynamically create databaase or anything so fance :p nor are they running distributed transactions. One or two will need be replicated up to a mine site but those I wont mirror or you can but then it gets messy.

    As for the RPO, RTO and SLA's this is the interesting part. The business have not defined them as yet. This is a green fileds site so its all brand spanking new. I am just trying to get ahead of the game and develope a robust system given the technologies I have at hand and the current limitations

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • January 30, 2012 at 3:50 pm

    #1440290

    Considering above discussion have you looked into below scenerio....

    Implement 2 node w2k8R2 (Site1Node1 & Site1Node2) cluster in first data center vsql called Site1Node

    Implement 2 node w2k8R2 (Site2Node1 & Site2Node2) cluster in second data center vsql called Site2Node

    Implement manual DB mirroring belween site1 cluster and site 2 cluster (asynchronous for performance reasons, avoid 2 phase commit)

    This method will provide highest availability of various databaes, and only in case data center failover manual intervention require...

    For comparision of diff HA tech have a look at this video (Its comparing with sql 2012 as well)....

    http://www.youtube.com/watch?feature=player_embedded&v=1PK5DRzl_Gc

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

  • January 30, 2012 at 4:55 pm

    #1440297

    Hi Prakash I have thought about that and have actually built that at my last place of employment with great sucsess. In the long run I will be going down that path, but for now I was looking to see if I had missed anything.

    Thanks

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

Viewing 11 posts - 1 through 10 (of 10 total)

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