SQL Server cluster – checking if SQL Server clustered instance is running on preferred node

Goal

Obtaining information about the SQL Server clustered instances and if they are running on the preferred node

My way of achieving it

Using SSMS, xp_cmdshell and WMIC. When we have to check and see if our clustered instances are running on the preferred node we can use different approaches and each approach has its own benefits in comparison with the other approaches. In my case because I am used to work more with SQL Server, the usage of SSMS, xp_cmdshell and WMIC is more appealing than everything else and I chose it to find that information.

The script is using:

– 1 WMIC query that uses MSCluster_ResourceGroupToResource – used to find all the groups that are related to SQL Server clustered instances

– 1 WMIC query that uses MSCluster_ResourceGroupToPreferredNode – used to find the preferred node for all the groups

The output returned by the first query is used to generate a result set of group names and the name of the SQL Server clustered instance that is found in each group.

GroupName  InstanceName
SQL11 SQL11\I11

The output above is used to actually generate the script that we will have to ran in SSMS after enabling the SQLCMD mode. Below is an example of the generated script.

:CONNECT APPDKBA1162

DECLARE @preferrednodes TABLE (line nvarchar(500))
DECLARE @InstanceInformation TABLE (InstanceName nvarchar(100), RunsON nvarchar(50), IsPreferredNode nvarchar(3), Comments nvarchar(max))
DECLARE @groupsandinstance TABLE (GroupName nvarchar(500), InstanceName nvarchar(100))
DECLARE @GroupName nvarchar(50)

BEGIN TRY

INSERT INTO @groupsandinstance VALUES (‘SQL Server (MSSQLSERVER)’,’APPDKBA1162′)
INSERT INTO @groupsandinstance VALUES (‘SQL11′,’SQL11\I11’)

SELECT @GroupName = GroupName FROM @groupsandinstance WHERE InstanceName = @@SERVERNAME
INSERT INTO @preferrednodes EXEC xp_cmdshell ‘wmic /namespace:\\root\mscluster path MSCluster_ResourceGroupToPreferredNode’
UPDATE @preferrednodes SET line = REPLACE(line,CHAR(13),”)

IF (SELECT COUNT(line) FROM @preferrednodes WHERE line like ‘%.Name%’ AND line like ‘%’+@GroupName+’%’) > 1
INSERT INTO @InstanceInformation
SELECT CONVERT(nvarchar(50),@@SERVERNAME) AS InstanceName, CONVERT(nvarchar(50),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)),
(SELECT TOP 1 CASE WHEN CHARINDEX(CONVERT(nvarchar(100),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)), line) > 0 THEN ‘YES’ else ‘NO’ END FROM @preferrednodes WHERE line like ‘%.Name%’ AND line like ‘%’+@GroupName+’%’ ),”
ELSE
INSERT INTO @InstanceInformation
SELECT CONVERT(nvarchar(50),@@SERVERNAME) AS InstanceName, CONVERT(nvarchar(50),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)),
(SELECT CASE WHEN CHARINDEX(CONVERT(nvarchar(100),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)), line) > 0 THEN ‘YES’ else ‘NO’ END FROM @preferrednodes WHERE line like ‘%.Name%’ AND line like ‘%’+@GroupName+’%’ ),”

SELECT * FROM @InstanceInformation

END TRY
BEGIN CATCH
INSERT INTO @InstanceInformation SELECT CONVERT(nvarchar(50),@@SERVERNAME) AS InstanceName, CONVERT(nvarchar(50),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)) AS RunsON,” AS IsPreferredNode,(select ERROR_MESSAGE()) AS Comments
SELECT * FROM @InstanceInformation
END CATCH

GO

:CONNECT SQL11\I11

……….

Below you can see the output of the script

iropn_1

The script can be downloaded from here