Two SQL Server with one TCP port

WePython 1 Months+

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.

-----------------Answer-----------------

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.

-----------------Answer-----------------

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


Previous : Fastest way to copy data from MyISAM to InnoDB
Next : Can spatial index be a part of a multiple columns index?