locked
SQL Server 2008 Mirroring with MS Windows Cluster RRS feed

  • Question

  • Hi,

    We're looking to build a highly available sql server 2008 database environment.  We have two identical high spec servers, with redundant PSU, local RAID 10 disks etc. that we want to install SQL Server 2008 onto, in an active/passive mode.  Looking at this the database resillance method I'd like to use is SQL Database mirroring with automated failover (adding a witness server to the architecture), however, from what I've read, on it's own this would require any clients pointing at the Primary Sql evironment to have their DB connections repointed to the Mirror server on failover.  We didn't want to use a DNS alias to repoint the db connection as it'll need a DNS flush at the client side, we'd like the failover to be seemless.

    To enhance this, is it possible to add MS Windows 2008 Server clustering into the equation to save having to repoint the client software on DB failover.  For example, server 1 is : SQLSERV01, server 2 is SQLSERV02, but the clients connect to a cluster called SQLSERVCLUST?

    Is that possible? I'm trying to get the best of both DB mirroring i.e. no single point of failure on the disk group, with the added benefit of Windows clustering.  Ideally, we want to be in a position where if the SQL database crashes we can fail over to the mirrored database, or if the server fails we can fail over to the redundant cluster node, while maintaining both the performance of local disks and negating the single point of failure that a shared disk area would bring (although I appreciate SAN based disk storage can offer high availability).

    Thanks.


    • Edited by tretlet Monday, November 14, 2011 11:57 AM
    Monday, November 14, 2011 11:55 AM

Answers

  • You cannot address your requirement with the current versions of SQL Server. You can wait until SQL Server 2012/Denali to implement AlwaysOn Availability Groups. This will allow you to create a Windows Failover Cluster without using a shared storage and configure your databases in mirroring sessions. You may need to modify your client applications to use the latest SQL Server native client to implement a virtual network name/Availability Group Listener on SQL Server so you don't have to worry about client redirection via DNS aliases

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn
    • Proposed as answer by PrinceLucifer Tuesday, November 15, 2011 9:06 AM
    • Marked as answer by Peja Tao Monday, November 21, 2011 2:15 AM
    Monday, November 14, 2011 10:48 PM

All replies

  • When you talking about Windows Clustering,  you need to know that it works along with SQL Server cluestering to be installed on the machine

    http://msdn.microsoft.com/en-us/library/ms190202.aspx

    Read that great arcticle about how combine both MS Clustering along with Mirroring

    http://sqlcat.com/sqlcat/b/whitepapers/archive/2010/06/07/proven-sql-server-architectures-for-high-availability-and-disaster-recovery.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Peja Tao Wednesday, November 16, 2011 6:03 AM
    • Unproposed as answer by Peja Tao Wednesday, November 16, 2011 6:05 AM
    Monday, November 14, 2011 12:33 PM
  • When you talking about Windows Clustering,  you need to know that it works along with SQL Server cluestering to be installed on the machine

    http://msdn.microsoft.com/en-us/library/ms190202.aspx

    Read that great arcticle about how combine both MS Clustering along with Mirroring

    http://sqlcat.com/sqlcat/b/whitepapers/archive/2010/06/07/proven-sql-server-architectures-for-high-availability-and-disaster-recovery.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, November 14, 2011 12:33 PM
  • One more important thing with mirroring on clustered environment ( set timeout)-If you don’t want a mirroring failover to occur during a cluster failover should set a timeout that accounts for network latency plus the time it takes for a node to failover

    pls check more here http://www.ryanjadams.com/2010/11/sql-mirroring-timeout/


    http://uk.linkedin.com/in/ramjaddu
    • Proposed as answer by Peja Tao Wednesday, November 16, 2011 6:03 AM
    • Unproposed as answer by Peja Tao Wednesday, November 16, 2011 6:05 AM
    Monday, November 14, 2011 12:50 PM
  • Hi Uri,

    Thanks from the reply, I had already read the articles you linked to: from my understanding of what I read SQL Failover clustering utilised Windows Clustering, i.e. you have to configure windows clustering before you can configure SQL clustering.  However SQL Clustering requires a SAN shared disk group used by each server in the SQL cluster.

    I was trying to avoid using a shared disk group. as we have a SAN that is going end of life, and two good servers with 1TB of RAID 10 disks in each, it made sense to utilise the local disk, a) for performance, and b) to eliminated the single point of failure we'd have with SAN presented storage.

    On this basis I am contemplatin settinf up a two node windows clustered environment, and installing sql server on each with databases mirroring configured, this is not a configuration that is mentioned in the articles you linked so I'm not sure whether it's supported, or any pitfalls to look out for?

    The link that RamJaddu put up seems to suggest that you should set a mirroring timeout when the sql servers are a cluster node (thanks RamJaddu), so that seems to suggest to me that you can do this.

    Anyone have experience of this type of set-up? is this possible?

    Thanks. 

     

     

     

    Monday, November 14, 2011 6:39 PM
  • You cannot address your requirement with the current versions of SQL Server. You can wait until SQL Server 2012/Denali to implement AlwaysOn Availability Groups. This will allow you to create a Windows Failover Cluster without using a shared storage and configure your databases in mirroring sessions. You may need to modify your client applications to use the latest SQL Server native client to implement a virtual network name/Availability Group Listener on SQL Server so you don't have to worry about client redirection via DNS aliases

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn
    • Proposed as answer by PrinceLucifer Tuesday, November 15, 2011 9:06 AM
    • Marked as answer by Peja Tao Monday, November 21, 2011 2:15 AM
    Monday, November 14, 2011 10:48 PM
  • Bass_Player gave the answer to the question above. Theoretically it is possible to write a custom cluster DLL for SQL 2008R2 that supports your scenario with the current version as well. I have seen such an implementation with a client once. Unforutnately this does require dev-work though, as it is not supported by the product.
    Tuesday, November 15, 2011 9:06 AM
  • I suppose your had a look at the following?

    http://technet.microsoft.com/en-us/library/cc917713.aspx

    If none of those client redirection methods work for you you can consider building a multisite cluster, but keep the nodes in the same datacenter.  Read the Microsoft White Paper here http://download.microsoft.com/download/3/B/5/3B51A025-7522-4686-AA16-8AE2E536034D/WS2008%20Multi%20Site%20Clustering.doc 

    I wrote a detailed step-by-step guide 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/

    Hope that helps.

     


    David A. Bermingham, MVP, Senior Technical Evangelist, SIOS Technology Corp
    Tuesday, November 15, 2011 7:51 PM