·
7 min read

SQL Server Management Studio (SSMS) 18.0 released for general availability

When I joined the SQL Tools team as a PM back in October 2018, my top priority was to release SQL Server Management Studio (SSMS) 18. I expected a fair amount of work but didn’t realize how complicated the process would be. 6 months later, having passed several internal checks such as accessibility, privacy, security, compliance, etc. among many others, and after 5 previews, I am very excited to share that SSMS 18 is now generally available. You can download SQL Server Management Studio 18 today.

It’s been a long journey. We were very close to getting the release out multiple times, until some ship blocker showed up at the last minute and we had to bail. Ideally, we want to ship a product that’s better than the version its replacing. 🙂

My work is not done but has just begun. We will continue to bring cool features into both SQL Server Management Studio as well as Azure Data Studio as it makes sense.

Some of you may have been following the journey, and some not. For the sake of completeness, as well as to recognize all the fantastic work the SSMS team has done, I’d like to highlight all the cool stuff  we’ve brought to you across all the preview releases including the incremental work between Preview 7 and GA. You can also refer to the full SQL Server Management Studio 18 Release Notes for all details.

SQL Server Management Studio improvements

  • Smaller download size – We’ve taken SSMS 18 on a diet and brought it down to half the size of SSMS 17.x.
  • Updated Visual Studio 2017 isolated shell – We picked up the latest shell from Visual Studio (VS) that unlocks all the accessibility fixes that end in both SSMS and VS as well as the latest security fixes that went into VS.
  • SSMS can be installed in a custom folder – This has been a long standing request. With SSMS 18, you can install SSMS in any folder and the option is available from both command line and the setup UI.
  • SSMS allows installing in a language other than the OS – You can now, for example, install SSMS German on a French Windows (Note: if the OS language does not match the SSMS language, the user needs to change the language under Tools | Options | International Settings).
  • SSMS ships with MS Object Linking and Embedding Database (OLEDB) driver
  • Support for SQL 2019 – This is the first release of SSMS that will be fully aware of SQL Server 2019 (compatibility level 150).
  • Better Azure SQL Database support
    • SLO/Edition/MaxSize database properties now accept custom names, making it easier to support future editions of Azure SQL Databases
    • Support for recently added vCore SKUs
  • SQL Server Management Objects
    • Extend SQL Server Management Objects (SMO) support for resumable index creation
    • Exposed new properties on configuration/server/database objects
    • Support for Data classification “read-write” permissions
    • And many more
  • SSMS/Azure Data Studio integration – I’ll write a separate blog post on our SSMS + Azure Data Studio (ADS) story, but in a nutshell, think of these two tools not as separate tools doing different things, but as one integrated tool. Each tool has different experiences built into it and can be launched from the other seamlessly. For example, Notebooks is a super cool feature built into ADS. SSMS users can simply right click on a database > Azure Data Studio > New Notebook.
    • Launch Azure Data Studio from Tools, or Object Explorer
    • Right click database to launch a new query or new notebook in Azure Data Studio
  • General updates:
    • Exposed AUTOGROW_ALL_FILES config option for filegroups in SSMS.
    • Removed “lightweight pooling” and “priority boost” options from SSMS GUI. Users can still enable/disable these via sp_configure.
    • New Firewall Rule dialog now allows the user to specify a rule name, instead of automatically generating one on behalf of the user.
    • Improved support for multi-monitor systems by making sure that dialogs and windows pop up on the expected monitor.
    • Exposed the “backup checksum default” server configuration in the new Database settings page of the Server Properties Dialog.
    • Exposed maximum size for error log files under Configure SQL Server Error Logs.
    • Added Migrate to Azure option under Tools menu. We have integrated Database Migration Assistant and Azure Database Migration Service to provide quick and easy access to help accelerate your migrations to Azure.
  • SSMS | Object scripting
    • Added new menu items for “CREATE OR ALTER” when scripting objects.
  • SSMS | ShowPlan
    • Added actual time elapsed, actual vs estimated rows under ShowPlan operator node if they are available. This will make the actual plan look consistent with the Live Query Stats plan.
    • Added logic to display the Materializer Operator (External Select).
    • Added new showplan attribute BatchModeOnRowStoreUsed to easily identify queries that are using the batch-mode scan on rowstores Anytime a query performs batch-mode scan on rowstores, a new attribute (BatchModeOnRowStoreUsed=”true”) gets added to StmtSimple element.
    • Added showplan support to LocalCube RelOp for DW ROLLUP and CUBE.
  • SSMS | Database compatibility level upgrade – Added a new option under <Database name> -> Tasks -> Database Upgrade. This will start the new Query Tuning Assistant (QTA) to guide the user through the process of:
    • Collecting a performance baseline before upgrading the database compatibility level.
    • Upgrading to the desired database compatibility level.
    • Collecting a second pass of performance data over the same workload.
    • Detecting workload regressions and providing tested recommendations to improve workload performance.
  • SSMS | Query store
    • Added a new Query Wait Statistics report

Query Wait Statistics Report.

  • Audit files
    • Changed authentication method from storage account key based to Azure AD based authentication.
    • Updated the list of known audit actions to include FEATURE RESTRICTION ADD/CHANGE GROUP/DROP.
  • SQL Server Integration Services
    • Added support to allow customers to schedule SQL Server Integration Services (SSIS) packages on Azure-SSIS IRs which are in Azure Government cloud.
    • When connecting to Azure SQL DB/Managed Instance, you can connect to it with “<default>” as the initial database.
    • Added a new entry item “Try SSIS in Azure Data Factory” under “Integration Services Catalogs” node, which can be used to launch the Integration Runtime Creation Wizard and create Azure-SSIS Integration Runtime quickly.
    • ISDeploymentWizard now supports SQL authentication, Azure Active Directory integrated authentication, and Azure Active Directory password authentication in command-line mode.
    • Deployment Wizard now supports creating and deploying to Azure Data Factory SSIS integration runtime.
  • Data classification
    • Reorganized data classification task menu by adding sub menu to the database tasks menu and adding an option to open the report from the menu without opening the classify data window first.
    • Added new Classification Report menu item to the Data Classification flyout.
  • Vulnerability assessment
    • Enabled vulnerability assessment tasks menu on Azure SQL Data Warehouse (DW).
    • The Vulnerability assessment now supports Azure SQL DW.
    • Added a new exporting feature to export the vulnerability assessment scan results to Excel.
  • Always encrypted
    • The Enable Always Encrypted checkbox in the new Always Encrypted tab in the Connect to Server dialog now provides an easy way to enable/disable Always Encrypted for a database connection.

Always Encrypted with Secure Enclaves Connection Window.

  • Always Encrypted with secure enclaves
  • SSMS settings migration (from 17.x and older 18.0 Preview – one time migration of settings from a previous version of SSMS to SSMS 18).

SSMS Import User Settings.

  • Flat file import wizard
    • Added logic to notify the user that an import may have resulted in renaming
  • Data-tier application wizard
    • Support for graph tables
  • Azure SQL Managed Instance
    • Added new AAD logins as a new login type in SMO and SSMS when connected to an Azure SQL Managed Instance.

And this is still a short list of all the great work the team completed. More details can be found in the SSMS 18 release notes.

In addition to all of the feature work, we also did a ton of work to improve the experience of existing features. We fixed several issues in the following areas:

  • General crashes/hangs
  • SSMS editor
  • Object explorer
  • Help viewer
  • Object scripting
  • Table designer
  • SMO
  • Analysis services
  • Integration services
  • Flat file import wizard
  • AlwaysOn AG
  • Data classification
  • Backup/restore/attach/detach DB
  • Job activity monitor
  • Managed Instance support in SSMS
  • Azure SQL Database
  • Query data store
  • Result grid
  • XEvent profiler
  • Copy database wizard
  • ShowPlan
  • Data masking

For a full list of fixes, please see the SSMS 18 release notes.

In the process, we’ve also had to make some hard decisions to deprecate/remove some features and tools including:

  • T-SQL debugger
  • Database diagrams

The following tools are also no longer installed with SSMS:

  • Osql.exe
  • Dreplay.exe
  • SQLDiag.exe
  • SSBDiagnose.exe
  • Configuration manager tools – Both SQL Server Configuration Manager and Reporting Server Configuration Manager are not part of SSMS setup anymore.
  • DMF standard policies – The policies are not installed with SSMS anymore. They will be moved to GitHub. Users will be able to contribute and download/install them if they want to.
  • Command line option -P removed – Due to security concerns, the option to specify clear-text passwords on the command line was removed.
  • Generate scripts | Publish to web service removed – This (deprecated) feature was removed from the SSMS UI.
  • Removed static data masking (preview) feature – While this feature was available in previous previews, it did not make it into the GA version of SSMS, unfortunately.

As with any software product, we also have some known issues. These have been described in the SSMS 18 release notes.

If you have a previous version of SSMS 18.0 preview, you will need to uninstall it first.

Support policy

I would also like to remind our users that with the general availability of SQL Server Management Studio 18, any previous versions of SSMS will become unsupported as per our support policy. I strongly urge you to update to SSMS 18 and enjoy all the goodness that it brings. SSMS is also updatable via Windows Server Update Services (WSUS) so if you work at an organization that does not allow direct downloads, you can have the update pushed through WSUS.

We’d love to hear from you with any questions, comments or feature suggestions.

a man smiling for the camera
Dinakar Nethi
Senior Program Manager, Azure Data

Related Posts