http://www.sqlservercentral.com/blogs/brian_kelley/2009/05/27/catalog-view-sys-tcp-endpoints/

Printed 2014/07/29 12:22PM

Catalog view: sys.tcp_endpoints

2009/05/27

 I was playing around with the endpoint catalog views this afternoon just looking at ways to do poor man's configuration collection on SQL Server and the options avaliable. The endpoints naturally represent the way in to SQL Server and since TCP is the default network protocol for SQL Server 2005 and 2008, I was looking specifically at sys.tcp_endpoints. Basically, I was looking to execute the following:

SELECT 
    
[name]
  
[state_desc]
  
[port]
  
[is_dynamic_port]
  
[ip_address]
FROM sys.[tcp_endpoints];

This query would seem to return a lot of useful information. It returns the name of the TCP endpoint, whether or not it's active, what IP address and port it is listening on, as well as whether or not that port was configured dynamically. However, what I got back was 0 for port, 1 for is_dynamic_port, and NULL for ip_address for several servers I hit. basically, these are the three columns in sys.tcp_endpoints that are not in the catalog view sys.endpoints. They represent TCP specific configuration information. But with one glance it's obvious that the information being returned isn't useable. So I went back to Books Online and checked, and sure enough, there was this proviso:

The information that is displayed regarding ports and IP addresses is not used to configure the protocols and may not match the actual protocol configuration. To view and configure protocols, use SQL Server Configuration Manager.

So if you're looking at trying to extract the IP address and port SQL Server is listening on from this catalog view, don't. It can't be relied upon. The guidance from Books Online says to use SQL Server Configuration Manager and that certainly works, but it's a GUI tool, and not useful for retrieving the information using an automated process. All of the information on TCP endpoints is in the registry, it's just a matter of parsing the information out. So I'll be looking at writing a quick script that does just that.

 

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.