Hand-Crafted SQL and PowerShell from New York's Finger Lakes
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:
I am pleased to announce that I will be presenting at SQL Saturday #855 in Albany, NY on July 20, 2019. Join me at 2 PM in room LC05 for “dbatools for the Uninitiated“
Continue reading “SQL Saturday Albany NY 2019”Announced at DataGrillen 2019 today, the amazing dbatools PowerShell module has officially released version 1.0. This is a tremendous milestone for the best Open Source project built for data professionals.
Continue reading “dbatools One Point OH YEAH!”In my previous post, I outlined the preparations we undertook to migrate a large SQL Server 2008R2 instance to SQL Server 2016. This post details migration day.
Continue reading “A Monumental Migration to SQL Server 2016 – Part 2”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”@SQLMonkeyNYC asked on Twitter this morning:
#sqlhelp Does anyone have a product that will back up a single table and restore it to another database? Thanks in advance!!
— Mr Sunshine aka Gandalf!! (@SQLMonkeyNYC) November 14, 2018
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.
-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.CREATE TABLE script that we just exported from the source database and run it against the destination.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.
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!
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 🙂
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!
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:
Next time around, I definitely need to rehearse more and get my timing down better but overall, I’m happy.
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!
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).
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!
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.
