Recommended monitoring tool for SQL Server 2

[jlaw10]

I need a monitoring tool (preferably freeware) for SQL Server to monitor our Prod SQL DB servers. We currently have about 100 server and we normally have to manually monitor each box. We are looking to monitor the following:

- Failed SQL Server jobs
- Disk space
- SQL Agent up/down
- replication

I know that tools such as Whatsup Gold and Oracle Grid Control have certain capibilities but is there anything else out there in the industry? Thanks in advance!

 

[ptheriault]

I don't think your going to find a lot for free. I know Nagios is free. I use SQL diagnostic Manager by Idera but that isn't cheap.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[oracleSQLdba]

I agree with Paul, Free and monitoring generally don't come together. Using SQL Agent Mail can go a long way for a very reasonable price. If you can handle some vb scripting, it can handle everything....

DatabaseAdmins.com
Remote DBA Services

 

[mutley1]

Jlaw,

Have a look at thread

http://www.tek-tips.com/viewthread.cfm?qid=1316443

I posted a script that creates a couple of stored procs to tell you how much of a disk your database is using, it's free space and the amount of free disk space left on that particular drive. Includes growth options so you can calculate how close to thresholds you are if any autogrow takes place.

Cheers,

M.

 

[mutley1]

Jlaw,

Here's another one. this needs to be set up as a DTS package - It will ping your SQL Servers (you create a text file with a list of all the servers you want to check) and it load the results to a table, scan the table and email set recipients if there is not a "Response Received" for any servers in the aforementioned list.

We have 40 servers and 1 dedicated monitoring server, so this is set on our monitoring server. I'll leave the architechture up to you, but this is how I have done it. If you use a different database name, be sure to change the references. I this example the DB is called ServiceStatus. I may get round to posting another one later when I have the time that checks all the SQL services as well, which is also housed in this database so it might be worth sticking with this name.

Here we go:

1. Create a blank database ServiceStatus then in there create this table:

CREATE TABLE [PING_SQL_Servers] (
	[datestamp] [datetime] NULL CONSTRAINT
[DF_PING_SQL_Servers_datestamp] DEFAULT (getdate()),
	[servername] [varchar] (50) COLLATE NULL ,
	[serverstatus] [varchar] (50) COLLATE NULL
) ON [PRIMARY]
GO

2. In the same DB, create this stored proc.

CREATE PROCEDURE usp_SQL_not_pingable AS declare @msg varchar(2048); set @msg = ''
select @msg = @msg +  convert(varchar, datestamp, 120) + ' [' + servername + '] is not responding to a PING.
'
from PING_SQL_Servers
where serverstatus <> 'RESPONSE RECEIVED'

print @msg
GO

3. Create a directory called D:\SQLServicesAnalysis (again, call it what you like, but change references in following steps!!)

4. Create a batch file called pingsql.bat with the following:

@echo off
echo Server,result > D:\SQLServicesAnalysis\sqlping.txt
echo Server,result > D:\SQLServicesAnalysis\sqlping.txt
for /f "tokens=1,2,3,4 delims=," %%a in
(D:\SQLServicesAnalysis\LISTSqlservers.txt) do (

PING -n 2 %%a |FIND "TTL" > NUL

            IF NOT ERRORLEVEL 1 (

                        Echo %%a ,RESPONSE RECEIVED >> D:\SQLServicesAnalysis\sqlping.txt

            ) ELSE (

                        Echo %%a ,FAILURE >> D:\SQLServicesAnalysis\sqlping.txt

            )

)

5. Create a text file called LISTSqlservers.txt in the same directory and populate it with a list of all your SQL servers. No white space at the end!!!

6. Create the DTS package to run this lot. Firstly, creat the connection (local, with DB as ServiceStatus), then the steps would be as follows:

a. Truncate table PING_SQL_Servers (clear out last run)
b. EXEC master..xp_cmdshell 'D:\SQLServicesAnalysis\pingsql.bat'
c. Import D:\SQLServicesAnalysis\sqlping.txt into ServiceStatus table
d. on completion of step c. have the next SQL task with the following code - it basically scans for anything that is not "Response Received" and if it finds anything, then it will call the stored proc created earlier and email the details to defined users (amend the email address).

DECLARE @RowCount int
SET @RowCount =0
SELECT @RowCount =count(*)
FROM PING_SQL_Servers
WHERE serverstatus <> 'RESPONSE RECEIVED'

if @RowCount  > 0
BEGIN
    EXEC master..xp_sendmail @recipients = 'YOUREMAIL@YOURCOMPANY.com', 
       @message = 'One or more SQL Servers are not PINGABLE.

',
       @subject = '*** Warning -  SQL Server Ping Failure ***',
       @query = 'Servicestatus..usp_sql_not_pingable',
       @attach_results = 'false'
END

Don't know how your DTS skills are, so let me know if anything needs clarifying.

I will try to get you the Service status one later - it's a bit of VB you can put in an active X task that uses WMI to check for SQL Server Service and SQL Agent Service statuses.

HTH.

M

 

[jlaw10]

Thanks all for the assistance. Much obliged. I am also looking into setting up the free SQL Server plug-in for Oracle Grid control. I setup Grid environment here about 1 year ago so this would allow us to centralize everything plus have access to the GUI.

http://www.oracle.com/technology/products/oem/pdf/ds_sql.pdf

 

[mutley1]

No problem. If you're going to use the Grid, do you still want me to post the Services checker?

Cheers,

M.

 

[jlaw10]

No thanks but I really appreciate the assistance!