Find All SQL Server Instances On The Network Using Powershell

Searching for and cataloging the SQL server instances on your network is a key part of Administration. Powershell offers some quick and simple methods to find servers base on the their name or description in Active Directory. We can use the Powershell module to collect these servers and add them to a CSV file or a SQL table updated regularly.

In our example we will be looking for all of the SQL servers in Active Directory based on their Name or Description and then run some remote code to grab the SQL instances. Our assumption here is that you have implemented some level of Server naming convention and/or kept your AD Descriptions accurate.

The Active Directory module is not installed by default, at least not on Windows 10. You may need to install the tools called RSAT to make this module available. To see if it is already installed you can simple run the Import-Module command.

Install The Active Directory Module

Import-module -Name ActiveDirectory

Install Remote Server Administrative Tools (RSAT)

I was going to add screenshots and a walk through on how to add RSAT through the Control Panel -> Windows Features screen but then I found a Powershell method to do this,.. so much easier.

You will need to import the ServerManager module first to Add Windows Features, Use the following to do so in a Powershell window.

Import-Module -Name ServerManager
Add-WindowsFeature -Name "RSAT-AD-PowerShell" –IncludeAllSubFeature

Import-module -Name ActiveDirectory

Query Active Directory Using Powershell

With the AD module imported we now have access to the Get-ADComputer cmdlet. This will provide the details from Active Directory and allow us to filter the results based on our search criteria. Begin with a simple search looking for the Windows Operating system.

Get-ADComputer -Filter 'operatingSystem -like "*Windows *"' -Properties *

In this command we are filtering based on the “OperatingSystem” property of the AD object. You will notice we are using the asterisk to search for text that “contains” Windows. Change this to filter based on the Windows version if need be. We are also using the asterisk for the “-Properties” command switch so the search includes all properties in AD.

At this point we us the Pipeline and pipe the results to a Where-Object cmdlet to filter the results based on the “Name” or “Description” properties in Active Directory. As stated before the key to this search being useful is that the server naming convention or Description includes “SQL” in it’s text.

Change the ‘*SQL*’ to search for your naming convention or use it to further filter Production or Test servers based on their Name.

 Get-ADComputer -Filter 'operatingSystem -like "*Windows *"' -Properties * |
 where {$_.Name -like '*SQL*' -or $_.CN -like '*SQL*' -or $_.Description -like '*SQL*'} 

Since the command can take a long time depending your network size, we can assign the results to a variable that will stay populated as long as your Powershell session is active.

$SQLServers = Get-ADComputer -Filter 'operatingSystem -like "*Windows *"' -Properties * |
 where {$_.Name -like '*SQL*' -or $_.CN -like '*SQL*' -or $_.Description -like '*SQL*'} 

When this command completes, the $SQLServers variable can be piped to filter further or display. Pipe the results to a basic Select-Object cmdlet like this

Note: Be sure to highlight and then Run using the “Run Selection” (F8) instead of “Run Script” (F5).

$SQLServers | Select -Property Name,DNSHostName,operatingSystem, Description
$SQLServers | format-table -AutoSize
$SQLServers | Out-Gridview

Get SQL Instances On Remote Server

Now that we have a list of the Servers that should contain a SQL Service we can connect to each and find the SQL services and their instances. This code applies to a single static server but can be used inside a ForEach loop to go through the above results and collect SQL instances from each server.

This method can be applied to any service on the machine but we are focusing on SQL Instances here. The default SQL instance is called “MSSQLServer” and any named instances are prefaced with “MSSQL$” and the Instance name.

Get-Service -ComputerName VMDESKTOP3 | 
Where-Object { ($_.Name -eq 'mssqlserver' -or $_.Name -like 'mssql$*') -and $_.DisplayName -like 'SQL Server*'}

Take note of the Where-Object syntax as some will get confused grouping their “Equal, Like, And’s and Or’s”. The parenthesis allows us to group the filter as Equal to “MSSQLSERVER” or Like “MSSQL$” and the brackets enclose the full WHERE cmdlet.

---