How to Configure static ports for SQL Server

11 July,2013 by Jack Vamvas

If a SQL Server instance is configured with a static port , SQL Server only listens on the specified static port. But it is important to delete all entries for dynamic ports otherwise SQL Server will listen on the static and dynamic ports.

 Use Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Managerhttp://msdn.microsoft.com/en-us/library/ms177440.aspx

Follow these details to the letter. Failure to follow the details – can leave a situation where the SQL Server is listening to the static and dynamic ports.

Notes on configuring a static port for SQL Server

1)     If you want to use just a static port , clean up the entry for the dynamic port . If the dynamic port entry is not cleared , the setting  continues to be used.

You can easily verify  by use the sys.tcp_endpoints view.

 

SELECT       *
FROM            sys.tcp_endpoints

 You can also verify in the SQL Server error log , where an entry similar to this one appears:

 

Server is listening on [ 'any'  45426].

 

2)     Need to be careful to assign port not used by another program . Use netstat to view all ports currently open

netstat -a -o –n  

 Read More on netstat, OSPF and sql tuning

netstat monitoring and tuning performance - SQL Server DBA

Routing OSPF and Network Team - SQL Server DBA

SQL Performance tuning - Drilling into the problem - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to Configure static ports for SQL Server


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer