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.
8 Comments. Leave new
Same is true for me.. we all have VMs here for development. If I lose a laptop I am up and running in 2 minutes after getting the new laptop.. since all I have on the laptop is office, outlook and a browser. To get to a production DB we need to use a VM that can connect to production, these are restricted machines and only a handful of people have access to these
We have a jump box, and it’s pretty awesome. I have a work laptop that I undock and take home every night. Most of the time when I redock in the morning the laptop decides to reboot. With the jump box I can elave what I was working on open and get right back to it as soon as my laptop comes back up.
Denis – we are going the same route as you; our laptops will only have the basics for our “corporate” work and access to our SQL systems will be a restricted VM running running on a seperate laptop. Microsoft really has us locking things down for our retail applications per their specifications.
I also save all of my important SQL scripts in One Drive so no matter what machine I’m using to connect I always have all my scripts available…
Recommended by MS to help protect against “pass the hash” attacks
Just be careful if you have policies set for things like “disconnect after 30 minutes”. Long-running queries won’t work too well from those types of jump boxes.
They are useful in most situations, though.
For those I have a little vbs script that runs on login which sends a dummy key press every x mins
That’s where I am at Peter. What’s worse, it’s timeout is 15 minutes, not 30. So I am stuck waiting for it to complete anyway. 🙁