Determine SQL Server Network Protocol Information using T-SQL and DMVs



By:   |   Read Comments   |   Related Tips: More > Functions – System







Problem

You need to determine the network protocols and the enabled/disabled status in
your SQL Server instances, but for whatever reason, you cannot access SQL Server
configuration manager, or maybe you just want to know that information without having
to login to the server, so you need a quick, reliable way to determine this information
using T-SQL.  Also, you may need to know that information for many servers,
and it could require a lot of work to have to log in to each server and gather the
information manually.

Solution

Using the Dynamic Management View sys.dm_server_registry and some
T-SQL formatting you can achieve this.

The DMV sys.dm_server_registry was introduced in SQL Server 2008,
and it returns configuration information stored in the Windows registry for the
current SQL server instance.

The values that this DMV returns are these:

  • registry_key: registry key name, used in our queries to
    filter the required key.
  • value_name: name of the property related to the key.
  • value_data: the value we want to retrieve.

I will show you how to determine the network protocols in your SQL server instance,
and how to obtain the TCP port configured.

Determine Network Protocols – raw version

The network configuration is stored on HKLMSoftwareMicrosoftMicrosoft
SQL Server<yourinstance>MSSQLServerSuperSocketNetLib registry key
,
so if you want to obtain all the raw data with your network protocols you can just
run this query:

SELECT registry_key, value_name, value_data 
FROM sys.dm_server_registry
WHERE registry_key LIKE '%SuperSocketNetLib%'

This query will return all the information:

all the information is displayed with the simple query

But if you want this information in a more concise and cleaner way, you can follow
the other options below.

Determine Network Protocols – formatted version

We need to filter the registry key value and value name, we can achieve this
by filtering the query at the protocol level:

  • np – Named Pipes
  • sm – Shared Memory
  • tpc – TCP/IP

Using IIF and UNION logic to give it a more user-friendly
format:

SELECT 'Named Pipes' AS [Protocol], iif(value_data = 1, 'Yes', 'No') AS isEnabled
FROM sys.dm_server_registry
WHERE registry_key LIKE '%np' AND value_name = 'Enabled'
UNION
SELECT 'Shared Memory', iif(value_data = 1, 'Yes', 'No')
FROM sys.dm_server_registry
WHERE registry_key LIKE '%sm' AND value_name = 'Enabled'
UNION
SELECT 'TCP/IP', iif(value_data = 1, 'Yes', 'No')
FROM sys.dm_server_registry
WHERE registry_key LIKE '%tcp' AND value_name = 'Enabled'

We obtain the following information:

our instance protocols and the enabled status.

But, what if we want to obtain the TCP port configured?

Determine TCP Port

We filter at the register_key level, using the IPALL text, then
filter at value_name level to obtain the port number, static or dynamic, using the
TcpPort, TcpDynamicPortsproperties.

We use the following T-SQL, if a static port is configured, it will show the
TCPPort value, if a dynamic port is configured, the value 0 will
show in the TcpDynamicPortsproperty.

SELECT 'TCP Port' as tcpPort, value_name, value_data 
FROM sys.dm_server_registry 
WHERE registry_key LIKE '%IPALL' AND value_name in ('TcpPort','TcpDynamicPorts')

For a static port, it will show this info:

static tcp port info

And for a dynamic port:

dynamic tcp port info

Next Steps

  • You can customize the query to be able to determine all the information
    in one batch.
  • To be able to use this DMV you need the VIEW SERVER STATE
    permission on the instance.
  • If you have multiple IP addresses configured for your server, you must change
    the IPALL for your IPn where n is the number
    you want to retrieve.
  • You can check the

    Microsoft official documentation
    for more info about this DMV.


Last Update:


next webcast button

next tip button

About the author

MSSQLTips author Eduardo Pivaral

Eduardo Pivaral is an MCSA, SQL Server Database Administrator and Developer with over 15 years experience working in large environments.

View all my tips


Leave a Reply

Your email address will not be published. Required fields are marked *

HashFlare