April 24, 2011 at 7:12 pm
Hello All,
per a task I need to get the name of the Cluster name which is running on a Clustered node of a SQL Cluster. Is there any Class that I can use in the Get-WMIObject which I can use for this purpose.
Let me make is more clear with an example :
There is a Clustered SQL service named "T500\ABCDE" running on 2 nodes T500A and T500B. I need to gather the Clustered service server name(T500) and the only thing that I have is the node name T500A or T500B and the instance name "ABCDE".
Any ideas?
April 24, 2011 at 7:43 pm
I know this isn't PowerShell. From SQL, you could run this:
LEFT(@@SERVERNAME, CHARINDEX('\', @@SERVERNAME)-1)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 24, 2011 at 8:34 pm
Hi Wayne,
That actually is not an approach that I can follow cos my intention is to get the Cluster server name from a node name but the approach provided by you is to go though the SQL server service first which will not be known to me to run the query.
But thanks for the response anyways, ill try and see if this can be used to have a reverse approach and get my task through.
April 25, 2011 at 2:02 am
Ill try and refine what I am exactly trying to do.
I need to copy some files on every node of a SQL server clustered server service. The files to be copied will be based on the version number of the SQL cluster. I need to repeat this step for multiple sql clusters in my environment.
As mentioned in the earlier example what I am trying to do is something like this where T500A and T500B are two nodes of a sql cluster T500\ABCDE. I want to create a smo object that connects to T500\ABCDE and then gathers both node names T500A and T500B and then passes the values to a function that copies the required files per the version number of T500\ABCDE.
Please do provide any approach that any one has, even if it does not provide the complete solution. I can try and build up something from it.
April 25, 2011 at 5:04 am
you can use this function once u connect through powershell.
select * from ::fn_getvirtualservernodes()
April 25, 2011 at 5:06 am
i mean
SELECT * FROM fn_virtualservernodes()
April 25, 2011 at 1:42 pm
Try to use Serverproperty('ComputerNamePhysicalNetBIOS')
BOL: (SERVERPROPERTY (Transact-SQL)
NetBIOS name of the local computer on which the instance of SQL Server is currently running.
For a clustered instance of SQL Server on a failover cluster, this value changes as the instance of SQL Server fails over to other nodes in the failover cluster.
On a stand-alone instance of SQL Server, this value remains constant and returns the same value as the MachineName property.
Note:
If the instance of SQL Server is in a failover cluster and you want to obtain the name of the failover clustered instance, use the MachineName property.
NULL = Input is not valid, or an error.
April 25, 2011 at 10:19 pm
If I'm following what you're asking for I think this gathers the name you are looking for. But if it's not let me know and I will take another crack at it in the morning.
Get-WmiObject -computername WIN7W510 -query "
select SystemName, Name, DriveType, FileSystem, FreeSpace, Capacity, Label
from Win32_Volume
where DriveType = 2 or DriveType = 3" `
| select SystemName `
, Name `
, @{Label="SizeIn$unit";Expression={"{0:n2}" -f($_.Capacity/$measure)}} `
, @{Label="FreeIn$unit";Expression={"{0:n2}" -f($_.freespace/$measure)}} `
, @{Label="PercentFree";Expression={"{0:n2}" -f(($_.freespace / $_.Capacity) * 100)}} `
, Label
@SQLvariantI have a PowerShell script[/url] for you.
April 26, 2011 at 6:33 am
mpadegroot (4/25/2011)
Try to use Serverproperty('ComputerNamePhysicalNetBIOS')BOL: (SERVERPROPERTY (Transact-SQL)
NetBIOS name of the local computer on which the instance of SQL Server is currently running.
For a clustered instance of SQL Server on a failover cluster, this value changes as the instance of SQL Server fails over to other nodes in the failover cluster.
On a stand-alone instance of SQL Server, this value remains constant and returns the same value as the MachineName property.
Note:
If the instance of SQL Server is in a failover cluster and you want to obtain the name of the failover clustered instance, use the MachineName property.
NULL = Input is not valid, or an error.
I'm using this approach as well to copy files. It works well.
SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
April 26, 2011 at 7:22 am
Thanks for all the suggestions from all, although I tried but I think I failed in explain my approach. In my case the failover never happens from the active node to the passive one so I do not get the information of the Failover node from the SMO Object.
SELECT * FROM fn_virtualservernodes() does provide the information that I need but as this would return the information of all the nodes that are hosting this cluster in a table format which again is viewable for me but can not be arranged into an array or some thing and then passed to a function to copy the installation files, But it does provide a useful approach, Thanks.
@ All
This is what I tried and it did work for me, Although it is just a workaround.
If i considered that I have only 2 nodes hosting my cluster, 1 active and 1 passive as this is what I have in my environment.
I used the virtual server name from the Cluster name and then queried its Registry for the second node that has been clustered with it. Then passed this node to the function to copy the files per the version of the SQL server cluster. Then I get the active node of the cluster using $server.ComputerNamePhysicalNetBIOS and pass the same again to the copy function to copy the files on this node.
Ill paste a tailored code that I have used for myself in the next reply .
April 26, 2011 at 7:47 am
Below is the code that I used with a few adjustments where i again consider that I have a SQL cluster T500\ABCDE hosted by two nodes T500A and T500B where presently T500A is active and the T500B node is passive.
function copyinstaller
{ param([string]$server)
## function definition
}
function getclustermachinename
{
param([string]$Servername,[int]$foldernumber)
$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $ServerName)
$regKey= $reg.OpenSubKey("Cluster\\Nodes\$foldernumber" )
$machinename= $regkey.getvalue("Nodename")
return $machinename
}
#############################################
$instance = "T500\ABCDE"
$smoobject= New-Object (’Microsoft.SqlServer.Management.Smo.Server’) "$instance"
$activenode = $smoobject.ComputerNamePhysicalNetBIOS
copyinstaller $activenode ## executes the coyinstaller function for active node
if($smoobject.IsClustered -eq 1)
{
$Clustername= $instance.Split("\")
$splitname = $clustername[0]
$firstnode = getclustermachinename $splitname 1
if($firstnode -eq $activenode)
{
$secondnode = getclustermachinename $splitname 2
copyinstaller $secondnode ## executes the copyinstaller function for passive node
}
else
{
copyinstaller $firstnode ## executes the copyinstaller function for passive node
}
}
The above executes the copyinstaller function for both nodes T500A and T500B.
You can run the above for many instances using a for or foreach loop with many instances.
April 27, 2011 at 2:56 am
This is in VBScript, but is easy to port to Powershell...
Set objCluster = CreateObject("MSCluster.Cluster")
objCluster.Open ""
strClusterName = objCluster.Name
If you run this on a machine that is not clustered, then the CreateObject returns an empty object and the Open will fail, so you might want to add a test for this condition.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply