Rajendra Gupta
Completed installation page

Upgrade SQL Server Management Studio (SSMS) along with Azure Data Studio

October 20, 2021 by

This article explains upgrading SQL Server Management Studio (SSMS) and Azure Data Studio (ADS) to their latest version with a single installation.

Introduction

SQL Server Management Studio, aka SSMS, is famous for performing SQL Server database development and administrative tasks in GUI and T-SQL mode. You can graphically launch various tasks for quickly performing activities. It allows database creation, administration, Management for both On-premise and Cloud-based SQL Servers such as Azure, AWS, GCP.

Starting from SQL Server 2016, the management studio is not part of the SQL Server Installation Center. It is a separate standalone application, and Microsoft releases regular upgrades with minor and major versions. The latest version is 18.9.1, and it was released on April 20, 2021. Its build number is 15.0.18384.0

Download Latest version from Microsoft docs

Azure Data Studio is a cross-platform (Windows, Linux, macOS) development tool that offers modern IDE having IntelliSense, Code snippets, integrated PowerShell and Bash terminal, Custom Insights/Dashboards, Source control integration. It is built on top of the Visual Studio Code. It has a marketplace from where you can download various extensions to enhance the experience with ADS. These extensions can offer GUI interfaces for specific tasks, Server & database dashboards, visuals, extended connectivity to other databases such as Postgres. ADS is suitable for IaaS, PaaS such as Azure SQL, Azure SQL managed instances, Azure VM or Azure SQL Data Warehouse. Its rich, customized dashboard helps database monitoring, troubleshooting the performance bottlenecks quickly.

The latest version of Azure Data Studio is 1.13.0, released on June 17, 2021.

  • Note: To learn more about the Azure Data Studio, refer to these articles on the Azure Data Studio category
  • Latest release number and date

    When Should we use Azure Data Studio and SSMS?

    Sometimes, users ask this question whether we should use ADS or SSMS. The point to note here is that Microsoft has designed both products for specific purposes, and there is no plan to deprecate the management studio. You can go through the following list to understand the primary usage and difference of both tools.

    ADS common uses:

    • You can use it to write SQL queries or executing them
    • You can use custom visuals, SQL query result charts and Save results in the JSON format
    • You can execute sqlcmd, bash, PowerShell commands directly in ADS using the integrated terminal
    • You can use it to experience the rich query editor with code snippets
    • If you use Linux or macOS, you can use ADS and connect with the SQL Server
    • If you perform fewer administrative tasks and require minimal requirements of GUI, it can be the preferred tool for you

    SQL Server Management Studio common uses:

    • It is required for performing complex database administration tasks
    • Security management, vulnerability management, user management and audits
    • Performance tuning advisors, extended events, high availability solutions such as SQL Server Always On Availability Groups or Windows failover cluster
    • You can design database diagrams, or table designers, index wizards
    • Registered servers functionality or Multi-server administration
    • Live query statistics or client statistics, activity monitor, deadlock viewer

    Azure Data Studio and SQL Server Management Studio single installer upgrade

    SQL Management Studio v18.7 onwards, ADS is also part of the installation and upgrade. Previously, you need to download, upgrade the SSMS and ADS separately, and these tools were not integrated.

    Before we proceed, let’s verify the installed versions in my lab environment.

    • Installed SSMS version: v18.5.1

      Check management studio versions

    • Installed ADS version: 1.27.0

      Check ADS version

    I will upgrade the SSMS v18.5.1 to the latest version, 18.9.1, using a single installer. In this process, it should automatically upgrade the ADS. We will verify the ADS version later in the article.

    To upgrade the management studio to the latest version, navigate to Microsoft docs and download the SSMS setup file. If you want to upgrade to a specific version, you can navigate the link and download the respective version file.

    Click on the Save file in the pop-up window shown below. The setup size is approximately 635 MB. Thus, its download time depends on your internet download speed. Therefore, you need to wait until the setup is downloaded completely.

    Launch the installer

    Launch the setup, and it shows the heading as “Microsoft SQL Server Management Studio with Azure Data Studio”. It also highlights Release 18.9.1 that is the latest version of SQL management studio.

    It uses the default location of your previously installed management studio. You can modify the installation directory if required. However, it is advisable to use the existing directories.

    At the bottom, it prints a message about the telemetry data collection for SSMS. You cannot disable the telemetry configuration that allows the tool to share data about installation experience and performance data to Microsoft.

    Click Install to begin

    Click on Install to accept the privacy statement and license terms for both SSMS and ADS. Next, it begins the management studio and ADS upgrades. Finally, as shown below, it has two progress bars.

    • The first progress bar is for component level upgrade status
    • The second progress bar is for overall management studio and ADS upgrade status

    View progress

    During the upgrade progress, you see various components, such as below:

    • Microsoft SQL Server for Integration Services
    • Microsoft SQL Server for analysis services
    • Microsoft Visual Studio Tools for Applications 2017

    Package and Overall progress

    SQL Server Management Studio upgrade:

    Monitor status

    As shown below, it starts the upgrade of ADS along with the SQL Management Studio.

    Start of ADS installation

    Once SQL management studio, Azure Data studio, and related components are installed successfully, you get the following screen.

    Completed installation page

    • Note: The single installer does not create a shortcut for the ADS. It does not register the tool as an editor for any file type

    Verify SQL Server Management Studio and Azure Data Studio version

    To verify the management studio version after an upgrade, launch SQL management studio from the Start menu. In the launch window, it shows the latest version, v18.9.1.

    Verify installed version

    Additionally, you can go to SQL management studio help and view individual component versions.

    View individual component details

    For the ADS, you can either launch it from the start menu or navigate to Tools-> Azure Data Studio. It is the shortcut of the ADS inside the SQL Server Management Studio.

    Launch ADS from SSMS tools

    It reflects the upgraded version as 1.28.0.

    View existing upgraded version

    At the beginning of the article, we saw the latest version of ADS is 1.30.0; however, with SSMS v18.9.1, it installed ADS 1.28.0. SSMS v18.9.1 was released on April 2021. Thus, it includes the latest version of Azure Data Studio during its build, i.e. April release version 1.28.0.

    However, if you wish to upgrade ADS to the latest June 2021 release, you can browse Microsoft docs and download its latest version installer on Windows, Linux or MacOS.

    Download release number 1.30.0

    If SQL Management Studio’s later version detects the latest version of ADS, it skips the ADS upgrades and continues with upgrading the remaining components.

    Skip ADS upgrade using SSMS installer

    As highlighted earlier, the SQL management Studio installer might not include the latest version of ADS. Therefore, you might want to avoid the upgrade of ADS during the management studio upgrade. Unfortunately, there is no GUI method to skip this upgrade. However, you can do it using the command-line interface.

    Launch an administrative command prompt and navigate to the directory where you downloaded the SSMS installer. You can add the command line flag DoNotInstallAzureDataStudio=1 for skip ADS upgrade. The complete command is as below.

    >SSMS-Setup-ENU.exe /Passive DoNotInstallAzureDataStudio=1

    Do not Install Azure Data Studio

    Conclusion

    This article explored the upgrade of SQL Server Management Studio and Azure Data Studio using a single installer. It gives flexibility and avoids running upgrades separately for each application. If you are a beginner at ADS, you should learn its useful functionalities using SqlShack articles.

    Rajendra Gupta
    Latest posts by Rajendra Gupta (see all)
Azure Data Studio, Development, SQL Server Management Studio (SSMS)

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

630 Views