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
Add the C: CheckSQLServerCheckSQL_Lib.ps1 file below:
. ./checkservices.ps1
Now C: CheckSQLServerCheckSQL_Lib.ps1 will have both pinghost and checkservices as shown below.
#Source all the relate to functions CheckSQL
. ./PingHost.ps1
. ./checkservices.ps1
Note: This CheckSQL_Lib.ps1 file will be updated with the source of the new script like checkservices.PS1
Step 3
Attach the file C: CheckSQLServerCheckSQLServer.ps1 by copying and pasting the code below.
checkservices $ Hostname
Now the file C: CheckSQLServerCheckSQLServer.ps1 will have both pinghost and checkservices as shown below. We have added some statements to show the whole 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 "Check 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 the following settings of this series.
The source basically loads the functions listed in the script file and makes it available throughout the PowerShell session. In this case, we are citing a scenario.
Step 4
Execute the CheckSQLServer.ps1 script by passing the host 'Powerpc' as an argument, as shown below.
./CheckSQLServer.ps1 PowerPC
You will get the results shown below (see Figure 1.0).
Checking SQL Server .
....
Arguments accepted: PowerPC
....
Pinging the host machine
....
PowerPC is REACHABLE
Check 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 AUTHORITYNETWORK SERVICE
Host = PowerPC SQLAgent $ NY0QD1 Stopped OK False .mak
Host = PowerPC SQLAgent $ SQL2008 Stopped OK False .mak
Host = PowerPC SQLBrowser Stopped OK False NT AUTHORITYLOCAL SERVICE
Host = PowerPC SQLWriter Running OK True LocalSystem
Figure 1.0
From the results you can see that any SQL Server related to the service is not initialized or marked with a 'OK' status in red and all SQL Server related services are active. is highlighted in blue.
Step 5
Let's execute the script on the computer that doesn't exist as shown below.
./CheckSQLServer.ps1 TestMachine
You will then get the results shown below (see Figure 1.1).
Result
Checking SQL Server .
....
Arguments accepted: TestMachine
....
Pinging the host machine
....
TestMachine is NOT reachable
Check windows services on the host related to SQL Server
......... ..
Get-WmiObject: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
At C: checksqlservercheckservices.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 get the error 'Get-WmiObject: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) 'the cause may be some of the following reasons:
If you want to disable the firewall, execute the following command at the command prompt:
netsh.exe firewall set service type = REMOTEADMIN mode = ENABLE scope = ALL
If you want to keep the firewall settings and only allow the required ports for remote administration, execute the following code:
netsh firewall add portopening protocol = tcp port = 135 name = DCOM_TCP135
Conclude
In this second part we have seen how to access the Windows Service on the remote machine using Windows PowerShell and WMI-Object. In the next part of the series, we will add other tests so that we do not have to perform all tests if ping fails. In addition, I will show you how to capture some hardware and operating system information.