locked
why setup database mirroring in cluster environment RRS feed

  • Question

  •  

    Two technique are both designed to  increase the availability of database.

    And the difference is one is server level and other one is database level.

     

    And i don't know why setup database mirroring in cluster environment, is there some complementary ?

     

     

    any suggestions would be appreciated

    Monday, May 19, 2008 4:49 PM

Answers

  • I guess I'm having trouble understanding what you're looking for.

    Are you asking whether the database mirror principal and the mirror servers should be clustered?  That would kind of defeat the purpose of disaster recovery.  Let me describe a hypothetical configuration and maybe this will answer your question.

    Your SQL Server that is the Principal server in a database mirroring configuration would have additional High Availability capabilities if it were a member of a cluster.  In the event your principal server needed to failover to another server in the cluster, your database mirroring would continue to function.  This is why the two technologies are complementary.  Clustering provides an additional layer of availability at the hardware level for the box that is intended to be your principal database server.

    Presumably, your Mirror server is at a completely different physical site, possibly even in a different domain, and may or may not be a member of a cluster.  Since the mirror is an emergency failover option, I have rarely seen the mirror as a member of a cluster and it often has a less robust physical configuration in terms of memory and processing power.

    Your witness server is at still a third physical location and can actually be configured on an instance of SQL Server Express.

    I configured one site that absolutely had to have 24-hour availability and couldn't afford to lose data.  The princpal server was an extremely powerful 64-bit box with lots of memory and was clustered with an equally powerful box in the organizations primary data center.  This provided the ability to perform routine maintenance on either one of the servers without having to failover database support to the mirror as well as providing support for other hardware scenarios that might cause an automatic failover from server A to server B in the cluster.  The primary objective was to keep the principal database available.

    The server with the mirror database was a less robust 64-bit stand-alone box in a different domain in a town 50 miles away.  The reasoning was that if the principal site lost power in a disaster they could still stay up because the database support would failover to the mirror site.

    The witness server was an older box in yet a third town that did nothing but run SQL Server Express and serve as the witness to the database mirroring configuration.

    Why three different locations?  If you lose two of the three components of a database mirroring session then the quorum is lost and database access is lost.  For example, if you lost your witness and your mirror then, even though your principal remains up, database access, and potentially data, is lost because quorum is lost.

    This is a fairly extreme example but I think it demonstrates how the technologies can complement each other.
    Tuesday, May 20, 2008 2:58 PM

All replies

  • They serve two different purposes but are complementary.  Database mirroring is intended to address the availability and integrity of your data while clustering is intended to address the availability of your hardware.

    It is a popular misconception, perpetuated by Microsoft, that clustering is a high availability technology.  It isn't really.  It is a useful component of a high availability strategy but the automatic failover capability only works in a fairly limited set of circumstances.  If, for example, you lose a disk controller on the operating system drive it won't automatically fail over.  Clustered servers are connected via network cables, i.e. there are usually two network cards on each server, and one of those cards is used in a dedicated server-to-server connection that is referred to as the "heartbeat".  Typically, the heartbeat has to be severed for the cluster manager to realize that a failover is necessary.  There are a lot of application or hardware problems that can occur that do not sever the heartbeat so it is possible for data to be lost in some scenarios.

    Database mirroring uses the "other" network card and, like clustering, the principal is in synchronous communication with the mirror.  (At least in high-safety mode they're synchronous.)  The primary difference is that the mirror is an integral part of every transaction.  A transaction is not considered committed until it has been safely committed on both principal and mirror.  Theoretically, this means that it is impossible for data to be lost.  This is truly Highly Available technology since the primary goal is data integrity in the face of disaster.

    It is not uncommon for several strategies to be employed in the interest of disaster recovery.  I have personally built clusters where we configured database mirroring as well.  We also log shipped to a warm-standy server and used transactional replication.  The ultimate in a "belt and suspenders" mentality.

    I hope I answered your question.
    Monday, May 19, 2008 7:03 PM
  • Thanks for your reply.

     

        But I still don't understand how they complement if a database mirroring setting in a cluster environment.

    A server level high-availability really need a database-level high-availability?

     

    Thanks.

     

    Tuesday, May 20, 2008 5:43 AM
  • I guess I'm having trouble understanding what you're looking for.

    Are you asking whether the database mirror principal and the mirror servers should be clustered?  That would kind of defeat the purpose of disaster recovery.  Let me describe a hypothetical configuration and maybe this will answer your question.

    Your SQL Server that is the Principal server in a database mirroring configuration would have additional High Availability capabilities if it were a member of a cluster.  In the event your principal server needed to failover to another server in the cluster, your database mirroring would continue to function.  This is why the two technologies are complementary.  Clustering provides an additional layer of availability at the hardware level for the box that is intended to be your principal database server.

    Presumably, your Mirror server is at a completely different physical site, possibly even in a different domain, and may or may not be a member of a cluster.  Since the mirror is an emergency failover option, I have rarely seen the mirror as a member of a cluster and it often has a less robust physical configuration in terms of memory and processing power.

    Your witness server is at still a third physical location and can actually be configured on an instance of SQL Server Express.

    I configured one site that absolutely had to have 24-hour availability and couldn't afford to lose data.  The princpal server was an extremely powerful 64-bit box with lots of memory and was clustered with an equally powerful box in the organizations primary data center.  This provided the ability to perform routine maintenance on either one of the servers without having to failover database support to the mirror as well as providing support for other hardware scenarios that might cause an automatic failover from server A to server B in the cluster.  The primary objective was to keep the principal database available.

    The server with the mirror database was a less robust 64-bit stand-alone box in a different domain in a town 50 miles away.  The reasoning was that if the principal site lost power in a disaster they could still stay up because the database support would failover to the mirror site.

    The witness server was an older box in yet a third town that did nothing but run SQL Server Express and serve as the witness to the database mirroring configuration.

    Why three different locations?  If you lose two of the three components of a database mirroring session then the quorum is lost and database access is lost.  For example, if you lost your witness and your mirror then, even though your principal remains up, database access, and potentially data, is lost because quorum is lost.

    This is a fairly extreme example but I think it demonstrates how the technologies can complement each other.
    Tuesday, May 20, 2008 2:58 PM
  • I see the whole issue of database HA a little further up the OSI scale, like at the Application HA level


     

    Lets start by looking at a non Application HA Scenario:

     

    Your DW Manager has been asked to load up the US, reports for the last 7 years and reconcile against a particular customer strategy.  So they need to do a some heavy reports.

     

    By chance your network link is going up and down and at the same time load is generated by the DW on the SAN, which has all your primary systems attached,  causing SAN performance issues.  Data is becoming corrupted due to referential integrity, up and down restart effect on Applications, disconnections and having partial data written to database disks which are SAN replicated to a remote SAN Site, which do run lets say MS DB Clusters.

     

    Application HA Scenario on the above:

    User does some work -  types” ABCDEFG….” And hits the enter key, but now the network to the Database server is down, so the SQL driver redirects via the redundant network link to another Hot SQL Service, which had all the data there ready via a previous SQL Service data mirroring process.

     

    The work flow is this, User logs in and the load balancer checks to see if the Network is up and if not uses  the backup network path to get to another Hot Application Server.

     

    If the Application server is too busy to respond it fails over to another Hot Application server on another site and finds it’s Hot database server.

     

    So to break it up, we have:

    -       User PC

    -       Network Load Balancer

    -       Possible Local Hot Application Services Farm or even Hot Mirrored Database Services

    -       Redundant Network paths

    -       SQL Data redirector Services (Native or ADO.Net)

    -       Remote  Hot Application Services Farm and Hot Mirrored Database Services

     

    Notice one little thing, we have not discussed the need for HW failover yet.

     

     Why? 

    The reason is - We moved servers around from just one site to a few Hot sites.  All HW is independent, in use and all are in separate redundant sites.

     

    There are no single points of failure due to loads or network failures.  The whole thing is run via Application Services and not reliant on any restrictive HW Relationship Failover Services.

     

    Is that not better and simpler than engineering the HA from the HW end and up?

     

     

     App HA can be designed with a combination of network and App load balancing, with Application failover systems such as IIS/ MOSS farms, Citrix farms or User PCs along with Data Failover technology like SQL Mirroring.

     

    The above HW is placed with respect to the user physical location, primary and then 2nd hot site. In many cases the luxury of Hw failover for the above is no longer required as all HW IS load balanced and fails over to use other live HW anyway.

     

    I do understand though that there are unique  24x7 critical apps which may really need very low RPO/RTO stats.  But in many cases and due to complexity, I think these are prone to many lack of maintenance processes and subsequent failures due to more human interaction required.

     

     

    Rgs

    GeoChed

    Monday, October 19, 2009 10:31 AM
  • Sans the long-winded replies, the answer is simple; it takes TIME to fail-over between nodes in a clustered instance.  If your environment requires little to no down time, as mine does, you can't afford your databases being unavailable during the fail-over between nodes; even if it is only a matter of minutes.  

    Therefore, you set up mirroring of the principle databases as well.  If configured properly (won't get into the specifics here as I am only answering your specific, high-level question but one thing that is necessary is that you have a third, witness instance set up to monitor the primary and secondary mirrored databases.) the INSTANCE failure will be detected and fail-over of the principle DATABASES to their mirrored counterparts will happen in SECONDs, in parallel with the clustered node fail-over and your users will experience little or no disruption in service.

    Here is a link to a Microsoft article that explains what I'm talking about in detail:


    Sunday, February 7, 2010 3:31 PM