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.
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
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!).
Install & configure SQL Server.
Install and execute community monitoring & analytical tools.
Locate duplicate & unused indexes.
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.
Monitor disk usage and collect base system information.
Manage encryption used for connections to SQL Server.
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.
About Andy Levy
Andy is a database administrator, PowerShell fan, former developer, Open Source contributor, RVer, and connoisseur of dad jokes (not in that order). He’s worn a number of IT hats since 1999 before landing in database administration, including web server administration and development, systems integration, and database development. When he isn’t picking queries apart and wrangling unruly herds of databases, he can be found by a campfire, planning the next family camping trip, or mentoring the TAN[X] FIRST Robotics Competition team.