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!
In the course of testing a major upgrade, one of my users in Accounting happened upon a problem with one of her scenarios. The web app we were working on had a habit of “locking up” on people when loading some pages; in most cases, it was because the server was pushing a huge HTML table to the client, and most web browsers struggle when faced with several megabytes of markup and thousands of rows in a single table. Digging into the source code for the page and SQL Profiler (yes, I know Extended Events are a thing), we were able to isolate the query.
The query started innocently enough.
SELECT TOP 301 Field1, Field2 FROM Ledger1
This system uses TOP every now and then trying to limit the number of records it gets back (and the developers always seem to use the arbitrary 301 – I’m guessing some degree of cargo cult programming going on). I’d prefer a well-constructed WHERE clause to limit the result set but beggars can’t be choosers when working with legacy vendor code.
What I found odd was that the Ledger1 table didn’t get a lot of traffic – with the WHERE clause in use (omitted here for brevity), you’d only get a handful of records, maybe a dozen at most. The query continued…
SELECT TOP 301 Field1, Field2 FROM Ledger1
SELECT Field1, Field2 FROM Ledger2 ORDER BY Field1 DESC
Now we’re onto something. In comparison to Ledger1, Ledger2 is huge – at least one order of magnitude larger. And with that same WHERE clause, you could easily pull back a couple hundred to a couple thousand records from Ledger2.
Where did the developers go wrong? In a query with UNIONs, you really have multiple independent, distinct queries whose results are getting glued together before being sent back to the client. In this case, the TOP 301 was only applied to the first subquery. I suspect that the developers’ intent was to limit the entire result set to 301 records, but never tested with enough data in either table to know that this was working properly.
To properly limit the results of a UNION query, we have to wrap the thing in parenthesis and treat it as a subquery.
SELECT TOP 301 Field1, Field2 FROM (
SELECT Field1, Field2 from Ledger1
SELECT Field1, Field2 from Ledger2
While this is better, I’m not a huge fan of it because it’s still pulling a lot of data back from Ledger2 that just gets thrown away. As I said above, I’d prefer to have the results constrained by the WHERE clause as a well-designed index can help out.
In this case the user was refunding payments, and it’s very likely that payments made two years in the past wouldn’t normally be refunded. Perhaps a business policy could be implemented here to limit the impact on the system by allowing us to only look back 6-12 months for records, instead of searching the full account history. There isn’t always a purely technical solution; it can be beneficial to speak with the business and end user to ask “do you really need this, or can we change our processes so cover 99% of scenarios and handle the edge cases on an individual basis?”.
I am very happy to announce that SQL Saturday returns to Rochester, NY on March 24, 2018. This is the Flour/Flower City’s seventh SQL Saturday and SQL Saturday #723 overall. This is a little earlier than in years past due to the scheduling of other SQL Saturdays as well as the availability of our venue and key people, and I can’t wait to see how this change works out.
SQL Saturday is a free one-day event for anyone working with the Microsoft data platform. Whether you’re a DBA, developer, work in PowerBI, work with people who spend their day with data, on-premises or in the cloud, or are just curious about it, this is the event for you.
This tweet showed up in the dbatools Slack channel Friday afternoon.
Just did my first Pull Request to "contribute" to @psdbatools. Granted, the code change was a single line of code that was spoon-fed to me… but it's still my 1st PR ever! Life. Changed. Special thanks to @cl for the spoon, and @wsmelton for the PR assistance. 🙂 #dbatools
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.
I met Allen in the winter of 2011. Allen was a consultant and my employer engaged with his firm for help with a SQL Server migration/upgrade and DBA services. Allen introduced me to PASS, the SQL Server community, and helped me discover a passion for the field. I had previously dabbled with SQL Server, and tried to pick up what I could on my own and from random internet searches, but I spent a lot of time fumbling around and learning bad habits. Allen got me pointed in the right direction and drew me into the #SQLFamily. He is one of the first people I look for every time I go to SQL Saturday Cleveland or PASS Summit.
A little over a year after I met Allen, Kendal joined his team and I started working with him. Kendal became our primary DBA and when we had difficulties with SQL Server (or, more accurately, our application was giving SQL Server agitation) Kendal was our first call. I worked on a number of issues with Kendal; he knows SQL Server inside and out, and I was deeply familiar with the application that was giving it trouble so we made a good combination and I learned a lot about troubleshooting/debugging and some creative ways to resolve/work around application issues that can’t be fixed at the application layer. Kendal even hosted my first-ever blog post! Every now and then, something pops up on Twitter and we reminisce about the good ol’ days.
I met Matt when I started attending my local user group meetings (or maybe it was at PASS Summit 2012, after which I started going to the local meetings). He somehow talked me into helping him run the local SQL Saturday, then talked me into taking over running SQL Saturday. As I described in my post earlier this year about how I became a DBA, he gave me that final nudge to say “yes, I want to be a DBA now. It’s time to make it happen.”
I met Chris through our local user group and it turns out we know a lot of the same people despite never having worked together; there aren’t a lot of SQL Server folks in town! Chris took a new job around the same time I changed jobs earlier this year so we’ve been out for quite a few lunchtime runs and walks where we discuss various technical (and non-technical) challenges we face at work.
Chrissy LeMaire, Rob Sewell, Constantine Kokkinos, Shawn Melton
Newcomers to this list. This group (and really, I can’t single any one of them out) has welcomed me into the dbatools team and been very patient with me as I’ve learned GitHub, learned how to work within the goals/parameters of dbatools, and let me fire off a lot of pull requests just to do things like spelling corrections. It’s a whole new piece of the SQL Server community they’ve helped me get exposure to, and at Summit this year I was shocked when I had people coming up to me saying “hi, I wanted to meet you” or “hey, you work on dbatools? I need some help…” My Impostor Syndrome has been working overtime here; it’s been a few months now and I still can’t believe they let me work on this stuff. I’ve been honing my own PowerShell skills, learning more about dbatools, and reinforcing what I thought I already knew by reading so much of their code as I work on my various fix-ups – and even making real code changes!
To everyone named here, and the whole SQL family, thank you. If you have ever attended or organized a SQL Server-related event – SQL Saturday, Summit, User Group meeting, webinar, or blog party – you have had an effect on me. My career would not be where it is today without you.
Continuing my series of posts about my PASS Summit 2017 experience. This is about gadgets/gear I brought & software I used, the gadgets I saw around the convention center, and then a little about the hardware & software that was demoed.
I only brought three gadgets, plus their support items:
iPad Air 2
Apple Watch Series 3
4-port Anker wall charger
Anker 15K mAH battery pack
2x Lightning cable (for the iPhone & iPad), 1x Micro-USB cable (to charge the battery pack), 1X Apple Watch charge cord
For the amount I used the iPad, I wish I had left it home. I only used it to watch a couple episodes of Stranger Things on the plane. The iPhone astounded me with its battery life. After charging overnight, it still had 30% left on it at 4:30 PM, even with heavy usage. Even better, it charged off the Anker battery pack fast– I was back up to 90% or better in an hour or less, much faster than I’ve experienced with other devices. This allowed me to top up the battery in the final session/event each afternoon and roam the city for the evening, comfortable that I had enough juice to last me until I returned to the hotel.
Throughout the week, I used Day One to jot down important things – people I met, conversations I had, thoughts that came to mind, photos that I didn’t want to lose to the depths of my photo library, etc. I could have used paper and pen, but these were things I didn’t want to lose to my terrible handwriting. The other benefit of using Day One is that it records metadata about each entry – location, the current weather, how many steps I’d logged to that point in the day, even tags for categorization. Plus, it’s secured by TouchID. All told, I recorded 38 notes from the time I got to the airport on Monday to the time I left Seattle on Friday (although the first one, in which I mused about the TSA, is not fit for publication).
Because I’m skeptical of free open WiFi especially in such a large gathering, I bought a 1-week plan for Encrypt.me for protection.
Slack was used in several sessions and pre-cons throughout the week to drive Q&A – Brent Ozar & Erik Darling used it for their pre-con, the dbatools crew used it for theirs, and it was used for the PowerShell panel discussion as well. There was general chatter on Slack as well, but I think a lot more was going on on Twitter.
I set up an IFTTT recipe to capture #PASSSummit tweets to a Google Drive spreadsheet and it collected over 10K tweets over the week; someday I’ll go back through them to see what I missed (I set one up for #SQLFamily too, but haven’t reviewed that one yet) and make the full dataset available as a download. Twitter seems to be better/more manageable for getting notifications than Slack.
Late on Thursday, I spotted this tweet but failed to note who wrote it (had to search just now):
Pro tip: Microsoft Lens takes great, framed & searchable snapshots of presenter slides. In sure there are others. #PASSsummit
If you are anywhere you find a need to take a photo of a whiteboard, projector screen, or document, get this app. Apple may have introduced document scanning in iOS 11 but this is several levels above and it has earned a permanent spot on my phone. It automatically straightens/de-skews images and makes them very readable, then OCRs them. It even works for business cards and integrates with a number of apps/services already on your phone (OneNote, Photos, Mail, etc.). Here’s an example:
The one place Lens falls short (in my experience thus far) is with color images, at least in Whiteboard mode. But if the content is text and line art, it’s quite useful.
Despite my terrible handwriting, I still like taking notes at events like Summit (or even in meetings at the office) with pen and paper as I find that writing helps cement the ideas in my mind. My weapons of choice are the Uniball Jetstream 2 pen (seems they’re no longer producing this one, or maybe I misremembered the model; the Jetstream RT is hopefully similar) and Staples Sustainable Earth 9 1/2″ x 6″ spiral-bound notebook. The notebook has a couple pockets for stashing stuff and the covers are rigid enough that they protect the pages and I don’t have to put the notebook on a table to write.
My Eddie Bauer sling backpack got over-stuffed in a hurry. Too much swag plus my water bottle and other daily carry stuff. I need to find a replacement for it but don’t want to give up the convenience/comfort of the single-shoulder sling style. On the bright side, its obnoxious orange color makes me easy to spot from across the convention center.
Around the convention center
I didn’t see a lot of people walking around with iPads or Android tablets. Maybe when the iPad Pro & Apple Pencil become more widespread we’ll see people taking notes on them instead of paper. I did see a number of Microsoft Surface computers amongst attendees, and a few laptops. Lugging a full laptop around all week sounds like a drag (not to mention the battery anxiety) but if I had a well-spec’d Surface and large enough backpack, I might consider taking it.
The WSCC WiFi seemed shaky on Tuesday, but settled down and worked well for the remainder of the week. This seems to be the pattern at Summit, in my experience.
There was a common thread running through almost every session I attended as well as the Tuesday meetings, of the projectors blinking on and off for no apparent reason. It wasn’t any one presenter’s computer, nor was it any one room. It was bizarre but after a while, I think we all got used to it.
New stuff demoed
In Wednesday’s keynote, Microsoft ran several PowerBI (and PowerBI-adjacent) demos, but I didn’t find them particularly captivating. They were quite brief, and didn’t get into the technical work that made it possible. The HPE ProLiant DL380 Gen10 was shown off, boasting high performance thanks to persisted memory. All these demos were very shiny, but very brief. This is a technical audience – give us some more depth here, please.
The item that I found most interesting spent about 5 seconds on screen – a desktop app that looked like someone stuffed SQL Server Management Studio into Visual Studio Code, then a quick slide where the name SQL Server Operations Studio was revealed, along with a note that it’s a cross-platform GUI for managing SQL Server. Ever since SQL Server for Linux/macOS was announced, I’ve wanted this, and they skimmed over it in 5 seconds! Apparently there was a demo session at the Microsoft booth in the Exhibitor Hall later, but only advertised via Twitter; I didn’t hear about it until Thursday.
I registered for Summit about a month before getting actively involved in the dbatools project, so when I saw the team was running a pre-con and I was going to meet them, I was pretty excited. It was amazing getting to meet and hang out with Chrissy, Rob, CK, Shane, Jess, John, Shawn, Aaron, Ben, Kiril, Shane, and Drew (sorry if I forgot anyone!), even if it was only for a moment.
But I’ll have another post about the people of Summit. This one’s about dbatools being talked about all over Summit and my experience with that as a member of the team. I’m certain there’s a heavy amount of confirmation bias here, but dbatools seems to have caught fire in the SQL Server community. And with good reason!
I was able to hand out about 300 of the dbatools fan ribbons I brought with me; half went to pre-con attendees, and the rest were handed out on the conference center floor at random. Sitting at the PowersShell table at the BoF lunches, people would join us and say “hey, I’ve heard about this dbatools thing but haven’t had a chance to learn it yet.” People would see mine and ask for one as they’d heard about the project and even used it themselves.
Rob Sewell talked about it at the SentryOne booth. I heard on Twitter and around the conference center that dbatools was getting mentioned in a number of speakers’ sessions, even the ones that didn’t advertise it in their abstracts. There was a panel discussion about PowerShell in general, spearheaded by the key dbatools team members and of course dbatools was talked about there. But the star of that session was Ken Van Hyning, aka SQL Tools Guy (t), talking about the roots and evolution of many of the tools we use and where he sees them going. He also hold us how we can impact the direction of the current tools and make pitches for new ones. Key takeaways:
Cross-platform, open-source where possible seems to be the way of the future
There’s a lot of work to be done to migrate the infrastructure and tooling around the tools to get the existing ones there (I think this is why we’re seeing new tooling come out instead of direct ports)
The squeaky wheel gets the love, so make your voice heard on Microsoft Connect and Twitter!
After all the “I can’t believe this is happening!” moments through the week, the final session on Friday was the icing on the cake. I was in Carlos L Chacon’s session Measuring Performance Through Baselines and dbatools popped up on one of his slides.
Later, Carlos demonstrated a couple of functions, Get-DbaAgentAlert and Get-DbaUptime. The latter sounded familiar, so I jumped on Github and checked the history to confirm. Yep, it’s one of the functions I’d done some (non-CBH) work on. Which means that code I wrote was executed in a PASS Summit presentation! Yes, it’s a small thing and I’m the only person who even knew it as it was happening, but it happened. Which is pretty awesome.
PASS Summit 2017 is only a week away and to say I’m excited about it would be an understatement. This will be my third trip to the epic gathering of SQL Server and Microsoft data platform professionals and each time, it gets better and better.
Not only is this a time for learning and networking, it’s a giant #sqlfamily reunion. The list of people I’m excited to see is long, both people I’ve known for a while and new friends I’ve only spoken with online.
As a “Summit Buddy” this year, I’ll be helping four Summit first-timers navigate the week. We’ve already been in contact via email and we’ll be meeting for the first time at the First-Timer Orientation & Speed Networking event late Tuesday afternoon. We’ll check in a few times through the week, probably over breakfast or lunch and hopefully see each other in the Community Zone and sessions as well. I’m hopeful that they’ll enjoy Summit as much as I do.
I’m still working out my session schedule. So many great sessions to choose from! My pre-conference and after-hours schedules are shaping up nicely though. For the first time ever, I’m attending as a User Group co-leader and SQL Saturday Organizer, so I’ll be in meetings for those on Tuesday.
Events to find me at outside the normal Summit hours:
One Summit tradition I’m undecided about right now is SQL run. It’s no longer an official event but people still do it. I’ve got a sore leg right now and if I can’t get it fixed I’ll pass on the running. Seattle is a nice place to run, especially by the waterfront. But it’s hilly.
As with every Summit, the schedule is jam-packed and it’s going to be exhausting. I can’t wait.
I’ve been a proponent of dbatools for close to a year now and even contributed to the project, but surprisingly haven’t been a heavy user of it. Mostly due to a lack of opportunity. I’m aware of many of the functions by virtue of working on the built-in documentation and following the project and presentations about it.
So when the need arose to move a development/test instance of SQL Server from a VM onto a physical server, I knew exactly what I wanted to do. I was warned that the contents of this instance had been moved once before and it resulted in over a week of work and a bunch of trouble. I can’t speculate on why this was as I wasn’t there to see it, but I wasn’t going to let that happen on my watch. So, with equal parts hubris and stubbornness (and a dash of naïveté), I dove in. We have the technology. We will migrate this thing.
The advertising for Start-DbaMigration makes it look so easy. Source, destination, your method of moving the data, and you’re done. Right? Well, sure – in a small, controlled sandbox. This one was neither. About 150 databases. Two dozen Agent jobs. User account cleanup. Different drive letters and sizes. And when it was all over, the server name, instance name, and IP of the new box had to match the old one so that we didn’t disrupt production or the developers.
Of course we’re going to rehearse this. But with the destination being a physical machine, I didn’t have the luxury of rolling back a snapshot each time, or restarting from a golden image. And I couldn’t do everything because it wasn’t an isolated environment – I couldn’t test all the Agent jobs (don’t want emails going out in error) and couldn’t reconfigure the IP or server name. Which meant that my script had to clean up any artifacts from previous runs before doing the migration. Each time.
I also wanted to bring the new instance up in a controlled fashion as opposed to just moving everything and letting it go, so that I could check things out before letting them break. I also had to work in checkpoints so the network/server admin could do his pieces. Which meant that after the migration, everything on the old server had to be stopped, and Agent jobs on the new one disabled (but with a record of what was enabled/disabled on the source, so I could replicate it).
I rehearsed what I could about a half-dozen times. Each time through took about 4 hours (having multiple tests helps build confidence in your elapsed time estimates), primarily because of the amount of data that had to be moved (about 700GB). Each time, I found another tweak needed. Maybe not entirely necessary, but I was out to prove something. I didn’t want this migration to be “good enough, a little rough around the edges” – this had to work right, right away.
This is truly standing on the shoulders of giants. Without the thousands of person-hours put in by Chrissy and the rest of the team, a short script like this to do a mountain of work simply is not possible. It’s not just having the huge amount of code to build on – it’s the suite of tests they run with every pull request that tells me that I can trust it’ll work right.
Looking back on it, there’s definitely a few things I’d change in this script, and more dbatools functions I could have used. But after successfully testing a couple times, I didn’t want to break what was working.
When the migration was complete, I did a brief checkout and then gave my server admin the green light. He flipped names & IPs around, and then I ran Repair-DbaServerName which I had just discovered a few days earlier. I was expecting to do it manually but I trust the dbatools crew and their test suite more than myself on this one as I’ve never done this before. When that was complete, I had a grand total of three issues (that I could find):
Database owners weren’t set appropriately. I was able to resolve this via Set-DbaDatabaseOwner easily enough.
Outgoing dbmail didn’t work. Turns out the SMTP relay on the new server wasn’t started. Easy fix.
I had a Linked Server on my production instance which was unable to communicate to the new test server. This took me the longest to figure out. We checked everything – SQL Server Configuration Manager, the network itself, and then finally my colleague suggested testing something outside SQL Server – mapping a drive from production to test. This last test succeeded, which pointed us at the SQL Server connection specifically. The root cause: I had two firewall rules on the new server that blocked connections from all but servers on the local subnet. The production server isn’t on the local subnet.
None of these are total showstoppers. I had workarounds (or quick solutions) for them and as this is a test instance we could live with minor inconvenience for a day or two. One or two final tests, and I was satisfied that everything was working properly so I went ahead and enabled my Agent jobs. Some of them still have incorrect owners but I can fix that later – they were wrong on the source instance too.
I consider this migration a huge success. We had 95% functionality by 9am. By 3pm, the last real problems were resolved (and only that late due to a series of meetings keeping me away from my desk). Most importantly, it was achieved with minimal downtime for the development and QA teams. I’m now one week post-migration and everything is still running smoothly on the new instance.