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

Ryan Adams

Ryan Adams has worked for Verizon for 15 years. His primary focus is the SQL Server Engine, high availability, and disaster recovery. Previously he was a Senior Active Directory Architect and designed the company's worldwide Active Directory infrastructure. He serves on the Board of Directors for the North Texas SQL Server User Group and is President of the PASS Performance Virtual Chapter. He also serves as a Regional Mentor for PASS and holds the following certifications: MCP MCSA MCSE MCDBA MCTS MCITP.

SQL Named Instance – Static Port Overrides with SQL Browser

I ran across an issue that I haven’t seen since SQL 2000 and had almost forgotten about.  Let’s do a quick review of ports in SQL Server.  A default instance of SQL Server uses port 1433.  That’s just the default and you can change it to whatever you want.  Bear in mind that if you change it you’ll have to define that port in your connection strings or enable the SQL Browser Service.

Speaking of the SQL Browser Service, you’ll need that for a named instance of SQL Server for the exact same reason.  A named instance of SQL Server uses a dynamic port that can change with every restart of the SQL Server service.  Take a look at the following screen shot of a named instance.

Ports in Configuration Manager

We are interested in the very last section which is the “IPAll” section.  You can see that the “TCP Dynamic Ports” is set for 49402.  That is what SQL Server has dynamically chosen for its port number.  For a normal named instance we’ll see a port there, but the “TCP Port” setting would be blank.  A default instance would be the other way around with a “TCP Port” defined but the “TCP Dynamic ports” being blank.

So what happens when you have a named instance using dynamic ports and you define a static port?  Well the title of post already gave it away, but whatever you put into the “TCP Port” setting will override anything dynamic.  I personally think it would make more sense to only be able to define one of the settings at a time.  Maybe MS will change that one day where one of them is grayed out until the value is removed from the other setting.

Comments

Leave a comment on the original post [www.ryanjadams.com, opens in a new window]

Loading comments...