Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jul 1, 2008

Check your SQL Server using Windows PowerShell - Part 2

By Muthusamy Anantha Kumar aka The MAK

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


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.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM