Don’t be scared of PowerShell – Just get dbatools!!!6 min read

It is no secret that I have become a Fan-Boy of the open source community project dbatools.  I have been in IT for over 20 years and have worked with different scripting languages for performing tasks.  In recent years, I have “hacked” together PowerShell scripts I found online to accomplish specific tasks.  dbatools takes DBA tasks to a completely new level using PowerShell.  Most things can be done with TSQL, but those are probably not scalable to 100’s of SQL Servers.  dbatools has functions that can execute against one or thousands of SQL Servers with a minimal amount of PowerShell code.

In the later part of 2016 I was introduced to the dbatools project. It opened my eyes to how my day-to-day life as a DBA could be automated and provided great benefit to my SQL environment.  At this point, I decided to learn PowerShell the correct way.  After discussions with some smart PowerShell professionals I started the book Learn PowerShell in a Month of Lunches by Don Jones and Jeffery Hicks.  I also joined the dbatools community and helped with any tasks I could as I built my skills in PowerShell.  When I started this journey, I was prepared to take on the challenge of learning PowerShell.  However, once I got started I quickly learned that not only was PowerShell on my learning road map, but Git, Pester, VS Code, and other technologies used to develop such a great tool for the community.

Find a Command

Now that the dbatools project has over 300+ functions it can be tricky to find the command you need.  There is even a beta search feature on the website for you to explore the function list. In a previous release Simone Bizzotto contributed Find-DbaCommand function that allows you to easily search the massive list of public functions.

Here is the full syntax for this function.  Since this is a dbatools function you will want to be sure to import the dbatools module first.  See a great blog on how to setup your environment for dbatools.

Now let us look at some examples on how the function works.  Say you are looking to see what functions are related to Backups.

We return a list of functions that have the word “Backup” in the name or description.  Also, the command searches the entire help system for the keyword that is used. You can see that we get an exhaustive list of functions that have the keyword.  I am looking for commands I can use for backup operations.  How can we filter the list more to be specific to the operation I’m interested in?

---

We can use the Tag parameter to search for a “backup” category, but this also returns restore commands.

 

---

Let us look at a way we can filter even more  of the list by using multiple tags.  We enter “Backup” and “Restore” to see commands that have been tagged in those categories.

 

---

Finally, what if you knew this awesome contributor on the dbatools project and see what functions they have worked on.  You can run Find-DbaCommand with the Author parameter to see the list.

Looky there, this awesome contributor named Garry has worked on 8 functions.  Go Garry!!!

---

*** Find-DbaCommand – BONUS ***

As an added bonus I will show you how to utilize the PowerShell Pipeline and Out-Gridview features to enhance your search.

You are prompted with a well formatted grid view with the same list of functions as before, but you can now select one or multiple items from the list.  Once you click OK you will be shown the Get-Help output for that function.

Real World dbatools Win

Using dbatools has made getting information from my work environment quick and efficient.  Recently I had an IM pop-up on my computer. My manager wanted to know if we collected VLF information for all the databases. I thought to myself: I know I have seen that in Quest Spotlight, but do not recall where it is or if I could easily pull the information for all databases.  Then popped up the thought bubble:  “I can get that with dbatools.”  Within 10 minutes I collected the VLF information into an Excel file for all user databases hosted across ~400 SQL Server instances and sent that to my manager.  I provided that information quickly for my manager, and was able to review if I had any troubled databases in my environment. (I didn’t) My manager was also impressed with the quick turn around considering how many databases and servers we maintain. The code below is what I used to gather this information and dump it into an Excel file.

Closing Thoughts

My excitement about dbatools and how it helps my daily routine continue to grow each day.  The community around this tool has been so welcoming and inviting.  The team encourages you to learn what you want to learn and help others where you can.  My day is made when I can contribute in any way to this project.  My PowerShell learning has not been as rapid as I was hoping, but I plan to renew that effort after having a busy work summer that kept me distracted.

I have a specific target project in the works that I think will keep me motivated.  At my current company, we utilize SQL Server Availability Group technology heavily.  It is always a pain point in keeping logins, jobs and other objects synchronized between all the nodes so we can have confidence everything would function as expected after a failover.

I have been in the design phase of building a solution to this pain.  Utilizing dbatools I am building a table-driven solution that will keep these things synchronized.  Doing research on this topic there are various methods people have created for this, but nothing that covers multiple scenarios or meets my specific requirements.  My goal is to create a set-it-and-forget-it solution that will lessen the headache for myself and anyone else that utilizes AG’s. In the interim, there are a few solutions that offer a starting point, one posted by Derik Hammer ( t|b ) is one that I have found recently.

If things go well I hope to get some momentum to release a prototype soon.  The plan would be to release this as a community project in the hopes that others will contribute and benefit.  Releasing a tool to the community would be a great way to show my appreciation and pay it forward.  Since I was so inspired by my interaction with the dbatools team.