3

I have an SQL server with 3 instances on it. If I connect to it with Management Studio i automatically connect to one of the instances. the same each time.

It looks like there is a default named instance between the instances.... is that true? and will it be the same thing if i connect withour an instance name from another client than Management Studio (fx a SSIS package)?

Thx Nicolaj

NicolajB
  • 275
  • 1
  • 6
  • 16
  • Are you sure that all 3 instances are named instances, and it's not just 2 named instance and a default one? – SWeko Feb 16 '12 at 14:52

1 Answers1

5

The SQL Server browser service is responsible for directing connections into the server to the appropriate instance of SQL Server. When you connect to SQL Server using only the computer name, it assumes that you are connecting to the default instance and directs you to port 1433. When you connect to a named instance, it takes the name of the instance and translates that to the port that that instance is listening on (by default it uses dynamic ports). You could bypass the browser service altogether and just connect directly to the port that you want, the name is just a shortcut to keep you from having to look up the port all the time.

If you take one of your named instances and change the port from dynamic to 1433, you will be able to connect to it with only the server name since 1433 is the default port. So in your case, either you have 2 named instances and one default instance and you're connecting to the default instance every time, or someone has changed the port of one of your named instances to 1433.

Pam Lahoud
  • 1,095
  • 6
  • 9
  • If a named instance is listening on port 1433, can you still connect to it as a named instance, or do you *have* to connect to it as if it were a default instance? – piers7 May 31 '13 at 02:34
  • You should still be able to connect to a named instance listening on 1433 with the name, as long as the browser service is running. – Pam Lahoud Oct 24 '13 at 15:52
  • @PamLahoud: Sorry for necropost: Can SSMS be connected to two different instances simultanelously? – MSIS Jan 25 '20 at 02:28
  • 1
    @MSIS Just saw your message, yes SSMS can be connected to several different instances simultaneously. You can have several different instances listed in the Object Explorer window and you can have several query windows open that are each connected to a different instance, but by default you can only submit a query to a single instance in each query window. If you want to submit the same query to multiple instances simultaneously from a single window, you'll need to configure Multi-server management https://learn.microsoft.com/sql/ssms/agent/create-a-multiserver-environment – Pam Lahoud Jan 28 '20 at 19:37