ARTICLE

Why You Should Care about dbatools

From Learn dbatools in a Month of Lunches by Chrissy LeMaire and Rob Sewell

This article presents dbatools and talks about why data professionals can’t afford to ignore PowerShell.

__________________________________________________________________

Take 37% off Learn dbatools in a Month of Lunches by entering fcclemaire into the discount code box at checkout at manning.com.
__________________________________________________________________

Why Data Professionals can’t afford to ignore PowerShell

Data is now at the heart of the majority of businesses. Data Professionals have a broad set of skills and are expected to be able to accomplish a wide number of tasks. They can be required to build SQL Server hosts, develop ETL solutions, ensure SQL instances are correctly configured, monitor and respond to alerts, troubleshoot performance and access issues, perform OS and SQL upgrades, deploy changes to schemas, evaluate index usage and settings, and many, many more.

In the process of performing their role they interact with many technologies; SQL Server, Virtualization (Hyper-V or VMWare), Operating Systems (Windows or Linux), Clusters, Networking, Storage, Active Directory, Certificates, Cloud, etc.

In the majority of cases, a data professional interacts with more than one SQL instance. While delivering sessions over the years, we’ve talked with people who are responsible for anywhere from 2 to 10,000 instances.

While pretty much everything can be achieved via the GUI consoles for any of those technologies, two problems come immediately to mind with this approach; the amount of time wasted and the inconsistency of humans compared to machines. This is best explained with an anecdote.

When Rob first became a SQL Server DBA, his first responsibility every morning was to check that Agent Jobs had run successfully across the SQL Server estate. This numbered a little over 100 instances. He would start by connecting to the first one in SQL Server Management Studio, clicking on SQL Server Agent, double-clicking “Job Activity Monitor” and checking the “Last Run” column for the jobs. This would take him a minimum of 90 minutes. He had heard about PowerShell and used it at home to re-order his digital photos into year and month folders and he asked his boss if he could use PowerShell to make this job easier. His boss said, “no, this was the way we did it and had done it for many years and that wasn’t going to change”.

Rob went on holiday and in his absence his boss took responsibility for checking the jobs. One particular job ran on the first of every month. When checking that instance his boss saw that all jobs had completed successfully on their last run, unfortunately, it was the 1st of November and that particular job had last run on the 1st of October! It took a number of days before this was noticed and caused some disruption. When Rob came back from holiday, he was given the time to write a PowerShell script which connected to all of the instances and filled a color-coded Excel file and saved it to a share.

Humans are fallible; they get tired, distracted or bored with repetitive tasks, but this is where a computer excels. A script, like Rob’s PowerShell script, runs monotonous tasks over and over, all day, without distraction and can also be scheduled to run at anti-social times. A script also wouldn’t mistake 11/01/2018 with 10/01/2018, but a human can!

By automating the task, the time he spent performing his daily tasks turned from at least ninety minutes each day to the time it takes to open the correct Excel sheet and scroll through looking for the red cells. If you’re doing something more than once “automate it,” as the popular saying goes. PowerShell, as a scripting language that can interact with many technologies, is an excellent tool for this.

Away from the problems, there are benefits, too. Repeatability, running that script, again and again, performs the exact same task, humans, unfortunately, aren’t good at that.

A well-known saying which is relevant on this point is: Use the right tool for the job.

We believe that it’s better if you write a script with good comments and headers which set up a particular scenario than a set of screenshots in a document for a human to follow. Documentation with too many assumptions can lead to mistakes, but over-documenting all of the steps leads to large, unwieldy, hard to follow documentation which is difficult to maintain.

Ultimately, learning PowerShell prepares you for the inevitable (and fun!) automation-rich future. PowerShell is everywhere: it’s now available on Windows, Linux, macOS and it even helps power the cloud. Once you learn how to use an automation tool like PowerShell, you can easily transition your skills to automate everything from Azure and SQL Managed Instances to Spotify and Slack.

PowerShell helps empower you to become the automator and not the automated.

What is dbatools?

dbatools is an open-source cross-platform PowerShell toolkit for SQL Server DBAs. With over 150 contributors from the SQL and PowerShell communities, dbatools is designed and written by the people who use it in their everyday work. dbatools includes solutions for everyday tasks like performing backups and restores, migrations, and setting up Availability Groups. dbatools is designed to enable SQL DBAs to reliably and repeatedly automate the usual daily tasks.

Often based on solutions found on popular blog posts, Stack Overflow, and Reddit, dbatools commands automate and simplify many of the tasks we’ve all had to do multiple times. This means that you don’t need to remember the formula for calculating maximum memory settings or where you saved the T-SQL for converting a trace to extended events (thank you, Jonathan Kehayias). dbatools also interacts with many popular SQL Community tools created by creators like Ola Hallengren (The Maintenance Solution we love), Glenn Berry (awesome diagnostic queries), Adam Machanic (sp_whoisactive), Brent Ozar (First Responder Kit), and Marcin Gminski (sqlwatch).

Where is Microsoft in all of this? While the SQL Tools Team has its own module, SqlServer (formerly SQLPS), Microsoft has been incredibly supportive of dbatools. Not only do Premiere Field Engineers use and blog about dbatools, but the SQL Tools Team also allows us to include many of the bits that power SQL Server Management Studio.

dbatools was made possible by the community, and we’d like to thank every single person who has ever written a command, suggested an improvement, created an issue, assisted with automation, testing, CI/CD, the website dbatools.io, documentation, written blog posts and presented presentations. Each and every one of you have helped to make dbatools what it is today and we, along with many others, are grateful.

Is this book for you?

dbatools helps make PowerShell easy for the Data Platform community, as its primary audience are end-users instead of developers. Now, you longer have to know how to program PowerShell to work with SQL Server at scale; you can run a few commands that we built for you.

Our focus in this book on PowerShell, however, it’s less about showing you how to write and develop PowerShell scripts and more showing how to accomplish tasks. We expect you have some knowledge of SQL Server and its administrative tasks as we won’t be teaching SQL Server concepts other than what is required to understand the PowerShell code.

If you use the GUI and are hesitant about a future filled with automation and command-line tools, our goal is to help you feel eager, confident and excited about using PowerShell.

The book serves as a learning guide, taking you from gathering information about your estate to performing complex migrations with a couple lines of code. We also give you the confidence to be able to explore PowerShell and develop your own solutions for administering SQL Server in your own estate and to use your increased knowledge of PowerShell with other technologies.

What are you waiting for?

If you want to learn more about the book, check it out on our browser-based liveBook reader here and see this slide deck.