Full script:
$VMs = @("server1", "server2")
$Connection = @()
$Jobs = @()
$LogFile = "$(Get-Location)\execution_log.txt"
Function to log messages to a file
function Log-Message {
param ([string]$Message)
$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
"$timestamp - $Message" | Out-File -FilePath $LogFile -Append
}
Log script start
Log-Message "Script execution started."
Write-Host "Execution started. Logs will be saved to: $LogFile"
Establish the sessions and execute the SQL command on each server in parallel
foreach ($VM in $VMs) {
Try {
# Attempt to create a session to the remote machine
$Session = New-PSSession -ComputerName $VM -ErrorAction Stop
Write-Host "Successfully connected to $VM"
Log-Message "Successfully connected to $VM"
# Start a background job to execute SQL commands
$Job = Invoke-Command -Session $Session -ScriptBlock {
Try {
Import-Module sqlps -ErrorAction SilentlyContinue
# Get the SQL Server instance name
$serverName = $env:COMPUTERNAME.ToLower()
$instanceNames = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
$queryResult = ""
foreach ($instanceName in $instanceNames) {
$sqlInstance = if ($instanceName -eq "MSSQLSERVER") {
$serverName
}
else {
"$serverName\$instanceName"
}
# Define the SQL query
$query = "
USE [master]
GO
"
Try {
# Attempt to execute the query
$result = Invoke-Sqlcmd -ServerInstance $sqlInstance -Query $query -QueryTimeout 65535 -ErrorAction Stop
$queryResult = "SQL executed successfully on $sqlInstance."
$status = "Success!"
}
Catch {
# Capture any errors from the SQL query execution
$lineNumber = $_.InvocationInfo.ScriptLineNumber
$queryResult = "Error executing SQL at line $($lineNumber): $($_.Exception.Message)"
$status = "Fail"
}
}
# Return the results
[PSCustomObject]@{
ComputerName = $serverName
SQLResult = $queryResult
Status = $status
}
}
Catch {
# Capture any critical errors within the script block
$lineNumber = $_.InvocationInfo.ScriptLineNumber
[PSCustomObject]@{
ComputerName = $env:COMPUTERNAME
SQLResult = "N/A"
Status = "Fail - Critical error at line $($lineNumber): $($_.Exception.Message)"
}
Log-Message "Critical error on $env:COMPUTERNAME at line $($lineNumber): $($_.Exception.Message)"
}
} -AsJob
# Add the job to the jobs list
$Jobs += $Job
}
Catch {
# Capture errors when trying to connect
$lineNumber = $_.InvocationInfo.ScriptLineNumber
$Connection += [PSCustomObject]@{
ComputerName = $VM
Status = "Fail"
ErrorMessage = "Connection error at line $($lineNumber): $($_.Exception.Message)"
}
Write-Host "Failed to connect to $VM. Error: $($_.Exception.Message)"
Log-Message "Failed to connect to $VM at line $lineNumber. Error: $($_.Exception.Message)"
}
}
Wait for all jobs to finish
$Jobs | ForEach-Object {
# Wait for the job to complete
Wait-Job -Job $_
# Fetch the result of the job
$JobResult = Receive-Job -Job $_
# Output the result for each server
Write-Host "Result for $($JobResult.ComputerName):"
Write-Host "SQL Execution Result: $($JobResult.SQLResult)"
Write-Host "Status: $($JobResult.Status)"
Write-Host "----------------------------------------------------"
Log-Message "Result for $($JobResult.ComputerName): SQL Execution Result: $($JobResult.SQLResult) - Status: $($JobResult.Status)"
# Check the job status before removing it
if ($_.State -eq 'Completed') {
# Clean up the job
Remove-Job -Job $_
}
}
If there were any connection errors, output them and log
if ($Connection.Count -gt 0) {
Write-Host "Connection Issues:"
Log-Message "Connection Issues:"
$Connection | ForEach-Object {
Write-Host "Computer: $($.ComputerName), Error: $($.ErrorMessage)"
Log-Message "Computer: $($.ComputerName), Error: $($.ErrorMessage)"
}
}
Log script completion
Log-Message "Script execution completed."
Write-Host "Execution completed. Log file saved at: $LogFile"