Hi Friends,
We all know that there are 4 protocols that can be used to connect a SQL Server instance from a client; namely Share Memory, TCP/IP, Named Pipes & VIA. At least one protocol needs to be enabled for a successful connection. You can enable all 4 if you wish so and you can also set the order in which they are attempted. Many a times, you will see Share Memory being used when the client and the instance to which you are connecting are on the same box. TCP/IP is widely used to connect to remote servers on inter-connected systems. So on and so forth. You can enable, disable, set the order of protocols – all from SQL Server configuration manager.
Remember, the client also needs to be configured with the correct protocols to connect to a SQL Server instance. SQL Server Native client is installed on client machine as part of SQL Server client connectivity setup which sets up the protocols, their order, enabling them, etc.
Finally, while you are connected to SQL Server and firing your queries or managing your boxes, you can find out which protocol is being used for the current connection:
SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID;
When I run this on my system, I get the output of Shared Memory, since that’s the order and client and server are on the same box.
