Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

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


By:   |   Read Comments (1)   |   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 HKLM\Software\Microsoft\Microsoft SQL Server\<yourinstance>\MSSQLServer\SuperSocketNetLib\ 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





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, August 06, 2018 - 10:29:12 AM - Ameena Lalani Back To Top

 Hi Eduardo,

Good information that will be useful to many of us.


Learn more about SQL Server tools