Announcement

Collapse
No announcement yet.

VMware HA design consideration for MS SQL Server 2008 failover clustering

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VMware HA design consideration for MS SQL Server 2008 failover clustering

    Hi All,

    I'd like to build highly redundant SQL Server 2008 database and Infrastructure monitoring software for both my production and DR environment, I wonder if this design below is make sense and can provide me with sufficient redundancy at any level ?



    My goal is that when the Production site is having a problem, the user can use the monitoring application from DR site as automated fall back plan.

    Any kind help and suggestion would be greatly appreciated.

    Thanks.

  • #2
    Re: VMware HA design consideration for MS SQL Server 2008 failover clustering

    Are there redundant network paths between the sites?
    gerth

    MCITP sa, ea & va, sysadmin@cydonia.

    Comment


    • #3
      Re: VMware HA design consideration for MS SQL Server 2008 failover clustering

      Hi Albertwt,

      A lot of misunderstandings here.

      VMware HA cannot operate accros two VMware Clusters as your drawing shows.
      Also HA works at the ESX level to protect your VMs from ESX host crash.
      Eventually with VM monitoring enabled HA can protect from a VM crash.

      You're drawing a SQL cluster heartbeat network, therefore I suppose you're planing a Microsft Cluster. Doing so accros two sites requires not only a network heartbeat but also a 'heartbeat' at the storage level to avoid the dual-brain syndrome.

      If the storage cannot provide such mechanism, you should go for a MNS type of MS Cluster but this type of cluster has other requirements to fullfil like a witness server preferably in a third site...

      DR wording is not appropriate here as you are looking more at an active/passive environment and eventually active/active one with load balancers (GSLB?) in front.

      There are many things to look at when considering geographically dispersed clusters aka geoclusters. To simplifying your design you should look at SQL DB mirroring as an easy way to provide fault tolerant accros the two sites. Leave VMware HA turned on of course to protect against ESX host failure on each site.

      I hope I did not get you more confused

      Cheers,
      Didier
      Last edited by PiroNet; 31st May 2011, 21:34.

      Freelance, Business Owner, Virtualisation & Cloud Computing Fan Boy, VCP4/5/Cloud, VCAP4/5-DCA/DCD, vEXPERT '10-'14, EMCCA-Specialist, VMware Alumni.

      Comment


      • #4
        Re: VMware HA design consideration for MS SQL Server 2008 failover clustering

        @Gerth: yes there will be Fiber channel in between the Prod and DR sites.

        @PiroNet: ok, so in this case the ESX HA cluster cannot be spanned across two sites ? I understand that there should be only one DB cluster and App. Server that must be running at the same time.

        Comment


        • #5
          Re: VMware HA design consideration for MS SQL Server 2008 failover clustering

          Originally posted by PiroNet View Post
          Hi Albertwt,

          A lot of misunderstandings here.

          VMware HA cannot operate accros two VMware Clusters as your drawing shows.
          Also HA works at the ESX level to protect your VMs from ESX host crash.
          Eventually with VM monitoring enabled HA can protect from a VM crash.

          You're drawing a SQL cluster heartbeat network, therefore I suppose you're planing a Microsft Cluster. Doing so accros two sites requires not only a network heartbeat but also a 'heartbeat' at the storage level to avoid the dual-brain syndrome.

          If the storage cannot provide such mechanism, you should go for a MNS type of MS Cluster but this type of cluster has other requirements to fullfil like a witness server preferably in a third site...

          DR wording is not appropriate here as you are looking more at an active/passive environment and eventually active/active one with load balancers (GSLB?) in front.

          There are many things to look at when considering geographically dispersed clusters aka geoclusters. To simplifying your design you should look at SQL DB mirroring as an easy way to provide fault tolerant accros the two sites. Leave VMware HA turned on of course to protect against ESX host failure on each site.

          I hope I did not get you more confused

          Cheers,
          Didier
          Ok, now i decided to re-think and draw again from scratch as previously it was my idea at 1 AM, so it seems that Failover clustering on top of VMware HA is too complex and slight downtime ~ 5 mins during the failback/server restart is acceptable for me, therefore I have now decided to go with SQL Server 2008 Mirroring HA (see the attached).

          the only single point of failure is the Witness VM (which runs SQL Server 2008 Express) if you can suggest any other placement then that'd be great.
          Attached Files

          Comment


          • #6
            Re: VMware HA design consideration for MS SQL Server 2008 failover clustering

            Hi Albertwt,

            Your latest drawing is great!

            The witness is only required if you go for automatic failover and if so, it has to be placed in a third site. This is recommended if your RTO is less than an hour!

            Note that whilst nothing is required on the client side for a regular MS Cluster, for a SQL DB mirroring setup, the client should be 'aware' of it. The connection string (i.e. ODBC) must refer to both SQL IP's. If the first one doesn't work (prod site) the client tries the second one (DR site).

            Note that the 'client' can be either the load balancers, or the end user device. As you plan on using load balancers, they should be aware of both IPs and failover to DR in case the prod is not available. Make sure your LBs support SQL mirroring.

            Note that if using load balancers, they should be the kind of GSLB, spaning both sites and redundant. If the whole prod site goes down, that is LB, web, db, storage, and network, then still clients can connect to DR site's LB, which connect to the web servers, which talk to the SQL DBs all over the network/storage.

            Note that the SQL DB mirroring can be sync (RPO = 0 second) and therefore network latency is critical, no more than 2-3ms all the time for proper user experience. If async then latency is not anymore critical but then you have a bigger RPO (>minutes if lot of IOs on DB) with the risk of data lost.

            Regarding this question:
            >@PiroNet: ok, so in this case the ESX HA cluster cannot be spanned
            >across two sites ?
            HA could eventually span accros two site if there is a stretched LAN, that is the same ip range/subnetmask available on both sites, aka stretched L2. But this is definitely not supported at the moment...

            Again I hope I haven't put too much here

            Cheers,
            Didier
            Last edited by PiroNet; 1st June 2011, 09:15.

            Freelance, Business Owner, Virtualisation & Cloud Computing Fan Boy, VCP4/5/Cloud, VCAP4/5-DCA/DCD, vEXPERT '10-'14, EMCCA-Specialist, VMware Alumni.

            Comment


            • #7
              Re: VMware HA design consideration for MS SQL Server 2008 failover clustering

              Many thanks once again Didier,

              After I submitted and presented the design to my IT manager and director, they would like to know if this topology can do SQL replication (peer to peer) in both direction ?

              is it as simple as setting the Prod1 Publisher and Node2 Subscriber alternatively ?

              Comment


              • #8
                Re: VMware HA design consideration for MS SQL Server 2008 failover clustering

                Originally posted by Albertwt View Post
                Many thanks once again Didier,

                After I submitted and presented the design to my IT manager and director, they would like to know if this topology can do SQL replication (peer to peer) in both direction ?

                is it as simple as setting the Prod1 Publisher and Node2 Subscriber alternatively ?

                Yes it is as simple. Both SQL servers are active while being a mirror for each other DBs.

                Wording like prod and DR and not meaningful in such scenario... This is more active/active datacenters.

                IMO that's the way to go cause no company can afford a 'stand by' site anymore. Companies want to use their assets. Dormant kits waiting a disaster is wasted money CAPEX and OPEX wise...

                Rgds,
                Didier
                Last edited by PiroNet; 1st June 2011, 10:46.

                Freelance, Business Owner, Virtualisation & Cloud Computing Fan Boy, VCP4/5/Cloud, VCAP4/5-DCA/DCD, vEXPERT '10-'14, EMCCA-Specialist, VMware Alumni.

                Comment


                • #9
                  Re: VMware HA design consideration for MS SQL Server 2008 failover clustering

                  Originally posted by PiroNet View Post
                  Yes it is as simple. Both SQL servers are active while being a mirror for each other DBs.

                  Wording like prod and DR and not meaningful in such scenario... This is more active/active datacenters.

                  IMO that's the way to go cause no company can afford a 'stand by' site anymore. Companies want to use their assets. Dormant kits waiting a disaster is wasted money CAPEX and OPEX wise...

                  Rgds,
                  Didier
                  Cool, I thought that it is not possible without clustering.
                  Now I learn something new with just two SQL Server peer to peer configuration (as per this article:http://msdn.microsoft.com/en-us/libr...=SQL.100).aspx) data replication occurs in both direction so that it synch one and another to form Active/Active configuration without the use of MSCS (Clustering).

                  Comment

                  Working...
                  X