Getting Started With The dbatools PowerShell Module

If you are looking for a super easy way to start using PowerShell to manage your SQL Servers, then the dbatools PowerShell module is for you!  For those that aren’t familiar, dbatools is a free PowerShell module with over 400 SQL Server best practice, administration, development and migration commands included.

I have to admit, I’ve been reluctant to start integrating PowerShell into my daily DBA tasks, even though I’ve used it a good bit in the past.  My main reason for this that I didn’t have time to write all of the PowerShell scripts to do the things I need to do.  But with the dbatools module, a lot of the IF/THEN and TRY/CATCH logic seems to have been written for you. So, for example, if you want to add a user, just call the command to add a user.  If that user already exists, or if there is a problem, dbatools will exit gracefully with a message telling you what happened.  And that’s just a small example, dbatools has really been well thought out and takes care of a lot of stuff for you behind the scenes.

Installing dbatools

Installing dbatools is pretty simple and straight forward.  Open a Powershell window (or “blue DOS” as I refer to it) as Administrator and run the following command:

You will then be greeted by a couple of confirmation prompts where you have specify “Y” or “N” to do the install, and you’ll be done.  However, there are a few things to note about this install:

• Using “Install-Module” requires Windows 10, or on Windows 7 and Windows 8 you’ll need PackageManagement.
• dbatools makes heavy use of SMO (Server Management Objects), which is a C# library for interacting with SQL Server. In fact, it’s the same library that SSMS uses to interact with SQL Server.  Thankfully, Microsoft allows dbatools to include the required SMO libraries in their download.
• You do not need Powershell, or dbatools, installed on each of your SQL Servers in order to run the SQL Server-only commands, only the machine running the dbatools commands needs the installation. (so you can install on your workstation and run it against your SQL Servers)
• For servers that you want to run Windows commands on, you will need PowerShell 2.0 (or greater) with remoting enabled.

Once your installation completes, you’ll probably want to test it.  Here is a simple command you can run to check the MAXDOP on a server, just replace YOURSERVERNAME with the name of the server you want to check.  (LOCALHOST will work here too, for testing the local server)

Or, if you would prefer just to see the “configured” and “running” values, simply do this:

And that’s it.  The dbatools website has a lot of examples for getting started, and I will be doing more posts with real world examples of how I am using it as well.  One of the things we are already working on is a standard SQL Server configuration script that we will use on all new SQL Server installations.