SQL Server Essentials: Installing SQL Server Management Studio and Azure Data Studio

Microsoft’s SQL Server Management Studio (SSMS) is the primary tool for working with SQL Server databases. In this article, I’ll guide you through the steps you need to follow to download and install SQL Server Management Studio on your PC.

What is SQL Server Management Studio?

SQL Server Management Studio provides an integrated environment that includes graphical tools like Object Explorer, which allows you to browse, select, and work with all of the objects on the server. There’s also Query Editor, which enables you to develop and run T-SQL commands and scripts.

A short history of SQL Server Management Studio

Microsoft first launched SQL Server Management Studio with SQL Server 2005. It is the successor to Enterprise Manager, which was delivered as a part of SQL Server 2000 and earlier.

In June 2015, Microsoft announced that future versions of SSMS would be released independently of SQL Server itself. Users now need to download and install SSMS separately from SQL Server.

The current release of SSMS is version 19.1, which can connect to SQL Server 2022 and has backward compatibility with SQL Server 2014 and higher. It also supports working with the latest cloud features in Azure SQL Database, Azure Synapse Analytics, and Microsoft Fabric.

What is Azure Data Studio?

Beginning with SSMS 18.7, Azure Data Studio (ADS) is now automatically installed alongside SSMS. Prior to this, ADS was a separately downloaded product. 

Unlike SSMS, which is a Windows-specific offering, ADS is a cross-platform and open-source desktop app that is primarily oriented toward T-SQL development. It offers basic object browsing and some limited management functionality such as the ability to perform database backups. However, it does not offer the full range of SQL Server management options that are present in SSMS.

For development purposes, Azure Data Studio provides several features that are not in SSMS including support for projects, notebooks, integration with source control, and the ability to export query results to CSV, JSON, XLSX. ADS can run on Windows, Linux, and macOS.

Both SSMS and ADS are completely free to use. They can be installed on a physical system or a virtual machine, and they both allow you to work with multiple SQL Server instances in your SQL infrastructure.

How to download SQL Server Management Studio

SQL Server Management Studio can be found on Microsoft’s website. The minimum hardware requirements are pretty low as Microsoft is listing a 1.8 GHz CPU, 2 GB or RAM, and 2 GB of storage.

If you need to connect to earlier versions of SQL Server, Microsoft also provides SSMS 18.x, SSMS 17.x, SSMS 16.x. The SSMS 19.x installation doesn’t upgrade or replace SSMS versions 18.x or earlier. To download previous versions of SSMS you can go to Previous SSMS releases.

To download SQL Server Management Studio, go to the Microsoft Learn website and then click the Free Download for SQL Server Management Studio(SSMS) 19.1 link. This will download the SSMS-Setup-ENU.exe executable.

If you are using a different language than English the ENU part of the name will be different. You can download different languages for SSMS/ADS by going to available languages.

Install SQL Server Management Studio

Running the SSMS-Setup-ENU.exe program will install both SQL Server Management Studio and Azure Data Studio and all their components. Please note that it’s not always recommended not to run this installation on your production SQL Server systems: For production servers, you should install SSMS and ADS on a networked client system. For development servers, it’s OK to go ahead and install SSMS/ADS on the server system itself.

Let’s kick off the installation of SQL Server Management Studio:

• The Welcome dialog that you can see below confirms the version of SSMS (19.1) that you are going to install. After choosing where you want to install the app, click Install to begin the installation process.

• Once the installation starts, it will display a dialog showing the overall progress. The entire setup takes a few minutes, and there are no other intermediate steps or choices that you need to make.

• When the installation is completed, you’ll see the following dialog and you can just click on Close.

At this point, both SSMS and ADS have been installed onto your PC, and you can go ahead and run them. Typically, there is no need to reboot your system.

Connecting SQL Server Management Studio to SQL Server

Let’s start using SQL Server Management Studio to connect to one or more SQL Server instances: 

• To launch SSMS, open the Start menu and look for Microsoft SQL SERVER Tools 19. Expand the node by clicking the down arrow, and then select SQL Server Management Studio 19.
• The first screen that you’ll see is the following Connect to Server dialog.

• To connect to the relational database server, select Database Engine from the Server type dropdown, and then enter the name of the SQL Server instance that you want to connect to. If you are on a development system and SSMS is on the same system as the SQL Server instance, you can enter just a “.” (a dot or period – no quotation marks) for the Server name.
• Next, supply the authentication method. In most cases, this should be Windows Authentication, which will use your current login credentials to connect to the SQL Server instance.
• You may also choose might also be SQL Server authentication or Azure Active Directory. To use SQL Server authentication, you would need to supply a SQL Server login ID and password.
• Clicking Connect will attach SSMS to the SQL Server instance, as you can see below.

In the left pane of the SSMS Object Explorer, you can see some of the databases and tables that are on the SQL Server system.

How to connect to Azure Data Studio to SQL Server

The connection process for Azure Data Studio is very much the same as SSMS:

• When ADS first opens, you’ll the following Welcome screen.
• To establish a new connection to a SQL Server instance, click New then select New connection in the drop-down menu.

• The ADS Connection dialog is a lot like the SSMS connection dialog. Here, the Connection type is set to SQL Server.
• The Server parameter takes the name of the SQL Server instance, which can be a “.” (a dot or period – no quotation marks) if you’re connecting to a local computer.
• Just like with SSMS, you need to choose the Authentication type. Typically, you would use the Windows Authentication mode, but you might also be using SQL Server or Azure AD authentication depending on your SQL Server instance.
• You can also specify the database name that you want to connect to, but this is optional.
• Clicking the Connect button will connect ADS to your SQL Server system.

After ADS is connected to a database instance, you can use it to browse through the databases and their objects, or you can create and run queries and notebooks.

Should you use SQL Server Management Studio or Azure Data Studio?

In this tutorial, I showed you how to download and install SQL Server Management Studio and Azure Data Studio, which are the two primary management and development tools for SQL Server. SSMS is primarily a management tool, and it only runs on Windows. ADS, however, is primarily a development tool that runs on multiple platforms including Windows, Linux, and the Mac.

As you saw, the SSMS download now includes ADS, even though there is also a separate download for that app. Both products are completely free for personal and commercial use, and they can connect to multiple instances of SQL Server.

Related Article:

• How to Install SQL Server Data Tools for Visual Studio
• How to Install SQL Server 2022 and Azure Data Studio on Linux