Problem
I was told to test the new SQL Server Management Studio (SSMS) 19. What is new in this new version? Why should I download and install this new version? How will this new version help me?
Solution
SSMS Version 19 is the latest release by Microsoft at the time of this tip’s publication. To review and check out the new features, we will install and explain what is new in SSMS. This latest version includes new features and more compatibility related to Azure specifically, which is Microsoft’s cloud platform. This tip will examine these new features in SSMS version 19.
What is SSMS?
SQL Server Management Studio (SSMS) is a graphical user interface (GUI) tool used to configure, monitor, and administer SQL Server databases. Also, you can use this software to create tables, views, stored procedures, and functions. In addition, SSMS can be used to administer Azure SQL databases and Azure SQL Named Instances.
SSMS 19 Download and Installation
You can download SSMS from the following URL: Download SSMS 19. When I downloaded it, it was a 617 MB download.
After downloading, run the SSMS-Setup.exe file. A welcome message will appear. Note that this wizard installs not only SSMS but also Azure Data Studio (to be discussed later in this tip).
Check the location and make changes if a different location is needed. Click Install to continue.
Once the installation is finished, a Setup Completed message will appear. You now have SSMS installed.
In the Windows Start menu, you will see the new version of SSMS installed.
Note that you have other tools installed as well:
- Analysis Services Deployment Wizard: Used to deploy and configure SSAS databases, cubes, etc. You can deploy a single object or an entire project. It is also possible to deploy the databases to multiple servers and configure security settings. For more information about this tool, refer to our article: Using the SQL Server Analysis Services (SSAS) Deployment Wizard.
- Database Engine Tuning Advisor: Recommends how to improve indexes and statistics. The Tuning Advisor can analyze trace files from SQL profiler or SQL queries and suggest indexes and statistics in the tables or views involved in the trace and queries. The index and statistic suggestions may increase the performance significantly. For more information about the Tuning Advisor, click here. For more information about indexes, click here. If you need more information about statistics, click here.
- SQL Server Profiler: Used to monitor SQL Server Databases or Analysis Services databases by capturing and saving data and to analyze and troubleshoot databases. We can use it to check the security and performance of the databases. Note that SQL Server Profiler will be removed in future versions. For more information about SQL Profiler, read here. For more information about extended events, read this link.
Importing Settings from SSMS 18
This new version of SSMS cannot be upgraded from SSMS 18, so when you start SSMS 19, you can import settings from SSMS 18, as shown below.
New Authentication Modes in SSMS
In the new SSMS, we have more Authentication types for Azure:
Azure Active Directory Password
The Password is a traditional one where you must specify the user’s name and password.
Azure Active Directory Integrated
The Integrated is synchronized with the AD in Azure. Basically, it is like a Windows Authentication on-premises but using Azure Active Directory, which is the active directory in the cloud.
Azure Active Directory Universal with MFA
The Universal with MFA uses Multi-Factor Authentication. Managed identities provide an automatically managed identity in Azure AD. Service Principal in an application authorized to access resources in Azure. This feature in SSMS is a security feature that requires users to provide different authentication methods to log in to the server or database. This method is used to provide more security. Some methods are a password, a code sent to the cellphone, a fingerprint, or facial recognition.
Azure Active Directory Service Principal
This authentication is a feature of Azure Active Directory that allows users to authenticate to SQL Server Management Studio using Azure Active Directory credentials. The authentication method is a secure way to access SSMS. You do not need to remember a separate username and password. Also, it allows users to access SSMS from any computer, tablet, or phone if they have an Azure Active Directory account.
Azure Active Directory Managed Identity
Azure Active Directory Managed Identity allows users to authenticate to Azure SQL Database and Azure Synapse Analytics using Azure Active Directory credentials. You do not need to store credentials in the connection string, so it is easier to connect using this feature.
SSMS Output Window
There is a new Output Window option in SSMS when you go to Tools > Options to configure which window channels should appear in the Output window.
Fully Supports SQL Server 2022
This version fully supports SQL Server 2022, which is compatibility level 160.
Data Classification
The Data Classification Windows are easier to navigate and read on the screen compared to earlier versions. Classification is a function to investigate and classify data in SQL Server.
Other Updates in SSMS 19
- It added support to audit SQL Server 2022 actions.
- Permissions in SQL Server 2019 and 2022 are now supported.
- The compatibility level is now 160 (SQL Server 2022) for scripting.
Azure Data Studio (ADS)
As mentioned earlier in this tip, the wizard installs both SSMS and Azure Data Studio during the installation process. Azure Data Studio is a tool for coding and administering SQL Server.
In SSMS, Azure Data Studio can be found when you go to Tools > Azure Data Studio.
It looks like Microsoft may want us to migrate from SSMS to Azure Data Studio in the long run. That is why the installer is now including both SSMS and Azure Data Studio. SSMS is a great tool to handle SQL Server. However, Azure Data Studio is a more flexible tool and can handle other technologies like:
- PostgreSQL
- Machine Learning
- MySQL
- Azure CLI
- Azure Arc
- Python
- Notebooks
You can add extensions in Azure Data Studio to extend the functionality of ADS. Below shows some of the extensions.
Azure Data Studio is oriented toward development and Azure. You can manage and work with SQL Server databases on-premises, but it is not as good as SSMS for SQL Server administration yet. There are still some features not available in Azure Data Studio as compared to SSMS. However, with Azure Data Studio you can work with Python, Notebooks, PostgreSQL, Machine Learning, Big Data, MySQL, and PowerShell. Azure Data Studio is open source, so a lot of extensions have been created (and more to come) increasing ADS capabilities.
With Azure Data Studio, you can work with the following:
- SQL
- PySpark – An open-source library in Python to work with Apache Spark
- Spark Scala – A programming language for Apache Spark
- Spark R – A package to handle Apache Spark using R
- Python 3
- PowerShell – A Microsoft shell scripting language
Next Steps
- I believe Microsoft is pushing us to move to Azure. They are trying to move our databases to Azure, and then they are pushing us to install Azure Data Studio. I think Azure is great, and Azure Data Studio is also nice. Most of the new features in SSMS and SQL Server in general are oriented to Azure.
- Check out these additional resources:
