Powershell is Really Easy… If you know what you’re doing
October 18, 2010 at 8:00 am (PowerShell, SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication) (PowerShell)
Sigh… I just spent almost a full work day trying to come up with, what turned out to be a single line script. The requirements were simple. After patching of servers is completed, and everything is rebooted, sometimes, not often, but sometimes, SQL Server Agent doesn’t restart correctly. I was asked to put together a script that will check a list of servers to see if Agent is running. Any PowerShell guru’s reading this are either laughing or crying, already.
So, I started exploring how to connect to the server using using some type of PS-Drive, but I quickly figured out that I’d have to loop through the servers that way. Plus, I don’t think there’s a way to check Agent status through the SQL drive. So that went out the window. Then, it was to use Invoke-SQLCmd. After all, I can call multiple servers through the -ServerInstance property, so I just need to find a good T-SQL statement to check Agent status… Googling & Binging didn’t turn up a good T-SQL statement to check Agent status (not saying there isn’t a way, but I couldn’t find one easily, plus, this is a story about PowerShell). On to SMO. All I had to do was figure out how to connect to the server and the SQL Agent object through SMO. I’d have to loop through the servers, but for such a simple script, that shouldn’t be hard. Ever tried to connect directly to the Agent through SMO? Me neither. It’s a pain.
Finally, I referred back to Don Jones (blog|twitter) book, Windows Powershell 2.0, TFM (and a more appropriately named book, there isn’t), and noticed a statement, Get-Service… Hey! Isn’t SQL Agent basically a service? Yes. Yes it is. Here’s the final script, in all it’s simplistic glory:
Get-Service -DisplayName "SQL Server Agent*" -ComputerName (Get-Content "servers.txt") | Select-Object MachineName,Status
Yep, it was that easy. Kids, don’t try this at home. Take the time to learn what you’re doing. Don’t assume you know.
Home of the Scary DBA 
Allen White said,
October 18, 2010 at 8:19 am
Grant, I use that in the performance metrics script to test if the SQL Server instance is running before attempting to gather perf stats from it. You attended that session as I recall. I guess it shows you (generic ‘you’, not specifically you) retain the information you need at the time, and subconsciously ignore the info you aren’t ready for yet.
scarydba said,
October 18, 2010 at 8:31 am
Way too true. Yeah, I remember your session. Thinking about it, I’ve pretty sure I’ve got notes including a comment on that method. Actually, I’ve seen this used all over the place, but when it came time to write my own code… everything seems to have left my tiny brain. Still, figured it out, even if it took a lot longer than it should have.
Andy Warren said,
October 18, 2010 at 11:44 am
Grant, I think that’s a great post, takes time to absorb new tools, and for a while we’re back to beginners, wondering if we’re doing it the “right” way and if we forgot to hit the turbo switch. Keep posting these lessons.
SQLRockstar said,
October 18, 2010 at 12:16 pm
*OR*
you could just look at the dashboard in OpsMgr to see what agents are running or not…
scarydba said,
October 18, 2010 at 1:16 pm
Right. You’re assuming my co-workers know where OM is located…
Thanks Andy. Lots of my blog posts are just me figuring stuff out, publicly.
Artem Ervits said,
October 18, 2010 at 5:53 pm
Grant,
try this:
Get-Service -DisplayName “SQL Server Agent*” | where {$_.status -eq “Stopped”} | selec
t name, status, displayname | write-host -background “RED” -foreground “Black”
WIDBA said,
October 20, 2010 at 8:51 am
Glad i caught this – I was fiddling with different ways to do this for my SQL Sat 58 presentation coming up on monitoring. After having such a problem myself recently – I used WMI, but if you have an active/active cluster that does not work too well.
Thanks for sharing, learned yet another thing about powershell.
Alisdair Craik said,
October 20, 2010 at 9:00 am
You could also automate the service restart as well as logging the servers where the service is stopped (I assume that would be your next step):
Get-Service -DisplayName “SQL Server Agent*” -ComputerName (Get-Content “servers.txt”) | Start-Service -PassThru | Select-Object MachineName,Status
scarydba said,
October 20, 2010 at 9:02 am
WIDBA, glad it helped.
Alisdair, yeah, possibly. I suspect we’ll want to determine why it didn’t restart before we just automatically restart it.
scarydba said,
October 20, 2010 at 9:02 am
Artem, nice touch.
WIDBA said,
October 20, 2010 at 10:43 am
Just wanted to throw this back in – I was adding this to my monitoring script this AM and came up with a different shot at it. Since we do the multi instance per server style, I needed a way to query the agent for each sql instance I am checking and I pass in a SMO SQL object. (There are a number of other ways to do this, I tried this one, it may have some “gotchas” for other installs.)
$computerName = $SmoSqlServer.NetName
$sqlName = $SmoSqlServer.Name
$sqlAgentName = $smoSqlServer.Name.Replace($computerName,”SQLAgent”).Replace(“\”,”$”);
Get-Service -Name $sqlAgentName -ComputerName $computerName `
| where {$_.status -eq “Stopped”} `
| Select-Object MachineName,DisplayName,Status
scarydba said,
October 20, 2010 at 11:07 am
WIDBA,
Only thing wrong with that approach is that you’ll have to loop the calls. I try to avoid that as much as possible, where possible.
Powershell… Making it difficult for yourself! | youdidwhatwithtsql.com said,
October 21, 2010 at 4:58 pm
[…] I was reading the post Powershell is Really Easy… If you know what you’re doing and it really struck a chord with […]