SQL Server listening on multiple ports

Question

Post reply

SQL Server listening on multiple ports

Lynn Pettis

SSC Guru

Points: 442467
More actions
October 10, 2016 at 2:17 pm

#331202

Here is a question that has been posited to me. Can an instance of SQL Server listen for traffic on two separate ports, 1433 and some yet to be determined port.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

lynn,

yes it can, in configuration manager under tcp/ip network protocols, list both separated by a comma.

right, back to the cooking and golf.

---------------------------------------------------------------------

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

george sibbald (10/10/2016)
lynn,
yes it can, in configuration manager under tcp/ip network protocols, list both separated by a comma.
right, back to the cooking and golf.

Okay. Never did this before so I wasn't sure that it could be done. Next step is to find out how we can test it. One last question before you hit the links or get in the kitchen. Can this be configured to be done by an automated process, like an installer?

happycat59 One Orange Chip Points: 29389 More actions · Answer 3

To test it, try connecting to "servername, portnumber".

george sibbald SSC Guru Points: 104210 More actions · Answer 4

Lynn Pettis (10/10/2016)
george sibbald (10/10/2016)
lynn,
yes it can, in configuration manager under tcp/ip network protocols, list both separated by a comma.
right, back to the cooking and golf.
Okay. Never did this before so I wasn't sure that it could be done. Next step is to find out how we can test it. One last question before you hit the links or get in the kitchen. Can this be configured to be done by an automated process, like an installer?

As happycat said, you can test by specifying the port on connection, say through ssms. The ports SQL is listening on are also listed at the start of the errorlog. I should also mention SQL will require a restart and the ports will need to be open in any firewall.

The native installer cannot be configured to do this as part of the install, whether there are tools out there that can (edvassies sql server finebuild?) I don't know.

---------------------------------------------------------------------

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

happycat59 (10/10/2016)
To test it, try connecting to "servername, portnumber".

Really wasn't thinking this, more getting the resources to actually do the testing.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

You can use separate port or separate port and ip address. You would usually do this when creating a soft NUMA configuration for your sql server instance.

The port assignments are basically a set of registry keys, define the registry import files and push them out. Reg key looks like

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉