dbatools is a very well known tool within the SQL Server community, and probably most of the production DBA’s are using it on daily basis.
But what about that IT guy whom main role is to take care of the company infrastructure not just SQL Server, and because there is no DBA around he does his best to fill the role as “accidental DBA” …
In case you are one of these accidental DBA’s that is looking an easy way to do “DBA” things in the most efficient and not too complex manner, let me tell you this: dbatools is what you have been looking for.
Let’s start defining what it is dbatools: It is a free and open source PowerShell module created by the SQL community with more than 500 SQL Server administration, best practice and migration commands included.
You can think of dbatools like a swiss army knife it has all the necessary things that you will make success and more!! To give you an idea of what kind of activities you can perform using dbatools, here you have a list of some of the most important categories:
- Availability groups
- Backup and restore
- File system and storage
- Log shipping
- Account management
- Instance configuration
- Network and connectivity
- Server management
The migration commands are probably the most popular in the entire collection of dbatools, there is multiple testimonies (including myself) of DBA’s that are been very successful migrating multiple instances of SQL Server from one version to other just using dbatools. Here is a short video you can watch to convince yourself.
I know theory can be boring sometimes, so let’s look at what dbatools can do for us. Let’s use an availability group scenario as example, imagine you have two replicas and you need to keep in sync the logins created in the primary with the secondary most importantly for disaster purposes. This could be a very boring and repetitive task that we can easily automate with the help of dbatools.
I created a sandbox environment with an AG called “Legend”, where I have two replicas “Apollo” (primary) and “Adonis” (secondary). with the help of dbatools I will sync all the logins from “Apollo” to “Adonis” as follows:
PS C:\Users\master> Copy-DbaLogin -Source Apollo -Destination Adonis Type Name Status Notes ---- ---- ------ ----- Login - WindowsUser MASTER\Apollo Successful Login - WindowsUser MASTER\Adonis Successful Login - WindowsUser MASTER\Duke Successful Login - SqlLogin Drago Successful Login - WindowsUser NT AUTHORITY\SYSTEM Skipped Local Login - WindowsUser NT SERVICE\ClusSvc Skipped Local Login - WindowsUser NT SERVICE\MSSQLSERVER Skipped Local Login - WindowsUser NT SERVICE\SQLSERVERAGENT Skipped Local Login - WindowsUser NT SERVICE\SQLTELEMETRY Skipped Local Login - WindowsUser NT SERVICE\SQLWriter Skipped Local Login - WindowsUser NT SERVICE\Winmgmt Skipped Local
Now, let’s imagine I also have to copy all the SQL Agent jobs from “Apollo” (primary) to”Adonis” (secondary), let’s do it:
PS C:\Users\master> Copy-DbaAgentJob -Source Apollo -Destination Adonis Type Name Status Notes ---- ---- ------ ----- Agent Job DBA - Daily Backup Successful Agent Job DBA - MSDB Maintenace Successful Agent Job DBA - Consistency Check Successful Agent Job DBA - Weekly Index Maintenance Successful Agent Job DBA - Error log monitoring Successful
That’s it! A simply line of code using the
Copy-DbaAgentJob functions I was able to sync the logins and jobs between two replicas, the same process can be created on T-SQL but it will require a decent amount of effort and some investment of time creating the code to make this happen.
And this is just the tip of the Iceberg in case you want to learn more about dbatools, I encourage you to check their website. There is a section where you can check all the commands available, also a ton of videos in YouTube where you can learn more about this awesome tool!
Thanks for reading!
Carlos Robles is a Solutions Architect at AWS, a former Microsoft Data Platform MVP, a Friend of Redgate, but more than anything a technology lover. He has worked in the database management field on multiple platforms for over ten years in various industries.
He has diverse experience as a Consultant, DBA and DBA Manager. He is currently working as a Solution Architect, helping customers to solve software/infrastructure problems in their on-premise or cloud environments.
Speaker, author, blogger, mentor, Guatemala SQL User group leader. If you don’t find him chatting with friends about geek stuff, he will be enjoying life with his family.