Last fall, Microsoft split the coding and release of SQL Server Management Studio away from any dependency on the server code. With that, they began an aggressive and exciting series of releases with exciting new functionality in each release. However, you don’t care.
How do I know? I’ve seen the telemetry that shows that most of you are still using old versions of SSMS.
I want to know why. Please, post comments below so I can understand.
Why You Should Move to SSMS 17
There are a ton of reasons for you to make the move. The least of which is bug fixes are coming hot and fast. The more exciting things are the ability to compare execution plans or the new Extended Events sessions that look just like those old Trace sessions that people love so much. Heck, the latest version has exposed the AUTOGROW_ALL_FILES option for FileGroups (read more, and download it, here).
With all that new functionality, why aren’t you using it?
You no longer have to run the installer for the server and risk accidentally installing a new instance (not that I’ve ever done that… more than about three times). The new installer stands alone. You can put it where you need it without issues. Further, Microsoft assures us that it works as far back as SQL Server 2008. I happen to know that Redgate has tested it on SQL Server 2005, where it (mostly) works fine. We also tested it on 2000, where… well… you could run queries successfully.
If the issue isn’t compatibility, then what is it?
As a nerd, I want the new toys. However, as a software vendor, I’d love if it more people went to a more stable, more easily maintained, release of SSMS. Not only can you get more and better functionality from Microsoft, but your third party tools are likely to play nicer too.
What is the hold up?
Conclusion
This is not a rant or a criticism of your choices. I’m seriously inquiring so that I can understand. We have a better piece of software, with zero cost, easier maintenance, easier installations, cool new toys, and a very low adoption rate. So, what gives?
Want to learn about all the really cool ways that SSMS 17 can help you with query tuning? I’m teaching all day classes all over the US and in Europe. I’d love to help you find a reason to make the switch. Check out these locations for the seminar:
For SQLSaturday Indianapolis (and my first time speaking in Indiana) on August 10, 2018. Please go here to sign up.
I’ll be at SQLSaturday Sioux Falls (and my first time speaking in South Dakota) on August 18, 2018. Please sign up here.
For SQLSaturday Oslo on August 31, 2018. Click here right now to register.
Let’s talk at SQLSaturday Boston on September 21st. You can go here to register.
Coming soon, SQLSaturday Munich.
try imagine the nightmare of deploying the tool to hundreds of servers, dev, qa, staging, and so on.
on top of that, we learned that it moves around a bunch of executables from one folder (13) to another (14). so your database deploys will fail (I know, we should really know powershell, but that’s a different story altogether). which means that you either deploy to all servers at once (restart required), or keep a hold of them.
Calin, not knowing your environment, but you only need to have SSMS on the client side, as long as you have access to the database instances, no need to install anything on your servers
not gonna get into a debate about should or shouldn’t… but there are times where you need to do things locally… maybe it’s matching drive paths, maybe it’s identifying issues related to network connectivity…
sure, in theory, SSMS only on the clients.
then there’s reality. And yes, every SQL server (across all environments) has SSMS installed. And double yes, it matches the version of the engine.
Makes using Server Core difficult. However, I get it, some people do it that way.
I use SSMS Tools, only works on management studio 2012 unless I pay for an upgrade or use an alternative free tool and I love SSMS Tools.
I don’t want to use SSMS 17 because:
It locks-out the sa account when you create maintenance plans from SSMS 17, I didn’t have this issue when using SSMS 16 in my staging environment.
If you open a (second) instance of SSMS 17 and open Replication Monitor, it crashes after a few hours for no apparent reason.
It’s just not stable enough.
Every time an update comes out, I need to download the installer and install it. Having to do this manually every couple of weeks is a pain. I wish SSMS could automatically launch the upgrade like how Visual Studio does. I still like the ability to control which update I deploy, but having to manually download and install is what discourages me from using it in my main dev & QA environments.
I’m replying to koenverbeeck but this reply interface is above his reply so I’m not certain I’m doing this right. Anyway, I agree with koenverbeeck. I had no idea it was a separate download until I started rolling out Win10 in our environment. This prompted me to go searching for the newest SQL version and now I’m using it. I would have happily continued with my 2012 interface for as long as I was on that PC.
OK, I see what happened. There’s a misplaced line between the person’s comment and their posting date. This appears to be a separator between the posts but, again, it’s misplaced.
I think a lot of people (using older versions of SQL Server) don’t even realize there’s a separate download for SSMS.
We use SSMS only when we have to, and completely avoid it in any of regular our development work.
To get equivalent features to our preferred db IDE (datagrip) we’d have to spend thousands of dollars for add-ons. On top of that, expecting people to hack support for version control in to it is kind of ridiculous – I’m honestly not sure if that is actually still the case, because I stopped paying attention around that point, and probably stopped upgrading around that time too.
will be interesting with 2017+… in the past I’ve found that the server release matched versions are more stable than mid-cycle updates (even now the SSMS reports like ‘disk usage by table’ only work half the time – no idea why, but on the server they always work no issue – I’d investigate if I had more time, it’s just not significant enough when I can run the DMVs instead).
I am also going to be watching bloat AGGRESSIVELY… I watched with anticipation as the SQL team tried to move BIDS into extensions, then failed spectacularly (I still recommend people use the VS2015 extensions as a result)… I watched CodeLens waste CPU, memory, and document space (one of the first things I disabled)… I watched VS spin up a dozen background threads (my project doesn’t need node, please get the #!@& rid of it)… I also watched SSMS drop the TFS extensions (and while I was quick to submit connect feedback asking that they replace it with something “not sucky”, I still use it) – thankfully they can be re-enabled.
so am I anxious for SSMS to continue with the hype? No, not really.
I will watch for the features that I can benefit from – INSERT INTO [FileGroup] is nice, I’d love to see WITH (DATA_COMPRESSION)… I’ll wait to see if the context menu’s “SCRIPT TO” includes “CREATE OR ALTER” (current handling of ‘create’ script when ‘create or alter’ was used is stupid, given that I know what it’s doing).
but SSMS hasn’t had notable improvements – they’re incremental and evolutionary at best… and the SQL Ops Studio is even worse for more reasons than I’ll bother listing.
I use SSMS 2014 and 2016 at work while at home I have 2017.
2017 is fancy but I as @nakulvachhrajani said every now and then you need to download and run the installer and that is a pain
Maybe, because many people just do not get an (automatic?) update? Microsoft announced to provide SSMS updates by February 2017 with SSMS 17: https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-changelog-ssms?view=sql-server-2017
But there was just one update released within one year: 17.4 on 2/27/2018. An no update via WSUS since then.
If you want to connect to SSIS you have to use the same version of SSMS. SSMS 16 for SSIS 2016 and so forth. This sucks.
The question then becomes: why are you still using the package deployment model when you have SSIS 2016?
I agree with this comment about connectivity to SSIS requires SSMS to have the same version. As for using package deployment vs the newer project deployment, I’ve found that when you have several years worth of packages developed that operate a specific way with package deployment, retrofitting all of those to use a different deployment model adds a lot of extra work. I still have one SSIS server that’s running 2008 R2, we do have an open project to have that replaced but of course I always get more and more urgent requests that need to be done before that project. I’ve tried using the newer features of SSISDB and project deployment for new work, but ran into several roadblocks that would have required even more work just to make them behave the way the old model packages did when run from SQL Agent. Also, each package I have within a project may be related to the same system but is developed and works completely independent so if I make a change to one package why should I deploy the whole project?
The same problem exists with Project Deployment – you cannot use SSMS 17 to deploy SSIS 2016 packages (unless MS have brought out a fix recently).
Thanks for the all the feedback everyone. It’s very useful. Please keep it coming.
Doesn’t work with my SQL Prompt version. Plus they pissed me off with the incompatibility of SSIS in VS 2017. In short, I don’t trust them to make products that work well together, while i do trust them to drop useful functionality because they don’t know how to make it work with their new version. And all the notices that there were fixes were actually other users checking back it to complain nothing is being released to fix it.
Agree!
We do use SSMS 17, however there are occasions when we have to drop down to a version that matches the database engine. Example: Most of the time we deploy SSIS projects via PowerShell. However sometimes we’ll use SSMS to deploy to a dev server. Using SSMS 17 to target a 2016 instance breaks our scripts tasks (SSMS 17 always uses the 140 version of ISProjectWizard.exe). Visual Studio is much better at respecting the target version. As a consequence some of my devs are reluctant to install any version that exceeds the highest engine they are currently working on.
The first time I installed it on a PC the installer ran forever. I have found a couple of things that are not backward compatible to SQL 2008R2 (can’t remember which now). The creating a Maint plan via Windows Auth has issues locking out the ID. Very frustrating.
Installed it, broke my ability to create/modify SSIS packages. Uninstalled and all is well. [Sigh of relief]
I’ve used my MSVL (admin) and VS Ent. subscriptions to search for a stand-alone copy of SSMS 2017 and it just isn’t there. Yes, I can download the entire 1.5GB MSSQL 2017 installer, but…then we run into the singular issue you mentioned: inadvertent installations of new Instances by the DBAs. Not to mention the inability to include paths and mapped drives to offload backups, etc.
And, I have to agree with some of the previous comments: All servers have SSMS installed on their Sql20xx servers.
Management Plans are best left to the versions they “manage” until Microsoft continues to improve SSMS stand-alone.
Thank you for the heads-up. I would like to have an all-in-one SSMS. it would ease the pain of upgrading and managing our Sql farms.
Here you go: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017
Downloading the 1.5GB SQL Server 2017 won’t do you much good, since SSMS isn’t included anyway.
One feature of SSMS 2008 R2 allows the user to double-click in the left margin to select all text between the previous GO statement to the next GO statement. This statement (or batch of statements) can then be executed with 1 click (or F5). This saves the user a lot of time from having to scroll up and down just to make sure that the correct start and end points have been selected. SSMS 17 does not have this double-click feature, so I use both versions, depending on the script I need to run.
We’re still on Windows 7 !
SSMS 17.4 messed up my Win10 VM when I installed it, because it installs an extra set for Powershell ! causing conflicts when trying to use out of the box functions.
Yikes. Luckily I didn’t hit that problem.
I use it because we recently upgraded to SQL 2017 from 2008R2, but honestly I’d be happy with the 2008R2 version if I could install it without having to install the 2008R2 database engine as well. Why? Because SSMS 2017 is dog slow (and I have a relatively fast machine). It takes forever to start up on an average machine. On my machine, I ran them side by side until I had to wipe my hard drive clean and reinstall the OS, so I could see the difference. I really hope SQL Operations Studio lives up to its promise of being “lightweight” so I can start using it instead (I’m not using it until it gets out of beta).
Is anyone noticing that installing the SSMS17 takes forever to install versus SSMS08R2, 2012, or 2014?
Yes. I’m absolutely seeing that.
Why am I not using SSMS 17? Because I cant get it to work on my Windows 10 laptop. SSMS 16 works fine but when I try to run 17 I get an error about visual studio “missing one or more components”. I’ve tried repairing, uninstalling and reinstalling, and everything in between, Nothing has worked. Each time MS puts out an update to 17 I try installing that too, but get the same error. I’ve googled the error and found 100’s of other people with the same problem. One fix works for one person but not for others. I’ve tried them all. Nothing fixes it for me I’ve even tried removing all versions of SSMS, deleting all remnants of all versions of SSMS from the registry and file system, and reinstalling just SSMS 17. I still get the same error.
Running it with the /log options seems to reveal a problem clearing old cache’s from the registry and every time I run it it creates a new registry subkey under “HKEY_CURRENT_USER\SOFTWARE\Microsoft\SQL Server Management Studio” named 14.0_Config_xxxx where xxxx is a random number. The error I see in the log is “Could not remove abandoned configuration cache”.
I’ve pretty much given up trying to use it until MS can get it to install correctly on all machines and not just most.
Company policy on allowing staff to install software on work equipment. I can, and do, use SSMS 17.x, as I have admin permissions, but a lot af analysts have thier software updates performed automatically by IT and getting IT to approve a new piece of software takes ages. By the time they have evaluated and approved version 17.8.1 version 17.10.2 will be out. Getting new software added to our old VM image based system was even harder.
My experience has been that I have to match the version of SSMS to the environment I’m connecting to otherwise I can’t open an Integration Services session. If I’m just connecting to a database instance then yes I like to use the latest SSMS available.
Maybe someone could help me understand better how to open an integration services connection to multiple versions of a sql server instance using the most current SSMS.
From several comments I get the feeling that a lot of peole are using the old msdb based SSIS model, and not the new, more secure SSISDB/Integration Services Catalogs model? Am I correct that peole are still using the pre SQL 2012 models for SSIS deployment?
Based on the comments, it’s about 1/3 of the people here. Interesting point. It’s one there’s a good solution for anyway. The bugs, and slow load time though, that’s more on Microsoft.
Thanks everyone for the feedback. Please, keep it coming.
I switched over, but I still have the older versions installed for every version of SQL I support. Aside from the previously mentioned SSIS problems and REALLY SLOW start up times, I can’t emphasize enough the problems with failed installs. In our organization the installer failure rate seems to be about 50%. That’s likely the result of our corporate OS image configuration, but the lack of useful error messages makes fixing the issue quite time consuming.
My other main complaint I have with it are the choice of monochromatic DB icons in the Object Explorer (previous versions color coded read-only and offline databases).
Oh, I don’t like those either. Nor the fact that I had to redo all the screen captures in the book that I just finished.
The compelling features you listed are not at all compelling for the fleet of our ssms users outside of our DBAs, in my particular organization. For better or worse, that’s the answer.
That’s a very tiny amount of the new functionality. Plan comparisons and a whole bunch more new functionality in just execution plans alone. I’d at least explore it to see the interesting stuff.
In SSMS 17, I had really slow response when I was connected to a server over a VPN, but that seems to have been corrected with the latest update. It was most evident when expanding a node in the Object Explorer, but it seemed generally slow with whatever I was trying to do. As I mentioned earlier, the slow startup time is still very annoying since the version I previously used started much, much quicker.
I use it but do get fed up of the constant crashes since upgrading to 17.8.1 a few days ago. 3 crashes already today. 1 time I was just opening the file/save as dialog box.
if you find ssms crash data in Windows Error Reporting or in the event log, tweet them @shueybubbles. Post issues on aka.ms/sqlfeedback, we do triage them. I also take mini dumps from tools like procdump.
it is really slow. I use the Object Explorer Details screen to show rowcounts across all the tables and when you refresh that in SSMS 2017 it sssssllloooowwwwwllllyyyyyyyyy recalculates all the rowcounts. SSMS 2012 was instantaneous or as near as dammit.
It will just freeze for 10 or 20 seconds opening a table list on a database.
Did I mention that it was painfully slow?
Oh, and it is slow.
HA!
But is it slow?
Have you tried SSMS 18? I do believe it’s faster. Also, it’s less stable and prone to crashing. So, it has that going for it.
I can’t believe nobody has made the most obvious point. I use 17 but it is painfully slow, especially to load, compared to older versions.
I do find the load times slow, as many others have pointed out. However, general behavior doesn’t seem appreciably slower than other versions of SSMS. However, I haven’t tested that directly.
Mike, I don’t think you have read all of the responses. There have been a number of references to the slowness.
Thanks everyone for all the feedback. I really appreciate it.
1) I’m really tired of being asked to update by SSMS. I’m a DBA, I’ll update when I want don’t ask me
2) Bugs.. bugs as far as the eye can see. Search stinks now (really I don’t wanna use regex and it breaks normal searches) I also was trying to QA a new install and couldn’t hook a 17.x version of SSMS to SSRS. Yeah.. not getting off on the right foot there.
3) I use whatever version of SSMS comes with the SQL Installer. If I install a newer version I do use 17.x.. but I don’t really like it
and more subjective, the UI looks a bit chinzy compared to say 2012.
Sure not going to argue with you at all. I will point out though that SQL Server 2017 and all future versions won’t come with the SQL Server installer at all. From that point, you have to use the independent SSMS install.
Our firm cannot go to SQL Server 2017 because of the simple fact that it will break the service agreement for a third party ERP database we are using. Essentially, the third party indicated that they would need to test it on 2017 and give the sign off before we can upgrade.
1) Distrust & Arrogance – I do not trust Microsoft to do whats right by customers. They will do whatever it takes to lock in users to their model and that means being forced to do as they say and not as the customer wants. Just look at the arrogance Microsoft has exhibited had in the past. Office Ribbon Example: This should have never been forced onto users but made as an optional way of using Office but they forced it because they (Microsoft) know better then the customer. They got a lot of negative customer response for this but were able to keep going because as a business its VERY expensive to switch systems so vendors know that customers will tolerate a lot before seriously leaving.
2) Reliability – As several have already cited Microsoft places speedy updates/releases over reliability. Does anyone here actually use (in live environment) a day one release of anything from Microsoft? Thus the reason for holding off on using VS 2017. After a few years of maintenance it should be good/reliable.
I just love SQL Server 2017 & SSMS 2017. It just work fast compare to prev versions. I have SQL 2012/14/16 & 17 on my laptop along with VS2013. No issues encountered and also have SQLAssistance installed.
Great to hear. I am seeing slow load times, but I don’t care that much because of the functionality.
If I compare the work I did with Informix 4 and DB2, I find SSMS at any version a joy.
I’m using it when I have to, also including SSMS 18, however it has bugs even in the most basic functionality like switching between tabs or moving a tab near the other to see them at the same time.
The latest version of SSMS 17 crashed when I had about 10 tabs (files) open in it, and I attempted to move one of them to the right, to have two tabs near each other at the same time. I restarted it, it recovered the files, then I attempted to do this move again, and it crashed again.
So then I replaced it with SSMS 18.4, and it turns out, that I cannot even switch between tabs from time to time, as the content of the new tab is not getting drawn, and it appears, that I’m still in the previous tab, until I start scrolling its content, so it would be redrawn. This issue also occurs when I close a tab, and the newly selected one still appears with the content of the closed one, until a redraw is triggered.
Visual Studio 2017 and 2019 is working well and without these kind of problems on the same machine, so the problem must be with SSMS.
SSMS 17 also had issues with its configuration windows, where the possibility of setting the fonts of the editor disappeared, and I could not recover it regardless of how many new versions I have installed from it.
In SSMS 17 the data import wizard has also been rendered unusable. To populate the list of tables from the source database, so I could select the one I need the data from takes so so long, that it is unusable. I had to revert back to SSMS 12 for my data importing tasks.
Another surprise was, when I found out, that debugging has been removed from SSMS 18.
Just a note, two years ago, it was interesting to know why people weren’t using SSMS 17. Now, it’ would be 18. Soon, it’ll be 19.
However, you bring up good points. Debugging is an issue for some. Microsoft responds to user feedback. I’d strongly recommend getting in touch with them and letting them know. They will switch on stuff. While I disagree with this one, they did on removing the data models from SSMS. They were gone, then they were back. They just need sufficient feedback to know the importance of the issue.