Friday, December 21, 2007

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.


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


      '*** 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
      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
      TstTime=formatdatetime(now, 0)
      set conn = CreateObject("ADODB.Connection")
      conn.ConnectionTimeout = 3
      RecDate = formatdatetime(now, 0) ' current time on monitoring system."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, "'","''") & "')"
      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) & "')"
      Set rs = Nothing
      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"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
      exit sub
      End If

      'run a query to log the results
      conn.execute (TxtToWrite)
      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
      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

No comments:

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.