6

#PSTip Enumerate all SQL Server instances in a network

Note: This tip requires PowerShell 2.0 or above.

Ever thought about how you can list of all SQL Server instances on the local network? SQL Management Objects (SMO) provides a way to do that. We can use the EnumAvailableSqlServers() Method in the SmoApplication class to achieve this.

Let us see how:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
[Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers()

There are a couple of other variants of this method.

We can use EnumAvailableSqlServers($true) to list only the local SQL Server instances. Using $false as the method argument has the same effect as the above method of listing all instances on the network.

[Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers($true)

And, EnumAvailableSqlServers(“MyDBServer”) will return SQL instances on the remote server named MyDBServer.

[Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers("MyDBServer")
Filed in: Columns, Tips and Tricks Tags: , , ,

6 Responses to "#PSTip Enumerate all SQL Server instances in a network"

  1. Elijah Gagne says:

    I’m getting:
    Add-Type -AssemblyName “Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”
    [Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableServers($true)

    Method invocation failed because [Microsoft.SqlServer.Management.Smo.SmoApplication] doesn’t contain a method named ‘EnumAvailableServers’.

    I think the method name should be: EnumAvailableSqlServers
    http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.smoapplication.enumavailablesqlservers.aspx

  2. Vicky says:

    Please can i get detailed step on how to find all sQL server along with editions on network

Leave a Reply

Submit Comment

© 2016 PowerShell Magazine. All rights reserved. XHTML / CSS Valid.
Proudly designed by Theme Junkie.
%d bloggers like this: