Sunday, June 16, 2019

dbatools 1.0 and how to make yourself a SuperHero

dbatools 1.0

Have you ever needed to migrate a SQL Server database from one version to another and dreaded all the T-SQL you will have to write and debug?

What about migrating 30 SQL Server machines with 100's of database on each machine?

I feel your pain.

How about using Log Shipping to migrate the data over time, then cut over with little downtime.

# Also supports multiple destinations!
# Oh, and has a ton of params, so use a PowerShell splat
 $params = @{
    Source = 'sql2008'
    Destination = 'sql2016', 'sql2017'
    Database = 'shipped'
    BackupNetworkPath= '\\backups\sql'
    PrimaryMonitorServer = 'sql2012'
    SecondaryMonitorServer = 'sql2012'
    BackupScheduleFrequencyType = 'Daily'
    BackupScheduleFrequencyInterval = 1
    CompressBackup = $true
    CopyScheduleFrequencyType = 'Daily'
    CopyScheduleFrequencyInterval = 1
    GenerateFullBackup = $true
    Force = $true

# pass the splat
Invoke-DbaLogShipping @params

# And now, failover to secondary
Invoke-DbaLogShippingRecovery -SqlInstance localhost\sql2017 -Database shipped

dbatools is an expansive and extremely flexible PowerShell toolkit that leverages  the scripting abilities within PowerShell to manipulate SQL Server objects.

Do NOT let the 1.0 fool you. This code base has been building incrementally over the past few years, with many contributors writing code and creating Pester tests.

There are modules for every common operation that a SQL Server DBA will have to perform.

This toolkit contains over 500 modules to help you get your work automated and projects completed.

My favorite modules are the discovery modules, to help you find details about your SQL Server instance.

Get-dba* in the Search box will show you all the cool discovery modules. The naming convention of the module will give you a hint of what it will do.

There is good documentation in the code and on the dbatools blog.

Support is the backbone of this tool, where you can chat or email directly to the developers, contributors, and power users that will give you tips, code samples, and help you debug any roadblock you may encounter.

To get started, try this Getting Started web page.

On Slack you can connect with the dbatools family and just soak in all the great chats and posting that relate to the tool, PowerShell, and SQL Server. The Slack channel is #dbatools and it is a great place to hang out and meet really awesome DBA's and developers from around the world.

The code is community driven, open source, and free.

What environments does dbatools support?

SQL Server 2000 – 2017
Express – Datacenter Edition
Clustered and stand-alone instances
Windows and SQL authentication
Default and named instances
Multiple instances on one server

We love new contributions of code, so please join up!

If you want to file an issue with the code or have a suggestion, go to

Chrissy LeMaire (t) @cl is the ring leader of this awesome group, and she can always point you in the right direction for help, support or encouragement.

No comments:

Post a Comment