Site icon Andy M Mallon – AM²

Should I install SSMS on a server running SQL Server?

Andy

There’s a lot of advice out there on the internet that suggests that you should not install SSMS on your database servers running SQL Server.

This came up at work recently, where I was asked “Should we install SSMS on our database servers?” and my answer is:

1000%! Heck Yes. Install SSMS on your servers!

“But wait, Andy. That’s not a best practice!” you say?

The pseudo best practice of “don’t install SSMS” is a misguided one–advice that I even fell into repeating in the past. However, that’s actually a proposed solution to a best practice, rather than being itself a best practice.

“OK Andy. Stop saying “best practice” and get to the point”

(I know you were thinking that!)

Yes, install SSMS on your servers. Eventually, you’re going to have an emergency situation where the server is in trouble, you need to respond, and connecting remotely is problematic. Maybe your laptop is dead. Maybe there’s a network issue. Maybe your server is being DDoS’ed. Maybe the server got knocked off the domain and you’re having auth issues. Maybe Kerberos is refusing connections because of SPN issues. Or maybe you’re on an airplane trying to fix a problem from 30,000 feet on a low bandwidth connection and SSMS pulls back too much data over that network connection.

In all those situations, your fastest response to fix the problem might be to RDP directly to the server. At most of the jobs I’ve had over my career, having the fastest possible resolution to downtime is extremely important. Every single one of the situations I list above are scenarios that have happened to me in real life over the years, and every time, having SSMS on the server saved my bacon.

SSMS is my security blanket. I’ve been using it for 15 years, and just about everything I need to do during a critical response will be fastest if I do it in SSMS. In some cases, PowerShell is the tool of choice–but not always. Azure Data Studio and I are building our relationship, but there’s still some work to do before it can be my DBA daily driver. SSMS is still the tool I need in an emergency–at least for now.

But there’s a catch. My advice is still:

Don’t use SSMS on your servers

SSMS should be on your servers for an emergency, not for every day use. If you find you have any operational tasks where you’re logging in to the server to use SSMS locally as standard operating procedure, reconsider your process. My goal is to only use SSMS on the server if there’s an emergency.

All your normal processes should be things that you can do from your desktop, or from a central management server. If you have processes that involve “Log in to the server via RDP…” you should keep track of those processes, and revisit them so that you can eliminate the need to RDP to the server directly.

Why not use SSMS on the server itself?

There are a bunch of reasons, but lets hit a few of the bigger/fundamental ones.

Firstly, just logging in via RDP can cause a pretty decent resource spike. Establishing your session & running your login scripts from Active Directory will cause a memory & CPU spike. If you watch Task Manager (or your monitoring software) while a user logs in via RDP, the CPU spike can be noticeable, particularly on a smaller server. On one (random) server I looked at, I could reliably see one CPU core bounce to 100% for almost a minute. In the past, I’ve seen clunky login scripts cause an even larger impact.

Another reason is that SSMS is a resource hog. Go ahead & check Task Manager on your desktop right now. On my work computer, my SSMS is currently using over 500MB of memory–and I only have a dozen tabs open! Now imagine your entire DBA team all has an SSMS running on the server in your separate RDP sessions. That’s a bunch of memory & CPU that you are taking away from your server’s processing capacity.

Some tips to avoid running SSMS on the server

You can’t just say “NO! STOP DOING THAT!” without making sure there is a viable alternative. In some cases, folks will RDP to work directly on the server out of habit or comfort. In other cases, they might have a legitimate reason where they don’t have viable alternatives. By keeping track of those processes where it’s necessary, you can eliminate roadblocks and make it easier for folks to comply with not logging directly into the server via RDP to do their work.

So go ahead and install Management Studio on all your servers. But only use it in an emergency.

Exit mobile version