Category: dbatools

A Monumental Migration to SQL Server 2016 – Part 1

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. With SQL Server 2008R2’s EOL looming, it was time to migrate production to SQL Server 2016.

Continue reading “A Monumental Migration to SQL Server 2016 – Part 1”

Copying Individual Tables with dbatools

@SQLMonkeyNYC asked on Twitter this morning:

Pat Phelan replied, suggesting that dbatools can do it, but after thinking on it for a bit and poking at a few functions, I realized that it’s not possible with a single function (yet). No worries. We can do it in three lines for now.

Explanation

  1. Grab the table from the source database and export the create script. I had to use -PassThru because otherwise, Export-DbaScript will create a file. Not the worst thing, but writing the file and the reading it immediately afterwards is a bit messy. Although now that I write that out, I suppose I could capture the filename that’s output and pass that to -QueryFile in the next step. Gotta love PowerShell – there’s always a few ways to do things.
  2. Take the CREATE TABLE script that we just exported from the source database and run it against the destination.
  3. Run Copy-DbaDbTableData to bring the data over from the source to the destination.

This is a really quick & dirty example where I’m copying between two databases on the same instance, but should give you the idea. You can copy across instances and copy multiple tables as well.

Update

Cláudio Silva (blog | twitter) has written a post that expands on the above code to include scripting out other objects related to the table being copied. The code above overlooks keys, constraints and more. Check it out!

PSPowerHour v1.0 Wrap-Up

The first edition of the PSPowerHour is in the books and it looks like it was a big success. This one was dbatools-heavy but I chalk that up to the dbatools community having lots of free time because we’ve automated so many of our tasks 🙂

Overall Impressions

I signed in about half an hour ahead of the webcast and was the first one there. Shortly thereafter, I was joined by Michael Lombardi (t, then Jess Pomfret (b|t) and Chrissy LeMaire (b|t). After ironing out a few glitches, we got everyone in the right place and kicked off the broadcast. Everything ran very smoothly, especially considering the number of people involved – Michael and Warren F. (b|t) did a terrific job of orchestrating everything.

While watching and listening to Chrissy, Doug, Andrew & Jess give their demos, I ran through my own in my head a couple times, adding and rearranging a few things as I observed how they were doing theirs. The big dilemma for me was whether or not to run the camera or exclusively screen share (I ended up going with the screen share only). Having not rehearsed my demo enough in the weeks leading up to the event, I was still not sure where to dip into more detail or dial things back and seeing what others were doing helped quite a bit. Having familiar faces & voices ahead of me in the queue put my nerves to rest.

I wasn’t able to watch the sessions after mine in their entirety due to family commitments. Joshua’s Burnt Toast module looks like it’ll be fun to experiment with and add some nice functionality to scripts (I got to see about half of his demo), and I’m really looking forward to catching a replay of Daniel’s demo of PowerShell on the Raspberry Pi – I didn’t realize that it had been ported already!

My Demo

I demoed Invoke-DbaSqlQuery and why one should use it over Invoke-SQLCmd – primarily for protection from SQL injection. Things didn’t go exactly the way I’d practiced; I ran short of time despite feeling like I rushed things and cutting back on some of what I had planned to say. The latter was in part because of the lead-ins from Chrissy, Andrew, and Jess. Because they did such a good job introducing dbatools, I was able to skip over it. But I was able to throw in a teaser for Matt Cushing’s (b|t) demo at the next PSPowerHour.

Running the demos inside a VM and screen-sharing just that VM made things easier for me as opposed to flipping between apps. My scripts will be available on GitHub along with the other presenters’ once the pull request is approved.

I achieved my goals:

  1. I did it
  2. I successfully demonstrated a SQL injection problem and explained why it’s so bad
  3. I demonstrated how to make database queries from PowerShell both more reliable and safer
  4. I learned about some new stuff that I desperately want to experiment with.

Next time around, I definitely need to rehearse more and get my timing down better but overall, I’m happy.

Check it out!

I Will See You in Seattle!

A few weeks ago, I teased good news.

One person hypothesized that I’m joining Microsoft (it seems to be the thing to do lately) and another jumped to the conclusion that I must be pregnant. Both creative responses, but not quite correct.

I’ll be at PASS Summit 2018!

So much to do!

  • Pick some sessions
  • Make my checklist of #sqlfamily I need to see
  • Find a way to pack lighter (I think the iPad will stay home this time)
  • Up my selfie game
  • Get back into shape for #sqlrun
  • Print up some more dbatools ribbons
  • Figure out the social media photo situation (see above, “Up my selfie game”)

If you’re attending Summit, let’s meet up! I’ll be on Twitter, Slack, and Instagram (@alevyinroc across the board) all week and roaming the convention center & various evening events so ping me there to find out where I am.

Because Summit starts on Election Day here in the USA, be sure to either get to your local polling place that morning or follow your state’s process to request & submit an absentee ballot. Every election is more important than the one before it (that’s the most political I’ll get on this blog, I swear).

Speaking: PowerHour, August 21st 2018

It’s official! I will be speaking at the inaugural PowerHour online lightning demo event on Tuesday, August 21st at 2200 UTC. I’ll be demoing Better, Safer SQL Queries from PowerShell.

If you’re working with SQL Server from PowerShell, either as a DBA, analyst, or anyone else running queries, you’ve probably used Invoke-SqlCmd. But depending on how you’re building your queries, this can be error-prone or a huge security exposure! With the help of the dbatools module, I’ll show you how to write and run these queries better and safer – and make them easier to work into your scripts to boot.

I’m excited to be a part of this – it’s been far too long since I’ve done a presentation. Please join us on the YouTube channel/stream next Tuesday!

PowerHour – Online PowerShell Lightning Talks!

Earlier this week, the PowerHour was announced. What is it? It’s kind of like a virtual user group. One hour, 6(ish) lightning demos (10 minutes or less), centered on PowerShell. All community-sourced and driven – anyone can submit a proposal for a demo and if accepted, you’ll be slotted into an available spot.

They’ve already set up a YouTube Channel so you can either watch live or catch up later on, and the whole deal is being organized and managed through GitHub. Got something you want to show off? Log an issue using the template!

I’ve submitted my first (but hopefully not last) proposal. Yep, it involves dbatools because that’s my jam.

It’ll be fun for speakers and attendees alike! You can even use your demo(s) for user group meetings or SQL Saturdays – anywhere lightning talk/demo spots are available. Several SQL Server community folks have tossed proposals in and with so many DBAs getting hooked on PowerShell, it’s a great way for these two communities to come together.

Quick Table Info Export with PowerShell

This week I had a user come to me asking about how fields were defined on a few tables he was using in writing some reports. Long story short, he’s been tasked with writing some new reports and updating existing ones, but he doesn’t have visibility to the database itself so he’s left with the “ok, let’s try this” approach and then reading error messages to debug when things go sideways. Very tedious.

I asked him for a list of the tables he was most interested in, and while he worked on that I set to work on (of course) a quick & dirty PowerShell script to collect the data he needed – field names, types, and whether they’re nullable.

With the help of dbatools and Doug Finke’s ImportExcel module, the resulting script to retrieve the requisite data and export it to an Excel workbook with one sheet per table is very short. Each table is on its own worksheet, with the header row bold and frozen so it stays in view while scrolling.

Line by line:

  • Fetch the list of tables from a file provided to me
  • Gather a collection of SMO objects for the tables from the server
  • Loop over each table and extracting the relevant data about each
  • Write the table’s information out to a worksheet named for the table in an Excel workbook

The only tricky part was fetching the field lengths for the nvarchar fields. I had to go spelunking into each field’s Properties property to get that. The result:

Export-TableInfo Results

But I can tell this isn’t disco enough for you, so I’m gonna put it right here.
– Justin Hammer, Iron Man 2

You can do this in a one-liner too. It’s not as pretty, but it’s quite doable and the resulting Excel file is identical.

End to end, I turned this around in about 20 minutes and the the requestor’s life got easier in a big hurry. Best of all, when he comes back with another batch of tables for his next set of reports, I can turn the request around in less than five minutes.

How to Help with dbatools Comment-based Help

I wrote a post over on the dbatools website about how to get involved with improving comment-based help

Working on the CBH is a great way to get started with the dbatools project, even (especially) if you’re not a PowerShell expert or MVP-level DBA. Getting everything clean and consistent in the CBH is an important step on the road to 1.0. Along the way, you’ll pick up on how dbatools is put together, discover functions that you can use in your day-to-day work, and get a feel for PowerShell best practices. You will learn from this experience!

Join us!

One Line of Code Is All It Takes

This tweet showed up in the dbatools Slack channel Friday afternoon.

My first thought was “huh? John (t) hadn’t kicked code in previously? I thought he had.” Once I was over that, I reflected a bit on what John wrote here, and was reminded of how I felt when I started helping out with dbatools.

It’s similar to Impostor Syndrome – I felt like I wasn’t doing much, small things here and there, in large part “just” documentation cleanup. The feeling that I was just throwing changes into the codebase just for the sake of making changes. It took me a couple of months and talking to several people before I understood that what I was doing was useful to someone other than myself and internalized what I was hearing.

Here’s the thing that I have finally come to realize. Every contribution to an open source project is beneficial, no matter how small it may seem. I’d heard this over the years but didn’t really understand until very recently.

John’s single line of code, no matter how it is that he got it into the dbatools codebase, made it better. His code will be executed by thousands of users of dbatools the world over.

Most open source project maintainers/leaders are looking for help. Get out there on GitHub and look up a project you use. Find an issue that’s tagged good first issue or help wanted. Hop over to Up For Grabs and find a project that needs a little help. If your PR isn’t immediately accepted, work with the maintainers to get it into a condition where it can be merged .

Single lines of code are welcome improvements to projects. Find yours.