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

https://sqlpal.blogspot.com/2019/06/powershell-script-to-find-new-servers.html

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" 
try
{

$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 
$domainObj

$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"
                       exit
               }
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 = @()
""
$servers
""

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

foreach($server in $servers) 

        { 
try
        {

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

$SqlInstancesList += $SqlServices
        }
catch
        {
# 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:"
"--------------------------------------------------------"
$ErrorServers
        }
""
"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" 
}

Catch
{
    (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]
GO
drop table [dbo].[sql_server_instances_stage]
GO

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,
PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

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

Powershell script to find SQL Server instances on remote servers

Rate

Share

Share

Rate