SQL Server Availability Monitoring - Part I

The most typical issue that any production support DBA faces is SQL Server "SERVICE" availability. Many times it happens that the 24/7 support team calls up and says XYZ application is down and it shows the message as “Database Error”. I log in remotely and check the service, and service looks like is up and running, and when I check back the application, it's running fine now. So what went wrong in the mean time ?

Usually the SQL Server might be busy and the application might have timed out, either lock or connection timeout issue either way from application's point of view the database service was not available. So the trouble here is

1 - Exactly when the application/database was was experiencing the issue?
2 - Was application connection timeout setting was too low?
3 - Do you have statistics of SQL Server service availability?

Lot of questions will be raised when the issue is escalated and both app and database management team sit for discussions.

To address all such situation I came up with a utility/script "SQLPing". Yes, you got it right I will ping the SQL Server not to kill but tocheck if the service is available or not, so that I have answers to all abovequestions. I have implemented this as a VB Script because of its obvious advantage and I can edit as and when I want to change some parameter. And theother advantage is I don’t have to document it to make other understand what is it all about, have a look at the code and you will get what it is doing.

Let me quickly explain the files used in providing this solution,

1 - SQLPing.VBS

' Script description
'----------------------
' This procedure only reads the SQL Server name from the given(paramer) file
' and launches the sub.vbs per SQL Server found in the file.
' This design was choosed to avoide the delay caused in pinging subsequent server while
' ping scirpt is waiting for current one. Also, all servers can be pinged almost (difference of fraction seconds)
' at the same time.

'CODE STARTS

Set WshShell = WScript.CreateObject("WScript.Shell")
IF Wscript.Arguments.Count = 0 THEN
MsgBox "Server List Missing", vbCritical, "Provide the list of servers."
Wscript.Quit
END IF

PingSQLServer


'*** Procedure to query database and create the HTMLlized content ***
Sub PingSQLServer

Dim conn, rs, Qry_Txt, Qry_01, Qry_02, Qry_03, sTarget, RptFileName
const RptFldr = "C:Girish_PatilisAliveUsingVB"
SET fso = CreateObject("Scripting.FileSystemObject")
SET oFileTargets = fso.OpenTextFile(Wscript.Arguments(0),1)
DO UNTIL oFileTargets.AtEndOfStream
WshShell.Run "sub.vbs " & oFileTargets.ReadLine
LOOP
End Sub

Save the contents of about table to a file and name it as SQLPing.vbs. This file basically when executed, reads the server name from the file (passedas input parameter) and in-turn executes the Sub.vbs




2 - Sub.VBS

' Script description
IF Wscript.Arguments.Count <> 0 THEN
SQLWork Wscript.Arguments(0)
END IF


'*** Procedure to connect to SQL Server ***
Sub SQLWork(SqlSrv)
On Error Resume Next
myTimer=timer
TstTime=formatdatetime(now, 0)
set conn = CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 3
RecDate = formatdatetime(now, 0) ' current time on monitoring system.
conn.open("DRIVER=SQLServer; SERVER="& SqlSrv & ";DATABASE=master;Trusted_Connection=Yes")
if err.number <> 0 Then
WriteToDB "INSERT INTO DBA_BenchMark.dbo.Tbl_isAlive VALUES('" & TstTime & "', '" & SqlSrv & "', " & timer-myTimer & ", '" & REPLACE(Err.description, "'","''") & "')"
err.clear
exit sub
End If
set rs = CreateObject("ADODB.Recordset")
set rs = conn.execute("select getdate() AS [DateTime]")
WriteToDB "INSERT INTO DBA_BenchMark.dbo.Tbl_isAlive VALUES('" & TstTime & "', '" & SqlSrv & "', " & timer-myTimer & ", '" & "Response " & RS(0) & "')"
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub


'*** Procedure to WRITE the output to a given file ***
Sub WriteToDB (TxtToWrite)
On Error Resume Next
err.clear '-- clearing the error from previous sub
set conn = CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 10
conn.open("DRIVER=SQL Server;SERVER=SQL2K5;DATABASE=DBA_Benchmark;Trusted_Connection=Yes")
' Error handler / Timeouts
if err.number <> 0 Then
LogErrors ("-- Error : " & Err.Number & " ==> " & Err.description)
LogErrors TxtToWrite
Err.Clear
exit sub
End If

'run a query to log the results
conn.execute (TxtToWrite)
conn.Close
Set conn = Nothing
End Sub


'*** Procedure to WRITE the output to a given file - for error logging ***
Sub LogErrors (TxtToWrite)

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile( "AppErrorLog.txt", ForAppending, True)
f.Write date & " @ " & time & VBTAB & TxtToWrite & VBCRLF
f.Close
End Sub


Sub.vbs is the one which actually connects to SQL Server and checksif it’s responding, and collects the response time. In case of connection timeout and query timeout it will log the same information.

I will explain how to deploy this solution in my next article SQL Server Availability Monitoring Part-II