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