Link to home
Start Free TrialLog in
Avatar of jmachado81
jmachado81

asked on

SQL clustering failover

Is there a way to have all cluster groups failover to the secondary node if one cluster group fails over?  

For instance, i have  "Available Storage", "Cluster Group", and "SQL Server (MSSQLSERVER)" listed as my cluster groups.  If all of them currently reside on a single node then an admin restarts sql service from SSMS, now i'll have the "SQL Server (MSSQLSERVER)" on one node and the others on another node.

I understand some places like to have multiple groups so they can run different instances on different nodes, but if I chose to run them all on a single node, how could i force that?

I'm currently running 2012 and 2016 SQL and OS.
ASKER CERTIFIED SOLUTION
Avatar of Patrick Bogers
Patrick Bogers
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Why would "an admin restarts sql service from SSMS"? that should never hapen because they restart the service on the active node causing the resources to failover to the other (passive) node.
In a active/passive SQL cluster you and anyone else should use ONLY Failover cluster Manager to perform such actions and all resources (including Quorum) are associated to one (active) node.
This way Passive node can be patched, reboot/SQL Service restarted on it from windows services for instance NOT SSMS - at any time right? and all this without impacting the SQL Clustre operations.
Avatar of jmachado81
jmachado81

ASKER

I agree it should not be done the way it was and should only be performed with the cluster manager.  However I assume in your experience you know that sometimes we need to put these measures in place to protect the system and ourselves from 'users.'  So if there is a way I'd very much like to know so I do not have the clustered role on one node and the Windows (cluster) group on another node.  I'd like them to failover together.
If all of them currently reside on a single node then an admin restarts sql service from SSMS, now i'll have the "SQL Server (MSSQLSERVER)" on one node and the others on another node.
As stated by lcohan you shouldn't restart the SQL Server service from SSMS. But even if you do it, who told you that the service will failover to another node? Failover feature is only available through Cluster Administrator.

I understand some places like to have multiple groups so they can run different instances on different nodes, but if I chose to run them all on a single node, how could i force that?
SQL Server should be dependent of the Storage so during a failover both will be failed over to the passive node. If one SQL Server is failing over and the storage stays in the other node then it will be impossible to bring SQL Server online in the other node.
Although meant for different purpose ("SQL load balancing") you could use "Preferred Node Policy-Setting on Traditional SQL Cluster" as described here http://www.sqlservercentral.com/articles/Clustering/151250/ to have all resources running on one (preferred) node.
I've watched a web-ex that if you restart SQL via SSMS it will failover to the other node because the service stops for a moment thereby interpreting the node as offline and fails over to the secondary.

I believe the available storage group is there by default and is offline (at least in my cluster).  When you create a windows cluster, i believe it creates the "Cluster Group."  Then it will create a cluster group for each Role you create, in my case its "SQL Server."  I then have an "Availability Group" (which is offline), a "Cluster Group" and a SQL Server group.

Restarting of the service via SSMS will failover the SQL server group to secondary node (although not recommended not all Admins follow best practices), however the windows cluster does NOT failover and remains on the primary node.  I then have an active cluster group on each node.  Could there be a dependency that causes this or is this simply the expected behavior?
"Could there be a dependency that causes this or is this simply the expected behavior?"
Of course this is behavior by design and that is not just best practice to use the Failover Cluster Manager but THE way to do it - period.
If those that have SQL sysadmin access and/or local admin rights (for that mater because if they restart it via services is same thing) and do it via SSMS then in my opinion they have some learning to do right?

That way you wont have SQL Cluster resources running all over - period. well unless they don't bother to look at ALL resources  in Failover Cluster Manager and move them ALL to the same node.
I've watched a web-ex that if you restart SQL via SSMS it will failover to the other node because the service stops for a moment thereby interpreting the node as offline and fails over to the secondary.
What webex was that? And more important, who presented it? It's something that an experienced SQL Server professional won't do and surely won't show or recommend anybody to do it.