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 Jun 18, 2008

Check your SQL Server using Windows PowerShell - Part 1

By Muthusamy Anantha Kumar aka The MAK

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



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