Find SQL Server Instances

Any environment will kick up the odd surprise with extra servers you did not know about (suddenly appearing), which is why I always like to have a look around the environment every so often to see what is there. So how do you find SQL instances and without a 3rd party tool?

SQL Command Line

Luckily you can do this via the built-in query tools that Microsoft provides.

Depending on your version of SQL, the command line entries will look like this:

ISQL -L
OSQL -L
SQLCMD -L

Finding Servers that might have SQL

Active Directory

Some companies like to put “SQL” in to the name of the server, or in the description field. So we can look for those with this:

Import-Module ActiveDirectory;
Get-ADComputer -Filter 'Name -like "*sql*" -or Description -like "*sql*"' -Properties Description | Select-Object @{Name="ServerName";Expression={$_.Name}},@{Name="InstanceName";Expression={$null}};

While this only gets a list of probable servers with SQL instances we can use this to scout for the actual installs.

ManagedComputer

Using ManagedComputer only requires a computer name and it will return the instances it knows about. This can also easily be combined with the AD search shown above.

[Void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null;
$Computer = $Env:COMPUTERNAME
$ManagedComputer = New-Object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" $Computer;
$ManagedComputer.ServerInstances | Select-Object @{Name="ServerName";Expression={$Computer}},@{Name="InstanceName";Expression={$_.Name}};

SqlDataSourceEnumerator

GetDataSources is very easy and only requires a simple call to return all the instances which it can see.

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

I tend to use this version below as I only want server and instance name.

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | Select-Object @{Name="ServerName";Expression={$_.ServerName}},@{Name="InstanceName";Expression={$_.InstanceName}};

Sledgehammer

The final option is to check if the SQL service(s) are installed. Which is basically the go and have a look everywhere option.

Import-Module ActiveDirectory;

ForEach ($Computer in Get-ADComputer -Filter 'Name -like "*"'){
$ComputerName = $Computer.Name;
$Ping = Get-WmiObject Win32_PingStatus -Filter "Address='$ComputerName'";

 IF($Ping.StatusCode -eq 0)
 {
  Get-WmiObject -class "Win32_Service" -Computer $ComputerName|?{$_.Name -like "*MSSQL*"}|Select @{Name="ServerName";Expression={$ComputerName}},@{Name="InstanceName";Expression={$_.Name}};
 }
}

Finally…

Sometimes an instance will just refuse to show up… This is for a variety reasons such as firewalls, permissions, network issues…and sometimes they are just hiding.

If you want something that is a more complete solution with regards to searching and presentation then you may want to go and look at Microsoft Assessment and Planning (MAP) Toolkit.

Leave a Reply