Leave it better than you found it

Comments 0

Share to social media

This is for the folks who still have to log into remote machines and do work manually on the box. Yes, we still exist, and we will for as long as we’re still using physical servers in data centers and even IaaS. Not everyone has transitioned to server core and full-on PowerShell remoting for everything.

At a previous gig, we had multiple dedicated jump boxes, and they’d have a dozen active or idle sessions at any given time. This made patching the jump boxes problematic, especially for shared client tools everyone always left open, like Management Studio, Visual Studio Code, the PowerShell ISE, and (yes) Notepad. And worse, it became everyone’s dumping ground for everything they needed to get into the data center, from patches and ISOs to JSON config files and Excel spreadsheets.

So, I thought I would share some thoughts on how I’ve gotten better at using RDP, in an effort to leave no trace.

First, what’s a jump box?

I often forget that “jump box” isn’t a common term. Basically, this means any kind of machine that sits between you and a server that you can’t get to directly. For example, one behind a firewall or VPN, in your data center or in the cloud. It is not always the case that you can RDP directly to the server, and sometimes you need to first RDP to a “jump box” before you can then RDP or otherwise manage the target servers from inside that network environment. It is actually quite common practice to do this, since it allows the servers to have fewer direct attack vectors (sometimes just port 1433 and a handful of others).

Here’s a diagram of a simple environment, where my workstation is outside of data center or cloud boundary, and I must first RDP into one of these jump boxes before I can perform any sort of administrative tasks:

Simple network/RDP diagram

I show two different types of jump boxes: one that is my own, and so I don’t share files or binaries or sessions with anyone else, and one where the jump box is actually shared and can be used concurrently by other members of my team. These boxes typically then go through network security group (NSG) and/or firewall rules to get to the target server. In some cases, I can RDP directly to the server once I’m on the VPN, but usually, that is only permitted from designated jump boxes.

No matter which type of jump box you use (or none at all), when you establish RDP sessions:

Don’t leave idle RDP sessions active

Oh, you’re done with your maintenance? Cool, sign out! Close your windows, save your work, and come back later. It saves resources on the host, keeps the server available for other folks to jump on without having to decide who to kick off, and allows other folks (like me) to update shared client tools like Management Studio (which are shared across all users using the server or jump box). I was at Wayfair for two years and I never once had the ability to upgrade SSMS on the jump box we all shared, because there was always someone with an active session.

Unless you have to

Sometimes you want to leave the remote session locked without logging off, since maintenance or other activities can drag on longer than a single session. Hey, I get it; we all have lives.

If you need to stay signed on, save your work. Preferably in a safe network location, not on the RDP machine. It’s tedious and unexciting, but someone’s going to have to make a choice, and if it’s you…

Someone's going to kick you off...

Aside from this, some orgs have group policies that will kill your session after it’s been idle for a certain period of time. And sometimes someone will be doing patching and won’t care about existing sessions or whether they have any work to save. And sometimes a firmware update will hose a raid driver and force you to pave a new operating system, which means all the stuff you saved in C:\scripts has gone bye-bye.

So where do I put my scripts to avoid this problem? I try to avoid saving anything locally, but we don’t always have access to outside resources like Google Drive, OneDrive, and so on. A network drive may make the most sense here, even if it is less than convenient.

Required reboots

If you’re doing maintenance (applying Windows updates, installing CUs, or upgrading SQL Server), you may come across a server that needs to be rebooted first. Isn’t that annoying? It sure is! Don’t leave it that way for the next person!

Often SQL Server updates will require a reboot at the end, even if you restarted to get rolling, and even if you shut down the SQL-related services beforehand. Don’t put it off! Just do it as part of the maintenance window. You want the next person to be able to perform maintenance without first having to clean up after you. This may mean you are without a secondary for an extra five minutes, but you’ll be saving the next person some hassle.

Still, if you have planned maintenance coming up, I recommend you run a PowerShell script like this one (shamelessly adapted from Michael Simmons):

This will tell you if you’re going to need to reboot at the beginning of maintenance anyway, and why, giving you a little extra info about your timeline.

Pro tip, though: If you’re combining Windows Updates and patching for SQL Server, apply the updates to SQL Server first, before you open Windows Update. Cumulative Updates typically require a restart, but Windows Update doesn’t care if you just patched SQL Server. In this case, you will only need to reboot once. On the other hand, if SQL Server fails the “restart computer” rule, then apply Windows Updates before you reboot, and then apply the updates to SQL Server. This way you’ll only have to reboot twice.

Another pro tip: If you want to bypass the reboot requirement, and know it is safe to do so, you may come across advice to muck around in the registry and delete entries from PendingFileRenameOperations. Instead, it would be safer to launch setup with an additional argument:

This prevents setup from even checking this registry key at all.

Web browsers and other apps

If you used a web browser – hey, sometimes it’s the simplest way to grab a cumulative update – consider clearing cookies / history before signing out. And if you’re not signing out, shut the browser down anyway. Such a wasteful resource to leave open. That goes for other applications, too. If you ran a Profiler trace or were watching perfmon or had anything else running, shut it down.

You can also go review the Users tab in Task Manager to see if anyone else has any resource-intensive applications running. If you find any, ping them and maybe nudge them to clean up after themselves, too. I’ve even found cases where employees who no longer worked at the company still had abandoned sessions kept alive in spite of a disabled account.

Unnecessary services

On more than one occasion, on production servers running paid editions of SQL Server, I’ve shut down the CEIP service – this is the service that sends telemetry and usage information back to Microsoft. Now, I’m not afraid of the data that’s sent, but on mission critical systems, this thing can’t get out of the environment anyway.

CEIP service

There are probably other services that don’t need to be running, but please check with your team before having a field day.

Installers

Don’t leave old installers lying around. If you installed a cumulative update, you’re never going to install it again. If you copied the file locally, clean it up! If you mounted an ISO to upgrade SQL Server, you’re also not going to do that again. Dismount it and remove that massive file. On older machines I’ve found copies of ISOs from three major versions back.

That said, keep a copy of those old installers somewhere on the network, since they may need to be used on other machines even if they’re no longer applicable to the one you’re on, or to change features later.

For cumulative updates specifically, the names of the files are not exactly intuitive. I rename them to, say, SQLServer2022-CU11.4105.exe, so I know exactly what version, CU, and build number it is. This makes it much more obvious to everyone involved whether this file is no longer relevant.

Summary

Some of the concepts above apply only to specific types of RDP sessions, for example you don’t need to worry about concurrency if you have your own dedicated jump box, but most are pretty universal: save your work (ideally off-machine), clean up your local junk, leave no trace.

Load comments

About the author

Aaron Bertrand

See Profile

Aaron Bertrand (he/him), a cheery Canadian with industry experience dating back to SQL Server 6.5, is a Staff Database Reliability Engineer for Stack Overflow. He also blogs at sqlblog.org, mssqltips.com, and thebertrandfamily.com.