Ever wondered how many SQL Server instances are there in your network? Or maybe you wanted to check a settings or run a script on all of them? Of course you can use Registered Servers functionality and run a query on all servers in a group, but this can save you the pain of adding all servers one by one.
Since Power Shell builds on .NET framework, you are free to use all the mechanisms available in it. The one we are interested in here is SqlDataSourceEnumerator, which allows you to enumerate all available instances of SQL Server in a local network. The only prerequisite is that servers must have SQL Server Browser service running and the task is completed by the following one-liner:
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
The output you get is a table consisting of four columns – server and instance name, version and information if the server is clustered.
ServerName InstanceName IsClustered Version
---------- ------------ ----------- -------
PHOBOS-MOBILNY No 9.00.4035.00
Now take a simple extension of it and you are able to connect to every single SQL Server in your network. It will give you the list in standard SERVER\INSTANCE format. Then you can expand it further using SMO or Power Shell snap-in and you will get all the information you need from all servers at the same time.
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | % {if ($_.InstanceName -eq "".ToString) { $_.ServerName + "\" + $_.InstanceName} else { $_.ServerName }}
I’m awaiting - as always – your feedback, what’s good and what still needs to be improved. Or maybe there’s just something you would like to share.