Managing Your Microsoft SQL Server Express Databases with SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) stands as a robust, integrated environment designed for managing your entire SQL infrastructure, seamlessly working from SQL Server to Azure SQL Database. For those utilizing Microsoft Sql Server Express, SSMS provides essential tools to configure, monitor, and administer your database instances effectively. Whether you need to deploy, monitor, or upgrade data-tier components for your applications, or construct intricate queries and scripts, SSMS is your go-to solution.

SSMS empowers you to effortlessly query, design, and manage your databases and data warehouses, irrespective of their location – be it on your local machine or in the cloud. This makes it particularly valuable for developers and small businesses leveraging the free Microsoft SQL Server Express edition, offering a professional-grade management interface without additional costs.

For users seeking a cross-platform alternative to SSMS, especially when managing SQL and other Azure databases across different operating systems, Azure Data Studio offers a versatile solution.

To delve deeper into the latest features and enhancements in this release, refer to the Release notes for SQL Server Management Studio (SSMS) 20.2.

For detailed information and download options for the SQL Server Management Studio 21 Preview 1, consult Install SQL Server Management Studio 21 Preview

Download SSMS to Enhance Your Microsoft SQL Server Express Management

Download SQL Server Management Studio (SSMS) 20.2

SSMS 20.2 represents the most current Generally Available (GA) version. If you have a preview version of SSMS 20 installed, it’s crucial to uninstall it before proceeding with the installation of SSMS 20.2. Importantly, installing SSMS 20.2 will not upgrade or replace any existing SSMS 19.x or earlier versions you might have installed.

Release number: 20.2
Build number: 20.2.30.0
Release date: July 9, 2024

By downloading and utilizing SQL Server Management Studio, you are agreeing to the associated license terms and privacy statement. For any feedback, suggestions, or issue reporting, the SSMS team encourages you to use the SSMS Developer Community site.

The installation process for SSMS 20.x is designed to coexist with previous versions of SSMS (19.x and earlier). This means you can have multiple versions installed side-by-side, allowing you to choose the version best suited for your specific tasks. However, if you are running a preview version of SSMS 20, ensure you uninstall it before installing the latest SSMS 20.2 release. You can verify if you have a preview version by navigating to Help > About within SSMS.

In environments with side-by-side SSMS installations, always double-check that you are launching the intended version, especially when managing your Microsoft SQL Server Express instances. The latest version is clearly labeled as Microsoft SQL Server Management Studio v20.2.

It’s worth noting that for SSMS versions 18.7 through 19.3, Azure Data Studio was automatically bundled with SSMS. This integration allowed SQL Server Management Studio users to leverage the innovative features of Azure Data Studio, a cross-platform and open-source tool beneficial in cloud, on-premises, and hybrid environments. However, this bundling is not present in SSMS 20.0 and later versions.

To explore Azure Data Studio further, you can visit What is Azure Data Studio or consult the FAQ.

Language Options for SSMS Users Managing Microsoft SQL Server Express

This SSMS release offers installation in a variety of languages:

SQL Server Management Studio 20.2:

If you are viewing this page in a non-English language, and wish to access the most current information, select Read in English at the top of the page. To download SSMS in different languages, please refer to the available languages section.

It’s important to note that the SQL Server PowerShell module is distributed separately through the PowerShell Gallery. For more details, see Install the SQL Server PowerShell module.

What’s New in SSMS for Microsoft SQL Server Express and Beyond

For comprehensive details regarding the new features and improvements in this release, consult the Release notes for SQL Server Management Studio (SSMS). These notes will provide insights into how the latest SSMS enhances the management experience, including for Microsoft SQL Server Express users.

Accessing Previous SSMS Versions for Legacy Microsoft SQL Server Express

This article specifically addresses the latest SSMS version. If you need to download earlier versions of SSMS, perhaps for managing older Microsoft SQL Server Express instances or other SQL Server editions, visit Previous SSMS releases.

A critical update to note is that as of December 2021, SSMS versions prior to 18.6 no longer support authentication to Database Engines using Microsoft Entra multifactor authentication (MFA). To maintain MFA functionality, it is necessary to use SSMS 18.6 or later.

Similarly, connectivity to Azure Analysis Services via Microsoft Entra multifactor authentication requires SSMS 18.5.1 or later.

Unattended Installation of SSMS for Streamlined Microsoft SQL Server Express Management

SSMS installation can be automated using PowerShell, enabling unattended setups. This is particularly useful for consistent deployments across multiple environments, including those utilizing Microsoft SQL Server Express.

Follow these steps to perform a silent, background installation of SSMS without GUI prompts:

Launch PowerShell as an administrator.
Execute the following command, adjusting the paths as needed:

$media_path = "<path to your SSMS-Setup-ENU.exe file>"
$install_path = "<desired root installation path for SSMS>"
$params = " /Install /Quiet SSMSInstallRoot=$install_path"
Start-Process -FilePath $media_path -ArgumentList $params -Wait

For example:

$media_path = "C:InstallersSSMS-Setup-ENU.exe"
$install_path = "$env:SystemDriveSSMSto"
$params = "/Install /Quiet SSMSInstallRoot=`"$install_path`""
Start-Process -FilePath $media_path -ArgumentList $params -Wait

You can replace /Quiet with /Passive to observe the setup UI during installation.
3. Upon successful installation, SSMS will be located at %systemdrive%SSMStoCommon7IDESsms.exe (based on the example paths). If any issues arise, examine the returned error code and consult the installation log file in %TEMP%SSMSSetup.

Uninstalling SSMS and Shared Components

When uninstalling SSMS, be aware that it might have installed shared components if they were not present on your system. SSMS uninstallation does not automatically remove these shared components.

These shared components include:

Microsoft OLE DB Driver 18 for SQL Server
Microsoft ODBC Driver 17 for SQL Server
Microsoft Visual C++ 2017 Redistributable (x86)
Microsoft Visual C++ 2017 Redistributable (x64)
Microsoft Visual Studio Tools for Applications 2019

These components are not automatically uninstalled because they might be shared with other applications. Removing them could potentially disrupt other software functionality.

Integration with Azure Data Studio (Versions 18.7 – 19.3)

For SSMS versions 18.7 through 19.3, Azure Data Studio was included in the installation. However, SSMS 20.0 and later versions do not include Azure Data Studio in their installation packages.

The Azure Data Studio installation is skipped if a compatible or newer version is already installed on the system.
Refer to the release notes to find the specific Azure Data Studio version bundled with SSMS versions 18.7 through 19.3.
Azure Data Studio installation requires the same administrative privileges as SSMS installation.
Azure Data Studio is installed using its default settings, which include creating a Start Menu folder and adding Azure Data Studio to the system PATH. It does not create a desktop shortcut, nor does it register Azure Data Studio as the default editor for any file types.
Azure Data Studio localization is managed through Language Pack extensions, available in the extension marketplace.
In SSMS versions 18.7 through 19.3, you could skip the Azure Data Studio installation by launching the SSMS installer with the command-line flag DoNotInstallAzureDataStudio=1.

Supported SQL Offerings, Including Microsoft SQL Server Express

This SSMS version is compatible with SQL Server 2014 (12.x) and later, making it fully suitable for managing Microsoft SQL Server Express editions from 2014 onwards. It offers optimal support for the latest cloud features within Azure SQL Database, Azure Synapse Analytics, and Microsoft Fabric.

Furthermore, SSMS 20.x can be installed alongside SSMS 19.x, SSMS 18.x, SSMS 17.x, and SSMS 16.x, providing flexibility in managing different SQL Server environments, including various versions of Microsoft SQL Server Express.

For SQL Server Integration Services (SSIS), SSMS 17.x and later versions do not support connections to the legacy SQL Server Integration Services service. To manage older Integration Services versions, use the SSMS version that corresponds to the SQL Server version. For instance, SSMS 16.x is appropriate for connecting to the legacy SQL Server 2016 (13.x) Integration Services service. Note that SSMS 17.x and SSMS 16.x can coexist on the same machine. Since SQL Server 2012 (11.x), the SSIS Catalog database (SSISDB) is the recommended approach for managing Integration Services packages. See SSIS Catalog for more information.

SSMS System Requirements for Optimal Performance with Microsoft SQL Server Express

The current SSMS release supports the following 64-bit platforms, assuming the latest service packs are installed. These requirements are relevant whether you are managing a full SQL Server instance or Microsoft SQL Server Express.

Supported Operating Systems:

Windows 11 (x64)
Windows 10 (x64) version 1607 (10.0.14393) and later
Windows Server 2022 (x64)
Windows Server Core 2022 (x64)
Windows Server 2019 (x64)
Windows Server Core 2019 (x64)
Windows Server 2016 (x64)¹

¹ SSMS requires .NET Framework 4.7.2.

Additional Requirements:

Administrator privileges are necessary for SSMS installation or updates.
Running SSMS in a virtual machine requires a full Windows operating system.
Windows containers are not supported.
SSMS is not supported in application virtualization environments like Microsoft App-V, MSIX for Windows, or third-party virtualization technologies.

To install SSMS on Windows Server Core, you must install the Server Core App Compatibility Feature on Demand.

Supported Hardware:

1.8 GHz or faster x86 (Intel, AMD) processor. Dual-core or better recommended
2 GB of RAM; 4 GB of RAM recommended (2.5 GB minimum if running on a virtual machine)
Hard disk space: Minimum of 3 GB up to 10 GB of available space

SSMS is exclusively available as a 32-bit application for Windows. For users needing a tool compatible with other operating systems, Azure Data Studio is recommended. Visit Azure Data Studio for further details.