- Azure PowerShell vs. Azure CLI - Wed, Mar 15 2023
- Use Azure Storage for backup - Thu, Feb 23 2023
- Azure Data Studio vs. SQL Server Management (SSMS) - Wed, Feb 8 2023
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
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
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
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
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
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
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
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.
