Tag: dbatools

After the MVP Summit, I was excited to check out more code and features in all aspects of SQL Server, but since I’d had a quick conversation with Aaron Nelson and Chrissy LeMaire, I decided to tackle another part of dbatools.

BTW, they gave me a cool sticker.

I like using PoSh for some tasks, especially when I don’t have an easy way to do something in SSMS or want to run a task across a variety of instances. In this case, as I glanced through the September updates, I found a good one.

Get-DbaTcpPort

I don’t love the mixed naming, and I’ll get used to it, but I do love the autocomplete in PoSh. I can type this:

When I hit tab, I get this:

So I don’t need to worry about the case, but I can easily use Get-DbaTcpPort. This is one of those quick things I’ve done more than a few times as I troubleshoot connectivity with VMs or new instances. I have learned I can get this from the error log, but that’s a pain.

The main thing I can do here is pass in the SqlServer parameter and get a port. Here’s the ports for an instance on this machine:

There is a –Detailed option, which gives me the server name and cleaner results.

I was expecting the result of whether this was static or now, but it’s not appearing. I see the warning, but am not sure why this doesn’t work. Apparently this works with SqlWmi, but I’d like to know more to troubleshoot things.

When I hit one of my other instances, I got this:

At first I thought this was my remote admin connections setting, which was disabled, but that wasn’t it. I found instead that I’d left TCP disabled by default on this instance. Once I changed that and restarted the instance, it worked fine.

This isn’t something I’d use often, but it’s a really quick way to check on the status of an instance port if you need to. I’d urge you to give it a try and see what you think.

I had the chance to watch dbatools in action recently at SQL Saturday Cambridge. I watched a presentation from Chrissy LeMaire (@cl) that was impressive in how easy she made a number of DDL type changes between instances.

I decided to give some of these new cmdlets a try, since I was impressed with the work done. The first step in trying one is to get the dbatools module. If you go to the dbatools.io site and click the download link, you get great instructions.

The first thing I did was start a command line as an administrator, then run Powershell. From there, it’s a simple “Install-Module dbatools” command. As you can see below, you’re asked if you want to actually do the install. If you do, then things just work.

Let’s test.

First, I’ll type “copy-sql”, as shown here.

When I hit TAB, I get the first matching item. Hmm, I’ve never seen Copy-SQLAgentCategory. Not that I’d ever done this, but this is certainly going to be simpler than digging through DMVs or reading BOL.

Hit TAB again and get the next Copy-Sql command.

In fact, there are lots of Copy-, Export-, Test-, Get-, and more cmdlets. It’s a very handy list, and the types of things I’m stunned Microsoft just didn’t provide.

I’ll be digging in a bit more and looking at how some of these items work, as I suspect these will be invaluable for DBAs that need to manage and migrate various settings at any scale across systems.