In many environments, DBAs and SQL Server administrators need to keep a close eye on server connectivity. That's the case where I work. We try to minimize LAN downtime as much as possible so that our extraction, transformation, and loading (ETL) processes run undisturbed.
Although regularly monitoring server connectivity is important, it can be time-consuming. For this reason, I developed a flexible and easy-to-implement SQL Server Integration Services (SSIS) package that uses the Ping command to check the connectivity of each host in my company's IP network. The SSIS package not only finds servers that are unresponsive but also flags servers that aren't responding to pings as often as we'd like, which can help identify potential problems.
The SSIS package executes three stored procedures to ping each host and retrieve the output. I'll show you how to create this package. But first you need to create the stored procedures and the database and tables they use.
Creating the Database, Tables, and Stored Procedures
The first step is to create the database, two tables, and three stored procedures. The database, which is named PingNotification, will hold two tables: ServerList and PingOutput.
The ServerList table will contain a list of the hosts that the SSIS package needs to ping during each run. It has the following columns:
- ServerName. This column contains the names or IP addresses of the hosts.
- ServerFileName. If desired, you can use this column to store the pathname of a text file in which you can store the ping results. I won't be using this field for this example.
- NumberOfPings. This column contains the number of echoes (pings) requested for each host. The default value is 1, but you can specify a different number if desired.
- ThresholdAvailable. This column contains the threshold value for a host to be considered active. The default value is 100, but you can change that value.
The PingOutput table will store the output from each ping. It has the following columns:
- ServerName. This column contains the names or IP addresses of the hosts.
- PingDate. This column stores the date and time of the echo requests.
- PingAvailability. For each host, this column provides a percentage indicating how many echo requests received replies compared with how many were sent out. When the host isn't found, the value is 0.
- PingStatus. This column can contain one of three string values. "Normal Response" indicates that the host met or exceeded the value specified in the ThresholdAvailable column of the ServerList table. "Weak or No Response" indicates that the host hasn't replied at all or the number of echoes returned is less than the value specified in the ThresholdAvailable column. "Host Not Found" indicates that the remote server doesn't exist.
I wrote the script PingNotification_CREATE_DATABASE_PROCEDURE.sql to demonstrate how to create the database and tables. This script also populates the tables with sample data in case you want to follow along with the example I'm presenting.
Besides creating the database and tables, the script creates three T-SQL stored procedures:
- PingListOfServers. This stored procedure executes the Ping command against the servers listed in the ServerName column of the ServerList table. The Ping command's -n switch is used with the value in the NumberOfPings column of the ServerList table to indicate the number of echo requests to send. You can modify this stored procedure by adding other Ping switches. For example, you might want to add the -w switch to set a timeout period.
- ResultOfPingFailures. This stored procedure determines how many servers didn't exist ("Host Not Found") and how many servers had a lower number of successful echoes than requested ("Weak or No Response"), based on the data in the PingStatus column of the PingOutput table.
- ResultOfPingForEmail. This stored procedure constructs an email message about any servers that didn't exist, had a weak response, or had no response. Besides the server names, the email message includes the date and time of each ping.
Note that PingListOfServers uses the xp_cmdshell system stored procedure to execute the Ping commands. So, prior to executing PingListOfServers, xp_cmdshell must be enabled on local host. You can enable it by running the command
EXEC sp_configure 'xp_cmdshell', 1; GO RECONFIGURE; GO
When xp_cmdshell is enabled, the PingListOfServers stored procedure uses the Ping command to send echo requests to the servers, as the code in Listing 1 shows.
SET @sql = 'EXECUTE XP_CMDSHELL ''ping ' + LTRIM(RTRIM(@ServerName)) + ' -n ' + CAST(@NofPings AS VARCHAR(10))+ '''' CREATE TABLE #pingtemp ( ID INT IDENTITY(1,1) ,line VARCHAR(2000) ) INSERT INTO #pingtemp(line) EXECUTE SP_EXECUTESQL @sql
PingListOfServers stores the results in a temporary table, which it creates with an identity column for easier tracking of rows. Figure 1 shows sample Ping command output with the row (i.e., line) numbers added.
In the output of each Ping command, the needed result always starts with line 4. When line 4 starts with Reply from, the echo request is successful. When line 4 starts with Request timed out, the echo request failed.
Because the output is set up this way, PingListOfServers uses the code in Listing 2 to capture the information it needs to determine the @ActualAvailability value. This value is important because it's used to determine the values for the PingAvailability and PingStatus columns in the PingOutput table.
@ActualAvailability = SUM(x.Val_)/@NofPings*100 FROM ( SELECT id ,line ,CASE WHEN line LIKE 'Reply%' THEN 1 ELSE 0 END AS Val_ FROM #pingTemp WHERE -- BEGIN CALLOUT A id > 3 AND id < @NofPings+3 -- END CALLOUT A -- BEGIN CALLOUT B AND EXISTS (SELECT * FROM #pingtemp WHERE id = 1 AND (line IS NULL OR LEN(LINE) = 0)) -- END CALLOUT B ) AS X
The @ActualAvailability value is a percentage indicating how many pings received replies compared with how many were sent out. The number of pings sent (@NofPings) is already known. It's the value in the NumberOfPings column in the ServerList table. So, you just need to know how many replies were received. You can find out by searching the output in the temporary table for the text Reply. If a line (i.e., row) has the word Reply, it's counted as 1. Otherwise, it's 0. However, before you do this, you need to filter the output.
The code at callout A selects only those lines that need to be checked to see whether they contain the word Reply. The statement id > 3 is used to ignore the first three lines because they have nothing to do with the replies. The statement AND id < @NofPings+3 is used to ignore the lines after the replies. In this statement, you need to add 3 to the number in @NofPings because the first three lines are ignored. So, for example, if the number of pings requested is 3, the code at callout A will select lines 4 through 6.
The code at callout B filters out any output indicating that a host didn't exist. When a host doesn't exist, the Ping command returns the message Ping request could not find host in the first line. When a hosts exists, the first line can be null or an empty string, so the code
WHERE id = 1 AND (line IS NULL OR LEN(LINE) = 0))
If you'd like to see how the rest of PingListOfServers (or the other two stored procedures) works, check out the comments in PingNotification_CREATE_DATABASE_PROCEDURE.sql. You can download this script (and the SSIS package) by clicking the Download button. I've tested the script on several different Windows environments, including Windows Server 2008 R2, Windows Server 2008, Windows Server 2003 R2, Windows 7, Windows XP, and Windows 2000. For this article, I used Windows 7 and SQL Server 2008 R2. Both are set to English as the native language. If your systems are set to a different language, slight changes need to be made to the PingListOfServers stored procedure. For more information, see the ChangingLanguageSettings.txt file that accompanies the script.
Creating the SSIS Package
As Figure 2 shows, the SSIS package contains four tasks:
Run Stored Procedure PingListOfServers. This Execute SQL task executes PingListOfServers, which populates the PingOutput table with ping results for each host.
Check for Not Pinging Servers. This Execute SQL task executes the ResultOfPingFailures stored procedure, which determines how many servers didn't exist and how many servers had a lower number of successful echoes than requested. When creating this task, you need to create a variable named User::Failed_Yes and initialize it to zero, as shown in Figure 3. The stored procedure's result is passed to this variable, which is checked by the precedence constraint that's connecting second and third tasks. This constraint contains the simple expression @Failed_Yes >= 1. When the User::Failed_Yes variable equals 0, the SSIS package will stop executing (indicating success). When the variable is greater than 0, the flow continues to the next task.
Run Stored Procedure ResultOfPingForEmail. This Execute SQL task executes the ResultOfPingForEmail stored procedure, which constructs the email message about the ping results. When creating this task, you need to create a variable named User::Failed_Message, as shown in Figure 4. This variable will contain the constructed message.
Send Mail on PingFailures. This Send Mail task sends the email message. As Figure 5 shows, you need to specify Variable for the MessageSourceType property and User::Failed_Message for the MessageSource property. You also need to specify the SMTP connection, sender, recipients, and Subject line.
Using the SSIS Package
As you've seen, you can use the Ping command in a standalone SSIS package to notify you when there's a problem with connections between computers. For example, I've scheduled a job to run this package every minute to check the LAN connection between my local host and a remote server. If I get a notification email, I can act immediately. In case it happens during non-working hours, we have an SMS notification system that sends an SMS notification to our DBA for immediate action. This simple SSIS package can also be used to check connections in failover scenarios, backup processes, ETL processes, and more.