If your Windows systems administration work touches SQL Server products, you need to know when Azure Data Studio is the right fit over the time-tested and venerable SQL Server Management Studio (SSMS).
Avatar
Latest posts by Timothy Warner (see all)

Azure Data Studio and SQL Server Management Studio are tools used for managing and querying Microsoft relational databases, but they have some key differences you should know about. First, let's level the playing field by briefly describing each application.

SQL Server Management Studio, or SSMS for short, is a closed-source Windows-only desktop application used for configuring and administering all components within Microsoft SQL Server. It provides a graphical user interface for managing and interacting with SQL Server databases and objects, and it allows users to write and execute Transact-SQL (T-SQL) queries and scripts, manage database objects, and perform various other database administration- and development-related tasks.

SSMS has traditionally been bundled with SQL Server, but now ships separately. This allows SSMS development to push bug fixes, enhancements, and new features without having to wait for a new SQL Server product release.

SQL Server Management Studio

SQL Server Management Studio

Azure Data Studio is a free, open-source, cross-platform data management tool used to manage SQL Server, Azure SQL Database, and Azure Synapse SQL pools. Azure Data Studio is built on top of the Visual Studio Code platform, which in turn is based on the Electron framework. The tool provides a modern, lightweight, extensible user interface for working with Microsoft relational databases.

Azure Data Studio

Azure Data Studio

Platform support

Despite SQL Server Management Studio's maturity and full feature set, the application is at heart a Win32 desktop application. Thus, unless your macOS or Linux machine is equipped with a remote desktop or virtualization solution, you're limited to Windows Server and client operating systems.

By contrast, Azure Data Studio is built on top of the Visual Studio Code platform. Visual Studio Code, as I'm quite sure you know, is a free, open-source, cross-platform code editor developed by Microsoft. It is based on the Electron framework and runs on Windows, macOS, and Linux.

Therefore, you are welcome to file issues and even contribute to the Azure Data Studio project via GitHub flow.

Open source software like Azure Data Studio symbolizes the new Microsoft

Open source software like Azure Data Studio symbolizes the new Microsoft

Target audience

Azure Data Studio is focused on providing a streamlined experience for data professionals and developers working with SQL Server, Azure SQL Database, and Azure Synapse SQL pools. Personally, I was disappointed that Microsoft chose not to embrace NoSQL databases in Azure Data Studio. For example, being able to work in the Cosmos DB outside the Azure portal would be useful.

By contrast, SSMS is a more fully featured tool that is intended for DBAs and other IT professionals who need to manage and configure SQL Server instances. Note that you can use SSMS to connect to Azure SQL Database and Azure Synapse SQL pools, as well as local SQL Server databases.

Newer versions of SSMS also have native integration with Azure Data Studio, which you can see in the following screenshot. I particularly like how you can start your work in SSMS and pick it up in a Jupyter notebook in Azure Data Studio; that's a nice touch.

SSMS integrates nicely with Azure Data Studio

SSMS integrates nicely with Azure Data Studio

Feature set

SQL Server Management Studio has a wide range of capabilities to support Windows-based Microsoft relational database architects, administrators, and developers. Some of the major features include:

  • Query Editor: Write and execute T-SQL queries and scripts
  • Object Explorer: Hierarchical view of all the objects within a SQL Server instance, including databases, tables, views, and stored procedures
  • Backup and Restore: Create database backups and restore them in the event of data loss
  • Security: Tools for managing users, roles, and permissions, and for implementing security policies
  • Maintenance: Tools for managing database maintenance tasks, such as index defragmentation and statistics update
  • Scripting: Allows users to generate scripts for creating and modifying database objects, as well as for data transfer

Azure Data Studio includes built-in support for features such as IntelliSense, source control, extensions, and notebooks. The Git integration is particularly notable in today's DevOps-central software development landscape.

Jupyter notebooks provide a nice way to combine Markdown-formatted documentation with live code that you can execute directly within the notebook.

Working in a Jupyter notebook in Azure Data Studio

Working in a Jupyter notebook in Azure Data Studio

With regard to the user interface, Azure Data Studio has a more modern and streamlined user interface, while SSMS has a more traditional interface that may be more familiar to experienced SQL Server professionals.

How about language support? In SMSS, you can write in Microsoft-specific data access and analysis languages:

  • Transact-Structured Query Language (T-SQL)
  • Data mining extensions (DMX)
  • Multidimensional expressions (MDX)
  • Analysis Services Scripting Language (ASSL)

In Azure Data Studio, you can write in all those languages, plus a lot more, including:

  • Scala
  • R
  • Python

If you think in terms of "big picture" trends like I do, then you might agree with me that the core question isn't whether you should use SSMS OR Azure Data Studio. Instead, it's a question of when to use which tool in your database development work.

Extensibility

SQL Server Management Studio (SSMS) is not natively extensible. No official APIs or SDKs are available for developing extensions for SSMS. However, there are some third-party tools and frameworks that can be used to add custom functionality to SSMS.

SSMS supports limited extensibility via add ins

SSMS supports limited extensibility via add ins

Because Azure Data Studio is built on top of Visual Studio Code, first- and third-party extensions are "baked into" the product. Therefore, you can find many extensions for Azure Data Studio that can help you manage various aspects of SQL Server, Azure SQL Database, and Azure Synapse SQL pools.

Azure Data Studio supports extensions

Azure Data Studio supports extensions

Although I've limited our discussion to Microsoft's relational database products, you should know that Azure Data Studio, through its extension framework, does enable you to connect, query, and manage MySQL and PostgreSQL databases as well. This decision makes sense when you remember that Azure offers managed solutions for both platforms.

Wrap-up

In summary, if you are a developer or data engineer who needs a simple, extensible tool for managing your SQL Server instances, Azure Data Studio may be a good choice for you, particularly if your client operating system is macOS or Linux.

Subscribe to 4sysops newsletter!

If you are a DBA or system administrator who needs a more comprehensive and feature-rich tool for managing and monitoring multiple SQL Server instances, SSMS may be a better choice.

avataravataravatar
0 Comments

Leave a reply

Your email address will not be published. Required fields are marked *

*

© 4sysops 2006 - 2023

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account