Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
SQL Server Transparent Data Encryption Alternative - Free Webinar
 

Static Port Assignments in SQL Server 2005


By:   |   Last Updated: 2008-01-15   |   Comments (1)   |   Related Tips: More > SQL Server Configurations

Problem
We have noticed some connection issues on a variety of our web based applications when the corresponding SQL Server is rebooted.  We have noticed the issue with SQL Server 2005 Express edition as well as the SQL Server 2005 Standard edition.  It seems like after the reboot we have to change our application connection strings and firewall settings in some circumstances in order to correct the situation.  We know in some of the circumstances the applications are in a transient state, but others have been deployed and unchanged for a while.  When we have to reboot servers, the whole team is on guard to test the applications and ready to troubleshoot the issues.  We having found that in some circumstances SQL Server 2005 is listening on different ports.  Should we write this issue off to gremlins or is there a real solution for our problem?

Solution
Based on the issue described, it sounds like the dynamic ports configuration could be the culprit for SQL Server 2005 Express edition installations as well as the named instances of SQL Server 2005 Standard edition.  If the default instances are also having a problem, then more research is needed.  If that is not the case, then let's walk through configuring a SQL Server 2005 named instance to a static port setting to address the issue once and for all!

Start the SQL Server 2005 Configuration Manager

Navigate to Start | All Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Configuration Manager


 

TCP/IP Properties

Navigate to root | Expand SQL Server 2005 Network Configuration | Click on the correct 'Protocols for instance name' entry | In the right pane, double click on the TCP\IP entry | Once the TCP/IP Properties window loads click on the IP Addresses tab


 

In this example, the 2 IP Addresses are bound to the NIC card.  First (IP1) is a public address.  Second (IP2) is the loopback address.  Third (IPAll) is an option for all IP Addresses.

Configuring the SQL Server Port

Under these circumstances, let's set the public IP address to a fixed port (1501), retain the loop back configuration (0) and also specify a all port configuration (1503).


 

*** Note *** - When a static port is configured for any of the NICs, it is necessary to stop and restart the SQL Server instance in order for the change to be implemented.

SQL Server Error Log - Original configuration


 

In this example, port 2082 is the static port the SQL Server instance is listening on for non loop back scenarios.

SQL Server Error Log - Final configuration


 

In this example, port 1503 is the static port the SQL Server instance is listening on for non loop back scenarios.

Changes to Connection Strings

With this sort of change, be sure to specify the port in your connection strings for your application and in your Management Studio connection to the SQL Server.  To reference the SQL Server instance, the format of the connection string should be either:

  • <ServerName>[\InstanceName],<Port>
  • <IP Address>[\InstanceName],<Port>

Next Steps

  • Based on my experience with computers in general and SQL Server specifically, nothing happens due to gremlins.  Everything should have a logic answer.  At times you might not have all of the information needed or understand the data points you have found, but at some point a logic answer should surface.
  • Most times when an issue occurs the last change to the system is responsible.  So either keep a log of your changes or rewind your memory to find the issue.  Keep in mind that some changes do not take affect until SQL Server or Windows is restarted, so a change a day, week or month ago might be to blame for the post reboot issue you are experiencing now.
  • Although the debate for dynamic versus static ports could be viewed from a few different perspectives (positive, disaster waiting to happen, indifferent, etc.), this feature should be understood in case you need to leverage it or troubleshoot it.


Last Updated: 2008-01-15


next webcast button


next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, July 04, 2012 - 12:24:29 AM - Rajasekhar Back To Top

Hi ,It is so good ,Thank you so much But i need some info on which situation go for dynamic and static port ?is it possible to mix up both?


Learn more about SQL Server tools