locked
Hi everyone, RRS feed

  • Question

  • Hi everyone,

    We currently have a need to Setup a SQL Failover Cluster.

    I know that as of 2012, we can setup a Cluster on a CIFS share.

    I have this crazy idea and wanted to know if anyone ever implemented it or what you would think about it.

    To prevent storage being a single point of failure, what we usually would do is setup a storage array replication between two SANs. Since I don't have this luxury here, I figured I would use a CIFS share to which both SQL Nodes would connect. So far everything is fine. But what if that CIFS share goes down? SQL database would also go down.

    This is where it gets crazy... What if I was to use a CIFS Share on a DFS namespace that would be replicated using DFS-R. In theory, this should provide high availability for the storage part. Are there any no-nos as to using a DFS Share for a CIFS Storage for SQL?

    Thanks,

    Michel

    Wednesday, March 27, 2013 12:56 PM

Answers

  • DFS will not work in this situation.  The real issue is that your database is continuously open, so it would never have a chance to replicate to the other DFS nodes.  

    You need to look at implementing a scale out file server, which of course means you have some sort of shared storage array.  Or you can implement an active-passive file server cluster to host your SMB share and use a host based replication solution instead of SAN based replication.  


    David A. Bermingham, MVP, Senior Technical Evangelist, SIOS Technology Corp

    • Marked as answer by Maggie Luo Thursday, April 4, 2013 9:14 AM
    Wednesday, March 27, 2013 1:25 PM
  • Hi Michel, 

    My opinion is that the more layers of complexity you add to an HA solution (particularly clustering) the harder it is to troubleshoot AND more risk to the stability of the whole offering. In terms of using CIFs for storage, you do not have resiliency in terms of a multiple path to it (as you could get through iSCSI or HBA cards). It is therefore a single point of failure and not necessarily something I would consider for an important production configuration. With respect to using DFS (and in particular DFSR) I would be surprised if this technology was considered "supported" for use with SQL Server and would need to see official MS documentation confirming it was (AFAIK it isn't).

    A far better solution to your backend HA storage IMHO would be to use multipath HBA to SAN and use SAN replication for storage level resiliency.


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog|SQLCloud)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    Watch my sessions at the PASS Summit 2012 and SQLBits

    • Marked as answer by Maggie Luo Thursday, April 4, 2013 9:14 AM
    Wednesday, March 27, 2013 1:36 PM
  • I agree with Mark to keep it simple.  The solution of multipath HBA to SAN and array based replication is certainly the cadillac of solutions and if you can afford it that is an excellent solution.  If your budget does not allow it then you are forced to look at next best solutions.  There are all sorts of options when it comes to SQL HA, and depending upon your RTO and RPO you have lots of options from simple data protection (log shpping, transactional replication, async mirror) to failover (sync mirror, failover cluster instance, availability groups).

    In addition to Storage Spaces, Microsoft has introduced SMB 3.0 as an alternative solution which could prove to be less expensive then traditional SAN solutions for clusters.  If archetected properly it can also have the same multipath features of MPIO.  Forget about DFS...it will not work for a SQL cluster.

    Here is a great resource for everything you need to know before using SMB 3.0 as storage for your cluster.

    http://blogs.technet.com/b/josebda/archive/2013/03/11/updated-links-on-windows-server-2012-file-server-and-smb-3-0.aspx


    David A. Bermingham, MVP, Senior Technical Evangelist, SIOS Technology Corp

    • Marked as answer by Maggie Luo Thursday, April 4, 2013 9:14 AM
    Wednesday, March 27, 2013 1:48 PM

All replies

  • DFS will not work in this situation.  The real issue is that your database is continuously open, so it would never have a chance to replicate to the other DFS nodes.  

    You need to look at implementing a scale out file server, which of course means you have some sort of shared storage array.  Or you can implement an active-passive file server cluster to host your SMB share and use a host based replication solution instead of SAN based replication.  


    David A. Bermingham, MVP, Senior Technical Evangelist, SIOS Technology Corp

    • Marked as answer by Maggie Luo Thursday, April 4, 2013 9:14 AM
    Wednesday, March 27, 2013 1:25 PM
  • Hi Michel, 

    My opinion is that the more layers of complexity you add to an HA solution (particularly clustering) the harder it is to troubleshoot AND more risk to the stability of the whole offering. In terms of using CIFs for storage, you do not have resiliency in terms of a multiple path to it (as you could get through iSCSI or HBA cards). It is therefore a single point of failure and not necessarily something I would consider for an important production configuration. With respect to using DFS (and in particular DFSR) I would be surprised if this technology was considered "supported" for use with SQL Server and would need to see official MS documentation confirming it was (AFAIK it isn't).

    A far better solution to your backend HA storage IMHO would be to use multipath HBA to SAN and use SAN replication for storage level resiliency.


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog|SQLCloud)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    Watch my sessions at the PASS Summit 2012 and SQLBits

    • Marked as answer by Maggie Luo Thursday, April 4, 2013 9:14 AM
    Wednesday, March 27, 2013 1:36 PM
  • I agree with Mark to keep it simple.  The solution of multipath HBA to SAN and array based replication is certainly the cadillac of solutions and if you can afford it that is an excellent solution.  If your budget does not allow it then you are forced to look at next best solutions.  There are all sorts of options when it comes to SQL HA, and depending upon your RTO and RPO you have lots of options from simple data protection (log shpping, transactional replication, async mirror) to failover (sync mirror, failover cluster instance, availability groups).

    In addition to Storage Spaces, Microsoft has introduced SMB 3.0 as an alternative solution which could prove to be less expensive then traditional SAN solutions for clusters.  If archetected properly it can also have the same multipath features of MPIO.  Forget about DFS...it will not work for a SQL cluster.

    Here is a great resource for everything you need to know before using SMB 3.0 as storage for your cluster.

    http://blogs.technet.com/b/josebda/archive/2013/03/11/updated-links-on-windows-server-2012-file-server-and-smb-3-0.aspx


    David A. Bermingham, MVP, Senior Technical Evangelist, SIOS Technology Corp

    • Marked as answer by Maggie Luo Thursday, April 4, 2013 9:14 AM
    Wednesday, March 27, 2013 1:48 PM
  • Hi,

    SQL replication is usually the cheapest way to duplicate storage (thus eliminating it from your "single point of failure list")

    On the other hand, it adds some complexity to your databases (like dealing with IDENTITY fields).

    Don't forget other basic components that may turn into single points of failure : DNS, DHCP and AD services.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Wednesday, March 27, 2013 2:21 PM