Test SQL Server with Windows Powershell - Part 3

This guide provides a practical overview of test SQL server with Windows powershell - part 3 with clear explanations and useful details. It focuses on the information users are most likely to need.

Test SQL Server with Windows PowerShell - Part 1 Test SQL Server with Windows PowerShell - Part 2

How Test SQL Server with Windows Powershell - Part 3 Works

Muthusamy

Network Administration - Part 1 of this series showed you the first check on SQL Server — how to ping a host. In Part 2, I will show you how to check all Windows services related to SQL Server. This guide provides a practical overview of test SQL server with Windows powershell - part 3 with clear explanations and useful details. It focuses on the information users are most likely to need.

Step 1

Type or Copy and paste the code below into the C: CheckSQLServerCheckhardware. ps1 file.

#Function để kiểm tra thông tin cứng từ một máy phục vụ Function checkhardware ([string] $ Hostname) { $ computer = get-wmiobject -class win32_computersystem -computername $ hostname -errorvariable errorvar $ errorvar. size If (-not $ errorvar) { $ message = "Host =" + $ Hostname Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "Description =" + $ computer. Description Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "NumberOfLogicalProcessors =" + $ computer. NumberOfLogicalProcessors Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "NumberOfProcessors =" + $ computer. NumberOfProcessors Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "TotalPhysicalMemory =" + $ computer. TotalPhysicalMemory Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "Model =" + $ computer. Model Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "Manufacturer =" + $ computer. Manufacturer Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "PartOfDomain =" + $ computer. PartOfDomain Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "CurrentTimeZone =" + $ computer. CurrentTimeZone Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "DaylightInEffect =" + $ computer. DaylightInEffect Write-host $ message -background "GREEN" -foreground "BLACk" } }

Step 2

Type or Copy and paste the code below into file C: CheckSQLServerCheckOS. ps1.

#Function để kiểm tra các thông tin thông tin về máy host CheckOS function ([string] $ Hostname) { $ os = get-wmiobject -class win32_operatingsystem -computername $ hostname -errorvariable errorvar If (-not $ errorvar) { $ message = "OSArchitecture =" + $ os. OSArchitecture Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "OSLanguage =" + $ os. OSLanguage Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "OSProductSuite =" + $ os. OSProductSuite Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "OSType =" + $ os. OSType Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "BuildNumber =" + $ os. BuildNumber Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "BuildType =" + $ os. BuildType Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "Version =" + $ os. Version Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "WindowsDirectory =" + $ os. WindowsDirectory Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "PlusVersionNumber =" + $ os. PlusVersionNumber Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "FreePhysicalMemory =" + $ os. FreePhysicalMemory Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "FreeSpaceInPagingFiles =" + $ os. FreeSpaceInPagingFiles Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "FreeVirtualMemory =" + $ os. FreeVirtualMemory Write-host $ message -background "GREEN" -foreground "BLACk" $ message = "PAEEnabled =" + $ os. PAEEnabled Write-host $ message -background "GREEN" -foreground "BLACk" } }

Step 3

Append to file C: CheckSQLServerCheckSQL_Lib. ps1 code below.

. ./checkhardware. ps1 . ./checkOS. ps1

Now the file C: CheckSQLServerCheckSQL_Lib. ps1 will have pinghost, checkservices, checkhardware and checkOS as shown below.

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

Note: This CheckSQL_Lib. ps1 file will be updated from new scripts such as checkhardware. ps1 and checkOS. ps1

Step 4

Append to the file C: CheckSQLServerCheckSQLServer. ps1 code below.

Checkhardware $ Hostname CheckOS $ Hostname

Now file C: CheckSQLServerCheckSQLServer. ps1 will have both checkhardware and checkOS as below, We have added a write-host statement 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 Write-host "Checking hardware Information." Write-host "…" Checkhardware $ Hostname Write-host "Checking OS Information." Write-host "…" CheckOS $ Hostname

Note: This CheckSQLServer. ps1 file will be updated with new conditions and parameters in later sections of the series.

The source will load the functions listed in the script file and make it available during the entire PowerShell session. In this case, we source a scenario, which will be sourced from many other scenarios.

Step 5

Now let's execute the script, CheckSQLServer. ps1 by passing 'Powerpc' host as an argument as shown below.

./CheckSQLServer. ps1 PowerServer2

Then the result will be as follows (refer to Figure 1.0)

Checking SQL Server. … Arguments accepted: PowerServer2 … Pinging the host machine … PowerServer2 is REACHABLE Check Windows services on the host related to SQL Server … Host = PowerServer2 MSSQLSERVER Running OK True. Administrator Host = PowerServer2 MSSQLServerADHelper100 Stopped OK False NT AUTHORITYNETWORK SERVICE Host = PowerServer2 MSSQLServerOLAPService Stopped OK False. Administrator Host = PowerServer2 SQLBrowser Stopped OK False NT AUTHORITYLOCAL SERVICE Host = PowerServer2 SQLSERVERAGENT Stopped OK False. Administrator Host = PowerServer2 SQLWriter Stopped OK False LocalSystem Checking hardware Information. … Host = PowerServer2 Description = AT / AT COMPATIBLE NumberOfLogicalProcessors = 2 NumberOfProcessors = 1 TotalPhysicalMemory = 2145738752 Model = OptiPlex GX270 Manufacturer = Dell Computer Corporation PartOfDomain = True CurrentTimeZone = -240 DaylightInEffect = True Checking OS Information. … OSArchitecture = 32-bit OSLanguage = 1033 OSProductSuite = 274 OSType = 18 BuildNumber = 6001 BuildType = Multiprocessor Free Version = 6.0.6001 WindowsDirectory = C: Windows PlusVersionNumber = FreePhysicalMemory = 1511144 FreeSpaceInPagingFiles = 2402648 FreeVirtualMemory = 3966452 PAEEnabled = False

Figure 1.0

From the results, you can see hardware and operating system information.

Step 6

Please execute the script on the machine that does not exist as shown below.

./CheckSQLServer. ps1 TestMachine

Then the results will be as follows (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 Checking hardware Information. … Get-WmiObject: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) At C: checksqlservercheckhardware. ps1: 5 char: 24 + $ computer = get-wmiobject <<<< -class win32_computersystem -computername $ hostname -errorvariable errorvar Checking OS Information. … Get-WmiObject: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) At C: checksqlservercheckOS. ps1: 5 char: 18 + $ os = get-wmiobject <<<< -class win32_operatingsystem -computername $ hostname -errorvariable errorvar

Figure 1.1

Step 7

We do not have to continue with checkservices, checkhardware and checkos if ping fails. So update pinghost. ps1 as shown below.

Function Pinghost ([string] $ Hostname) { $ status = get-wmiobject win32_pingstatus -Filter "Address = '$ Hostname'" | Select-Object statuscode If ($ status. statuscode -eq 0) { Write-host $ Hostname is REACHABLE -background "GREEN" -foreground "BLACk" } Else { $ global: errorvar = "host not reachable" Write-host $ Hostname is NOT reachable -background "RED" -foreground "BLACk" } } Update the checksqlserver. ps1 as shown below. #Objective: To check various status of SQL Server #Host, instances and databases. #Author: MAK #Date Written: June 5, 2008 Param ( [string] $ Hostname ) $ global: errorvar = 0 . ./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 If ($ global: errorvar -ne "host not reachable") { Write-host "Check Windows services on the host related to SQL Server" Write-host "… " Checkservices $ Hostname Write-host "Checking hardware Information." Write-host "…" Checkhardware $ Hostname Write-host "Checking OS Information." Write-host "…" CheckOS $ Hostname }

Now execute the script by passing the name 'testmachine', the name does not really exist as an argument.

./CheckSQLServer. ps1 TestMachine

Result

Checking SQL Server. … Arguments accepted: TestMachine … Pinging the host machine … TestMachine is NOT reachable

Note that you can download the latest code in the third section here.

Conclude

This is the third part of this series. In this third part, I have shown you how to access hardware and operating system information using Windows PowerShell and WMI-Object. In the next section, we will add some other checks and introduce how to capture some hardware and operating system information.