Ok, I was on this blog today and it showed a cool Powershell method for checking which cluster node is active. And while there’s nothing wrong with the script, it does go to show the thing I dislike the most about how many DBAs work with Powershell in SQL Server. Now, I can’t stress enough that I’m not picking on this guy, and I’m not saying he’s wrong. I’m just saying that I prefer to rely on the built-in methods of the provider because it’s just simpler. Here’s what I mean.
I’m going to borrow his code for a moment just to show you an example. But I encourage you to visit his blog yourself as he’s got some cool stuff out there. Anyway, here’s his method for doing this in PS:
# Set cluster name
$cluster_name = "ClusterName";
# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null;
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $cluster_name;
# Get server properties
$properties = $srv.Properties
$owner_node = $properties.Item("ComputerNamePhysicalNetBIOS").Value;
$is_clustered = $properties.Item("IsClustered").Value
if($is_clustered)
{
Write-Host "The current active node of $cluster_name is $owner_node.";
}
else
{
Write-Host "$cluster_name is not a clustered instance of SQL Server.";
}
Overall, there’s nothing wrong with that except it’s just too long. See, too many DBAs forget what Powershell is all about so they’re constantly re-inventing the wheel. I like to use the kind of wheels I’m given whenever possible.
Here’s how I would tackle this same task (there are actually 2 ways):
Method 1
>$a = dir
>$a.ComputerNamePhysicalNetBIOS
Method 2
>(dir).ComputerNamePhysicalNetBIOS
And that’s it. Why would you want to type all that other stuff when you can so easily type just a few characters… 33 to be exact.
All you have to do is make sure you’re in the server node of the provider tree.
So if you connect through sqlps, then you’ll right-click on the server itself and that’ll take you to the Default instance. Then you just need to go one level up by typing ‘cd ..’ From there, just type one of the lines above and you’re golden.
Oh yeah, his script also told you whether the box was even clustered to begin with. We can handle that with another line.
>$a.IsClustered
That returns ‘True’ so if you want to make it pretty for yourself then just a quick change can make that happen.
>IF ($a.IsClustered) {“It’s clustered alright.”} ELSE {“No it isn’t.”}
And strictly speaking you don’t need the ELSE in there.
Now, using both methods you can easily cycle through a ton of boxes so there are no worries there.
I’m gonna get on my soapbox for a minute and say that this is the bulk of the PS I see being taught and to me it shows a lack of fundamental understanding of what PS is supposed to do for us. I just prefer to use what the provider gives me. And it really matters too. If you find yourself somewhere without your scripts, which happens to me all the time when I’m working from a user’s box, or at another shop, then you’ve got to remember all that SMO provider info. My method is not only much easier to remember, it’s much easier to investigate because you can easily do a GM against $a to see all the properties it has. But if you go even just a couple weeks without typing in that code to load the SMO provider, you can forget nuances and you’ll find yourself looking something up. And that’s not efficient DBA work. PS is supposed to make our jobs easier, not complicate them. PS isn’t just a replacement for VBScript. It’s an entirely new way of thinking. And there are tons of guys out there teaching PS who haven’t switched to the PS way of thinking. To me this is the exact same thing as those guys who use those really complicated cursor methods for finding log space usage when they could just type DBCC sqlperf(logspace). Can you do it the other way, sure, but why would you when the SQL team has given you such an easy method? This is why most of the time whenever I see a PS blog somewhere, I typically have to translate it into real PS.
So guys, let’s get rid of all that needless SMO code and bring PS back to what it’s supposed to be… simple one-liners that make our job easier.
I also did a video last week about changing server-level properties that talks you through the same methods as well. Take a look.
http://midnightdba.itbookworm.com/VidPages/PowershellServerProps/PowershellServerProps.aspx
You know you could just use get-clustergroup “ResourceGroupName” and it will tell you, right? One line. Done.
True, but you have to either be on the server, or have something non-default installed on your client box because even loading the windows modules on my win7 box doesn’t yield that cmdlet. So my solution is assuming a default install where you wouldn’t have anything special. I do like how easy your way is though… I’ll file that away.
Also, if you notice my 2nd method is also a 1-liner.
HI Sean,
Powershell for me is mainly about automation. I don’t want to fire up the SQL Server PS console to check my active node. I want to create scripts that can be run automatically to inform me when certain things change or to produce periodic reports. I would imagine the sql ps console is probably doing something very similar to the first few lines of my script when initialising the environment.
@Allan ditto Seans’ comment. I don’t have complete control over where my scripts run so I may not have access to certain cmdlets.
Cheers,
Rhys
PS I don’t feel picked on but can I have my lunch money back?
In general, it’s always MUCH easier to use the cluster cmdlets (assuming W2K8 R2 – they’re not in W2K8). DBAs really should use that stuff. Just sayin’. Take SMO out of the picture completely.
Hey Rhys… while I agree with automating things, the same thing can easily be accomplished in PS proper like this:
>cd sqlserver:\sql\servername
Then the rest of my method kicks in and it’s still tons shorter, and easier to write, read, support, etc.
So while I did my example in sqlps, it’s not required. Try it, you’ll love it.
Sean,
A lot of this stuff forms the basis of emails that get sent out, i.e. an email with subject “Cluster has failed over” so a one-liner probably isn’t going to do it (there’s a challenge!). Your last example doesn’t work in my standard ps shell so presumably there’s a provider to load there. I could certainly shrink the example down but very complex Powershell one-liners can be hard to follow.
Allan, you’ll be please to know I do use cluster cmdlets. If they’re not packaged with the OS you need to install The remote server administration tools and then active the Powershell stuff in the control panel. I’m using them on Windows 7 at work.
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=21090
SEAN EDIT: Adding good link to RSAT for Win7.
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=7887
Rhys
I avoid the SQL PS provider if I can as it exists now. It’s not my favorite thing. The cluster cmdlets are very handy and with cluster.exe going away, people need to learn them.
Agreed, but read my comment below. My methods work just fine in PS proper. I just used sqlps as a quick example.
I get the implications of what you’re saying, but the 1-liner just replaces how to get the info. Once you’ve got it, you can easily send an email with it and then we’re back on equal footing. The only thing that’s in question is how to get the info to begin with… then you can do with it what you want.
Yeah, you have to load the sql providers to get sqlserver in psdrive.
add-pssnapin sqlserverprovidersnapin100
add-pssnapin sqlservercmdletsnapin100
Add them to your profile and you never have to worry about them again.
I was talking to a Jr. about this today and I pointed out that we could both create functions for our respective methods and get the typing down greatly. But this is the kind of thing that’s meant to run as a server-level process so it’s best not to rely on that kinda thing.
I’ll say while I like Allan’s method for doing this, both of our methods beat his in a full prod enviro because they’re more reusable. You don’t have to change anything to get other server properties you may want. You could easily create a function where you just passed in the box(es) and the property you’re interested in and it’ll fetch it. Allan’s is only usable for clusters. And I’m not extremely fond of having mult methods for practically the same thing.
Sean – I’m trying to get your example to work, but am not seeing the expected output. I have a 2k8 cluster with 5 clustered dev instances installed.
I’m at the machine and run the $a=dir and then $a | gm and get what seems like the proper type, but then typing $a.property doesn’t yield any output.
This is my object type:
TypeName: Microsoft.SqlServer.Management.Smo.Server
Any ideas what I have wrong?
Thanks for the writing.
hey Sam… upload a screenshot of what you’re doing and I’ll be able to tell you what’s going on.
I believe your code may not be an exact replacement of the PowerShell code in SQLSMO.
The code from the other blog actually tests if the instance of SQL Server is Clustered, and if the current host is the active node…not if the OS is Clustered.
Your code tests if Windows is clustered. A windows clustered server does not have to run SQL Server in a clustered mode. It also may not be the active node for an SQL Instance.
There are other ways to perform this same test without using SMO that are lighter weight. But, I’m not sure your code is equivalent.