For many people (including me), when it comes to managing SQL Server databases, SQL Server Management Studio is still the de facto tool. It’s a solid product that has existed for over 17 years and has matured alongside SQL Server itself. However, in recent years, Azure Data Studio has been quietly chipping away at the advantages that SSMS once held by bringing those features to ADS, either as a built-in capability or as a Marketplace extension. In this post, let’s explore this tool to see how it stacks up against SSMS and see if it can replace or complement it for your database management needs.
Why Should I Migrate?
You may be wondering why one should migrate in the first place. That’s a fair question, especially if you have been a fervent SSMS user for a long while. Here are a few points you may want to consider:
- SSMS is Windows only while Azure Data Studio is an open-source, cross-platform tool that works on Windows, Mac and Linux. It made sense that the premiere SQL Server management software was Windows only as the database itself only ran on Windows. But all that changed in October 2017 when Microsoft released SQL Server 2017, a cross-platform version that ran not only on Windows but also in Linux and in docker containers. Not only that, but a vast majority of the development tools and frameworks that come out of Redmond these days are open-source and multi-platform, targeting Windows, Linux, and Mac users. So, even if you are a Windows user but you have team members that are on Linux or a Mac, you may want to use a tool that supports those environments, as well.
- ADS boasts a modern interface. If you are a Visual Studio Code (VS Code) user, you’ll feel right at home with ADS. The UI is strikingly similar. In addition to the look and feel, ADS has the same concept of a Command Palette that you can pull up to easily recall a feature in the software.
- Marketplace Extensions: ADS has the same extensibility model that VS Code features, arguably the number one reason behind VS Code’s meteoric success, earning it the most beloved code editor status in the world of web development today. Similarly, these ADS extensions provide additional features to the core software that allow you to carry out a multitude of tasks. However, note that that the number of ADS extensions, as of the time of this writing (February 2023) is less than a 100 while VS Code has over 43,000 extensions in the Marketplace.
- Support for Multiple Data Sources: In addition to SQL Server, you can use ADS for PostgreSQL, SQL Lite and Azure SQL.
- ADS is the future. You’ll see increased investments being made into ADS as it is the new cross platform tool. The cross platform and modern foundation that this software has will allow for much room for extensibility and improvement that the 17-year-old SSMS won’t be able to do.
Installing Azure Data Studio
If you are on Windows and have a recent version of SQL Server Management Studio on your machine, you may not even have to download and install it because Microsoft has been automatically installing it alongside SSMS for a while now! Bring up your Start Menu and do a quick search for “Azure Data Studio” to see if it’s already there. If not, you can download it from here.
Command Palette
Just like in VS Code, the Command Palette is that ultimate weapon that allows you to instantly search for and recall features within the application. You can access it by the keyboard shortcut Ctrl + Shift + P (on Windows) or Cmd + Shift + P (on macOS). Just by getting familiar with this one feature, it easily allows you to discover all the other ones. You can use it to open up settings or create a new table or run a new query. If you need to carry out a specific function but don’t know how to get there, simply bring up the command palette and search for it.
Table (Designer)
If you’re used to creating and updating tables through the visual designer in SSMS, you have access to similar UI features in ADS. Simply right-click on the Tables folder and choose “New Table” and you’ll be greeted with a Create Table designer.
Add / Edit / Delete Rows
Want to quickly add a row of data without writing an INSERT statement? Or want to make a quick edit without writing any SQL? Simply right-click on a table and select “Edit Data” to insert new rows and update existing ones in a visual UI editor like Excel. You can also delete a row by right-clicking its row number and choosing the “DELETE” option.
Generate Scripts
You can quickly script out CREATE and DROP table scripts by right-clicking on the table and selecting the “Script…” options.
Snippets
Azure Data Studio comes with a set of useful Data Definition Language (DDL) and Data Manipulation Language (DML) statements. Start typing a keyword and the editor will give you suggested choices to select from. Select the desired one using the up and down arrow keys on the keyboard and press Enter to select.
Export Capabilities
Azure Data Studio comes built in with a set of data export capabilities. You can save your results as Comma Separated Volume (CSV), Excel, JSON, XML and Markdown files.
Notebooks
This topic requires a whole blog post of its own as there is a lot to unpack here. But as far as this introductory post is concerned, Azure Data Studio integrates with open source Jupyter Notebooks technology, allowing you to collect, organize and share queries along with any associated textual data (notes) in a simple and streamlined manner. You can create a new notebook from the File menu.
Extensions
Azure Data Studio’s extensibility model allows Microsoft and third parties to enrich the software by adding functionality that can be added in on an as-needed basis. While the ecosystem is still quite young with only a few extensions (compared to that of Visual Studio Code), there are some great choices to snag, depending on your needs. The Admin Pack for SQL Server, a collection created by Microsoft itself, brings in features such as SQL Server Agent, SQL Server Profiler, SQL Server Import Wizard and SQL Server Dacpacs to Azure Data Studio. Browse the Extensions tab for many others.
Keep an eye out for a future episode where we dig deeper into the coolest extensions that Azure Data Studio Marketplace has to offer.
Conclusion
I’m quite excited for the future of Azure Data Studio. For me, it still is not at a place where it can be my primary driver as it still lacks some of the features that SSMS provides such as the Database Engine Tuning Advisor, Database Diagrams, and others. However, it is only a matter of time where those features are added in, either as an extension or as a core feature.