(scottish) sql bob blog


Some thoughts of your typical data shepherd / data plumber / data dance teacher sort of person.

How to find SQL Server Instance IP address
Quite often I need to get some information about a SQL server instance, this morning it was the IP address for an SQL server instance.  Having googled for this more times than I care to remember this time I thought I would put the information in a blog post.

Method 1 – TSQL 
SELECT   ConnectionProperty('net_transport')          AS 'net_transport'
      , ConnectionProperty('protocol_type')          AS 'protocol_type'
      , ConnectionProperty('auth_scheme')            AS 'auth_scheme'
      , ConnectionProperty('local_net_address')      AS 'local_net_address'
      , ConnectionProperty('local_tcp_port')          AS 'local_tcp_port'
      , ConnectionProperty('client_net_address')      AS 'client_net_address'
      , ConnectionProperty('physical_net_transport') AS 'physical_net_transport'

Local_net_address was the field that I required.

This post was inspired by - https://www.sqlservercentral.com/forums/reply/1519373
Further information on ConnectionProperty can be found here.

Method 2 – DBA Tools

Test-DbaConnection <InstanceName>  see -> https://docs.dbatools.io/#Test-DbaConnection

The output includes a property IPAddress which is what I wanted.   If you have not looked at DBATools then you should, it is an amazing, open source project, and a genuine time saver.

Are there any other methods which I have not thought of please let me know.