Community Tools Month 2022 - dbatools

The Prompt

In July, Brent Ozar (blog | twitter) asked us to make September Community Tools Awareness Month.

In September, I want you to improve community knowledge about one free tool that you rely on every week in order to get your job done.

I’m sure it will come as no surprise to readers of this blog, but I’m going to select the amazing PowerShell module dbatools.

dbatools Is for Everyone

Despite what the name may imply, dbatools is a valuable toolkit for anyone who needs to interact with SQL Server. It’s not just about upgrades and migrations anymore. I like to tell people it’ll make them look like a wizard to their colleagues because of how quickly things can get done with just a few commands.

Role	Uses
Security & audit team	Collect logins, users, permissions, and roles across any number of instances for review. Create, drop, enable/disable logins, and manage user role membership. Collect backup history & error logs for review. Identify, repair, and remove orphaned users. Manage stored credentials
Data Analyst	Bulk-import data directly from CSV files or local object collections, including automatically creating or adding to tables. Copy data between tables, databases, or even instances in bulk - by copying a whole table, a view, or the results of a query (yes, you can do lightweight ETL with PowerShell!).
DBA	Install & configure SQL Server. Install and execute community monitoring & analytical tools. Locate duplicate & unused indexes. Copy/move databases. Enable/disable TDE. Manage Extended Events sessions. Create & manage Availability Groups and Replication. Manage and test backups & restores. Copy individual table schemas. Check which database(s) are using the most memory & tempdb space. Create and drop database snapshots.
Sysadmin	Monitor disk usage and collect base system information. Manage encryption used for connections to SQL Server. Locate orphaned database files Locate instances of SQL Server on your network. Test disk configuration and speed
Developer	Build & deploy DACPACs from an SSDT Database Project. Extract a DACPAC from an existing database.

Especially in smaller shops, these roles will overlap to some degree. They sure do for me.

But don’t just take this post’s word for it; I’ve written about dbatools a few times here on my blog.

But Why?

Automation is your key to survival in this game and dbatools is a huge enabler for it. By automating even “simple” tasks, you are freed up from doing the things you have to do so that you can concentrate on doing the things you want to do. It just so happens that in my case, the things I want to do usually involve building more scripts and automation.