SQL Server: Which protocol is being used for client-server connectivity in SQL Server?

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.




About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

Leave a Reply

Your email address will not be published.