SQL Saturday Albany Slides & Demo Code

Thanks to everyone who came out to see dbatools for the Uninitiated at SQL Saturday Albany on July 20th, 2019. I had a lot of fun sharing dbatools with you and hope you’re ready to start exploring on your own!

The slides and demo scripts are available in my GitHub repository.

If you have any questions about the session, please feel free to contact me via:

T-SQL Tuesday #113 -A Database for the Great Outdoors

This month’s T-SQL Tuesday comes from Todd Kleinhans (blog | twitter) who wants to know what we’re doing with databases outside of work.

T-SQL Tuesday Logo

I’m curious- outside of work and learning, what do you personally use databases for? Tracking books you have, recipes, collections, etc? While it can be said using databases for personal use could be either overkill or a hammer in search of nails on the other hand, it is exactly what they are for- storing data.

Continue reading “T-SQL Tuesday #113 -A Database for the Great Outdoors”

T-SQL Tuesday #112 – The Cookie Jar

This month’s T-SQL Tuesday comes from Shane O’Neill (blog | twitter). He starts us off with this:

Dipping into the Cookie Jar is about when the going gets tough and you don’t think you can handle anymore, then you think back about your accomplishments and take some sustenance from them. You dip back into that cookie jar and use whatever energy that provides to keep going.

So tell me about a time when you had an accomplishment that can keep you going.

I. Love. Cookies.

I feel like this could describe my entire career. I cannot tell you how many times I’ve found myself staring looking at a new project, thinking “how on earth am I going to pull this off? This is nuts!”

I do exactly what Shane describes above – I just didn’t know there was a name for it. I have to remind myself “hey, you’ve said this same thing before, and it worked out. Breathe, slow down, and you will figure it out. You always do.”

For the really gnarly problems in the past, I’ve hidden away for a few days to just hammer away at code. So, what do I do when I get to the next one? I remind myself that if I do the same thing, I’ll get through.

Here’s the thing

In this business, you should never be doing the same thing over and over. Those are the things you automate so you can move on to more interesting problems.

There should be something coming along new every week. If there isn’t, make something new happen. If all you’re doing is things you’ve done before, things that you already know exactly how to do, you’re going to stagnate. You’re not going to learn. You’re not going to grow.

For me, it’s all about breaking the problem down in two ways:

  • Find parts that you know you know how to do
  • Find ways to experiment with the parts that you don’t know how to do

The first one is your cookie jar. Break that project down and tackle the stuff you have seen before. Maybe you’ve even got a time-tested script or checklist to get you through it.

On to the stuff that’s new. This is the exciting part! You won’t get it right on the first try. Everyone falls the first time. That’s how we learn. That stuff that’s old hat to you in the paragraph above? There was a time you didn’t know how to do that, either. And now look at you – you’ve mastered those parts.

An Example

Right, Shane wanted real examples, didn’t he? Time for me to go back to the well that is my 8000-database migration. Let’s break that down:

  • Upgrading from SQL Server 2008R2 to a newer version? High-level, I’ve done that before. Had a sysadmin doing most of the heavy lifting, but I was right there with him for most of it. 🍪
  • Oh, but wait! I worked on an upgrade from 2000 to 2008R2 before that. We kept the whole workflow document and checklists around from that so we built on that. 🍪
  • I migrated from 2008R2 to 2008R2 before using dbatools. That went pretty well. 🍪
  • When I did that previous migration, I successfully copied things in groups, I didn’t have to do it all at once. Just like the big migration to 2016. 🍪
  • As an organization, we’d previously moved our 2008R2 setup to new server hardware, with minimal issues. We learned from the breakage we had and documented those lessons. 🍪
  • When I stood up the new test 2016 instance, I copied everything but the databases from the 2008R2 test instance and that worked out OK. 🍪

So, what was I left with here for the big migration? I’ve been through version upgrades. I’ve been through scripted migrations. I’ve copied everything except databases from 2008R2 to 2016. We’ve changed hardware before. So many cookies!

The only part that I was really worried about was attaching the databases themselves. As a result, I was able to test that out a couple dozen times to make sure it would work properly. And no, I didn’t get it right on the first attempt – it took probably a dozen attempts before I got the attach script working properly. But I knew it would be OK, because I’d worked on plenty of other projects where the first attempts were unsuccessful as well.

Keep at it

You didn’t give up in the past, and you found success. So keep plugging away and you’ll keep succeeding.

Speaking: Rochester SQL Server User Group March 2019

Probably a bit late getting this posted but I will be speaking at the March 5, 2019 meeting of the Rochester SQL Server User group (RSVP link). I don’t have a great title or abstract for the talk (yet!), but here’s the gist:

The relationship between DBAs and developers has a long history with challenging moments. Some developers see DBAs as roadblocks. Some DBAs see developers as rogues bent on destroying the database server’s performance. I’ll be presenting both technical and non-technical tips and tricks to help build a better understanding between developers, DBAs, and SQL Server.

It’s been four years since I’ve spoken at a meeting. If this talk goes well, I’ll keep refining and start submitting it for SQL Saturday events.

One Peril of Database Proliferation

By now many of us have upgraded from SQL Server 2008R2 and we’re on the “regular Cumulative Updates” train now. For the rest, it’ll (hopefully) happen soon. And since we want a long runway, we’re upgrading to SQL Server 2016 or 2017. Current software! New features! Mainstream support! But…there’s a catch.

Continue reading “One Peril of Database Proliferation”

Appearance: SQL Data Partners Podcast #161

Carlos Chacon (twitter) was kind enough to have me back on the SQL Data Partners Podcast to talk about my experiences with managing 8000 databases on a single instance and upgrading to SQL Server 2016. He, Kevin Feasel (blog | twitter) & I had a great conversation in which I may have gushed a bit about dbatools. Then we wrapped up with the SQLFamily questions as we didn’t do them on my previous appearance last year.

Check out SQL Data Partners Podcast Episode 161, Migrating/Upgrading 8000 Databases.

Tidier Powershell Scripts with Default Parameter Values

I was recently working on a PowerShell script to set up some new databases for my users and found myself writing the same things over and over again.

Invoke-DbaQuery -SqlInstance MyServer -Database $SrcDB -Query "select field1 from table...";
Invoke-DbaQuery -SqlInstance MyServer -Database $DstDB -Query "update table...";
Invoke-DbaQuery -SqlInstance MyServer -Database $DstDB -Query "insert into table...";

By the 4th Invoke-DbaQuery, I found myself thinking “this repetitive typing kind of sucks.” Then I remembered Chrissy LeMaire’s segment in the first PSPowerHour where she talked about default values, and her accompanying dbatools blog post. Most of the blog posts and demos of this feature focus on using it from the command line, so I had overlooked the fact that I could use it from within a script as well, and even change the values when looping.

As it turns out – it works inside scripts and functions as well, and can make them a lot easier to read. And you’re not limited to the default parameters every time you call a given function; you can override the defaults by specifying the parameters when you call it.

$PSDefaultParameters is a hash, and the name/value pairs take the format of 'FunctionName:ParameterName' = ParameterValue.


I set the destination database as the default for Invoke-DbaQuery because there will be a number of additional queries I need to execute against that database. In Step 1, I’m using the parameter defaults for both Databaseand SqlInstance, so I don’t need to specify them when calling the function. In Step 2, I need to override only Database, so I’m just specifying that one parameter.

Word of warning: Because $PSDefaultParameters is a hash, you might want to “build up” defaults over the course of a function, script or session. In that case, get in the habit of appending to the hash so that you don’t overwrite the defaults you’ve previously set.

If you’re already splatting parameters, this is a feature you can take advantage of to take the next step in tidying up your scripts and making them easier to read & manage.

Things I Wish I Knew When I Started

Way back in August, Matt Cushing (blog|twitter) was preparing to teach and asked for a list of “what do you wish you’d known when you started” items that he could present to his students. I threw a barrage of Twitter direct messages at him and he incorporated much of it into his post, but I thought it was worth posting here as well.

Continue reading “Things I Wish I Knew When I Started”