Voice of the DBA

Tag: dbatools

Hands off dbatools updates

I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

One of the downsides of an active project is that updates happen often. I know when I’m working with new versions of Redgate tools, they’ll deliver new builds to me every day if I want them (I usually don’t).

With dbatools, I find a similar issue, though at a slightly longer scale. I’ll see a tweet or post about a new cmdlet, only to find out that I don’t have the newest version. As a result, I need to update my modules, which means I need an elevated command window, which is distracting.

I saw a post about someone that built a SQL Agent task to update their server, which I was thinking to do until Anthony Nocentino pointed out Watch-DbaUpdate.

Install-DbaUpdate

There’s actually a cmdlet that does what I want. It’s Install-DbaWatchUpdate, and it’s designed to setup a scheduled task that calls Watch-DbaUpdate and updates the module. That’s what I want, so I decided to check it out.

First, I looked at Scheduled Tasks. In Windows 10, this is the Task Scheduler from the Start menu, and surprisingly, lots of software uses this. Google, Adobe, Microsoft, etc.

I then tried the –WhatIf parameter for Install-DbaWatchUpdate, but it didn’t work. I know this is a fun cmdlet, but this should work.

In any case, let me just run it. I don’t get much detail back.

If I refresh scheduled tasks, I see a dbatools version check.

If you look at the details, this is triggered at midnight and runs once an hour. The PoSh executable is called and it in turn calls the Watch-DbaUpdate cmdlet, which checks for a new version and should let me know if there are updates. Since I’m at the latest version, I’ll have to see if this works.

So far, so good.

I even tested the Uninstall-DbaWatchUpdate and it does remove the task.

May 25, 2018
Checking Tempdb with dbatools

I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

In SQL Server 2016, the setup program was altered to better configure tempdb at installation time. This was in response to the observation that few people actually make any changes to the default configuration, which was suboptimal in SQL Server 2014-.

Going through and checking all of the configurations you have isn’t easy, and isn’t necessarily the type of work that anyone wants to do. dbatools makes this really easy and quick with Test-DbaTempDbConfiguration.

Using this cmdlet is easy. I’ll call this with an instance and get results of a number of checks that are useful for your tempdb configuration:

This isn’t necessarily easy to read, so let’s add a Format-Table.

That’s not great, as I’m missing the CurrentSetting field. I’ll add a SELECT and include the fields I want. I can even add multiple instances in here:

Now I can scan through here, looking to see if my settings have deviated from the recommendations and best practices. This could easily be used to filter the results for items that don’t match, save the results as a CSV, and you now have a picklist of items to work on as you find time.

dbatools is an essential tool for me. I’d urge you to download the module and experiment with the cmdlets.

April 20, 2018
Remove-DbaBackup with dbatools
I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

One important item for any system administrator to manage is the removal of old files that aren’t useful. I know most of us hate to delete data, but there are log files, backups, and more that will clog up a drive over time if they’re not managed. I’ve had SQL Servers stop because old copies backups filled the disk and I’ve had IIS servers start throwing errors because 2 years worth of logs were on stored on the C drive.

Maintenance plans had a way to remove files and we have xp_delete_file, but there are limitations to ensure that only backup files are deleted. I think those are silly, but it wasn’t my decision to include restrictions, and I don’t get a vote on future changes.

In any case, dbatools has a cmdlet that can help: Remove-DbaBackup. I was interested to see if this worked on it’s own or had restrictions, but it seems to work wonderfully for me.

Required Parameters

Most cmdlets will allow quite a few parameters to be optional. In this case, however, there are some requirements. First, you need a path for the backup files. That makes sense and no big deal.

However, you also need a retention period. You can’t skip this, as if you do, you get a prompt.

The retention periods aren’t obvious, but not that hard to remember. There’s a numeric counter and a one character time period item. They are:
- h for hours
- d for days
- w for weeks
- m for months
That’s it and not a big deal, though for testing I need to play with my system clock a bit.

In any case, after this parameter, you need a backup extension. This is the file extension, without the period. You can put in anything, which is cool.

There are some other params, but not required.

For testing, I copied some backups and then changed some extensions. As you can see, my test folder has SQL backups with various extensions I’ve encountered as well as a few text documents.

If I run Remove-DbaBackup with some options, I’ll see what will happen with the –WhatIf parameter. I see plenty of files being marked for deletion as long as I have the right extension.

This is handy, and it makes perfect sense when you read it. This is exactly the type of maintenance job that you want to set up on a server to remove old files. I don’t know that I’d use this for general cleaning of files that I might need soon for a backup, since I always want to be sure that I have a good backup before I remove old files, but for managing very old files, this is helpful.

And, a little scripting logic would show you how to find the date of the most recent full backup and then remove files older than that. Or maybe older than the last two fulls.
April 13, 2018
dbatools is Growing Up

The dbatools.io project is growing up rapidly. I went to check something the other day and I was amazed at the command list. This used to fit on one page. Now it’s not close. Here’s the first page:

In fact, it doesn’t fit on two pages. Here’s the part below the fold.

It’s crazy how quickly this project has grown, and how many cmdlets are available to help you not only migrate objects and settings between instances, but perform useful operations on your systems and test or change their configuration. I’m starting to think that this needs to be required for most people that manage systems. The ways that you can easily manage your systems in a programmatic manner has dramatically changed with this module.

I was surprised the other day when I saw a blog post for a cmdlet I didn’t know existed. I went to try it on my system and it wasn’t available. I’d updated the module the previous week, but apparently there are new changes on a regular basis. I might need to update my module more often, and there’s a cmdlet to help me do that!

Those of us that have a lot of experience with SQL Server might find some of these tasks easier in T-SQL. I certainly spend more time inside the platform than out of it, but there are definitely advantages to using PowerShell to enforce consistency and easily work with multiple machines is nice. These utilities are often like scripts or procedures I’ve written to handle simple tasks in a wrapper-like fashion. dbatools do that from PoSh, and allow me to easily string together disparate comands. I won’t give up T-SQL anytime soon, but I’ll do more with dbatools than I might do with PoSh otherwise.

If you haven’t tried this project, you ought to. This will make it easier to learn PoSh and work with SQL Server. Maybe you’ll enjoy it enough to built your own module and contribute to the project. There are still holes, especially in the SSIS area, though I expect new cmdlets to appear any day.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

March 29, 2018