In this blog post, we will audit the dbatools command Invoke-DbaQuery. I will test, review, and evaluate the script based on a series of identical steps. Our goal is to provide insights, warnings, and recommendations to help you use this script effectively and safely. Invoke-DbaQuery is the Swiss army knife of all dbatools commands as you can execute almost any T-SQL script you can think of via PowerShell.
Scope
Dbatools is a community project full of bright minds, all likely brighter than my own. This audit series is more an informational series on useful capabilities and considerations rather than rigorous testing. This is to inspire even greater use of PowerShell and by extension dbatools as I believe it will assist in automating many tasks as a SQL Server DBA.
Script Overview
Script Name: Invoke-DbaQuery
Author: Friedrich Weinmann
Description: A command to run explicit T-SQL commands or files.
Testing Environment Setup
I created a multi-subnet lab with three developer edition SQL Server instances, one SQL Express and a network share based on this blog post by Jonathan Kehayias.
Preparation
Here’s the step-by-step execution:
1. Download and Install
You are probably familiar with dbatools at this point. If not, now is the perfect time to familiarize yourself with the install and use of the module. On your test environment you can open windows PowerShell or the PowerShell ISE, or visual studio code, and run the following scripts. These will install Dbatools and add it to the path for future projects as well as import it into the current session for the purpose of testing the scripts of this blog post.
Install-Module Dbatools Import-Module Dbatools
2. Initial Setup
There is some initial setup, primarily that you must have access to your own testing SQL Server environment in which to point these scripts at. Please make sure to test and verify each of the scripts you use before using them in production! With dbatools version 2.0 and above, certificate management is in play, self-signed or not. For most environments self-signed certificates are all that are being used and so we use the following script to register all connections in the session as trusted:
Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -register
I assume that you have access to the target SQL Server Instance with the windows account you are running PowerShell scripts with. However, if you do not or are not on windows, you will need to provide credentials as part of the execution. You can do that with the following script. Running this will prompt you for the username and password and store the password as a secure string in the session memory:
$credential = $host.ui.PromptForCredential("SQL Credential", "Please enter the username and password for the target SQL instance.", "Username", "")
Quick aside, PowerShell scripting is different than SQL Server in that variables and configurations are persistent throughout the session. Meaning, once you define a variable, it will stay in memory whether it is a part of highlighted code or not, and will stay unless it is changed or cleared manually. Running the above two scripts will change the dbatools configuration for the session, as well as store the credentials, if you needed to run that portion, to a variable which can be used in other commands without needing to redefine it like you would need to in SSMS. A great similarity of scripting with PowerShell compared to T-SQL Scripting in SSMS is that you can highlight sections and run just the highlighted code so long as you hit the small play button or use F8 to indicate that is what you want to do.
Execution
Now that we have the environment set up, we can begin with a few variants of Invoke-DbaQuery. Note that you would need to uncomment the sqlcredential argument if you needed to use a different credential than your own account. You can uncomment it by deleting <# and #> in the script.
$targets = 'labsql1','labsql2','labsql3' Invoke-DbaQuery -SqlInstance $targets <#-SqlCredential $credential#> -Query "SELECT @@Servername as ServerName, @@version as SQLVersion"
This is a simple example of a query running across all of the targets stored in the $targets variable.
The complexity can quickly grow per the use case as shown in the examples on the official documentation page, such as feeding parameters to the script:
Invoke-DbaQuery <#-SqlCredential $credential#> -SqlInstance $targets `
-Query “SELECT @@servername as ServerName, * FROM sys.databases WHERE name LIKE @name" `
-SqlParameter @{ Name = "%te%" } |
Out-GridView
Or lastly, using files instead of writing the queries out the script, which makes for cleaner scripts:
$targets | Invoke-DbaQuery <#-SqlCredential $credential#> -File "C:\Users\Administrator\Desktop\spcheckbackup.sql"
Results
Each of the above are useful when extended out – for example the output of the first script would be equivalent to setting up a central management server and running a query on all of the registered instances:
You can see the limitations of the window in terms out result output, but out-gridview or out-file help to solve this limitation. Nothing groundbreaking here but it is good for creating quick reports or querying your environment widely without needing to open SSMS.
The second example provides a little more flexibility in terms of being able to quickly call and provide arguments for a stored procedure or query with variable values, however the agent could provide the same functionality with a built-in scheduler.
The last moves into interesting territory as it replaces a feature previously only given by the SQLServer PowerShell module of being able to run SQL files on the targets. This makes maintenance rollout, configuration, deploying database objects and more a very simple and automatable process.
The power comes from integrating this command’s capabilities alongside the rich features of PowerShell. For example, you can spin up a SQL enabled VM in azure, update it to the latest version, run Invoke-DbaQuery over a series of configuration queries stored in files to setup maintenance and other important configurations of a SQL rollout, and then finally run a query that verifies all the relevant configurations of the new instance(s) and sends an email of that output with any errors outlined directly to you – all via default PowerShell combined with the dbatools module.
Review and Analysis
This command should be one of the first in a DBA’s toolbox as they transition to PowerShell or start using Dbatools. There are more specific commands that help with other scenarios but Invoke-DbaQuery is the Swiss army knife of this module. Pretty much anything you can do in an SSMS query window you can do in PowerShell with this command. Starting here opens up the world of PowerShell Scripting to the DBA since the learning bar is very low. All you need to do is take a query that you know works and target or series of targets and you can point that query at all of them. Want to check a configuration across your environment? Write the query, run it, and send a report to your email with just a bit more PowerShell knowledge and SMTP information in hand. The possibilities for time savings are endless!
Warnings and Caveats
The warning i give here will be repeated across all of the commands we audit as well as anytime you use PowerShell. The strength in using these commands and using PowerShell can also be its downfall. You can absolutely write a query that drops all databases on an instance. If you happen to write that and point that at all of your instances, and you have the permission to do so, PowerShell will do it. The same care you would have in working in SSMS needs to be multiplied when scripting in PowerShell because a bad command can be accidentally implemented across multiple targets if being developed carelessly. As always, anything you build should be done in a test environment with unimportant data and the results should be reviewed before deploying to production.
Recommendations
Use this command to build simple report and configuration check queries. Graduate up to scripts that create a one-off schedule for a job that failed, repoint backup jobs to a new share, or validate the configuration of an instance. Eventually build scripts that regularly query important instance features and import them into a management database so you can track the health and configuration of your environment over time by integrating task scheduler. A tip I like to share when I talk about automation is that you can use the SSMS GUI to get a general script of the action you want to automate by making all the changes and the clicking “Script Action to New Query Window”. Plop that generated code into an Invoke-DbaQuery wrapper and then substitute relevant values with variables and then save it as a template for the next time It needed.
Conclusion
Invoke-DbaQuery is my most used query in the dbatools community module. We serve many clients with different versions of SQL Server and varying numbers of instances per environment. I started in a support role and Invoke-DbaQuery helped me to build reusable queries for health checks, job modifications, maintenance rollouts, configuration checks and more. Invoke-DbaQuery can be a basic building block for many of the tasks in the DBA’s daily to do list and if you aren’t using it yet to save time and energy I recommend you do so today!
