Check your SQL Server using Windows PowerShell - Part 1

June 18, 2008

As a Microsoft SQL Server Database Administrator, when we are paged or emailed with red alerts, we tend to run some scripts, commands, or procedures to see if the status of the server looks ok. This article series is going to illustrate different methods and procedures to check the current status of the Operating system, SQL Server instances and databases etc., using Windows PowerShell.

Instead of using Vbscript, bat files, sql client executables etc., we will leverage the power of Windows PowerShell to check the various status of SQL Servers.

Pre-requisite

Before we dive in to the actual Checking of SQL Server, let’s do some ground work, and build the folders, libraries, etc.

At the end of this article series, there will be a Powershell library with many functions that can be sourced in any PowerShell script. One, many, or all of the functions can be called from any PowerShell script once the library is sourced.

Step 1

Launch Windows PowerShell by executing the command shown below. [Refer Fig 1.0]

%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe

Step 2

Let’s create a folder using the below PowerShell cmdlet. This folder will be dedicated to PowerShell scripts, libraries and functions related to Checking SQL Server. Execute the cmdlet shown below. [Refer Fig 1.1]

New-Item -Path C:\ -Name CheckSQLServer -Type directory

You can see the result of the cmdlet below. Basically, the folder CheckSQLServer has been created Successfully.

Step 3

Now let’s navigate to the CheckSQLServer folder and then create the following files using the PowerShell cmdlets shown below. [Refer Fig 1.3]

set-location C:\CheckSQLServer
Notepad CheckSQL_Lib.ps1
Notepad CheckSQLServer.ps1
Notepad Pinghost.ps1

You can see that the location is changed to C:\CheckSQLServer and also it has opened three notepad windows for editing the files CheckSQL_Lib.sql, CheckSQLServer.Ps1 and PingHost.ps1.

Note: Notepad is the editor I use for editing PowerShell scripts. You are not obligated to use notepad as your editor; you can use any 3^rd party editors.

Step 4

Type or copy/paste the following code in the notepad editor that opened PingHost.ps1 as shown below. [Refer Fig 1.3]

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
{write-host $Hostname is NOT reachable -background "RED" -foreground "BLACk"}
}

Save the file Pinghost.ps1 and exit notepad.

Step 5

Type or copy/paste the following code into the notepad editor that opened CheckSQL_Lib.ps1 as shown below. [Refer Fig 1.4]

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

Save the file CheckSQL_Lib.ps1 and exit notepad.

Note: This CheckSQL_Lib.ps1 will be updated with sourcing of new scripts like PingHost.PS1

Sourcing basically loads the functions listed in the script file, and makes it available in the entire PowerShell session.

Step 6

Type or copy/paste the following code into the notepad editor that opened CheckSQLServer.ps1 as shown below. [Refer Fig 1.5]

#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 
PingHost $Hostname

Save the file CheckSQLServer.ps1 and exit notepad.

Note: This CheckSQLServer.ps1 will be updated with new conditions and parameters in future installments of this article series.

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 7

Execute the CheckSQLServer.ps1 as shown below. [Refer Fig 1.6]

./CheckSQLServer.ps1 PowerMachine
./CheckSQLServer.ps1 TestServer

You will see the following results, based on whether the machine is pingable or not. If the machine is reachable, the message is highlighted in green and if not it is highlighted in red.

By default PowerShell scripts cannot be executed on the machine if you are using it for the first time. If you get the following error message as shown [Refer Fig 1.7] execute the following cmdlet to enable unrestricted execution of PowerShell Script.

set-executionpolicy unrestricted

Note: You don’t have to execute the above cmdlet every time. Execute the cmdlet one time only.

You can check the execution policy of the current PowerShell configuration by executing the following cmdlet as shown below. [Fig 1.8]

get-executionpolicy

Conclusion

This is the first part of the article series “Check your SQL Server using Windows PowerShell”. This article illustrated how to create a PowerShell script to ping the host machine. It also illustrated how to source the PowerShell function and call the function. Future installments of this series will go in depth on different features of Windows PowerShell to check various statuses of SQL Server.

» See All Articles by Columnist MAK