SSMS is a large monolithic application built on the Visual Studio shell. It’s slow to start, heavyweight, resource intensive, and can be a resource hog. It’s also indispensable for many SQL Server DBAs.
I grew up using various SQL Server tools. We had isql for Windows early on and then Enteprise Manager before SQL Server Management Studio (SSMS) was built. Now we also have Azure Data Studio(ADS), though arguably for many administrators and DBAs, ADS doesn’t have enough capabilities to supplant SSMS. That may change, but for now I find myself sticking with SSMS for most of the code writing I want to do.
Many people install SMSS on the database server itself, and many other people refuse to do so. Most experts agree that running SSMS on the server itself is a bad idea, though that doesn’t stop some people from doing so on a regular basis. While I agree with Andy Mallon that we should install SQL Server on a server, I also agree that we shouldn’t use it.
That seems counter intuitive doesn’t it? Install it but not use it? Why install it in the first place?
When there is a crisis, you will want this tool. It’s familiar, it has all the wizards, dialog boxes, and tools that many solutions will describe when you frantically search for a solution to a problem. It’s also the best way to get something done if you have to connect from a remote location. Even if your laptop connects, sometimes networking issues, long query times, and more mean you will want local execution of queries, not remote calls from SSMS on a workstation. After all, if something happens to your client, you don’t want your query to rollback.
Andy has a good list of reasons, as well as a few tips to avoid running SSMS on the server. For me, I’d recommend a dedicated jump box, server or workstation, where users can RDP to this dedicated box and run SSMS from there. Why? It’s becoming more common as organizations try to implement better security. They often do this by limiting access to production from most workstations. In fact, for some clients, that’s the only way they can connect to production. I expect that to become more prevalent than not in the future, so I’d suggest you get comfortable with another solution now.