Configuring a Named Instance to use a static TCP listener port

itsonlyme4
itsonlyme4 used Ask the Experts™
on
I have a WIN 2008 R2 Server hosting 4 Different SQL  Server Instances.   I would like to configure one the Instances to listen on a Statis TCP  port - different from the other Instances.   The reason for this is because I have a UNIX client trying to connect to one of the named Instances and being that it's UNIX, they cannot use the '\' in the SQL Server name.    

I figured is I could configure one of the named Instances to listen on a different port, they could just use the connection string  Servername:1435   instead of what they are trying to use now which is  servername\instancename :1433

can anyone help?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel_PLDB Expert/Architect
Top Expert 2011
Commented:
Use SQL Server configuration manager, configure TCP/IP prtocol of each instance.
How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)

Author

Commented:
Thanks for the link !!!

So,  It looks like I can I change just one of the named instances to use a static port.   what I'm struggling with is how to figure out which of the 12 IP addresses that is displaying in the Configuration Manager under Protocols\TCP\P should I change? I knowwhich is being used for my Default Instance because when I ping my servername (while logged into the server itself) it resolved to this:  
Pinging KOCSQLDEV02.supreme.com [fe80::99b:9351:b7d6:7436%11] with 32 bytes of ...

How to I figure out which IP address to configure as static and assign a port number for my one named instance?
DB Expert/Architect
Top Expert 2011
Commented:
fe80::/10 is a link-local address and is not available outside the box.
In SQL Server Configuration Manager for each instance under SQL Server Network Configuration edit TCP/IP protocol settings. Scroll down, at the bottom under IP ALL for named instances clear 0 in TCP Dynamic Ports and set your own port in TCP Port field.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
so that will set ALL named instances to use a different TCP port?
Daniel_PLDB Expert/Architect
Top Expert 2011
Commented:
No, only for instance for which you configure TCP/IP settings.

Author

Commented:
so for one Instance that I want to change..  for each IP ADDRESS I clear the 0 from the TCP Dynamic ports field and then for IP ALL  I clear the value  59520 from  TCP Dynamic ports and then assign a port number to TCP Port?
Daniel_PLDB Expert/Architect
Top Expert 2011
Commented:
Let's try following:
Edit TCP/IP protocol settings one of your named instances. Go to IPAll setting, clear 0 from TCP Dynamic port and edit TCP port to your custom value. Accept settings and restart SQL Server named instance which settings you've just changed. Then you can confirm whether you're able to connect to your instance by using <ip,port>.

Author

Commented:
Daniel

That is where I am confused.  Under IPALL there are only options for:  

TCP Dynamic Port: 59520
TCP Port:

I've attached a screen shot Screenshot of TCP\IP config screen Screenshot of TCP\IP config screen
Daniel_PLDB Expert/Architect
Top Expert 2011
Commented:
Clear TCP Dynamic Ports, leave no value. Put your desired port into TCP Port.

Author

Commented:
Going to try that..   Do I need to stay away from the port numbers associated with the Dynamic pool when assigning a static port?   In other words,   Can I change IPALL for this one named Instance to use Port 59520 ?
Daniel_PLDB Expert/Architect
Top Expert 2011
Commented:
>Can I change IPALL for this one named Instance to use Port 59520 ?
Yes, you can.

Please ensure that on the Protocol tab of TCP/IP properties you have Listen All set to yes.

Author

Commented:
daniel,

Thank you for your help (and your patience with me) on this!!!

My named instance is now listening on static port 59521

Server is listening on [ ::1 <ipv6> 59521].
Server is listening on [ 127.0.0.1 <ipv4> 59521].
Dedicated admin connection support was established for listening locally on port 59521.
SQL Server is now ready for client connections. This is an informational message; no user action is required.
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
You're welcome ;)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial