If you work in an offline environment, you’re probably familiar with how painful it can be to install anything, including PowerShell modules like dbatools.
In the past, dbatools had no dependencies, so the installation process was somewhat straightforward — you could even download a zip from our GitHub repo. However, with the introduction of the new dbatools.library dependency, the installation process has become a smidge more complex. In this post, I’ll walk you through the steps to install dbatools (and with it, dbatools.
SQL Client Aliases allow you to connect to a SQL Server instance using another name. This is especially useful during migrations. Want your servers to connect to the new SQL Server without modifying connection strings within your application? Or what if you could use easy-to-remember names for your docker containers? SQL Client Aliases can help.
For the longest time, I managed these aliases using cliconfg for 64-bit applications (C:\Windows\System32\cliconfg.exe) or cliconfg for 32-bit applications (C:\Windows\SysWOW64\cliconfg.
Within dbatools, you may notice file sizes are pretty and human-readable.
That was some C# based magic created by Microsoft PFE and creator of PSFramework, Fred Weinmann. In the background, SQL Server often gives us different types of numbers to represent file sizes. Sometimes it’s bytes, sometimes it’s megabytes. We wanted to standardize the sizing in dbatools, and thus the dbasize type was born.
Usage This size type is cool because it looks beautiful, showing KB, MB, GB, TB and PB.
After nearly 10 months of work, early access to Learn dbatools in a Month of Lunches is now available from our favorite publisher, Manning Publications!
For years, people have asked if any dbatools books are available and the answer now can finally be yes, mostly 😊. Learn dbatools in a Month of Lunches, written by me and Rob Sewell (the DBA with the beard), is now available for purchase, even as we’re still writing it.
Recently, a colleague asked me to assist with the migration of some older, customized application databases to a SQL Server 2017 instance. Most of the migrations I perform are rather vanilla, but this one was a bit more involved.
Setup Imagine this scenario:
Source (APPSQL1) Dedicated SQL Server 2008 R2 failover clustered instance with a non-default collation (SQL_Latin1_General_CP1_CI_AI) Custom .NET application with an intense database containing a non-default collation (also SQL_Latin1_General_CP1_CI_AI), multiple CLR assemblies, and thousands of tables, views, stored procedures, functions Nearly 30 SQL Agent jobs, many of which had to be disabled over the years due to compatibility issues and scope changes Out-of-support for both Microsoft and the application vendor Destination (APPSQL2) Shared server SQL Server 2017 Default collation (SQL_Latin1_General_CP1_CI_AS) There was even a linked server in the mix, but our biggest concerns revolved around the changing collation and the Agent jobs, which were known to be brittle.
Some dbatools functionality that has not been talked about much on the blog are our internal configuration options. This functionality was added back in October 2017. Kind of like how in ADS or SSMS, you can go to File -> Preferences -> Settings or Edit -> Preferences to edit advanced preferences and options, you can do something similar in dbatools!
Since dbatools is command-line based, you can view/modify your current options using our various config commands.
Today’s article is part of T-SQL Tuesday. T-SQL Tuesday is the brainchild of Adam Machanic. It is a blog party on the second Tuesday of each month and everyone is welcome to participate.
This month’s T-SQL Tuesday is hosted by Tracy Boggiano (b|t), is all about Linux.
dbatools and Linux As a long-time Linux user and open-source advocate, I was beyond excited when PowerShell and SQL Server came to Linux.
Hi, I am Gareth N – a SQL Server DBA in the UK. I have started to blog over at ifexists.blog – today I want to share some information around how dbatools can cache connection information, and how to reset it.
dbatools is smart. It can do things in the background when you’re using the commands, like cache connections and also cache the results of those connections. This can help speed things up as it will re-use the existing object.
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.
Today marks the 30 day countdown to dbatools 1.0, which we will be debuting at Data Grillen in Lingen, Germany! These next 30 days are important and I’m writing to ask for your help.
Integration Tests We could still use a hand getting in those last few Integration tests. If you’re interested in adding a couple tests, I did a quick lil livestream on Twitch about writing integration tests for dbatools that could be useful to you.
I am Andreas Schubert and I am working as a Principal Consultant and Database Reliability Engineer for SQL Server & Azure for multiple national and international companies. My focus is on implementing and operating complex 24/7 SQL environments with tens and hundreds of servers and multi-terabyte databases.
Welcome to a quick post that should help you operate your SQL Server environment more consistently and reduce manual, repetitive work.
The Problem When you are running SQL Server Availability Groups, one of the most cumbersome tasks is to ensure that all logins are synchronised between all replicas.
The Situation Hey all, I am Andreas Schubert and I am working as a Principal Consultant and Database Reliability Engineer for SQL Server & Azure for multiple national and international companies. My focus is on implementing and operating complex 24/7 SQL environments with tens and hundreds of servers and multi-terrabyte databases.
With the multitude of environments that I am operating, it’s impossible to remember every server, every database or the multiple different ways they are interacting with each other.
Recently, dbatools contributor Jess Pomfret teamed up with Bert Wager for a super cool video about dbatools.
Check it out!
They also wrote accompanying blog posts too! Jess wrote a post titled dbatools with Bert where she talks more in-depth about Backing up your databases and changing your recovery model. Bert penned a post titled Automating Database Maintenance with Jess Pomfret and dbatools 😄👍
How incredibly cool! Thank you Jess and Bert for the awesome and fun video 🍕
This post originally appeared in two parts on my personal blog at flxsql.com and has been reposted here by request.
A bit over a year ago, I blogged about my experience migrating a test SQL Server instance from a VM to a physical machine with a little help from my friends. That migration went well and the instance has been running trouble-free ever since. But it’s small potatoes. A modest instance, it’s only about 5% the size of production.
In this age of many companies adopting the ideas of either DevOps or SRE (Site Reliability Engineer) roles, there is constant need for automation. Mundane tasks like collecting metrics, alerts, server and database asset info into a central database on which you can do reporting is now a common task that many DBAs or system admins are performing.
In SQL Server world, we are fortunate to have a vibrant community of PowerShell enthusiasts that have open sourced dbatools – a community driven PowerShell module to automate database development and administration.
Splatting in PowerShell makes code easier to read. Instead of typing a bunch of parameters allllll across the screen, you can use an easy-to-read hashtable or array. Argument splatting was introduced in PowerShell v3 and works with all PowerShell commands, not just dbatools.
Note: I’ve only used splatting with hashtables, as they allow me to be explicit about which parameters I’m passing. It appears that arrays would employ positional parameters, which is less wordy but leaves room for error.
Data compression is not a new feature in SQL Server. In fact it has been around since SQL Server 2008, so why does it matter now? Before SQL Server 2016 SP1 this feature was only available in Enterprise edition. Now that it’s in Standard edition data compression can be an option for far more people.
dbatools has three functions available to help you work with data compression, and in true dbatools style it makes it easy and fast to compress your databases.
We recently released a VS Code extension that lets you highlight terms and search dbatools.io, Microsoft Docs, Google, StackOverflow, DuckDuckGo, Technet or Thwack right from your code! It’s called search from code and you can find it in the Extension Marketplace.
Options and Settings By default, only Google, docs and dbatools are enabled but you can configure whichever providers you’d like in VS Code Settings.
At first, I was just messing around to see what it took to create a VS Code extension, but then I realized that I was actually using it and decided to share.
We’ve made even more progress in the past week! Here are some highlights of 0.9.520.
Non-Breaking Changes Aliases have been added for the changes, so these are not breaking changes:
Mismatched Copy commands have been renamed to match their corresponding Get command names (ie. Copy-DbaCentralManagementServer is now Copy-DbaCmsRegServer). Most parameters named Password have been changed to SecurePassword. They’ve always been a SecureString data type but this makes that clear. The parameters ExcludeAllSystemDb and ExcludeAllUserDb have been changed to ExcludeSystem and ExcludeUser, respectively.
Before I go into the breaking changes introduced in 0.9.518, I wanted to highlight Invoke-dbatoolsRenameHelper which is an awesome command that will help you with a vast majority of our renames. Using it is as simple as:
Get-ChildItem *.ps1 -Recurse | Invoke-dbatoolsRenameHelper
This command even takes care of a couple parameter renames like NetworkShare and UseLastBackups.
Breaking Changes It’s been a busy couple days! Here’s a list of our breaking changes