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
- Databases
- Endpoints
- File system and storage
- Log shipping
- Account management
- Mirroring
- Instance configuration
- Replication
- Network and connectivity
- Server management
- Migration
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:
Type Name Status Notes ---- ---- ------ ----- Agent Job DBA - Daily Backup Successful Agent Job DBA - MSDB MaintenaceSuccessful Agent Job DBA - Consistency CheckSuccessful Agent Job DBA - Weekly Index MaintenanceSuccessful Agent Job DBA - Error log monitoringSuccessful
That’s it! A simply line of code using the Copy-DbaLogin and 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!
The post dbatools for the accidental DBA appeared first on DBA MASTERY.
