none
Running two SQL Server instances side by side

    Question

  • If I have two or more SQL server instances running on the same box do I need to configure different ports for each instance?

    Thanks

    Friday, April 28, 2017 7:19 PM

Answers

  • As they say, it depends.

    The normal way is to enable the SQL Server Browser service. This service listens on UDP port 1434. Clients can then use MACHINE\INSTANCE to connect. The client API will talk with the Broswer service that will return the port number to connect to.

    At some sites it's popular to disable the Browser service, and in this case, clients must connect by explicit port number, or they need to set up aliases. In either case, dynamic ports is not fantastic as the port number could change. Then again, SQL Server will only switch to a new port, if the one last time is in use by another process.

    • Marked as answer by markgoldin Saturday, April 29, 2017 7:05 PM
    Saturday, April 29, 2017 12:43 PM

All replies

  • They will all listen on port 1433 fine.
    Friday, April 28, 2017 8:22 PM
  • But after installing a second instance I cannot connect to my first one using SSMS.
    Friday, April 28, 2017 8:26 PM
  • on the problem instance can you check the error log and search on the term listening?

    It should return 1433.

    Is your browser service running as well?

    You might need to enable it and then restart your SQL Server services.

    Friday, April 28, 2017 8:28 PM
  • They will all listen on port 1433 fine.

    Absolutlely not!

    You cannot have two processes listnening on the same TCP port! All instances must have their own port. And that port must also not be used by something else on the machine. Furthermore, if you configure something like Service Broker endpoint, that must again have a different port number.

    Friday, April 28, 2017 9:48 PM
  • Yes, you are correct Erland - my mistake - what I noticed was that 2 of my instance are listed as listening on port 1433 in the error logs, and that I can connect to all of them via port 1433, ie .\SQL2012,1433 and .\SQL2014,1433

    What I did not notice was that both will redirect to my default instance.

    Saturday, April 29, 2017 2:05 AM
  • do I need to configure different ports for each instance?

    One IP port can be only use by one application, first starts blocks the IP port and no other app like an other SQL Server can use it ... absolutely impossible. Each SQL Server must use a free IP port.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]



    Saturday, April 29, 2017 5:45 AM
  • I have checked configurations of both instances. The one that was installed first is set to listen to a port 1433. Another is set to use dynamic ports. It's fine with me. My concern is an installation procedure for a customer. Do they need to manually configure ports or anything when install two instances on the same physical box? As I said before, after installing second instance I cold not connect to the first using SSMS. I needed to enable Shared Memory to make it work. Is that what needs to be for every instance running on the same box?

    Thanks

    Saturday, April 29, 2017 8:50 AM
  • No, need to configure different port for each instance. Just open Port:TCP, IP:1433 & 4022 for Sql Replication.

    Port 1433 – SQL Server listens for incoming connections on a particular port. The default port for SQL Server is 1433. It applies to routine connections to the default installation of the Database Engine, or a named instance that is the only instance running on the computer.

    Port 4022 – This is SQL Service Broker, though there is no default port for SQL Server Service Broker, but this is the port that we allow inbound on our firewall.[/us_message]

    Saturday, April 29, 2017 9:31 AM
  • Yes, you are correct Erland - my mistake - what I noticed was that 2 of my instance are listed as listening on port 1433 in the error logs, and that I can connect to all of them via port 1433, ie .\SQL2012,1433 and .\SQL2014,1433

    What I did not notice was that both will redirect to my default instance.

    Yes, when you specify a port number, the instance name is ignored.

    Saturday, April 29, 2017 12:39 PM
  • As they say, it depends.

    The normal way is to enable the SQL Server Browser service. This service listens on UDP port 1434. Clients can then use MACHINE\INSTANCE to connect. The client API will talk with the Broswer service that will return the port number to connect to.

    At some sites it's popular to disable the Browser service, and in this case, clients must connect by explicit port number, or they need to set up aliases. In either case, dynamic ports is not fantastic as the port number could change. Then again, SQL Server will only switch to a new port, if the one last time is in use by another process.

    • Marked as answer by markgoldin Saturday, April 29, 2017 7:05 PM
    Saturday, April 29, 2017 12:43 PM
  • My SQL browser service was on but I still needed to enable Shared Memory to connect to an instance.
    Saturday, April 29, 2017 7:07 PM