THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

Finding IP Addresses With CMS


I was just tasked with putting together a list of all the IP Addresses of the SQL Servers that one of my clients deals with on a daily basis. In this case there was over 30 of them and while I could ping each one individually I found an easier way that you might find useful as well if you are in the same circumstance. I figured there must be a column in one of the DMV’s that shows the IP Address and sure enough the first DMV I looked at had just what I was after. The local_net_address column of the sys.dm_exec_connections DMV shows the following information as taken from BooksOnLine:

Represents the IP address on the server that this connection targeted. Available only for connections using the TCP transport provider. Is nullable.

So chances are on a server with several connections you will get what you are looking for with the following query:

SELECT DISTINCT local_net_address
    FROM sys.dm_exec_connections
        WHERE local_net_address IS NOT NULL ;

Now this doesn’t do me much better than pinging each server unless I have a way to query them all at once. That is where the under utilized feature of SQL 2008 comes into play called the Central Management Servers.  I won’t bore you with my own explanation of that feature as it is well documented already. Here is one place to look.  But suffice to say that with this feature I can register all my servers and run a single query against them all at once. I then copied the results and pasted them into a spreadsheet and there you go. This is a pretty simple concept but I know people look for this enough that I thought I would share it.



Published Monday, July 25, 2011 5:14 PM by Andrew Kelly

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Elizabeth said:

Great post thank you!!


August 1, 2011 3:33 PM

kranthi said:

Very use full. Thank you

February 25, 2012 9:57 AM

Leave a Comment


This Blog


Privacy Statement