Technical Article

Port check for SQL Server connectivity

,

Recently, I participated in a project to migrate a set of production servers from company-owned SQL Servers to managed servers at a hosting company.  Due to the logistics of the move, we could not migrate all servers at once, so we had some production servers at the old location, and the rest of the instances at the new hosting firm. 

Once the new servers were running and configured, our most common issue in this exercise was firewall issues.  Since opening a port using a hosting company requires opening a ticket and waiting sometimes days before the request is completed, we wanted to make sure that all required ports were opened before the migration date.

Typically, you will want the default SQL Server port 1433 opened.  But, as I discovered in this migration, we also had jobs that needed port 135 for distributed transactions (DTC), and port 445 (Microsoft Directory Services) for writing SSRS reports.

For these reasons, I created this .SQL script to check connectivity between SQL Server instances on a specified port.

Since telnet has a user interface, I looked for a utility that could be run from a batch file or xp_cmdshell, and found Microsoft's PortQry Command Line Port Scanner Version 2.0.  More information is here:  http://www.microsoft.com/en-us/download/details.aspx?id=17148

To use the script, first and copy the PortQry executable to each SQL Server you will be testing.  Next, insert your SQL Server instance names as needed into @tblSQLServers, assign the port number you wish to check to @strPort.  Make sure to change SQL Server Management Studio to Send Results to Text (Ctrl-T), run the script, then copy the results to a new query window, and run.

If “NOT LISTENING,” or “FILTERED” appear in the result set, have your network administrator open the needed ports.

This script requires xp_cmdshell to be enabled while it is running.

Also, this generates a script that requires SQLCMD Mode, which I find very useful for connecting to multiple instances in a single script.  See this page for more details:  http://msdn.microsoft.com/en-us/library/ms174187.aspx

Feel free to contact me with questions or comments.

-- CheckBiDirectionalPorts.sql
-- 
-- Michael Lascuola
-- MichaelLascuola@gmail.com
-- August 9, 2012  8:15am
-- 
-- Script to check connectivity between SQL Server instances 
-- on a specified port.
-- Uses Microsoft's PortQry Command Line Port Scanner Version 2.0:
--   http://www.microsoft.com/en-us/download/details.aspx?id=17148
--
-- Instructions:
-- Download and copy PortQry.exe to each SQL Server
-- Assign the port number you wish to check to @strPort
-- Insert SQL Server instance names as needed into @tblSQLServers 
-- Change SQL Server Management Studio to Send Results to Text (Ctrl-T)
-- Copy the results to a new query window, and run.
-- If "NOT LISTENING" appears in the result set, open the needed ports.
--
-- This script requires xp_cmdshell to be enabled while it is running:
-- EXEC sp_configure 'show advanced options', 1
-- GO
-- RECONFIGURE
-- GO
-- EXEC sp_configure 'xp_cmdshell', 1
-- RECONFIGURE
-- GO
--


DECLARE @tblSQLServers       TABLE (ServerName sysname);
DECLARE @tblCartesianProduct TABLE (ServerName1 sysname, ServerName2 sysname);
SET NOCOUNT ON;
DECLARE @strPort CHAR(5)   -- Port number to check
       ,@strCRLF CHAR(2)   -- Constant for script formatting
       
SET @strPort = '135'
SET @strCRLF = CHAR(13) + CHAR(10);
INSERT INTO @tblSQLServers VALUES ('SQLInstance1');
INSERT INTO @tblSQLServers VALUES ('SQLInstance2');
INSERT INTO @tblSQLServers VALUES ('SQLInstance3');
INSERT INTO @tblSQLServers VALUES ('SQLInstanceX');

INSERT INTO @tblCartesianProduct
SELECT x.ServerName, y.ServerName
FROM @tblSQLServers as x CROSS JOIN @tblSQLServers as y ;

SELECT ':connect ' + ServerName1 + @strCRLF + 'exec xp_cmdshell ''portqry -n ' + 
  ServerName2 + ' -p tcp -e ' + @strPort + ''';' + @strCRLF + 'GO' + @strCRLF + @strCRLF 
FROM @tblCartesianProduct 
WHERE ServerName1 != ServerName2;  -- Don't need to check self connection
GO

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.5 (4)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.5 (4)

You rated this post out of 5. Change rating

Related content

External Article

SQL Server SQLCMD Basics

Sqlcmd makes many SQL Server tasks, such as automating test runs and maintenance tasks, easier and quicker. The sqlcmd command-line utility is valuable to any database developer or DBA as the prime means of executing batches of SQL Statements to SQL servers, and saving results to file. Rob Sheldon gives you the basic facts about this great utility.

2013-11-08

6,993 reads