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.