Check your SQL Server using Windows PowerShell – Part 1

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

a. 
Make sure .Net
2.0 is installed.
b. 
On your
desktop client machine, you need to have Windows PowerShell 1.0 installed. For install
instructions click here.

c. 
Your login should
have access to create folders and files in the desktop client machine.

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



Fig 1.0 Launch PowerShell

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.



Fig 1.1 Create new folder

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.



Fig
1.2 Create Powershell scripts and library

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 3rd 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”}
}



Fig 1.3 PowerShell Script PingHost.ps1

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



Fig
1.4 CheckSQL_Lib.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



Fig 1.5 CheckSQLServer.ps1 script

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.



Fig 1.6 Ping the Server

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.



Fig 1.7 Error on Executing any 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



Fig 1.8 Execution Policy

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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles