3

I used SQL Server configuration manager to enable TCP/IP on port 1433. I didn't have any problem but when I tried to enable another SQL service (different one) to listen on the same port I couldn't run it. When I stopped the first service there wasn't any problem. I also can run them together when only one of them has TCP enabled.

My question is why I can't run the both services together? I guess it has something to do with port but I thought that they just listen on this port so there shouldn't be problem.

1
  • In short, TCP can only open one port per IP for listening. Has nothing to do with services or applications. TCP can't do it.
    – usr
    Aug 19, 2012 at 15:24

2 Answers 2

3

With TCP, you can only have one process listening on each IP/port pair.

This applies to SQL Server. The first instance you start binds itself to listen on your_ip:1433, then when you start the second, the OS will not allocate the socket to the the new instance of SQL Server, because it has already allocated that listener socket to your first SQL Server instance. If you look in your error logs, you will see a "socket already in use" error of some sort when you start up the second instance.

If you have multiple physical or logical NICs you should be able to bind the SQL Server instances to the same port on different IP addresses.

3
  • "The first instance you start binds itself to listen on your_ip:1433". Your explanation is a bit wrong, or maybe I'm just misunderstanding. It has nothing to do with the sequential order of instance startup, but moreso default (TCP 1433) vs. a named instance (UDP 1434 for the port allocation service). Aug 19, 2012 at 20:33
  • Sorry if it was unclear. If both SQL Server instances are configured to run on TCP port 1433 on your_ip, whichever one it started first will have socket tcp://your_ip:1433 allocated to it by the OS. The second one will fail to bind to a port, because its configured port is already in use.
    – dbennett63
    Aug 19, 2012 at 23:12
  • You'd really have to jump through a lot of hoops to even have this configuration mishap, as you can only install one default instance. You'd have to play around with static ports for the other instances. I see what you mean, though. Aug 19, 2012 at 23:13
3

There is another way, without adding a physical card. You can assign multiple IPs to your network card. Assign a new IP to network card, and bind each instance with particular IP.

Say you have two IPs x.x.x.10 and x.x.x.11 and have two SQL server instances and you want to bind both to same port say 1433. Bind one with x.x.x.10,1433 and other with x.x.x.11,1433.

There are more details in the Internet Archive copy of this blog. (NOTE: the original blog seems to have been hijacked and redirects to a malicious link.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.