November 4, 2013 at 5:59 am
Hi
I'm a little confused, one of our Production Instances is configured to listen on a Dynamic port, which I thought meant that when the Instance is restarted, the port number will change. This doesn't seem to be the case though, as we've had to restart the Instance a couple of times but it's listening on the same port number?
Will the port number only change if the there's something else using that same dynamic port?
November 4, 2013 at 6:59 am
The name 'dynamic ports' is a bit confusing.
If selected on installation, SQL Setup will pick an available port, and assign that to SQL Server, but that's about as dynamic as it gets. SQL Server won't request a new port on startup.
You can set the port manully using SQL Server Configuration Manager, under 'SQLServer Network Configuration'.. 'Protocols for <instanceName'..'TCP/IP'..'Properties'.. 'IP Addresses' tab, 'IPAll' field.
Blank out 'TCP Dynamic Ports', and enter your desired port in 'TCP Port'.
Its sometimes recommended to fix the port of a named instance to a known consistant value (but it depends who you talk to). Having a standard for port numbers across all instances comes in handy when you're remoting to SQL 2005 or below and your network admins have blocked the browser port (UDP 1434) as defence against the SQL Slammer worm. 🙂
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
November 5, 2013 at 4:09 am
Aha. So that explains what I saw when I restarted a Instance with 'Dynamic Port' allocated, the port number did not change as it wasn't being used by any other process.
November 5, 2013 at 4:52 am
Kinda. Once allocated, nothing will change the port number unless it's changes manually.
AFAIK, there's no windows service or function that monitors or allocates TCP ports. Technically, there's nothing stopping 2 services requesting TCP 1433, but only one service can use any one port.
I've seen this before where an AV update service failed to work because Instant Messaging software on the client calimed the port before the AV service started.
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply