Every now and then, you’re going to need to run a query that takes a long time. You’re going to want to make sure that it succeeds and that you can see the full output – even if your workstation disconnects – or maybe you want to check the status from home later.
You’re going to be tempted to remote desktop directly into the SQL Server itself and run the query there.
Don’t do that.
Did I ever tell you about the time a training class student started a long query, then came back to find that it had returned millions of rows, blew up SSMS’s memory, ended up filling the drive, and causing the VM to crash? That was awesome. Thank goodness nothing like that has ever happened in a production environment. <coughs>
What you want instead is a jump box or jump server: a virtual machine that lives where your servers live, so you don’t have to worry about uptime or connectivity. Install your tools there, the client for your monitoring software, SentryOne Plan Explorer, etc.
Not only does this come in handy for long-running queries, but also for emergency troubleshooting. Disasters are carefully timed to strike when you’re at your parents’ house, when somebody else was supposed to be on call, and when you didn’t bring your laptop with you. A jump box means you only have to get onto your company’s VPN, then remote desktop into your jump box, and you’re right at home.
Just be careful with capacity planning. When disaster strikes, if there’s only one jump box VM available, knife fights will break out for who’s able to log in. I’m personally a fan of a jump box for every single admin – that way if somebody hoses up their own jump box with a crappy installation or they want to reboot to fix something, it doesn’t break anyone else’s productivity. When your Recovery Time Objective is measured in minutes, you can’t afford to be waiting for a jump box.
Jump boxes were a big part of what enabled me to switch from Windows to Mac over a decade ago and continue to work happily there today. I get way less nervous about updates to my client machine when I know that the only stuff installed there is productivity applications. Worst case scenario, if my entire desktop or laptop blows chunks, I can still just remote into my jump box and keep right on working.