Technical Article

SQL Server Availability Monitoring

,

This VB script will attempt to make a connection to all SQL Servers enumerated in the SQLInventory table over Named Pipes and TCP\IP. If a SQL Instance is listed, it will attempt TCP\IP connections using both port and instance name. Once the connection is established to each server using each protocol, the script will query sysprocesses to verify the connection is over the right protocol, to ensure that no redirection using aliases is taking place. Any connection failures will be dumped into an email, with the failure message. Once all connections have been attempted, the email will be sent, assuming a failure occurred.

If the NPDisabled or TCPDisabled bits are set in the SQLInventory table, that protocol will not be monitored for that server.

On Error Resume Next

strDBListserver = "INSERT SERVER for INVENTORY DATABASE HERE"

txtEmailbody = ""
Set cnDBList = CreateObject("ADODB.Connection")
Set cnDBTest = CreateObject("ADODB.Connection")
Set rsDBList = CreateObject("ADODB.Recordset")
Set rsDBTest = CreateObject("ADODB.Recordset")

strDBListconn = "Provider='sqloledb';Data Source=" & strDBListserver & ";Integrated Security='SSPI';Initial Catalog='zDBA';Network Library=dbmssocn"
strDBList = "SELECT * FROM zDBA..SQLInventory"

cnDBList.Open strDBListconn
rsDBList.open strDBList, cnDBList,0,2

txtEmailbody = Now() & vbnewline & vbnewline
intCounter = 0

While rsDBList.EOF = False
If rsDBLIst("instancename") <> "" Then
fullname = rsDBList("hostname") & "\" & rsDBList("instancename")
Else
fullname = rsDBList("hostname")
End If 

'txtEmailbody = txtEmailbody & rsDBLIst("hostname") & " starting at " & Now() & vbnewline
' Test Named Pipes Connectivity    
If rsDBList("NPDisabled") = 0 Then
strDBtestconn = "Provider='sqloledb';Data Source=" & fullname & ";Integrated Security='SSPI';Initial Catalog='master';Network Library=dbnmpntw;Connect Timeout=20"
cnDBTest.Open strDBTestconn
If err.number <> 0 Then
txtEmailbody = txtEmailbody & rsDBList("hostname") & " | Named Pipes | " & err.number & " | " & err.source & vbnewline & err.description & vbnewline
err.clear
intCounter = intCounter + 1
Else
strDBTestQuery = "SELECT * FROM master..sysprocesses where spid = @@spid"
rsDBTest.open strDBTestQuery, cnDBTest,0,2
If err.number <> 0 Then
txtEmailbody = txtEmailbody &  rsDBList("hostname") & " | Named Pipes | " & err.number & " | " & err.source & vbnewline & err.description & vbnewline
err.clear
intCounter = intCounter + 1
Else
If rsDBTest("net_library") <> "Named Pipes " and rsDBTest("net_library") <> "SSNMPN70.DLL" Then
txtEmailbody = txtEmailbody &  rsDBList("hostname") & " | Named Pipes |" & rsDBTest("net_library") & " | Protocols don't match " & vbnewline
intCounter = intCounter + 1
End If
End If
End If
End If

If rsDBTest.State = 1 then
        rsDBTest.Close
End If
If cnDBTest.State = 1 then
        cnDBTest.Close
End If 

' Test TCP\IP Connectivity using instance name
If rsDBList("TCPDisabled") = 0 then
strDBtestconn = "Provider='sqloledb';Data Source=" & fullname & ";Integrated Security='SSPI';Initial Catalog='master';Network Library=dbmssocn;Connect Timeout=20"
cnDBTest.Open strDBTestconn
If err.number <> 0 Then
txtEmailbody = txtEmailbody &  rsDBList("hostname") & " | TCP Instance Name | " & err.number & " | " & err.source & vbnewline & err.description & vbnewline
err.clear
intCounter = intCounter + 1
Else
strDBTestQuery = "SELECT * FROM master..sysprocesses where spid = @@spid"
rsDBTest.open strDBTestQuery, cnDBTest,0,2
If err.number <> 0 Then
txtEmailbody = txtEmailbody &  rsDBList("hostname") & " | TCP Instance Name | " & err.number & " | " & err.source & vbnewline & err.description & vbnewline
err.clear
intCounter = intCounter + 1
Else
If rsDBTest("net_library") <> "TCP/IP      " and rsDBTest("net_library") <> "SSMSSO70.DLL" Then
txtEmailbody = txtEmailbody &  rsDBList("hostname") & " | TCP Instance Name | " & rsDBTest("net_library") & " | Protocols don't match " & vbnewline
intCounter = intCounter + 1
End If
End If
End If

If rsDBTest.State = 1 then
        rsDBTest.Close
End If
If cnDBTest.State = 1 then
        cnDBTest.Close
End If 

' Test TCP\IP Connectivity using port
strDBtestconn = "Provider='sqloledb';Data Source=" & rsDBList("hostname") & "," & rsDBList("tcpport") & ";Integrated Security='SSPI';Initial Catalog='master';Network Library=dbmssocn;Connect Timeout=20"
cnDBTest.Open strDBTestconn
If err.number <> 0 Then
txtEmailbody = txtEmailbody &  rsDBList("hostname") & " | TCP Port | " & err.number & " | " & err.source & vbnewline & err.description & vbnewline
err.clear
intCounter = intCounter + 1
Else
strDBTestQuery = "SELECT * FROM master..sysprocesses where spid = @@spid"
rsDBTest.open strDBTestQuery, cnDBTest,0,2
If err.number <> 0 Then
txtEmailbody = txtEmailbody &  rsDBList("hostname") & " | TCP Port | " & err.number & " | " & err.source & vbnewline & err.description & vbnewline
err.clear
intCounter = intCounter + 1
Else
If rsDBTest("net_library") <> "TCP/IP      " and rsDBTest("net_library") <> "SSMSSO70.DLL" Then
txtEmailbody = txtEmailbody &  rsDBList("hostname") & " | TCP Port |" & rsDBTest("net_library") & " | Protocols don't match " & vbnewline
intCounter = intCounter + 1
End If
End If
End If

If rsDBTest.State = 1 then
        rsDBTest.Close
End If
If cnDBTest.State = 1 then
        cnDBTest.Close
End If 
End if

rsDBList.movenext

Wend

txtEmailbody = txtEmailbody & vbnewline & Now()

If rsDBList.State = 1 then
        rsDBList.Close
End If
If cnDBList.State = 1 then
        cnDBList.Close
End If 

If intCounter > 0 Then
Set objEmail = CreateObject("CDO.Message")
objEmail.From = "SQL_Availability_Monitoring"
objEmail.To = "INSERT SMTP EMAIL ADDRESS HERE"
objEmail.Subject = "SQL_Availability_Report"
objemail.textbody = txtEmailbody
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "INSERT NAME OF SMTP SERVER HERE, must not require authentication" 
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send
End if

Set rsDBList = Nothing
Set cnDBList = Nothing
Set rsDBTest = Nothing
Set cnDBTest = Nothing'




--- Table Creation 

CREATE TABLE [dbo].[SQLInventory] (
[Hostname] [varchar] (50)  ,
[Instancename] [varchar] (50) AS NULL ,
[TCPPort] [int] NULL ,
[Category] [varchar] (50) NOT NULL ,
[NPDisabled] [bit] NOT NULL ,
[TCPDisabled] [bit] NULL ,
[DoNotReport] [bit] NOT NULL ,
[Reason] [varchar] (255) NULL 
) ON [PRIMARY]
GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating