SQL Server connection monitor

Question

SQL Server connection monitor

Michelle Cook

SSCrazy

Points: 2119
More actions
February 3, 2006 at 11:28 am

#111374

We currently use a VBscript from a central server that attempts to connect to each of our SQL Server instances every 10 minutes. If the connection fails for any reason the script sends a page to the oncall DBA. The problem is more often then not the script reports false alerts, where the server is up and available, but the connection fails due to timeout or network issues. I am looking into modifying the script to only page if the connection fails three consecutive times. The problem is that I didn't write the script and making the change and testing it is going to take some time. I was wondering if anyone has a script that does something similar to what I'm looking for. Any help or ideas are appreciated.
Thanks,
Michelle
February 6, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Hi,

what you are trying to get? The actual data or just to assure that the server is up an running and SQL Server is up on database server?

I have my VBscript that uses WMI to get services that are running and that sends me emails that the services are running. I schedule this script for just several times to run for the time I need to monitor my server, it sends me emails every time. But you may use parts of the script in your code to figure out if it is SQL Server connection timed out or the service is not running. This is part of the script, you may find out that you have to declare more variables. The script logs rezults to a file and then another procedure emails this file, I don't post text for this procedure here because you say you have email script. In case of errors (AnyErrors variable) the email script will send me error messages about connection errors.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Constants for opening files

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Const OpenFileForReading = 1

Const OpenFileForWriting = 2

Const OpenFileForAppending = 8

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Some handy global variables

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim TabStop

Dim NewLine

NewLine = vbCrLf ' Chr(13)&Chr(10)

Dim strComputer

Dim AnyErrors

' Creating FileSystemObject

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

strComputer = "RemoteComputerName"

Call GetServices (strComputer,AnyErrors )

Call EmailOperator (strComputer, AnyErrors)

Set objFolder = Nothing

Set fso = Nothing

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Sub GetServices (strComputer,AnyErrors )

On Error Resume Next

Dim LogServices

Dim RecordString

Dim Lfile

Dim objComputer

Lfile = "z_" & strComputer & "Services.txt"

Set FileObject = fso.CreateTextFile(Lfile, True)

Set objComputer = CreateObject("Shell.LocalMachine")

FileObject.WriteLine "Scanning Computer name: " & objComputer.MachineName

Set objComputer = Nothing

Set objWMIService = GetObject("winmgmts:" _

& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

If Err.Number = 0 Then

AnyErrors = "No Errors, Computer is UP"

ELSE

AnyErrors = "Connection Errors"

Exit Sub

END IF

' Getting Computer Name

Set colComputerSys = objWMIService.ExecQuery _

("Select * from Win32_ComputerSystem" )

For Each objComp in colComputerSys

ServerName = objComp.Name

FileObject.WriteLine "Scanning " & ServerName & NewLine

NowIs = Now

FileObject.WriteLine "Current Time = " & NowIs & NewLine

' Getting Services

Set colServiceList = objWMIService.ExecQuery _

("Select * from Win32_Service where StartMode = 'Auto'")

For Each objService in colServiceList

' Do this only if the StartMode is Manual

' This will prevent PowerUsers from starting Manual services)

' errReturnCode = objService.Change( , , , , "Disabled")

RecordString = "Name = " & objService.Name & " Display Name = " & objService.DisplayName & " State = " & objService.State

FileObject.WriteLine (RecordString)

End Sub

Regards,Yelena Varsha

Michelle Cook SSCrazy Points: 2119 More actions · Answer 2

Yelena,

Thanks for the response. What we are trying to do is verify that the SQL Server instance is up and that we can connect to it. What we currently have is a script that just connects to the master database for each of our instances. If the connection fails the script sends an e-mail page to the oncall with an unable to connect message. The script is causing false alerts, probably due to timeouts. I have added a connection timeout of 60 seconds now and that has seemed to help, but wanted to change the script to only page after the 3rd failed connection attempt. The script runs every 10 minutes from a central server that we use for monitoring.

Thanks,

Michelle

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Michelle,

You may store the number of attempts in a file and read from file when the job runs. You may store pair of values: server name - number of unsuccessful attempts. Reset the attempts to 0 after the email is sent or if the successul attempt followed.

To read from File into array:

Dim TextStream

Dim File

Dim Icounter

Dim S(25)

Set File = FSO.GetFile(MyFile.txt")

Set TextStream = File.OpenAsTextStream(OpenFileForReading)

Icounter=0

Do While Not TextStream.AtEndOfStream

S(Icounter) = TextStream.ReadLine

Icounter=Icounter+1

Loop

TextStream.Close

Regards,Yelena Varsha

Michelle Cook SSCrazy Points: 2119 More actions · Answer 4

Hi Yelena,

Thanks for the reply and yes, that's exactly what I want to do. Unfortunately, the current script writes to a text file but just writes a 0 for failure or 1 for success, and on the failure sends the page. To modify the script is going to take some time, especially since I didn't write it and my scripting skills are not as good as I'd like. I was hoping that someone already has a script that does something similar that I could use. I guess I will just have to find the time and patience to modify the script.

Thanks,

Michelle

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

Michelle,

You will thank yourself many times after you spend this time writing scripts and figure out how to do it.

There is a quick help for you:

http://www.microsoft.com/technet/scriptcenter/tools/wmimatic.mspx

Scriptomatic Tool By The Scripting Guys

http://www.microsoft.com/technet/scriptcenter/createit.mspx

Script Center Scripting Tools and Utilities

On the scripting tools and utilities page to the right there are links to Learn to script and Find a Script

And one of my favorite sources for writing to and reading from the text files:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/2d4e6d4c-5cac-49de-b0b4-2e96ce56dd5f.asp

FileSystemObject Sample Code

Regards,Yelena Varsha

Michelle Cook SSCrazy Points: 2119 More actions · Answer 6

Yelena,

Thanks so much for the additional information and links. Yes, I know you're right that it would do me good to work on the script and become more proficient in that area. The problem is the time crunch, and that I have many other more pressing things that I am responsible for. I guess a little over time is in my future. Thankfully, I'm on vacation next week, but that is why this has become a pressing issue.

Thanks again,

Michelle