Connecting to a local SQL instance

Peter Schmitz

Administrator
Staff member
When you want to connect to a SQL instance on your local machine (and let's assume it has been installed as the default instance), there's a few ways you can do so.

  1. The machine-name of the server (i.e. TESTSQL)
  2. localhost
  3. 127.0.0.1 (the IP address that defaults to "localhost")
  4. The actual IP address of the server.
  5. . (a single period - for the lazy ones)
  6. (local) - (the parenthesis are required in this).
  7. A fully qualified domain name (FQDN). I.e. testsql.schmitzit.com

SQL Server connections.png

So, is there a difference between these different options? The answer, actually, is yes, and it can be seen by running the following command from every session, which will show the connection protocol used for a session:

Code:
select net_transport from sys.dm_exec_connections where session_id = @@SPID

The outcome for each of the 7 steps described above is as follows:

NumberConnection-nameProtocol
1.TESTSQLShared memory
2.localhostShared memory
3.127.0.0.1TCP
4.IP AddressTCP
5..Shared memory
6.(local)Shared memory
7.Fully Qualified Domain NameTCP

Keep in mind that in order to connect using the tables above, you will need to ensure that the protocols in question are enabled in the SQL Server Configuration Management tool:

SQL Server Configuration Manager.png
 
Top