Introduction
This is the fifth blog post continuing the series on the Pillars of PowerShell. The previous post in the series are:
The Windows Operating System (OS) is something a DBA should know and be familiar with since SQL Server has to run on top of it. I would say that on average most DBAs interact with the OS for troubleshooting purposes. In this post I just want to point out a few snippets of how PowerShell can help you do this type of work.
Services Console Manager
In the SQL Server 2000 days DBAs became very familiar with typing in “services.msc” in the run prompt. Scrolling through the list of services to find out what state it is, or what the login is configured for with a particular service. Now, if you are performing administrative tasks against SQL Server services it is always advised that you use SQL Server Configuration Manager. However, if you are looking to check the status of the service or performing a restart of just the service, PowerShell can help out.
Get-Service
This cmdlet has a few discrepancies that it can help to understand upfront when you start using PowerShell instead of the Services Console. In the Services Console you find the service by the “Name”, this is the “DisplayName in the Get-Service cmdlet. The “Name” in Get-Service is actually the “Service Name” in the Service Console, do you follow? OK. So with SQL Server the DisplayName for a default instance would be “SQL Server (MSSQLSERVER)”, and the “Name” would be “mssqlserver”. This cmdlet allows you to filter by either field so the below two commands will return the same thing:
Get-Service 'SQL Server (MSSQLSERVER)'
Get-Service mssqlserver
You can obviously see which one is easier to type right off. So with SQL Server you will likely know that a default instance’s name would be queried using “mssqlserver”, and a named instance would be “mssql$myinstance”. So if you wanted to find all of the instances running on a server you could use this one-liner:
Get-Service mssql*
Restart-Service
This does exactly what you think it will, so you have to be careful. You can call this cmdlet by itself and restart a service by referencing the “name” just as you did with Get-Service. I want to show you how the pipeline can work for you in this situation. You will find some cmdlets in PowerShell that have a few “special” features. The service cmdlets are included in this category, they allow an array as an input object to the cmdlet for the property or via the pipeline.
So, let’s use the example that I have a server with multiple instances of SQL Server, and all the additional components like SSRS and SSIS. I only want to work with the named instance “SQL12”. I can get the status of all component services with this command:
Get-Service -Name 'MSSQL$SQL12','ReportServer$SQL12','SQLAgent$SQL12','MsDtsServer110'
Now if I need to do a controlled restart of all of those services I can just do this command:
Get-Service -Name 'MSSQL$SQL12','ReportServer$SQL12','SQLAgent$SQL12','MsDtsServer110' | Restart-Service -Force -WhatIf
The added “-WhatIf” will not actually perform the operation but tell you what it would end up doing. Once I remove that the restart would actually occur. All of this would look something like this in the console:
Win32_Service
Some of you may recognize this one as a WMI class, and it is. Using WMI offers you a bit more information than the Get-Service cmdlet. You can see that by just running this code:
Get-Service mssqlserver
Get-WmiObject win32_service | where {$_.name -eq 'mssqlserver'}
The two commands above equate to the same referenced service but return slightly different bits of information by default:
However, if you run the command below, you will see how gathering service info with WMI offers much more potential:
Get-WmiObject win32_service | where {$_.name -eq 'mssqlserver'} | select *
Get-Service will not actually give you the service account. So here is one function I use often (saved in my profile):
function Get-SQLServiceStatus ([string[]]$server)
{
foreach ($s in $server) {
Get-WmiObject win32_service -ComputerName $s |
where {$_.DisplayName -match "SQL "} |
select @{Label="ServerName";Expression={$s}},
DisplayName, Name, State, Status, StartMode, StartName
}
}
One specific thing I did in this function is declaring the type of parameter you pass into this function. When you use “[string[]]”, it means the parameter accepts an array or multiple objects. You can set your variable to do this, but you also have to ensure the function is written in a manner that can process the array. I did this simply by wrapping the commands into a “foreach” loop. So an example use of this against a single server would be:
If you wanted to run this against multiple servers it would go something like this:
Get-SQLServerStatus -server 'MyServer','MyServer2','MyServer3' | Out-GridView #another option $serverList = 'MyServer','MyServer2','MyServer3' Get-SQLServerStatus -server $serverList | Out-GridView
Disk Manager
Every DBA should be very familiar with this management console and can probably get to it blind folded. You might use this or “My Computer” when you need to see how much free space there is on a drive. If you happen to be working in an environment that only has Window Server 2012 and Windows 8 or higher, wish I was there with you. PowerShell 4.0 and higher offers storage cmdlets that let you get information about your disk and volume much easier, and cleaner. They actually use CIM (Common Information Model), which is what WMI is built upon. I read somewhere that basically “WMI is just Microsoft’s way of implementing CIM”. They are obviously going back to the standard, as they have done with other areas. It is worth learning more about, and it actually allows you to connect to a PowerShell 2.0 machine to get the same amount of information.
Anyway back to the task at hand. If you are working on PowerShell 3.0 or lower you can use Get-WmiObject and win32_Volume to get similar information that the storage cmdlet Get-Volume returns in 4.0:
Get-Volume
Get-WmiObject win32_volume | select DriveLetter, Label, FileSystem,
@{Label="SizeRemaining";Expression={"{0:N2}" -f($_.FreeSpace/1GB)}},
@{Label="Size";Expression={"{0:N2}" -f($_.Capacity/1GB)}} | Format-Table
Windows Event Viewer
Almost everyone is familiar with and knows their way around the Windows Event Viewer. I actually left this last for a reason. I want to walk you through an example that I think will help “put it all together” on what PowerShell can do for you. Our scenario is dealing with a server that had an unexpected restart, at least for me. There are times that I will get paged by our Avail Monitoring product for a customer’s site, and I need to find out who or why the server restarted. The most common place you are going to go for this will be the Event Log.
Show-EventLog
If you just want to go through Event Viewer and manually find events, and it is a remote server, I find this to be the quickest method:
Show-EventLog -ComputerName Server1
This command will open Event Viewer and go through the process of connecting you to “Server1”. No more right-clicking and selecting “connect to another computer”!
Get-EventLog
I prefer to just dig into searching for events, this is where Get-EventLog comes in handy. You can call this cmdlet and provide:
- Specific Log to look in (system, application, or security most commonly)
- Specify a time range
- Look just for specific entry type (error, information, warning, etc.)
In Windows Server 2003 Microsoft added a group policy “Shutdown Event Tracker” that if enabled writes particular events to the System Log when a server restarts, either planned or unplanned. In an unplanned event the first user that logs into the server will get a prompt about the unexpected shutdown. When you are dealing with planned, they are prompted for a similar prompt for restart and it has to be filled in before the restart will occur. What you can do with this cmdlet is search for those messages in the System Log.
To find the planned you would use:
Get-EventLog -LogName System -Message "*restart*" -ComputerName Server1 | select * -First 1
Then to find the unplanned simply change “*restart*” to “*shutdown*”:
In this instance I find that SSIS and SSRS did not start back up and failed to start. I found this because I checked the status of the services for SQL Server using my custom function, Get-SQLServiceStatus:
To search for events after the shutdown I need to find the first event that is written to the Event Log when a server starts up, the EventLog source. I can then use that time stamp as a starting point to search for messages on the SQL Server services that did not start up correctly. I just need the time stamp of the event and pass that into the Get-EventLog cmdlet to pull up error events. I am going to do that with this bit of code:
$t = Get-EventLog -LogName System -Source EventLog -Message "*shutdown*" | select * -First 1 Get-EventLog -LogName System -Before $t.TimeGenerated -Newest 5 -EntryType Error | select TimeGenerated, Source, Message | Format-Table -Wrap
Summary
I hope you found this post useful and it gets you excited about digging deeper into PowerShell. In the next post I am going to close up the series digging into SQL Server and a few areas where PowerShell can help.
Learn more about our expertise in SQL Server.
No comments