Test SQL Server with Windows PowerShell - Part 2

Test SQL Server with Windows PowerShell - Part 1

Muthusamy

Network Administration - Part 1 of this series showed how to ping the host, which is the first test on SQL Server. The second important test at the operating system level is to see if all Windows services are related to SQL Server on the existing host, and report the status of the system.

This part 2 will show you how to access the Windows service on the remote computer using Windows PowerShell and WMI-Object.

Step 1

Type or copy and paste the following code into the C: CheckSQLServerCheckServices.ps1 file.

 # Phần mềm để kiểm tra các tham cấp các hệ thống Services đến 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

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:

1. Host not available (example: step 5)
2. Lockout firewall blocks remote administration

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 

1. The Windows Management Instrumentation service does not work: Set up the WMI service to run automatically and then initiate the service
2. Add the current user to DCOM users.

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.