Run SQL Query on Multiple Servers Using PowerShell – Serially

Ajay Dwivedi | December 23, 2023 | Powershell | 1 Comment

Spread the love

As a database administrator, often I have to fetch some metadata from all the SQLServers that we have. Other times, I have to execute some DDL or DML on all the servers.

In this blog and shared video, I show how to write a multiple server PowerShell script where server list source could range from raw text files to some inventory-based query result.

Below is the script used in the above video tutorial.

<#
    How to execute SQL Query for Multiple Servers
    Part 01 -> Serial Manner
    Part 02 -> Parallel Manner
#>

# Import dbatools module
Import-Module dbatools, ImportExcel

$ErrorActionPreference = 'STOP'

# Declare global variables
$InventoryServer = 'SQLMonitor'
$InventoryDatabase = 'DBA'

# Get list of SQLServers {list, excel, query against inventory server}
<# ***** List from file
$serverTextFile = 'E:\Github\SQLMonitor\Work\server-list.txt'
$servers = @()
$servers += Get-Content $serverTextFile

if($servers.Count -eq 0) {
    "Kindly add some servers to the list file '$serverTextFile'." | Write-Error
}
#>

# Get list of servers from Inventory
$sqlGetServers = @"
select id.sql_instance, id.sql_instance_port, id.[database]
from dbo.instance_details id
where id.is_enabled = 1
and id.is_available = 1
"@
$conInventoryServer = Connect-DbaInstance -SqlInstance $InventoryServer -Database $InventoryDatabase -ClientName "Run-MultiServer-Serially.ps1" `
                    -TrustServerCertificate -EncryptConnection -ErrorAction Stop
$servers = @()
$servers += $conInventoryServer | Invoke-DbaQuery -Query $sqlGetServers

# Create/Get the sql query that should be executed
$sqlGetDmServerServices = @"
select [sql_instance] = @sql_instance, [dba_database] = @dba_database, *
from sys.dm_server_services
"@

# Loop through all the servers, and execute the sql query against sql server
$serversSuccess = @()
[System.Collections.Arraylist]$serversFailed = @()
[System.Collections.Arraylist]$result = @()

foreach($server in $servers) {
    $sqlInstance = $server.sql_instance
    $portNo = $server.sql_instance_port
    $dbaDatabase = $server.database

    "Working on [$sqlInstance].." | Write-Host -ForegroundColor Yellow

    try {
        $conSqlInstance = Connect-DbaInstance -SqlInstance $sqlInstance -Database master -ClientName "Run-MultiServer-Serially.ps1" `
                        -TrustServerCertificate -EncryptConnection -ErrorAction Stop
        $conSqlInstance | Invoke-DbaQuery -Query $sqlGetDmServerServices -SqlParameter @{sql_instance = $sqlInstance; dba_database = $dbaDatabase} `
                    | ForEach-Object {$result.Add($_) | Out-Null}

        $serversSuccess += $sqlInstance
    }
    catch {
        $errMessage = $_.Exception.Message
        $serversFailed.Add( [PSCustomObject]@{
                            sql_instance = $sqlInstance
                            error_message = $errMessage
                        }) | Out-Null
        "`tError => $errMessage`n" | Write-Host -ForegroundColor Red
    }
}

"$($serversFailed.Count) of $($servers.Count) have failed." | Write-Host -ForegroundColor Red
"$($serversSuccess.Count) of $($servers.Count) have succeeded." | Write-Host -ForegroundColor Green

#$result | ft -AutoSize
# Save the end result of sql query from all the servers {SqlServer, Excel, Xml}
$excel = "$($env:USERPROFILE)\Downloads\MyExcel.xlsx"
$result | Export-Excel -Path $excel -WorksheetName 'Services-All-Servers'

"Pushing the result into table [$InventoryServer].[dbo].[all_servers_services]." | Write-Host -ForegroundColor Gray
$result | Write-DbaDataTable -SqlInstance $conInventoryServer -Database tempdb -AutoCreateTable -Table 'all_servers_services'

How to execute SQL Query for Multiple Servers

Part 01 -> Serial Manner

Part 02 -> Parallel Manner

# Import dbatools module

Import-Module dbatools, ImportExcel

$ErrorActionPreference = 'STOP'

# Declare global variables

$InventoryServer = 'SQLMonitor'

$InventoryDatabase = 'DBA'

# Get list of SQLServers {list, excel, query against inventory server}

<# ***** List from file

$serverTextFile = 'E:\Github\SQLMonitor\Work\server-list.txt'

$servers = @()

$servers += Get-Content $serverTextFile

if($servers.Count -eq 0) {

"Kindly add some servers to the list file '$serverTextFile'." | Write-Error

}

# Get list of servers from Inventory

$sqlGetServers = @"

select id.sql_instance, id.sql_instance_port, id.[database]

from dbo.instance_details id

where id.is_enabled = 1

and id.is_available = 1

$conInventoryServer = Connect-DbaInstance -SqlInstance $InventoryServer -Database $InventoryDatabase -ClientName "Run-MultiServer-Serially.ps1" `

-TrustServerCertificate -EncryptConnection -ErrorAction Stop

$servers = @()

$servers += $conInventoryServer | Invoke-DbaQuery -Query $sqlGetServers

# Create/Get the sql query that should be executed

$sqlGetDmServerServices = @"

select [sql_instance] = @sql_instance, [dba_database] = @dba_database, *

from sys.dm_server_services

# Loop through all the servers, and execute the sql query against sql server

$serversSuccess = @()

[System.Collections.Arraylist]$serversFailed = @()

[System.Collections.Arraylist]$result = @()

foreach($server in $servers) {

$sqlInstance = $server.sql_instance

$portNo = $server.sql_instance_port

$dbaDatabase = $server.database

"Working on [$sqlInstance].." | Write-Host -ForegroundColor Yellow

try {

$conSqlInstance = Connect-DbaInstance -SqlInstance $sqlInstance -Database master -ClientName "Run-MultiServer-Serially.ps1" `

-TrustServerCertificate -EncryptConnection -ErrorAction Stop

$conSqlInstance | Invoke-DbaQuery -Query $sqlGetDmServerServices -SqlParameter @{sql_instance = $sqlInstance; dba_database = $dbaDatabase} `

| ForEach-Object {$result.Add($_) | Out-Null}

$serversSuccess += $sqlInstance

}

catch {

$errMessage = $_.Exception.Message

$serversFailed.Add( [PSCustomObject]@{

sql_instance = $sqlInstance

error_message = $errMessage

}) | Out-Null

"`tError => $errMessage`n" | Write-Host -ForegroundColor Red

}

"$($serversFailed.Count) of $($servers.Count) have failed." | Write-Host -ForegroundColor Red

"$($serversSuccess.Count) of $($servers.Count) have succeeded." | Write-Host -ForegroundColor Green

#$result | ft -AutoSize

# Save the end result of sql query from all the servers {SqlServer, Excel, Xml}

$excel = "$($env:USERPROFILE)\Downloads\MyExcel.xlsx"

$result | Export-Excel -Path $excel -WorksheetName 'Services-All-Servers'

"Pushing the result into table [$InventoryServer].[dbo].[all_servers_services]." | Write-Host -ForegroundColor Gray

$result | Write-DbaDataTable -SqlInstance $conInventoryServer -Database tempdb -AutoCreateTable -Table 'all_servers_services'

There are many similar scripts present in the SQLMonitor tool. For example, check-instance-availability.ps1 connects to many SQLServers in parallel threads to check their availability as part of monitoring.

I hope this will be helpful. If you like the blog, do like and subscribe. Thanks.

Tags:Multi Server Execution, PowerShell, SQL Server, sql-server

About The Author

Ajay Dwivedi

I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.

One Comment

SQLServer Performance Optimization and Automation

Run SQL Query on Multiple Servers Using PowerShell – Serially

Like this:

Related

About The Author

Ajay Dwivedi

Leave a ReplyCancel reply

Share this:

Like this:

Related

Related Posts

About The Author

Ajay Dwivedi

Leave a ReplyCancel reply