SQL Server Failover Cluster

  • First sorry for my English, I am French speaking 🙂

    I've searched and did not found any information on how to do this.

    Let me explain (see file attached)

    I know how to setup an SQL failover cluster, did it and it works perfectly.

    Here is my situation : Node1 and Storage A is in one location (city) and Node 2 and Storage B is in a second location (different city).

    Node 1 and Node 2 are connected to Storage A and Failover works perfectly.

    NOW here is the catch.

    How to setup so that if Storage A fails and I want to failover to Storage B.

    I am wandering if its even possible since I have not found a single example on the net.

    Thanks you all.


    You must be logged in to view attached files.
  • if your storage is a SAN, you could implement Storage Replication with failover capabilities. ( Storage arranges sync )

    On the newer SQLServer side ( first on SQL2012 ), you could implement one ( ore more if no EntEdtn ) AlwaysOn Availability group(s)

    ( db-mirroring next-gen )

    This has separate nodes + separate storage and it is SQLServer that keeps them in sync.


    with such Always on AG's, sqlserver admin ( e.g. create/grant users instance level, sqlagent jobs, ... ) need to be performed 1 time for each node in the AG. ( Keep in mind to first create a SQLUser, then copy it including SID to the other nodes )



    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • What you want to do is possible with a Windows Stretch Cluster or with two Windows Clusters.

    If you have Enterprise Edition with Software Assurance  you may be able to do this with licensing for one server only.  The SQL2019 EE SA license allows both local and offsite failover, plus you can run integrity check jobs on the passive nodes.  However, if you run backups or any user queries on the passive nodes then you will need separate licenses for each site.  You need to be careful with Intent:Read connections as these could get routed to passive nodes and impact your licensing.  Always discuss your plans with your Microsoft reseller to confirm what licensing you need - their view has legal authority, our views do not.

    Stretch Cluster

    I have not used a stretch cluster, so I can only say what I have seen from the documentation.

    In this scenario you install additional cluster nodes at your location B.  They can be on the same subnet or a different subnet (Assuming Win 2012 or above).  The nodes installed at location B will have access to the storage at location B and you can fail over from a node at location A to a node at location B.

    You will have to work out how to get your data from location A to location B, and when you are live at location B you need to be able to get your data back to location A.  The easiest way to do this is using Enterprise Edition and Availability Groups.  You can put all user databases in an AG and the underlying technology will keep everything synchronised.  However, master and msdb cannot be put into an AG so you will need to build a process to keep Logins, Jobs, Certificates, etc synchronised.

    Separate Clusters

    I have implemented this and it works OK.  In this situation you install two separate clusters, one at location A the other at location B.  All the nodes on cluster A share location A storage, so for failover within cluster A both the system and user databases are covered.  It is similar for location B.  To get data synchronised between cluster A and cluster B use a Distributed Availability Group.  This means each cluster has its own local  availability group, with the dAG sitting on top of that .  All user databases should be in both your local and distributed AGs.  You will still need to build a process to synchronise master and msdb.

    To make failover simple, I recommend you set up a DNS alias with the same name as your dAG, and point that alias to your currently active local AG. Also, make sure you have a DNS Alias for each application.  Within your applications, set the connection string to point to the application alias.  Also set the application aliases to point to the dAG alias.  At failover time, do what is needed within SQL Server, then manually change the dAG alias to point to the new active local AG.  All of your applications will then connect to the new location and all you needed to do was change one DNA alias.


    It is up to you if you use a stretch cluster or separate clusters.  I prefer separate clusters as I can see clear separation between the two locations, so that problems at location A would not affect the cluster running at location B.  I am not sure if things would be so clear with a stretch cluster.  If you are using SQL 2016 or below then you only have the choice of a stretch cluster, but SQL2017 and above gives you the option of  adAG.

    With either approach if your workload increases and you want to use your passivle nodes for read-only queries you can just add the extra licensing.  A simple rule is any SQL inatance that is running user queries needs to be licensed.

    If you use Report Services, then a EE license with SA allows you to swap SSRS with PowerBI Server.  When installing this I also install IIS on the SQL box and use the IIS Default Document to route incoming http requests to reporting.  Again I use DNS aliases: The users are told to connect with a specific alias (eg MyCo_Reports), and this alias points to the dAG alias.  The http requests end up on the active cluster node and get routed via IIS to PowerBI.  At site failover time the dAG alias gets changed and the user reporting now connects to the active node on the new site.

    If you use SSAS you will need separate aliases for this - they cannot make use of the dAG alias.  If you need a connection from reporting to SSAS and you use an alias for SSAS, you will need a SPN for the SSAS alias - unlike all other connections both SSRS and PowerBI do not resolve the SSAS alias before building the SPN request, they use the name that is in the connection string.

    Looking again at aliases but not related to failover, another advantage of using application aliases is you have a layer of abstraction between your applications and your database servers.  If your workload changes you can fairly easily set up a new SQL Server and change one of the application aliases to point to it.  Likewise of you currently have multiple SQL Server instances and want to consolidate, the cut-over can be achieved by changing a DNS alias.  No connection strings get harmed during this process.

    Finally, if I had got all the documentation for this done in SQL FineBuild I would recommend you try that.  FineBuild can set all this up for you (I use it where I work), but it would be a bit hard to work out what FineBuild parameters you need while the Always On stuff lacks documentation.



    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 3 posts - 1 through 3 (of 3 total)

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