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:
Code:
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.
Code:
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:
Code:
@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).
Code:
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