Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
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 3 '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 3 '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 3 '12 at 17:12
    
A WMI query might be more lightweight than running cluster.exe –  Eric Nicholson Mar 15 '12 at 15:05
    
Which WMI query would give me the info I need? –  Andrew J. Brehm Mar 15 '12 at 19:26
add comment

2 Answers

up vote 0 down vote accepted

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.

share|improve this answer
    
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 –  steoleary Dec 21 '12 at 9:57
add comment

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
share|improve this answer
    
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 2 '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 2 '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 3 '13 at 21:30
1  
I agree. Your nitpicking is legendary! :-) –  Bob Jan 8 '13 at 16:34
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.