| Downloads |
|---|
| 143994.zip |
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.
Related: Troubleshooting Common SQL Server Performance Problems
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
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. 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.
Figure 1: Sample Ping Command Output with 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.
