We are so super excited to announce that after 5 long years, dbatools 1.0 is publicly available!

Our team had some lofty goals and met a vast majority of them 🏆. In the end, my personal goal for dbatools 1.0 was to have a tool that is not only useful and fun to use but trusted and stable as well. Mission accomplished: over the years, hundreds of thousands of people have used dbatools and dbatools is even recommended by Microsoft.

Before we get started with what’s new, let’s take a look at some history.

historical milestones

dbatools began in July of 2014 when I was tasked with migrating a SQL Server instance that supported SharePoint. No way did I want to do that by hand! Since then, the module has grown into a full-fledged data platform solution.

• 07/2014 – Started
• 07/2014 – Published to GitHub & ScriptCenter
• 06/2016 – First major contributors
• 01/2017 – Road to 1.0 began
• 03/2018 – Switch from GPL to MIT
• 05/2019 – Added MFA Support
• 06/2019 – Over 160 contributors and 550 commands

Thanks so much to every single person who has volunteered any time to dbatools. You’ve helped change the SQL Server landscape.

improvements

We’ve made a ton of enhancements that we haven’t had time to share even over the past six months. Here are a few.

availability groups

Availability Group support has been solidified and is looking good and New-DbaAvailabilityGroup is better than ever. Try out the changes and let us know how you like them.

Get-Help New-DbaAvailabilityGroup -Examples

authentication support

We now also support all the different ways to login to SQL Server! So basically this:

Want to try it for yourself? Here are a few examples.

# AAD Integrated Auth
Connect-DbaInstance -SqlInstance psdbatools.database.windows.net -Database dbatools

# AAD Username and Pass
Connect-DbaInstance -SqlInstance psdbatools.database.windows.net -SqlCredential [email protected] -Database dbatools

# Managed Identity in Azure VM w/ older versions of .NET
Connect-DbaInstance -SqlInstance psdbatools.database.windows.net -Database abc -SqCredential appid -Tenant tenantguidorname

# Managed Identity in Azure VM w/ newer versions of .NET (way faster!)
Connect-DbaInstance -SqlInstance psdbatools.database.windows.net -Database abc -AuthenticationType 'AD Universal with MFA Support' 

You can also find a couple more within the MFA Pull Request on GitHub and by using Get-Help Connect-DbaInstance -Examples.

registered servers

This is probably my favorite! We now support Local Server Groups and Azure Data Studio groups. Supporting Local Server Groups means that it’s now a whole lot easier to manage servers that don’t use Windows Authentication.

Here’s how you can add a local docker instance.

# First add it with your credentials
Connect-DbaInstance -SqlInstance 'dockersql1,14333' -SqlCredential sqladmin | Add-DbaRegServer -Name mydocker

# Then just use it for all of your other commands.
Get-DbaRegisteredServer -Name mydocker | Get-DbaDatabase

Totally dreamy 😍

csv

Import-DbaCsv is now far more reliable. While the previous implementation was faster, it didn’t work a lot of the time. The new command should suit your needs well.

Get-ChildItem C:\allmycsvs | Import-DbaCsv -SqlInstance sql2017 -Database tempdb -AutoCreateTable

future & backwards compatible

In the past couple months, we’ve started focusing a bit more on Azure: both Azure SQL Database and Managed Instances. In particular, we now support migrations to Azure Managed Instances! We’ve also added a couple more commands to PowerShell Core., in particular, the Masking and Data Generation commands. Over 75% of our commands run on mac OS and Linux!

Still, we support PowerShell 3 and Windows 7 and SQL Server 2000 when we can. Our final testing routines included ensuring support for:

• Windows 7
• SQL Server 2000-2019
• User imports vs Developer imports
• mac OS / Linux
• x86 and x64
• Strict (AllSigned) Execution Policy

new commands

We’ve also added a bunch of new commands, mostly revolving around Roles, PII, Masking, Data Generation and even ADS notebooks!

Want to see the full list? Check out our freshly updated Command Index page 🤩.

configuration enhancements

A few configuration enhancements have been made and a blog post for our configuration system is long overdue. But one of the most useful, I think, is that you can now control the client name. This is the name that shows up in logs, in Profiler and in Xevents.

# Set it
Set-DbatoolsConfig -FullName sql.connection.clientname -Value "my custom module built on top of dbatools" -Register

# Double check it
Get-DbatoolsConfig -FullName sql.connection.clientname | Select Value, Description

The -Register parameter is basically a shortcut for piping to Register-DbatoolsConfig. This writes the value to the registry, otherwise, it’ll be effective only for your current session.

Another configuration enhancement helps with standardization. Now, all export commands will default to Documents\DbatoolsExport. You can change it by issuing the following commands.

# Set it
Set-DbatoolsConfig -FullName path.dbatoolsexport -Value "C:\temp\exports" -Register

# Double check it
Get-DbatoolsConfig -FullName path.dbatoolsexport | Select Value, Description

help is separated

Something new that I like because it’s “proper” PowerShell: we’re now publishing our module with Help separated into its own file. We’re using a super cool module called HelpOut. HelpOut was created for dbatools by a former member of the PowerShell team, James Brundage.

HelpOut allows our developers to keep writing Help within the functions themselves, then separates the Help into dbatools-help.xml and the commands into allcommands.ps1, which helps with faster loading. Here’s how we do it:

Install-Maml -FunctionRoot functions, internal\functions -Module dbatools -Compact -NoVersion

It’s as simple as that! This does all of the heavy lifting: making the maml file and placing it in the proper location, and parsing the functions for allcommands.ps1!

Help will continue to be published to docs.dbatools.io and updated with each release. You can read more about HelpOut on GitHub.

breaking changes

We’ve got a number of breaking changes included in 1.0.

Before diving into this section, I want to emphasize that we have a command to handle a large majority of the renames! Invoke-DbatoolsRenameHelper will parse your scripts and replace script names and some parameters for you.

command renames

Renames in the past 30 days were mostly changing Instance to Server. But we also made some command names more accurate:

Test-DbaDbVirtualLogFile -> Measure-DbaDbVirtualLogFile
Uninstall-DbaWatchUpdate -> Uninstall-DbatoolsWatchUpdate
Watch-DbaUpdate -> Watch-DbatoolsUpdate

command removal

Export-DbaAvailabilityGroup has been removed entirely. The same functionality can now be found using Get-DbaAvailabiltyGroup | Export-DbaScript.

alias removals

All but 5 command aliases have been removed. Here are the ones that are still around:

Get-DbaRegisteredServer -> Get-DbaRegServer
Attach-DbaDatabase -> Mount-DbaDatabsae
Detach-DbaDatabase – Dismount-DbaDatabase
Start-SqlMigration -> Start-DbaMigration
Write-DbaDataTable -> Write-DbaDbTableData

I kept Start-SqlMigration because that’s where it all started, and the rest are easier to remember.

Also, all ServerInstance and SqlServer aliases have been removed. You must now use SqlInstance. For a full list of what Invoke-DbatoolsRenameHelper renames/replaces, check out the source code.

parameter standardization

Most of the commands now follow the following practices we’ve observed in Microsoft’s PowerShell modules.

• Piped input is -InputObject and not DatabaseCollection or LoginCollection, etc.
• Directory (and some file) paths are now -Path and not BackupLocation or FileLocation
• When a distinction is required, file paths are now -FilePath, and not RemoteFile or BackupFileName
• If both file and directory path needs to be distinguished, Path is used for directory and FilePath for file locations

parameter removal

-SyncOnly is no longer an option in Copy-DbaLogin. Please use Sync-DbaLoginPermission instead.

-CheckForSql is no longer an option in Get-DbaDiskSpace. Perhaps the functionality can be made into a new command which can be piped into Get-DbaDiskSpace but the implementation we had was 👎.

For a full list of breaking changes, you can browse our gorgeous changelog, maintained by Andy Levy.

book party!

In case you did not hear the news, Rob Sewell and I, are currently in the process of writing dbatools in a Months of Lunches! We’ve really excited and hope to have a MEAP (Manning Early Access Program) available sometime in July. We will keep everyone updated here and on Twitter.

The above is what the editor looks like – a lot like markdown!

If you’d like to see what the writing process is like, I did a livestream a couple of months back while writing Chapter 6, which is about Find-DbaInstance. Sorry about the music being a bit loud, that has been fixed in future streams which can be found at youtube.com/dbatools.

sponsorship

Since Microsoft acquired GitHub, they’ve been rolling out some really incredible features. One such feature is Developer Sponsorships, which allows you to sponsor developers with cash subscriptions. It’s sorta like Patreon where you can pay monthly sponsorships with different tiers. If you or your company has benefitted from dbatools, consider sponsoring one or more of our developers.

Currently, GitHub has approved four of our team members to be sponsored including me, Shawn Melton, Stuart Moore and Sander Stad.

We’ve invited other dbatools developers to sign up as well 🤗

Oh, and for the first year, GitHub will match sponsorship funds! So giving to us now is like giving double.

big ol thanks

I’d like to give an extra special thanks to the contributors who helped get dbatools across the finish line these past couple months: Simone Bizzotto, Joshua Corrick, Patrick Flynn, Sander Stad, Cláudio Silva, Shawn Melton, Garry Bargsley, Andy Levy, George Palacios, Friedrich Weinmann, Jess Pomfret, Gareth N, Ben Miller, Shawn Tunney, Stuart Moore, Mike Petrak, Bob Pusateri, Brian Scholer, John G “Shoe” Hohengarten, Kirill Kravtsov, James Brundage, Hüseyin Demir, Gianluca Sartori and Rob Sewell.

Without you all, 1.0 would be delayed for another 5 years.

blog party!

Want to know more about dbatools? Check out some of these posts ☺

dbatools 1.0 – the tools to break down the barriers – Shane O’Neill
dbatools 1.0 is here and why you should care – Ben Miller
dbatools 1.0 and beyond – Joshua Corrick
dbatools 1.0 – Dusty R
Your DBA Toolbox Just Got a Refresh – dbatools v1.0 is Officially Available!!! – Garry Bargsley
dbatools v1.0? It’s available – Check it out!
updating sql server instances using dbatools 1.0 – Gareth N

livestreaming

We’re premiering dbatools 1.0 at DataGrillen in Lingen, Germany today and will be livestreaming on Twitch.

Thank you, everyone, for your support along the way. We all hope you enjoy dbatools 1.0

💌,
Chrissy