Check your SQL Server using Windows PowerShell - Part 2

Part I of this article series illustrated how to ping the host, which is the first and foremost check on SQL Server. The second important check on the operating system level is to see if all of the windows services related to SQL Server on the host are up and running and report the status.

Part two of this series illustrates how to access the Windows service on a remote machine using Windows PowerShell and WMI-Object.

Step 1

Type or Copy and paste the following code to C:\CheckSQLServer\CheckServices.ps1.

# Function to check windows services related to SQL Server
Function checkservices  ([string] $Hostname )
{
$Services=get-wmiobject -class win32_service 
  -computername $hostname| 
  where {$_.name -like '*SQL*'}| select-object  
Name,state,status,Started,Startname,Description
foreach ( $service in $Services)
{
if($service.state -ne "Running" -or  $service.status -ne "OK" 
  -or $service.started -ne "True" )
{
$message="Host="+$Hostname+" " +$Service.Name +" 
  "" +$Service.state +" +$Service.status +" 
  " +$Service.Started +" " +$Service.Startname 
write-host $message -background "RED" -foreground "BLACk"
}
else
{
$message="Host="+$Hostname+" " +$Service.Name +" 
  " +$Service.state +" " +$Service.status +" 
  " +$Service.Started +" " +$Service.Startname 
write-host $message -background "GREEN" -foreground "BLACk"
}
}
}

Step 2

Append C:\CheckSQLServer\CheckSQL_Lib.ps1 with the following code:

. ./checkservices.ps1

Now C:\CheckSQLServer\CheckSQL_Lib.ps1 will have both pinghost and checkservices as shown below.

#Source all the functions relate to CheckSQL
. ./PingHost.ps1
. ./checkservices.ps1

Note: This CheckSQL_Lib.ps1 will be updated with sourcing of new scripts like checkservices.PS1

Step 3

Append C:\CheckSQLServer\CheckSQLServer.ps1 by copying and pasting the code shown below.

checkservices $Hostname

Now C:\CheckSQLServer\CheckSQLServer.ps1 will have both pinghost and checkservices as shown below. We added some write-host statements to show the entire process.

#Objective: To check various status of SQL Server 
#Host, instances and databases.
#Author: MAK
#Date Written: June 5, 2008
param (
  [string] $Hostname
)
. ./CheckSQL_Lib.ps1 
Write-host "Checking SQL Server....."
Write-host "........................"
Write-host " "
Write-host "Arguments accepted : $Hostname"
write-host "........................"
Write-host "Pinging the host machine"
write-host "........................"
PingHost $Hostname
Write-host "Checking windows services on the host related to SQL Server"
write-host "..........................................................."
checkservices $Hostname

Note: This CheckSQLServer.ps1 will be updated with new conditions and parameters in future installments of this article series as well.

Sourcing basically loads the functions listed in the script file and makes it available during the entire PowerShell session. In this case, we are sourcing a script, which in turn is going to source many scripts.

Step 4

Now let’s execute the script CheckSQLServer.ps1 by passing “Powerpc” host as the argument, as shown below.

./CheckSQLServer.ps1 PowerPC

You will get the results shown below. [Refer Fig 1.0]

Checking SQL Server.....
........................
Arguments accepted : PowerPC
........................
Pinging the host machine
........................
PowerPC is REACHABLE
Checking windows services on the host related to SQL Server
...........................................................
Host=PowerPC msftesql$SQL2008 Stopped OK False .\mak
Host=PowerPC MSSQL$NY0QD1 Running OK True .\mak
Host=PowerPC MSSQL$SQL2008 Stopped OK False .\mak
Host=PowerPC MSSQLServerADHelper100 Stopped OK False NT AUTHORITY\NETWORK SERVICE
Host=PowerPC SQLAgent$NY0QD1 Stopped OK False .\mak
Host=PowerPC SQLAgent$SQL2008 Stopped OK False .\mak
Host=PowerPC SQLBrowser Stopped OK False NT AUTHORITY\LOCAL SERVICE
Host=PowerPC SQLWriter Running OK True LocalSystem

From the result you can see that any SQL Server related service that is not started or whose state is not “OK” are highlighted in red and all the services related to SQL Server and that are running are highlighted in green.

Step 5

Now let’s execute the script on a machine that doesn’t exist as shown below.

./CheckSQLServer.ps1 TestMachine

You would get the following results as shown below. [Refer Fig 1.1]

Results

Checking SQL Server.....
........................
Arguments accepted : TestMachine
........................
Pinging the host machine
........................
TestMachine is NOT reachable
Checking windows services on the host related to SQL Server
...........................................................
Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
At C:\checksqlserver\checkservices.ps1:5 char:24
+ $Services=get-wmiobject  <<<< -class win32_service -computername $hostname| where {$_.name -like '*SQL*'}| select-obj
ect Name,state,status,Started,Startname,Description
Host=TestMachine

If you are getting this error “Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)”, it could be one of the following reasons.

This Error occurs due to one of the following reasons.

netsh.exe firewall set service type=REMOTEADMIN mode=ENABLE scope=ALL

netsh firewall add portopening protocol=tcp port=135 name=DCOM_TCP135

Follow the instructions described in the Microsoft website http://msdn.microsoft.com/en-us/library/ms365170(SQL.100).aspx and http://msdn.microsoft.com/en-us/library/aa393266(VS.85).aspx

Conclusion

This is the second part of the article series “Check your SQL Server using Windows PowerShell”. The article illustrated how to access the Windows Service on a remote machine using Windows PowerShell and WMI-Object. In the next installment, we will add some additional checking, so that we don’t have to do all the checking if ping fails. In addition, we will see how to capture some useful operating and hardware information as well.

» See All Articles by Columnist MAK