SQL Server listening on multiple ports

  • 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.

  • 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.

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

  • 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?

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

  • 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.

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

  • 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.

  • 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" 😉

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

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