Home / server / Questions / 356494
Accepted
Andrew J. Brehm
Asked: 2012-02-04 02:55:13 +0800 CST

Query another cluster group from SQL server?

  • 772

Is there a way to query from SQL Server in TSQL which node is active in another cluster group in the same cluster?

I have a cluster with two cluster groups, one with resources for SQL Server, one with resources for the application connecting to SQL Server. In order to send alerts from SQL Server I need to find out which node is active in the application group. Is there a way to do that in TSQL? I can find out the name of the active SQL node.

sql-server failovercluster

2 Answers

  1. Best Answer

    As of SQL Server 2008 R2 SP1, this is nice and easy, you can just query the dm_server_services DMV and look for the clustered service:

    select cluster_nodename from [sys].[dm_server_services] where is_clustered = 'Y'

    Earlier versions of SQL server, you may have to do something like read the registry, which you can do using the following code:

    declare @dir nvarchar(20)
exec xp_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName',
N'ComputerName',
@dir OUTPUT
select @dir

    Edit:

    So to get information on which node a cluster resource group is running on, you need to use WMI, the following powershell code will return the cluster node that the specified group is currently active on, as for doing this via T-SQL, you could either shell this out using xp_cmdshell, or create a table in your DB to store this value, append a little bit of code to the powershell to get it to update the value in the table each time it runs and run it as a powershell SQL agent job:

    $group = "Available Storage"
$computer = "."
$hostName = Get-WmiObject -Authentication PacketPrivacy -Impersonation Impersonate -Namespace root\MSCluster -ComputerName $computer -Class MSCluster_NodeToActiveGroup | Where-Object {$_.PartComponent -like "*$group*"} | Select-Object -ExpandProperty GroupComponent | Out-String
$hostName = $hostName.TrimStart("MSCluster_Node.Name=")
$hostName = $hostName.TrimStart()
$hostName = $hostName.TrimEnd()
Write-Host $hostName.Substring(1,$hostname.Length-2)

    It also would be pretty trivial to re-write this in C# and create a SQL server CLR function that accepts the parameters of a computer to run the query against and the group to check, then return the server name.

    Hope this helps at least get you on your way.

    • 2

  2. If you have xp_cmdshell enabled it's pretty simple. Just use xp_cmdshell to launch the DOS "cluster" command.

    set nocount on

declare     @commandtxt varchar(512),
    @rc         int

set @commandtxt = 'cluster group "Cluster Group Name"'

create table #output (output varchar(255) null)
insert #output exec @rc = master..xp_cmdshell @commandtxt
select * from #output where output LIKE 'Cluster Group Name%'
drop table #output
    • 1