Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Port check for SQL Server connectivity

By Michael Lascuola,

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.

Total article views: 1078 | Views in the last 30 days: 4
 
Related Articles
FORUM

Migration

MIgration

BLOG

Taking Script of Sql Server Jobs for Migration

when migration sql server database to a new server, i use the following powershell script to take al...

FORUM

Server Migration - Replication setup

Server Migration - Replication setup

BLOG

Migrating SQL Server logins

Once upon a migration I would use sp_help_revlogin to migrate logins and remap the SQL server logins...

FORUM
Tags
dtc    
port    
portqry    
sqlcmd    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones