Discover SQL Servers with Powershell

With Powershell and SMO you can easily discover SQL Server instances running on your network in just a few lines of code.

?View Code POWERSHELL
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
$smoObj = [Microsoft.SqlServer.Management.Smo.SmoApplication];
 
# This gets the sql servers available
$sql = $smoObj::EnumAvailableSqlServers($false)
 
foreach($sqlserver in $sql)
{
	Write-Host -ForegroundColor Green "Discovered sql server: " $sqlserver.Name;
}

discover sql servers with powershell

If this doesn’t seem to find all your sql servers check firewalls and the SQL Server Browser Service is running.


4 Comments

  1. Chuck Boyce says:

    Hi Rhys,

    The limitation of this is that unfortunately it relies on the SQLBrowser service. Discovery will not occur if the SQLBrowser service is not started.

    Chuck
    chuckboycejr

  2. Rhys says:

    Hi Chuck,

    Yep, no discovery without without SQLBrowser. It probably wouldn’t be difficult to remotely search the registry for installed SQL Server instances if this is an issue for you.

    Rhys

  3. John Henry says:

    Hi,

    Can this script modified to include the Edition and the version please ?

  4. Rhys says:

    Hi John,

    Yes. Something like below should do it…

    $sqlserver = "sqlinstance";
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;
    Write-Host "Edition: " $srv.Edition;
    Write-Host "Version: " $srv.Version;

    Cheers,

    Rhys

Leave a Reply