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.
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:
The outcome for each of the 7 steps described above is as follows:
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:
- The machine-name of the server (i.e. TESTSQL)
- localhost
- 127.0.0.1 (the IP address that defaults to "localhost")
- The actual IP address of the server.
- . (a single period - for the lazy ones)
- (local) - (the parenthesis are required in this).
- A fully qualified domain name (FQDN). I.e. testsql.schmitzit.com
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:
Number | Connection-name | Protocol |
---|---|---|
1. | TESTSQL | Shared memory |
2. | localhost | Shared memory |
3. | 127.0.0.1 | TCP |
4. | IP Address | TCP |
5. | . | Shared memory |
6. | (local) | Shared memory |
7. | Fully Qualified Domain Name | TCP |
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: