how to quickly check communication protocol used by SQL Server

Question

I am using VSTS 2008 + C# + ADO.Net + SQL Server 2008. When connecting from another remote machine, normally TCP/IP protocol is used by ADO.Net client to connect to SQL Server 2008. When connecting locally (ADO.Net client and SQL Server on the same machine), normally shared memory or named pipe is used.

My question is, besides the general rules, how to check exactly (e.g. from some SQL Server built-in commands/tools/store procedures?) what communication protocol is used by a specific connection?

thanks in advance, George

Solution 1

If you have the SPID of the process in question (from using something like Activity Monitor or sp_who or sp_who2), then you can perform this query on the server:

SELECT * 
FROM sys.dm_exec_connections 
WHERE session_id = (SPID);

replacing (SPID) with the actual SPID of your client process. Look at the net_transport column, that will tell you which protocol that process is using to communicate with SQL Server. If all you are interested in is the protocol, just use SELECT net_transport instead of SELECT *.

You could also filter your query by other columns, such as client_net_address which, for TCP connections, will be the IP address of your client machine. You could send this query via ADO.NET interfaces. SQL SMO might also have something.

Here's the MSDN reference page for sys.dm_exec_connections. Note that you will need VIEW SERVER STATE rights on the server to see more than just your current session.

You can also specify the transport protocol in the server directive of your connection string using the following syntax:

server=[protocol]:servername

Examples:

server=tcp:mysqlinstance1 (for TCP/IP)
server=np:mysqlinstance1 (for named pipes)
server=np:\\mysqlinstance1\pipe\pipename (for a specific pipe)

Solution 2

As a side note: TCP/IP or named pipes are the transport layer protocol and tabular data stream is the communication (application layer) protocol in SQL Server.

http://en.wikipedia.org/wiki/Tabular_Data_Stream

Comments

[George2]

Thanks! How to get the SPID of current connection? Could I got it from ADO.Net client or from server?

[George2]

I have tried to execute SELECT * FROM sys.dm_exec_connections, it has net_transport column, but from the MSDN link, seems there is no such column described? Confused. Any comments?

[joeqwerty]

Not that this answers your question, but as a clarification.

[George2]

I am asking for transport layer protocol, in more details, for 4 types of communication protocols, shared memory/named pipe/TCP IP/VIA, I want to know for a specific connection, which protocol is used. Any ideas or comments?

[joeqwerty]

I just ran the query squillman posted on my SQL 2008 server and the net transport column is definitely there. Have you actually tried the query on your server?

[squillman]

My bad, I linked to sys.dm_exec_sessions by accident. I fixed the link to point to the right page.

[squillman]

You might not even need to get the SPID. I've edited my answer with some other info.

[joeqwerty]

As a side note, shared memory is not a communication\network protocol so you will never see any non-local clients (network clients) connecting to SQL via shared memory.

[George2]

Thanks squillman, my question and real scenario is, from ADO.Net client side, in the connection string ADO.Net client is using, there is no protocol information. So, in this scenario I need to know how to match session_id or SPID (from rows in SELECT * FROM sys.dm_exec_connections) to a specific ADO.Net client application. Any ideas how to match session_id to a specific ADO.Net client application?

[George2]

BTW: are there any descirption (document) about the meaning of column net_transport? I did not find such information from MSDN link you provided.

[George2]

Yes, I tried. And I want to learn more about the function and meaning about net_transport column. Are there any descirption (document) about the meaning of column net_transport? I did not find such information from MSDN.