This is regarding finding the SQL Server database services/instances on remote computers, not any other SQL Server components like SSRS, SSIS, Full-Text services etc. maybe installed there...
But, first, if you are just interested in looking up sql server services on a single remote computer, you can simply use this PowerShell one liner:
Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -ComputerName "SQLSERVERVM1"
The idea is to:
.
- Get list of all servers in the AD and store the results in to a SQL table
- Get servers with SQL Server installed and store the results into a SQL table
- Compare the list with the previous list and send a report to DBAs
You can find the part 1 of this blog series at the following link:
https://sqlpal.blogspot.com/2019/06/powershell-script-to-find-new-servers.html
I will be using the CSV file (new_servers.csv) generated by the powershell script mentioned in the above post. You can also create your own text/CSV file with list of servers in it, with Name as the name of the first column, it could be the only column in it.
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.
https://sqlpal.blogspot.com/2019/06/powershell-script-to-find-new-servers.html
I will be using the CSV file (new_servers.csv) generated by the powershell script mentioned in the above post. You can also create your own text/CSV file with list of servers in it, with Name as the name of the first column, it could be the only column in it.
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.
The powershell script to find SQL Server instances on remote servers:
PowerShell script to load the collected information into sql table:
SQL Script to create the dbo.sql_server_instances_stage table:
<# 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 discovered to a CSV file. By default it uses the connected users credentials. Though, there is option ($user variable) to specify a different
credentials (Windows). 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 Domain\UserName 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 + ")" throw } } # 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" throw }
PowerShell script to load the collected information into sql table:
<#This too 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 = 'mssqlservervm\SQL2016AG01' $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 if object_id('sql_server_instances_stage', 'U') is not null drop table [sql_server_instances_stage] GO CREATE TABLE [sql_server_instances_stage]( [id] [int] IDENTITY(1,1) PRIMARY KEY, [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 DEFAULT GETDATE() )