Recently Microsoft released SQL Server Management Studio (SSMS) v20.0, which is a major release of the primary tool that many of us use to work with SQL Server. Over the last few years, the tools team at Microsoft has worked to separate the tools from the various editions, giving us separate SSMS downloads. There have been separate releases in the v16, v17, v18, v29, and now v20 versions. I asked how often many of you update, and it seems some people update regularly, but I still run into people who are running the version that came with their SQL Server 2014 or 2016. If you're doing that, stop. Go get a later version now, at least 19.
This post will look at my experience downloading, installing, and getting started with Management Studio v20.0.
Updating Plugins
Some of you might have plugins, such as those from Redgate. I have SQL Prompt, SQL Source Control, and an integration with SQL Test in SSMS. One thing I've learned is that I want to make sure all of these are up to date before I upgrade SSMS. This is especially true for major versions.
I checked the plugins in SSMS and Prompt was up to date, but not the others, so I ran those installers.
Once those were done, then I decided to install SSMS v20.
Installation
The installer is on the download page, which is also where release notes are stored. The download is nearly 500MB, which is not too large. This certainly didn't download in a few seconds for me, but it didn't take too long.
I had been running 19.3 on my desktop, so once I updated my plugins, I clicked setup and got started. This is a standard tools install, with a welcome screen. This can be installed side-by-side with SSMS v19, so be aware if you have shortcuts or pinned toolbar icons, they won't update.
I selected the default install, which is next to my v18 and v19 installs.
One thing to note that as this installed, it flashed a message that the VS 2017 isolated shell is still being used. I would have thought we'd be on VS 2019 or later, but I guess not.
Once the installer completes, you get a reboot message.
A quick reboot and I can test out how SSMS 20 works.
The Connection
I started up SSMS 20 and it asked me to import settings from either SSMS 18, 19 or nothing. I chose 19, as that's the version I've been using lately.
The first thing to notice is a new connection dialog. This is an expanded one by default. You can see that the Connection Security section has been added to the bottom, defaulting to Mandatory encryption and Trust server certificate unchecked.
If I were to click connect, I get the error message below, which is what has been coming from many other clients since the changes to require that Trust is checked for many new connection libraries. Somewhat annoying, and I wish I could default this to checked, but that's not going to happen. I expect that over time most infrastructure will find a way to automatically generate certificates for instances, perhaps on install, but for now, this is annoying.
Once I check this, I can connect.
The interface looks the same as SSMS 19, which is comforting. I dislike large scale changes. The one thing I did notice is that despite updates, none of my Redgate tools are here. I think I need to re-run the installers after the update, though since I know a couple tools aren't quite SSMS 20 ready, I decided to wait for now.
The release notes there is a new icon in the status bar. I can see this is the case, as I compare SSMS 19 and SSMS 20 below. SSMS 19 has the coloring from SQL Prompt, but note that next to the instance name, there is nothing. The yellow, default color in SSMS 20 shows a padlock.
If I hover over this, it shows the type of connection I made, where I had mandatory, instead of strict, encryption.
In reading the release notes, most of the changes here are related to connection security, by updating the drivers and connection dialogs. Note that some of these options were in previous versions. In SSMS 19, if I expanded the connection dialog, I'd see the Trust Server Certificate option, unchecked of course. If I click connect here, in SSMS 19, it connects.
This is because the Microsoft.Data.SqlClient version changed to a newer one that allows Strict security and requires trust.
In terms of the connection security, we have new options in the dialog. I can see optional, mandatory, and strict as choices. The default is mandatory and I don't see a way to change this to optional.
If you choose Strict, then you can enter a hostname to ensure you are connecting to the right server. This is documented by Microsoft in their TDS 8.0 section. While I appreciate this sort of improvement in security, I think we are in for some rough times as we transition to a place where certificates are more widely used. I dislike making this mandatory since it creates yet another hurdle for getting systems setup, but maybe that's what is needed to get the infrastructure folks to start automating better security.
In any case, there is an option to set the Trust server cert for imported connections.
I tried this by selecting Trust in one of my SSMS 19 Registered server connections.
I then exported this to a file. I right-clicked the same Local Server Groups in SSMS 20 and selected Tasks -> Import. You can see below I selected the file I'd exported. Trust me, this is the same file.
When I imported this, I wasn't asked, despite the setting a few images above showing Prompt. Instead, the connection was imported and available. You can see in the properties that Trust was selected. I tried this both ways, not selecting before export, and selecting, and the import/export works as it should.
Here's the crazy thing. The initial import of settings included these servers. To do the test above, I deleted the SQL2022 instance connection. However, when I checked the properties of my SQL2017 connection, I see this. Note that trust is not selected.
However, if I right click and select New Query, I get a new query window. This appears to work as expected, but there is no lock icon in the status bar, as I showed above. This appears to be connecting without the need for the Trust Server Certificate checked, which is strange.
Even more strange. If I click the "change connection" icon and hit Enter when it appears, supposedly the same connection settings used, it won't connect. I get the error shown above when I try to connect without trust server certificate selected.
I think there is a hole here, but I am not sure. If anyone knows, I'd be interested to find out.
Light Usage
I worked with SSMS for a few days, doing random SQL coding, checking instances, the normal type stuff I'd do with the tool. Everything seemed to work well and was where I expected it.
SQL Prompt Caution
I uninstalled SQL Prompt.
Then I downloaded the toolbelt to reinstall it. I saw that it wasn't installed and I let this run.
When I started SSMS 20, there was still no SQL Prompt.
However, when I restarted SSMS 19, I saw SQL Source Control and SQL Test, but no SQL Prompt.
Don't mess with SQL Prompt if you upgrade to SSMS 20. Keep your SSMS 19 around.
Other Changes
One other changes is that the connection type now includes Entra, which makes it easy for anyone that has switched from Azure AD, but confusing for those that haven't. I somewhat wish there was a parenthesis that said "Microsoft Entra MFA (formerly Azure AD MFA), but I can't imagine marketing people approving that. Once a name changes, it's changed. Not always in docs, but at least in the product. Mostly.
Other improvements are updates to SMO and DacFX. I don't see release notes for the SMO update, but the DacFX update includes online index bugs as well as fixing some deployment issues for certain situations.
There are a few bug fixes as well in SSMS related to connecting to Azure storage. There is a fix for an issue with Always Encrypted to access Azure Key Vault in the wizard. There are also improvements for MI connections.
There are also issues. I appreciate seeing a list of issues if they can't be fixed. In this case, there are a few random problems but workarounds are provided. As an example, there is this item: Selecting the Design option for a view referencing a table using spatial data causes SSMS to crash. The workaround is to change the view in T-SQL. I'm sure this is annoying to some people, but few, and I'd like to think that most people using SSMS could use T-SQL to handle this. Most of the other workarounds seem reasonable to me.
There are a few bugs where there is no workaround, like a problem in the maintenance plan designer that causes a dialog to crash, but stop using maintenance plans. Then you won't have this, or other, issues.
Official Blogs
Erin Stellato manages the SSMS team and she's written a few posts on the changes coming in SSMS. Part 1 was about the connection dialog changes, which I've discussed above. Part 2 looked at the changes to security, and Part 3 is the roadmap. You can read through those, with lots of links to documentation on the changes.
There is also a feedback link for you to post things you'd like to see changed. These do get read, so be professional, polite, and descriptive of what you want. Get others to vote. I know these are examined, as I've had debates about a few of these with Erin. She's aware, but making decisions about what to work on, and what makes sense, is hard. Lots of votes don't guarantee a change will be made, but it certainly gets debated.
Summary
I've covered my first impressions, which show that for the most part, not much is changed. The need to check "trust server certificate" is there, and if your organization is advanced enough to get certs for most of your instances, this might be something you like. If you are security conscious, then you'll appreciate this. If you want to just get work done, this will be lightly annoying. I'm getting used to it.
For the most part this feels like a minor update, though the security changes are breaking in some ways, so that probably warrants a major version. I know there are plans for the future to make more changes, and I look forward to those.
One last note of caution. Read the known issues. If any of these are things you do often, then don't upgrade for now.
