Powershell script to find SQL Server instances on remote servers


This is actually part 2 of a process I am creating to automatically discover SQL Server instances in an Active Directory domain. So there will be a series of handful of posts.

You can find the part 1 of this blog series at the following link:

 Part 1: Powershell script to find new servers in an AD domain


I will be using the CSV file generated by the powershell script mentioned in the above post.

In below powershell script all I am doing is to check if the remote servers have sql server instance winodws services setup and their current status. I am not checking yet whether I have access to them or what version of sql servers these instances are running. That will be in the next post in this series!

Additionally, in this post I am also inserting the collected information into a sql staging table.

But first, if you are just interested in looking up sql server services on a single remote computer, you can use this powershell one liner:

Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -ComputerName "SQLSERVERVM1" -ErrorAction Continue

Now the powershell script to find SQL Server instances on remote servers:


This powershell script uses WMI to connect to the each server and check windows services that matches %%sqlservr.exe%% pattern.
Therefore in order for this to work you would need to have access to the servers otherwise it will throw Access Denied errors.
However since I am getting the list of servers to check from a CSV, it will continue on to the next server after the errors.

At the end it displays list of servers it successfully connected to and a separate list where it errored out.
It also exports the list of sql instances it discovere to a CSV file.

By default it uses the connected users credentials.
Though, there is option ($user variable) to specify a different credentials.
The password field is in plain text so I am not a big fan of it.

(Get-Date).ToString() + ": Begin"

$user = "" # Should be in DomainUserName format
$pass = ""

if ($user -eq "") { $user = $Null}

# If user/pass pair is provided, authenticate it against the domain
if ($user-ne $Null)
"Authenticating user $user against AD domain"
$domain = $user.Split("{}")[0]
$domainObj = "LDAP://" + (Get-ADDomain $domain).DNSRoot

$domainBind = New-Object System.DirectoryServices.DirectoryEntry($domainObj,$user,$pass)
$domainDN = $domainBind.distinguishedName
"domain DN: " + $domainDN

# Abort completely if the user authentication failed for some reason
If ($domainDN -eq $Null)
"Please check the password and ensure the user exists and is enabled in domain: $domain"
throw "Error authenticating the user: $user"
else {"The account $user successfully authenticated against the domain: $domain"}

$passWord = ConvertTo-SecureString -String $pass -AsPlainText -Force
$credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $user, $passWord

$csv_file_name = "new_servers.csv"
$CSVData = Import-CSV $csv_file_name
$export_file_name = "sql_server_instances.csv"
$csv_row_count = $CSVData.Count
(Get-Date).ToString() + ": Total rows in the CSV file: " + $csv_row_count
$servers = $CSVData.DNSHostName

$SqlInstancesList = @()
$ErrorServers = @()

# iterate through each server and search for sql services on them

foreach($server in $servers)


if ($user-ne $Null)
{$SqlServices = Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -credential $credentials -ComputerName $server -ErrorAction Continue}
{$SqlServices = Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -ComputerName $server -ErrorAction Continue}

$SqlInstancesList += $SqlServices
# even though error occured, it will continue to the next server
"Error when looking up SQL Instances on: " + $server
$ErrorServers += $server + " (" + $_.Exception.Message + ")"

# if there were any errors with any of the servers, print off names of those servers along with the error message/reason
if ($ErrorServers.Count -gt 0)
"Error when looking up SQL Instances on following servers:"
"SQL Instances Found:"
$SqlInstancesList | select-object -Property PSComputerName, @{n="SqlInstance";e={$_.Name -replace "MSSQL$", ""}}, Name, ProcessID, StartMode, State, Status, ExitCode, PathName | Export-CSV $export_file_name -NoTypeInformation -Encoding UTF8
Import-Csv -Encoding UTF8 -Path $export_file_name | ft -AutoSize
(Get-Date).ToString() + ": Complete"

(Get-Date).ToString() + ": Error Occurred"

And now the powershell script to load the collected information into sql table:

This also uses the connected users credentials to connect to sql server instance.

Since I am loading data into a staging table, this first truncates that table then loads the data into it.
(Get-Date).ToString() + ": Begin Loading data into sql staging table" 

$sql_instance_name = 'mssqlservervmSQL2016AG01'
$db_name = 'AdminDBA'
$destination_table_name = "dbo.sql_server_instances_stage"
$export_file_name = "sql_server_instances.csv"

$truncate_table_command = "truncate table " + $destination_table_name
"Truncate table command: " + $truncate_table_command
invoke-sqlcmd -Database $db_name -Query $truncate_table_command -serverinstance $sql_instance_name

$SqlServices = Import-Csv -Encoding UTF8 -Path $export_file_name
foreach ($sqlservice in $SqlServices)
$PSComputerName = $SqlService.PSComputerName
$Name = $SqlService.Name
$SqlInstance = $SqlService.SqlInstance
$PathName = $SqlService.PathName
$ExitCode = $SqlService.ExitCode
$ProcessID = $SqlService.ProcessID
$StartMode = $SqlService.StartMode
$State = $SqlService.State
$Status = $SqlService.Status

$query = "INSERT INTO " + $destination_table_name + " (PSComputerName,ServiceName, InstanceName,PathName,ExitCode,ProcessID,StartMode,State,Status)
VALUES ('$PSComputerName','$Name','$SqlInstance','$PathName','$ExitCode','$ProcessID','$StartMode','$State','$Status')"

$execute_query = invoke-sqlcmd -Database $db_name -Query $query -serverinstance $sql_instance_name

$query = "select count(*) rcount from " + $destination_table_name
$rcount = invoke-sqlcmd -Database $db_name -Query $query -serverinstance $sql_instance_name -As DataTables
"Number of records inserted into sql table: " + $rcount[0].rcount
(Get-Date).ToString() + ": Complete Loading data into sql staging table"

SQL Script to create the dbo.sql_server_instances_stage table

USE [AdminDBA]
drop table [dbo].[sql_server_instances_stage]

CREATE TABLE [dbo].[sql_server_instances_stage](
[id] [int] IDENTITY(1,1) NOT NULL,
[PSComputerName] [varchar](500) NULL,
[ServiceName] [varchar](500) NULL,
[InstanceName] [varchar](500) NULL,
[PathName] [varchar](1500) NULL,
[ExitCode] [int] NULL,
[ProcessID] [int] NULL,
[StartMode] [varchar](500) NULL,
[State] [varchar](500) NULL,
[Status] [varchar](500) NULL,
[InsertedDate] [datetime] NULL,
[id] ASC

ALTER TABLE [dbo].[sql_server_instances_stage] ADD DEFAULT (getdate()) FOR [InsertedDate]

Original post (opens in new tab)