Run SQL Query on Multiple Servers Using PowerShell – Serially
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
<# 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.
Related Posts
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.
Leave a Reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.