Run SQL Script On Multiple Servers Using Powershell

In a more distributed environment there is a need to use Powershell script to connect to multiple SQL servers and execute queries. The results can include service or Database records that are then combined into an array to be manipulated or displayed. This is very useful to the modern SQL Administrator to avoid Linked Servers and other methods to collect SQL specific information across the environment.

Note: See a previous post on collecting All of the Database sizes and free space on an instance.

Connect To Multiple SQL Servers

Here I’m using invoke-sqlcmd to create a very simple Powershell script to return all of the SQL databases in an array and their status. Notice the T-SQL “@@servername” is included as part of the results to identify the SQL instance.

$sqlservers = @( 
     'server1', 
     '172.1.1.3', 
     'myserver3' )
$dbrecords = @() ## array for query results

Foreach($server in $sqlservers) {

   $dbrecords += invoke-sqlcmd -query 'SELECT @@servername, [name], [state_desc], [create_date], [compatibility_level] FROM master.sys.databases' -ServerInstance $server

}

$dbrecords | Format-Table
## $dbrecords | Out-GridView

List or Array of SQL Server Instances

One of the issues with this script is the list of servers and/or instances. The array value are directly used for the connection which means the instance (and possibly port #) would need to be included if SQL was not installed as default.
Assuming there are 3 instances on a single “Server1” and a default instance on “Server2” , we would need the array to include all 4 of them like this.

$sqlservers = @( 
     'Server1/myInstance03',
     'Server1/myInstance04', 
     'Server1/myInstance05',
     'Server2'  )

My approach has been to store the SQL servers and instances in a table and pull them out to be used in our foreach, this is included in the example below. It’s easy to manage but some may prefer to manually add the servers or use a basic text file. In this case add each Server or “server/instance” on a new line in a “.txt” file and load it into the array using Get-Content.

$sqlservers = Get-Content -Path .\serverlist.txt  ## same folder
$sqlservers = Get-Content -Path "C:\path\to\serverlist.txt"  ## full path

Execute SQL Script on Multiple Servers

To simplify the code and make this script useful for other DBA’s, I create a “.sql” script file and place it in the same folder. This T-SQL is what will be executed on each of the remote SQL servers and returned.
Using the simple example (db tales com) at the top, create a new “myquery.sql” file, paste the following and save the file.. This is what will be executed on each instance.

SELECT @@servername, [name], [state_desc], [create_date], [compatibility_level] FROM master.sys.databases

Modify the Powershell to execute this script file instead of the “-Query” and add the results to the array. Notice the “-InputFile” switch.

$dbrecords += invoke-sqlcmd -inputFile 'C:\path\to\myquery.sql' -ServerInstance $server

Anyone who can code T-SQL would be able to make changes to this file and execute the Powershell without changing the Powershell code.

Store and Retrieve Servers From a Table

Another improvement is to store the SQL server and instances in a table to be easily retrieved using the same invoke-sqlcmd method. Note that the value returned is not an array, it is an object and the query field names can be referenced using .dot notation.

$sqlservers = invoke-sqlcmd -Query 'SELECT servername FROM SQLServer' -ServerInstance 'AdminServer1'

This Query can also be stored in a separate file to be modified later if needed.

$sqlservers = invoke-sqlcmd -inputFile 'C:\path\to\serverquery.sql' -ServerInstance 'AdminServer1'

Powershell Full Example Code

This is the Powershell code, be sure to create the .sql files and use the correct Path. You will notice that the “-ServerInstance” parameter is slightly different and includes the column name returned by the query. Since this is an object being returned we reference the server name using dot notation. ($server.name)

$sqlservers = invoke-sqlcmd -inputFile 'C:\path\to\serverquery.sql' -ServerInstance 'AdminServer1'
$dbrecords = @() ## array for query results

Foreach($server in $sqlservers) {
   $dbrecords += invoke-sqlcmd -inputFile 'C:\path\to\myquery.sql'  -ServerInstance $server.name
}

$dbrecords | Format-Table
## $dbrecords | Out-GridView

---