Welcome back to my Server Review Essentials for Accidental and Junior DBAs series. Last week, we took a closer look at the first steps of getting your environment set up, including an overview of password management and VPN clients. This week we’ll address RDP issues, SSMS installation, building a script library, and more.
Let’s get started.
RDP Clients and Jumpboxes
I’m not going to go very deep into how to remote into another computer via RDP. In short, depending on your environment, you’ll need to have RDP set up on your computer (which is already part of the Windows operating system) and possibly need to also set up the Remote Desktop Connection Manager or the Universal Remote Desktop Client.
The most common workflow as a consultant is to connect to your client’s specific VPN, then RDP into your client’s server.
Once you’ve got your remote access client all set up, hopefully you’ll be connecting from your local machine to a jumpbox rather than directly to a production server. What’s a jumpbox, aka terminal server or Citrix server? Simply put, it’s a computer (either bare metal or VM) that servers as an intermediary between your local hardware and the servers you’re administrating.
It’s typically a bad idea to connect directly to a production server because…well…accidents and mistakes happen to the best of us. It’s somewhat safer to make mistakes on a jumpbox instead of a prod machine. One of the most common mistakes you can make is accidentally clicking “Shut Down” instead of “Sign Out” or “Disconnect” when you’re logging out of a server. At least if you shut down a jumpbox, you’re not taking down production.
Jumpboxes also serve as the perfect place to keep all your client-specific scripts and tools. Some clients may choose to set up the box that runs their monitoring software as the jumpbox. This is a cost-effective solution.
A best practice is to log into a jumpbox and use SSMS on that box to connect to the servers you administer. Yes, it’s still possible to mess up production, but as a junior DBA you’re likely logging in to run basic check script. Here at Dallas DBAs, if I’m doing something beyond running daily checks or periodic HealthChecks, Kevin is looking over my shoulder virtually via Zoom to make sure I don’t miss anything.
Even so, it’s still best to be careful and pay attention to what you’re doing. Depending on what you’re digging into with SSMS, it’s easy to let your mouse or attention slip and click the wrong thing. If you’re not paying attention, you could find yourself disabling a mission-critical job or worse!
If you’re in an environment that doesn’t use jumpboxes, see if you can push for one to get implemented. Your company/client may not want to spend the extra expense of spinning up a VM, but I can assure you the costs of accidentally taking down production is much higher.
Keep in mind that there may still be valid reasons to log directly into a particular production server, but we should aim to minimize our direct footprint in production as much as possible.
VM Environments
There’s not a lot to say about using virtual machines other than—if your work remotely like I do—you’ll likely be logging into a VM on a regular basis. Even if you’re administrating on-prem servers, a jumpbox in a VM is the safest approach.
Note from Kevin: On-prem folks will typically work with SSMS on their laptop—which is effectively their jumpbox—unless they have multiple internal domains.
For most usages, a VM isn’t all that different from physical hardware from an operational standpoint, but it wouldn’t be a bad idea to familiarize yourself with the basics of dealing with VMs. You never know when you’ll need to troubleshoot an issue unique to a VM.
Another consideration is to set up your own personal VM as a backup jumpbox of sorts. This allows you to have emergency access to your tools when you need them. Imagine being away from your office or work machine that has all your logins and tools—hopefully, a rare occurrence. If you have access to a computer with a secure internet connection you can get logged into your VM and jump from there to your client’s servers. If you’ve set up your VM to have all the login credentials and scripts you need to serve your clients, you’re (hopefully) good to go.
Note from Kevin: This doesn’t work in all situations. Some VPNs will kick you out of your VM when you fire them up.
Another scenario I’ve personally encountered is one that forces me to use a VM as a jumpbox. We have one particular client that uses both VMWare and another cloud provider for their environments. I have zero issues connecting to the VMWare boxes via RDP, but when I attempt to RDP into the cloud provider’s boxes I just get a black square on my screen. My workaround is to fire up my VM and connect via RDP from there. It’s a few extra steps, but necessary since I haven’t yet found a solution for my laptop.
SSMS Setup
SQL Server Management Studio (SSMS) is the main tool you’ll be using for the foreseeable future as a database administrator. Yes, Azure Data Studio (ADS) has come a long way recently, and the concept of using Jupyter Notebooks for running daily scripts is highly intriguing and something we plan to look into very soon, but there are enough missing pieces in ADS that keep it from being the defacto admin tool.
Note that, beginning with SSMS 18.7, Azure Data Studio is included in the install. Many of us aren’t too happy with that decision by Microsoft, and hopefully, they’ll listen to their user base and reverse that decision.
At one time, SSMS was part of the installation of a SQL Server instance, but beginning with SQL Server 2016, SSMS became a separate install. This post isn’t going to go through the ins-and-outs of installing or using SSMS.
Depending on your company or client’s policies, you may not have much say in how you get SSMS set up. You’ll need to discover for yourself how much freedom you have. Some policies will dictate the specific version of SSMS you’re allowed to use, others won’t care what you use as long as you don’t break their servers and get your work done.
Granted, I currently have less than two years of experience at this point, but my personal recommendation is to install the latest version of SSMS when allowed. SSMS is backward compatible with older versions of SQL Server, and newer versions of SSMS give you access to new and updated tools that help you get your work done. There’s really no reason to not use the latest version of SSMS unless you’re on hardware that simply can’t handle it for some reason.
Script Library
As we work our way through this series on server reviews, I’ll share several different scripts we use to check various things on client servers and show you how we use them. I’ve learned that it’s important to not only build your own library of useful scripts on your local machine, but to also create individual folders of scripts for each client.
Why? Because over time you’ll customize some of these scripts for the specific needs and quirks of your client’s servers. Having a backup of those specific customizations is going to be a lifesaver. As I stated above, you’ll hopefully have scripts available on individual client jumpboxes for your daily use, but keeping a local copy that you periodically update is a best practice.
I learned this the hard way a few months ago. One of our clients has a habit of quietly decommissioning servers without warning. We often don’t find out the server is gone until we’re running daily checks against all the boxes in a Registered Servers list and get a connection error of some sort. When we inform the client that the server is unavailable, they’ll often respond, “Oh, we don’t use that box anymore so we decommissioned it.”
One of the boxes it seems they had decided to dump was the jumpbox we use. Suddenly, I could no longer use the jumpbox to check their servers and was forced to log directly into a production machine to get my work done. I learned—to my frustration—that I had to spend a couple of hours rebuilding my tools and customized scripts. That’s on me since I hadn’t bothered to create a local copy of those resources.
A few days after we inquired about the decommissioned jumpbox—after receiving no response about the issue from the client—the jumpbox magically reappeared, complete with all our custom scripts and tools. I immediately pulled copies of everything possible in case the box disappeared again, and now have backup copies of every clients’ custom stuff. All of this is backed up to the cloud, and I’m also working on building a library of these files in our company Teams environment.
We’re DBAs. We should know how to back up our own stuff. Just get it done.
What should your script library look like? That’s up to you unless your employer has a specific protocol for such things. I know some folks like to have an application to manage their scripts and code snippets, while others use a private GitHub repository or Jupyter Notebooks. So far, simply organizing my scripts into folders and subfolders on my local drive—synced to OneDrive—has been sufficient for my needs.
Bonus: Slack
I can’t emphasize enough how much we use Slack on a daily basis. I’m not going to spend time selling it to you or talking about how to use it. Just check it out and see how you can best implement Slack usage in your team’s environment. You’ll be glad you did.
If you’d like to know more about SQL Server Maintenance, check out Kevin’s Getting Started with SQL Server Maintenance course at Pluralsight.
Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.
Again, depending upon the requirements of your employer and/or circumstances, what you use to accomplish some things will vary. For instance, here at Dallas DBAs we use 
For instance, there’s a 50/50 chance that any given Citrix update will take away my ability to copy/paste directly out of a VPN window into a local text document. Thankfully, 
The biggest challenge over the last few months has been preparing for the 70-764 Administering a SQL Database Infrastructure exam. About the time Kevin laid this challenge out for me, we discovered that Microsoft was retiring most of the role-based certifications in favor of a new framework.
I ran my VM through it’s paces in those days, taking a bare install and getting SQL Server Developer Edition on there as fast as I could. I ran through installs, patching, instance creation, database creation, and a whole lot more. Doing all of this over and over again really helped solidify the basics and give me confidence to move on to more critical issues such as backups, restores, and integrity checks.
