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”
As we open 2019, I thought I’d take a moment to reflect on the past year.Continue reading “2018 Year in Review”
In my previous post, I mentioned that I wasn’t checking the status of my RSJobs because I had logging built into my function. I originally tried to log out to plain text files with
Out-File, but quickly hit a wall. In a multi-threaded script, attempting to write to the same text file from multiple threads results in collisions and waits at best, and outright failure at worse. Perplexed, I turned to the PowerShellHelp channel in the SQL Community Slack and asked for suggestions for thread-safe logging to text files.
PowerShell has had a native method for spawning multiple “threads” ever since I can remember, in the form of the
*-Job functions. They work OK, but there are a couple downsides:
- Each job is its own PowerShell process, so it takes a non-trivial amount of time and memory to spin each up
- There’s no built-in method for throttling the number of concurrent jobs
This combination will become an ugly mess if something spins out of control and you spawn dozens or hundreds of jobs. PowerShell jobs are better tailored to small-scale asynchronous background processing. You can wrap the functions to limit the number of concurrent jobs but again, there’s a lot of overhead involved in creating and tearing down jobs. Warren Frame created
Invoke-Parallel, which uses runspaces (lighter-weight than jobs) and allows for throttling, but isn’t quite as full-featured as jobs are.
Boe Prox comes to the rescue with his
PoshRSJob module. This module provides runspace-based functions that mirror PowerShell’s native job functions to create –
Wait – plus some additional functionality like the ability to import modules (without needing
Import-Module inside the
Scriptblock) and throttling the number of concurrent jobs running.
The above is a fairly trivial script but it serves the purpose of demonstrating multi-threading with this module.
- In a loop, I’m creating two dozen RSJobs to fetch the PoshRSJob module from GitHub and write it out to a directory on my computer.
- I’ve set a throttle limit of 3; while all 24 jobs will be created, only three will run at any given time and the remainder will be in a
NotStartedstate, patiently waiting their turn.
- I’m passing value of the loop counter and my output directory into the job’s scriptblock so that I can write out 24 individually-named files.
- So that the RSJob system can keep track of the jobs properly, I’m lumping them into a named
- Because the module’s zip file is so small, I introduced a bit of a delay into each job. Why?
- To give me time to capture screenshots while the jobs are in various states.
- To prevent tripping GitHub’s anti-flooding protections.
So let’s fire this up!
I’ve started up the script and it’s created all the RSJobs, but only the first three are running because that’s where I set my throttle limit. Let’s check in on the progress with
Get-RSJob – all the same concepts you’re used to with regular PowerShell jobs (receiving, removing, checking for errors, etc.) work with RSJobs too!
You still need to clean up after yourself! Don’t forget to
Receive-RSJob and then
Remove-RSJob. I have something special for that.
Let’s say you’ve got a lot of RSJobs. Or maybe long-running RSJobs. Wouldn’t a nice, user-friendly view of the progress be handy? You bet it would!
This is both dense and verbose, but I wrote it really quickly and it works (but not just for this post, I’ve used it in production), so I’m not tinkering too much with it right now. Once the jobs are created (and a few have started), this script runs a loop watching the output of
Get-RSJob and updates a progress bar to reflect where we are. It also takes care of receiving and removing the completed jobs.
It’s true that I’m not checking for any errors in my job output. In the production process that I multithreaded, the
Scriptblock inside each thread does its own error logging to a file.
Did you notice the paths I’m writing to and the terminal theme? Yeah, this works on PowerShell Core too. Everything you see here ran on macOS as I was writing this post.
For light to moderate use, PoshRSJob is a really handy way to multi-thread processes that aren’t natively multi-threaded in PowerShell. It doesn’t seem to scale well to hundreds of threads, though. There is an open issue that seems to be related on GitHub, but I’ll discuss my own experience with it on a near-future post.
I wanted to get an idea of some good, bad, and surprise experiences that people had at everything from a SQL Server User Group meeting to PASS Summit. Things you found out right before, during or even after that you were glad you did or wish you did.
SQL Saturdays are similar to PASS Summit, but much smaller in scope and budget. Most SQL Saturdays have a twitter hashtag; follow it before the event so you can get an idea of who’s attending and make plans to meet some of those people.
If your SQL Saturday has an after-party, it’s usually not heavily attended as people tend to want to get home to their families (unlike Summit, where you’re already away from home). But I definitely encourage you to go if there is one! You might even find yourself invited to an after-after-party.
I can’t say I’ve had a bad experience at a SQL Saturday, although I have sat in on a session or two that didn’t really grab my interest the way I’d hoped. And unlike PASS Summit, it’s really hard to gracefully exit the smaller rooms at SQL Saturday mid-session if you decide it’s not really for you.
The biggest surprise to me after my first couple events was the inspiration that I had coming out of the event. After each event, I have new ideas for projects, changes to make at work, blog posts, and ways to give back to the community.
- Volunteer! If you don’t sign up before the event, talk to the folks at the registration/check-in desk and ask if they need any help. Every session needs a room monitor – just someone to get a headcount, help the speaker with timing (if they want), find help for A/V issues, pass out and collect session evaluations. You’re going to be in the room anyway. This is a great way to meet both organizers and speakers – folks who are active in the community.
- If there aren’t any sessions in a timeslot that grab your attention, skip it and read the next two items.
- Network with your fellow attendees. These are folks who live and work in your area. They may become your next job lead, or your next new hire.
- Talk to the sponsors! Make sure you thank them for sponsoring. If there are sponsors whose products you currently use, ask them questions about making the most of those products. Just don’t monopolize their time; they need to talk to new people as well to justify their sponsorship dollars. I’ve been known to hang out at the SentryOne booth on more than one occasion. I’m just a very satisfied customer and love talking with them about how I’m using their products.
- If you find a session particularly engaging or relevant to your interests, catch the speaker when the session is over and say “this is really interesting and I can see myself using it this way, can we talk later?” Ask if you can connect on LinkedIn; follow them on Twitter.
- Attend the after-party (if there is one)! It’s a great way to connect with organizers, speakers, and volunteers after the stress of the day has passed. It’s a smaller, quieter environment and easier to have a longer conversation.
After SQL Saturday
Remember those people you met on Saturday? Keep that conversation going. Make contact on LinkedIn or Twitter. Do they have a blog? Follow it! For example, I met Matt at PASS Summit 2017 and we haven’t seen each other since. But we talk regularly on Twitter and we follow each others’ blogs. If you’re meeting more local people (which will happen at SQL Saturday), catch up at the next user group meeting, or arrange lunch sometime.
Last year at PASS Summit 2017, I heard a number of comments related to people not recognizing me without the hat in my social media photo/avatar. The linked post started and concluded with the notion that I needed to take a new photo.
That never happened. Instead, I embraced The Hat. And it was totally worth it. I got a bit of ribbing from folks early in the week but it was all friendly. Trying to meet up with people at various points in the week, I’d get messages like “I’m in (location), looking for your hat, where are you?” People introduced themselves when they spotted me doing my PASS Ambassador duties because they recognized the hat from my photo.
A couple people had more fun than I expected:
WHERE IS YOUR HAT!? HOW WILL I FIND YOU!?!?
— Bert Wagner (@bertwagner) November 8, 2018
Wtaf? The hat can come off?!?
— Andy Mallon (@AMtwo) November 8, 2018
I met a few people sans hat and they didn’t immediately connect me (the person) with me (the Twitter account). Not a big deal, but I learned an important lesson from it. Apparently, this is how I’m known in a portion of the community now – the guy in the hat. Folks are accustomed to seeing/talking to me on Slack/Twitter with a particular image next to my name. That’s the image of me they have in their mind. Therefore, it’s best if I maintain that appearance offline as well.
I’m OK with this. It sets me apart from the crowd a bit and as time marches on and protecting my head from the elements becomes more important, it becomes a more necessary part of my wardrobe. At big events like this where I’m actively trying to meet people I’ve only spoken with online, I just need to be more consistent about wearing it.
The Hat stays!
My album of people and events at Summit 2018. Somehow a number of photo opportunities were missed!
@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.
- Grab the table from the source database and export the create script. I had to use
Export-DbaScriptwill 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
-QueryFilein the next step. Gotta love PowerShell – there’s always a few ways to do things.
- Take the
CREATE TABLEscript that we just exported from the source database and run it against the destination.
Copy-DbaDbTableDatato 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!
Malathi has asked us to:
Pick one thing you want to learn that is not SQL Server. Write down ways and means to learn it and add it as another skill to your resume. If you are already learning it or know it – explain how you got there and how it has helped you. Your experience may help many others looking for guidance on this.
The timing for this works out well for me, as I’ll be working on my 2019 goals very soon and education is part of those. But right now, we’re kind of a homogenous shop, which makes stretching beyond SQL Server a bit more effort. I’m occasionally indecisive, so I’m going to throw out a few ideas of things to learn in 2019.
- Need to learn for work: Postgres on AWS. We have a pilot project starting up using this and eventually, my team will need to support it. So, we need to at least understand the basics – backup & restore, basic monitoring/troubleshooting.
- Want to learn on my own: A document database of some description. I’ll probably land on MongoDB due to convenience and cost (or lack thereof). Runner-up: DynamoDB.
- Stretch goal: I still don’t entirely get the concepts behind Docker or Kubernetes (although I’m close on Docker). I’ve tried to run SQL Server 2017 on my Mac via Docker, but Docker itself seems to make the machine unstable. Kubernetes is a complete mystery to me; I know a few people who are doing “stuff” with it, but haven’t talked with them about it yet.
But you may be asking “Andy, where’s Azure? CosmosDB is a document database on that platform. And PostGres is on Azure too, why not learn it there?” My shop is starting to do new work on AWS, so the opportunities are more available there and I can apply what I learn at work more readily. So it’ll cost me nothing out of pocket if I go with that or MongoDB. As for the learning plan:
- I’m going to have to do this one for work regardless, so step one will be setting aside time at the office to work through the AWS tutorials and documentation. Then set up a few test databases, throw some data in, and play around.
- I know we have a few MongoDB experts locally and a user group, so hopefully they can get me started in the right direction. I’ve got a Raspberry Pi 3B as well as a Pi Zero W that I can put to use here.
- Apparently one can run MongoDB on Docker with Kubernetes so maybe I can kill two birds with one stone? It could be an excuse to pick up a couple more Pis. Or maybe fire up some VMs on the rack mount server that’s been sitting in my basement for 5 years.
MongoDB & Docker/Kubernetes are completely new ground for me to cover, so just figuring out where and how to get started may be a challenge. By the time publishes, I may already be starting to read the MongoDB manual en route to PASS Summit – and I know I can talk to people about this stuff at Summit to get ideas on where to start.
This post was written published early by accident. My reading of the MongoDB manual didn’t happen last Tuesday as I was not feeling well on my flight to Seattle.
On the eve of this year’s PASS Summit, I find myself reflecting on my first Summit in 2012. My employer was generous enough to pay for not only Summit itself, but a pre-con session on Tuesday as well.
I was a developer with an interest in SQL Server and PowerShell at the time, not a DBA. Becoming a DBA wasn’t on my radar yet. Regardless, I used the opportunity to attend a full-day class on managing SQL Server with PowerShell, taught by Allen White (blog | twitter). One of the big focal points of the day was the SQL Server Management Objects, aka SMO.
What I learned that day wasn’t immediately usable in my day-to-day job. Parts of it flew way over my head at the time. But the seeds were planted. It’d be several years before I got to the point of really applying it. Initially, my application was limited to some development “DBA-lite” tasks and suggestions for our sysadmins.
After a job change to become a full-time DBA, I started using and then contributing to dbatools, which uses SMO heavily. That previous exposure to SMO proved very beneficial. dbatools provides a lot of functionality, but it doesn’t do “everything.” In my own scripts, I’ve used that knowledge of the SMO object methods and properties to extend that functionality.
As I pore over this year’s schedule, I remind myself and ask you to consider not just what’s applicable to your present work and environment. Catch some sessions just to see what else is out there, or what’s coming. It’ll open doors for you in the future. Being informed about a wider range of topics will help you guide the conversation toward better solutions in the future. You don’t have to become a master of the topic; just know that something exists, what it can be applied to (equally valuable: where it won’t apply), and where to start gathering information if the solution is worth pursuing.