About Me

My photo
a Dynamic and Energetic guy.....

Sunday, November 22, 2015

Set Priority for a SQL Instance in a Failover Cluster Manager

After we set up a SQL Cluster with all the NODES and INSTANCES(Roles), In a Fail-over scenario the Instance will be move to "Best Possible NODE". In an environment that we have 3++ NODES, can we set the ORDER to fail-over for an INSTANCE?

When we select the relevant INSTANCE, we can see the "Preferred Owners = Any Node" in the panel. That means in a fail-over it will be moved to best possible node (after considering available resources). If we want to change it, CLICK "Any Node" link button.
We will get a pop-up and there we can see all the available NODEs. They are in the default Order (ie:- Node1, Node2, Node3)
First we have to change the ORDER using UP button, until we are getting most necessary NODE to the TOP. 

After we set the ORDER we have TICK the necessary NODES to make them available for Fail-over. If not these changes wont be get effected. We have set the HIGHEST PRIORITY for NODE-3. 

*** Since all other INSTANCES also running in NODE-3, if we select 'Best Possible Node', by default the INSTANCE-1 Should move to NODE-1.(Because Node-1 is not having any running Instance)

But after we set the Priority, the "Best Possible Node" will be the highest priority node.
That means though Node-1 is idle, the INSTANCE-1 has moved to NODE-3 from NODE-2. Also we can see the "Preferred Owners=User Settings" in bottom of the panel.
This is a  cool feature that comes with Fail-Over Cluster Manager.

Add and Remove Nodes from a SQL Cluster and Move Instances

After we set up the Windows cluster we need to add / remove nodes with different requirements. First we have to open "Windows Cluster Manager"
We can see all 3 Nodes in the Panel. If we want to remove NODE3 from the server, we need to run a small power-shell script.

We have to specify, "remove-clusternode [servernodename]"
After  the execution we can see the 3rd Node is not available in the Cluster Manager.
When we want to add a NODE, it is also simple. After we open the "Windows Cluster Manager", we can see "Add Node" option in right panel.
There we have specify the Server - Node name and ADD option. After the execution we can see new NODE has been added to Cluster Manager.
If we want to Move an Instance (Role) , we can choose either "Best Possible Node" or "Select Node" option. For that we have to RIGHT Click on the necessary ROLE. In the pop-up we can see available running possible NODES.

We can select necessary Node to swift the INSTANCE.
If we disable (make offline) any NODE using cluster Manager,
When we try to Move a NODE, we will wont get that option in the POP-UP window.

Like wise we can Move Roles (Instances) between NODEs.

Friday, November 20, 2015

SQL Server Management Studio is Missing (SSMS.exe) in Clustered SQL Farm

After we install SQL Server 2012 including Management Studio Tools, and trying to connect to a SQL server we are getting a surprising ERROR. Here SSMS.EXE means SQL Server Management Studio.

"The application has failed to start because of side-by-side configuration is incorrect".
As expected the executable  of Management Studio is missing. So we have to go to physical folder where we install SQL Server.
As Expected we can see a few DLLs files resides in ManagementStudio folder. There should be a mismatch in DLLs to get this error. Because of that. If we install Management Studio EXE only, it wont work. So we have Open SQL Installer.
Then We have to Select REPAIR Option Under MAINTENANCE.
The other important thing is, we have Select "REPAIR SHARED FEATURES ONLY" if you have Clustered SQL Environment.
After the Installation is completed, we can see Management Studio is repaired. We can see few more DLLs in the ManagementStudio folder.
Now We can open "SQL Server Management Studio" properly.

My Masters