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 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.
- Have a management server / jump server – Your team should have at least one server in each data center that you can remote desktop into to do work. This server should have an RDP license that allows everyone on your team to connect simultaneously, and have enough resources for you to all work in parallel. This ensures that you have a workspace physically close to your servers–so client tools will be fast in getting results back, and you don’t have to worry about network disconnects causing long-running scripts to fail. I generally prefer to work from my laptop, but if I have to leave something running overnight, or if I am on a low-bandwidth/high-latency network connection (like airplane wifi), RDP to a server is a better experience than SSMS over that slow network connection.
- Learn remote management tools – Windows & SQL Server both provide great remote management tools. Learn about PowerShell remoting, Windows Admin Center, Remote Server Administration Tools (RSAT), as well as any other management tools your employer might have.
-
Log out idle connection on your database servers – You can set RDP to automatically log off an idle session via Group Policy. Just go to
.../ Computer Configuration / Administrative Templates / Windows Components / Remote Desktop Services / Remote Desktop Session Host / Session Time Limitsand you’ll find the settings to enable idle logoff. This will help ensure that if someone does log in to perform a task, their session will get automatically cleaned up, and prevent a pile up of long-idle sessions all hogging resources. It also reinforces the behavior you want–after folks lose unsaved work a few times from the automatic logoff, they’ll start to remember to depend on other processes. -
Ensure remote admin connections are enabled – As part of your standard configuration, I recommend running
EXEC sp_configure 'remote admin connections',1;and opening port 1434 to your management servers & DBA workstations. This will allow you to connect to the Dedicated Admin Connection(DAC) remotely via TCP. If this isn’t enabled, then the DAC only listens on the server’s loopback IP (127.0.0.1). The DAC is necessary/helpful in certain situations–such as when your server hits the 32k connection limit & stops accepting new connections.
So go ahead and install Management Studio on all your servers. But only use it in an emergency.
The post Should I install SSMS on a server running SQL Server? appeared first on Andy M Mallon - AM².
Top comments (3)
I 100% agree with this. I'd like to make just one more addition: You should consider if you really need to install SSMS or if you could just install Azure Data Studio. I think for many DEV server you actually could take ADS which does have some interesting features that SSMS does not. Of course, I did NOT say that you shouldn't install SSMS on the server hosting SQL Server instance, but just on DEV servers. My point is, ADS is still so new that it very easily gets forgotten even though it has awesome features like Notebooks.
Thanks for this great post!
There's still a really big functionality gap between ADS & SSMS for a DBA. Managing Replication, Availability Groups, Agent Jobs, etc are better supported in SSMS than in ADS.
Also, I like to be able to use my Dev Environment as a testing ground for my own operational changes (like installing a patch update to SSMS). So I need to keep my Dev/Prod installs consistent to be able to test that. Using ADS on Dev & SSMS on Prod would get in my way of that.
Of course, that is absolutely true!
I usually also work with SSMS but I also like to work in ADS sometimes. ADS is still not really the tool for DBA yet, but it can serve for Devs and Analysts working on queries very well.
Thanks for your reply though! 😁