Monitoring SQL Server availability

[wbodger]

So, I have scripts that run to check if SQL Servr 2k is accepting incoming transactions, but if SQL Server itself becomes unavailable, my scripts are useless. So, what do others out there use to monitor their SQL Server 2k availability? I am running on a Windows 2003 Server.

Thanks!

WB

 

[JayKusch]

NetIQ if you have the bucks ... if not SQLPing is a great little app ...

http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx

Thanks

J. Kusch

 

[wbodger]

Cool, thanks, I will look into those.

wb

 

[mutley1]

You can use a WMI_32 call from a DTS package. See code below:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()

On Error Resume Next

'Set the report output directory (end with a backslash)
dir = "D:\SqlServicesAnalysis\"
'Set the text report file name
filename = "SQLServicesStatus.txt"


'open the file system object
Set oFSO = CreateObject("Scripting.FileSystemObject")
set WSHShell = wscript.createObject("wscript.shell")
'open the data file
Set oTextStream = oFSO.OpenTextFile("D:\SqlServicesAnalysis\LISTSqlServers.txt")
'make an array from the data file
RemotePC = Split(oTextStream.ReadAll, vbNewLine)
'close the data file
oTextStream.Close

For Each strComputer In RemotePC

Set objWMIService = GetObject _
        ("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colRunningServices = objWMIService.ExecQuery _
    ("Select * from Win32_Service")
    For Each objService in colRunningServices
        If InStr(UCase(objService.DisplayName), "SQL") > 0 Then
        Report = Report & vbCrLf & "Computer ," & strComputer & ", reports service ," & objService.DisplayName  & ", is ," & objService.State 
        End If
    Next
set colRunningServices = Nothing
set objWMIService = Nothing
Next

If Not oFSO.FolderExists(dir) Then
    oFSO.CreateFolder(dir)
End If
Set ts = oFSO.CreateTextFile (dir & filename, True)
ts.write report



	Main = DTSTaskExecResult_Success
End Function

Just change the 3 path sections near the top, where D:\SqlServicesAnalysis\LISTSqlServers.txt would be a txt file containing a list of the servers you want it to check (not comma delimited, just enter a servername, hit return, next server on next line etc.

'Set the report output directory (end with a backslash)
dir = "D:\SqlServicesAnalysis\"
'Set the text report file name
filename = "SQLServicesStatus.txt"

Will be the output directory and file name.

HTH.

 

[wbodger]

So, run this from a different SQL Server?

 

[mutley1]

Depends - we used to have 1 server dedicated to monititoring, but can run from wherever you like. I had 40 servers to monitor so they were all in the list (txt file) and it ran from the main monitoring server, but you can run it from anywhere. What I used to do was import the results to a table and then check that. If you try the first part whereby you get an output file, then let me know and can pass on other items.

Rgds,

Mutley

 

[wbodger]

So, that looks like it just checks to see if the service is running, is that correct? Or does it have some level of interaction to determine functionality? Our main SQL Server was running, but it was inaccessible, how would this handle a situation like that?

wb

 

[mutley1]

That would just check the services to see if they were running. If you have a monitoring server (or seperate server) it might be worth just doing a select * from master..sysobjects and ensuring you get a result. As the other experts have said, you can buy tools if the budget allows, but I was trying to give you a cheap option to see if things are up and running.

Rgds,

M.

 

[wbodger]

BTW, thanks for the help!

wb

 

[PRPhx]

We have a pearl (*I THINK anyways*) program that continuously pings my sql server. After 3 failed attempts, the program sends out e-mails, pages and text messages to at least 3 people. It seems to work ok. My server is really old, and on ocassion, it reboots itself for no apparent reason. Fortunetly, this server is going away in the very near future.