AboutPart 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
Figure 1.0
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
Figure 1.1
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.
a. Host is not available [Example: Step 5]
b. Firewall is blocking remote administration
If you want to disable the firewall, you can execute the following command at the command prompt as shown below.
netsh.exe firewall set service type=REMOTEADMIN mode=ENABLE scope=ALL
If you want to keep the firewall settings and only enable the ports required for remote administration, then execute the following:
netsh firewall add portopening protocol=tcp port=135 name=DCOM_TCP135
c. Windows Management Instrumentation service is not running.
Set WMI service to start Automatic and then start the service.
d. Add current user to the DCOM users.
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.