The Multifunctioning DBA

Oct 24 2008   6:14PM GMT

Powershell: Verify SQL Server is running

Colin Smith Colin Smith Profile: Colin Smith

I call this Pinging the SQL Server. I am just going to make a connection to verify that the server is running and then close the connection. This is something that I am running in conjunction with the SQL Server Monitoring that I am posting about on this blog as well. I did not build this directly in but you could if you want to. This is mostly done separate because I run the monitoring script on the local host whenever possible. If I have a problem on the Local Host of the SQL Server then I will never know. This script runs from another server that is used primarily for monitoring other servers and should have essentially no downtime. So I run this every ten minutes to verify that my SQL Servers are up and that I can make a connection to the server. Essentially I am pinging the SQL Server. I have also built in a piece that will verify the server is actually down by waiting until the second failed ping in a row before sending out notifications. On multiple occasions this script has notified us of a server being down and we find that it is actually a problem with the Server or host OS and not SQL Server. My company does have MOM in place but us DBA’s almost always know about a problem on one of our SQL Servers before the Windows Administration team.

##Ping SQL Servers
function Set_Pagers
{
##Check Day and Time for Pager_Day or Pager_Night and if Pager_Night check if server is Test or Prod
$time = Get-Date -displayhint time
$day = Get-Date -uFormat “%A”
Clear-Content i:\$folder\mailout.txt
cd h:
if (($time -gt “7:00:00 AM”) -and ($time -lt “4:00:00 PM”) -and ($day -ne “Saturday”) -and ($day -ne “Sunday”))
{
Get-Content “i:\$folder\Pagers_Day” >> “i:\$folder\mailout.txt”
Get-Content “i:\$folder\email” >> “i:\$folder\mailout.txt”
$pager = Get-Content “i:\$folder\mailout.txt”

}
else
{
if ($torp -eq “Prod”)
{
Get-Content “i:\$folder\Pagers_night” >> “i:\$folder\mailout.txt”
Get-Content “i:\$folder\email” >> “i:\$folder\mailout.txt”
$pager = Get-Content “i:\$folder\mailout.txt”
}
else
{
Get-Content “i:\$folder\email” >> “i:\$folder\mailout.txt”
$pager = Get-Content “i:\$folder\mailout.txt”
}

}
}
#################################
####### Start Script ############
#################################
$time = Get-Date -displayhint time
New-PSDrive i -PSProvider filesystem -Root \\pni-vmdbasql\D$\Monitoring
Clear-Content -Path i:\functions\OUT\Pingservers.out
##Read in List of SQL Servers and open a connection
##If no connection can be established send out notifications.
$servers = Import-Csv -Path “i:\inputs\sqltab.txt”
foreach ($server in $servers)
{
$machine = $server.server
$instance = $server.instance
$torp = $server.torp
$ping = $server.ping

if ($instance -eq “NULL”)
{
$SqlServer = $server.server
$folder = $server.server
}
else
{
$SqlServer = “$machine\$instance”
$folder = “$machine-$instance”
}

if ($ping -eq “Y”)
{
if (!(test-path “i:\$folder\pingfail.txt”))
{
New-Item -type File “i:\$folder\pingfail.txt”
}
$failcountfile = “i:\$folder\pingfail.txt”
$failcount = Get-Content “i:\$folder\pingfail.txt”

$SqlServer
$SqlCatalog = “master”
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True”
$SqlConnection.Open()
if($SqlConnection.state -eq “Open”)
{
echo “$sqlserver – Connection Succesfull at $time” >> i:\functions\OUT\Pingservers.out
Clear-Content $failcountfile
#echo “connection Succesfull” >> i:\PS_Scripts\Files\Pingservers.out
$SqlConnection.Close()
echo “Succes”
}

else
{
echo “$sqlserver – No connection could be established at $time” >> i:\functions\OUT\Pingservers.out
if ($failcount.length -gt 0)
{
#echo “No connection could be established” >> i:\PS_Scripts\Files\Pingservers.out
echo “Fail at $time” >> $failcountfile
echo “Fail $sqlserver”
echo “Setting Pagers”
Set_Pagers
Echo “Notify DBA”
.{I:\Functions\Ping_Notify.ps1}
}
else
{
echo “Fail at $time” >> $failcountfile
}
}
}
if ($ping -eq “N”)
{

echo “$sqlserver – is not being pinged at this time. Check SQLTAB” >> i:\functions\OUT\Pingservers.out
}

}

So let’s walk though this and see what is going on. Again this will be a high level overview of what I am doing so please feel free to ask questions if needed. The first part of the script reads in my sqltab file. We talked about this file in the SQL Server Monitoring discussion and I am using the same file here. This file contains all the pertinent information about all of the SQL Servers in the company. Once the script gets all the needed variables set I can attempt to connect to the server. I look in the sqltab file and if the server is set up to be pinged, we may shut this off for maintenance windows where we know the server will be offline, then it will continue to make a connection. If that connection is successful then I write that to a log file for future reference.

If the connection fails, then I need to know about this. I log the connection failure in the same historical log file for the future and I also check a failcount file for this server. If this is the first time the connection for this server has failed then I just make note of it in that file as well and continue on going through the list of servers to ping. If the server has failed the previous time then I need to make someone aware of the outage. I then call another function called Ping_Notify.ps1. This script is what will notify the appropriate people of the issue. This will continue to send notifications every ten minutes until the server pings successfully. You will notice that if the ping is successful I also clear-content on the failcount file for that server. That way no more notifications will be sent out for this server.

Again this is a very high level overview of what I am doing. If you have any questions comments or suggestions please let them be known. If you would like to email me just head over to my web site at sysadminsmith.com and let me know.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: