Query another cluster group from SQL server?

Question

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.

You might be better asking on StackOverflow: if the info is available programmatically, you can do it via .net or such direct from the app. Or CLR in SQL Server. Although it is a DBA.SE question, it'll involve some code monkeying... — gbn, Feb 03 '12 at 11:07
Also need to define "send alerts from SQL Server" a little more. Using powershell or cluster/exe you can find the active node for all the cluster groups but this is not exposed in T-SQL. You can find the active node of the SQL group using the serverproperty function but this will not work for a cluster group that doesn't host SQL, like your application tier. — Jason Cumberland, Feb 03 '12 at 15:30
Sending alerts is just using databasemail to send mails to operators. But those mails need to contain the name of the active application node. I guess I have to go with running cluster.exe from SQL Server, even though doing such is discouraged (but if there is no other way...). — Andrew J. Brehm, Feb 03 '12 at 17:12
A WMI query might be more lightweight than running cluster.exe — Eric Nicholson, Mar 15 '12 at 15:05

steoleary · Accepted Answer · 2012-12-20T17:16:04.343

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.

That gives me the node SQL server is running on, not the node a certain cluster group is running on. Or am I understanding this wrong? — Andrew J. Brehm, Dec 20 '12 at 14:00
Ah, I see what you're after now, not sure about doing this in T-SQL, it will most likely require a WMI call, I know that you can get this data from the **MSCluster_NodeToActiveGroup** class in the **root/MSCluster** namespace. Let me see if I can figure you out a query to get the right data. — steoleary, Dec 20 '12 at 15:22
I've edited this answer with some powershell code that will return the information that you are looking for, all you need to do now is package this in a way that works for your application. — steoleary, Dec 20 '12 at 17:17
It's a correct answer, although I recall that accessing WMI from C# is a lot more difficult than from PowerShell. I'll look into it from here. Thanks. — Andrew J. Brehm, Dec 20 '12 at 23:53
It's not too bad with C#, see here for more info [link](http://msdn.microsoft.com/en-us/library/ms186120(v=vs.80).aspx) — steoleary, Dec 21 '12 at 09:57

score 1 · Answer 2 · answered Dec 20 '12 at 20:02

1

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

answered Dec 20 '12 at 20:02

Bob

597
2
8

It's not enabled. And since I am a famous nitpicker, it's not a DOS command. Cluster.exe is a Win32 command (and it is being phased out and shouldn't be relied on any more). – Andrew J. Brehm Dec 20 '12 at 23:51
Ok, TECHNICALLY there have been NO DOS commands since Windows 3.1. But they follow DOS syntax, and they run in a DOS emulator window... so I still call them DOS commands. I'm a rebel. And it still works fine as of Windows 2008R2... So I'll continue to do it the easy way until it quits working... – Bob Jan 02 '13 at 15:55
for reference, I just ran it. It took less than one second, returned this line: "PHCMD120-A Online" Couldn't be any faster or simpler. – Bob Jan 02 '13 at 16:00
They do not run in a DOS emulator window, the DOS emulator runs in their window. Ta. – Andrew J. Brehm Jan 03 '13 at 21:30
Hmm.. Not sure what you mean... Have you tried running a console app outside of the DOS (emulator) window? :-) – Bob Jan 04 '13 at 04:53
Yes. 64 bit Windows doesn't have the DOS emulator and all Win32 console apps run fine. – Andrew J. Brehm Jan 04 '13 at 23:25
What do you mean "64 bit Windows doesn't have it"? If I do start:run: cmd.exe on 64 bit windows, I get... a DOS emulator window. – Bob Jan 07 '13 at 14:26
No, you get a Win32 console window which can run Win32 console apps. DOS programs _will not_ run in that window. – Andrew J. Brehm Jan 07 '13 at 22:06
2

I agree. Your nitpicking is legendary! :-) – Bob Jan 08 '13 at 16:34

Query another cluster group from SQL server?

2 Answers2