Diving into Azure Data Studio

SQL Server Management Studio (SSMS) has been a mainstay of the SQL Server ecosystem as far back as SQL Server 2005. While the strong feature set and robust capabilities have helped DBAs and developers alike for the better part of 15 years, there remained a need for a cross-platform solution to allow additional accessibility to a new generation of developers, database administrators, data analysts, and data architects alike.

Enter Azure Data Studio (ADS). Launched as an open-source initiative, ADS first appeared as “SQL Operations Studio” with pre-release public previews being available in late 2017. With the general availability release launched in September 2018, the product was renamed to what we know today as Azure Data Studio.

While relatively new, ADS is starting to mature into a quality addition to cross-platform tooling following closely in the footsteps of its cousin, Visual Studio Code. ADS is a downstream fork of VS Code and is merged regularly. For avid users of SSMS, ADS may not look like much of a competitor; however, there are some features unique to ADS that even ardent users of SSMS will come to appreciate. If the acknowledgment or awareness of ADS from SSMS users has been lacking, this will likely change since starting with the release of SSMS (18.7.1 on October 27, 2020), ADS was included with the SSMS installation and installed alongside SSMS .

The early iterations of ADS targeted SQL Server specifically. However, additional support for other databases has steadily increased as the product continues to evolve. Currently, ADS includes support for SQL Server, Azure SQL, Apache Spark, and Hive. Additional support for open-source databases is included through an extension such as PostgreSQL with pending support for MySQL currently on the roadmap.

Target Audience

The capabilities that were included with the early preview releases of Azure Data Studio were targeted at Database Administrators, Database Developers, Application Developers, and Data Analysts. With the release of SQL Server 2019 and the added support for Apache Spark and the Hadoop Distributed File System, a new architecture was born that combined these technologies into a platform known as “SQL Server Big Data Cluster”.

It was at this point where the ADS team began to consider the potential for additional support and capabilities to include user roles such as Data Engineers and Data Scientists. Interactive programming is common practice within these data roles, and the usage of live coding documents such as Jupytor Notebooks has gained widespread adoption in the data science community. As we will see later in this article, the introduction of Jupytor Notebooks support directly in ADS has opened the tool to a wider audience across all the major OS platforms.

Cost

From the inception of Azure Data Studio to the most recent release as of August 18, 2021, the cost continues to remain free for both private and commercial use.

Relationship to SQL Server Management Studio

The developer DNA for Azure Data Studio comes directly from SQL Server Management Studio as the same team at Microsoft is responsible for both products. With the release of SQL Server 2017 and the support for SQL Server on Linux and containers, it was clear to the data team at Microsoft that a cross-platform option for SSMS features was required to provide access to developers that are heavily using platforms such as Linux, containers, and Mac OS. It was this cross-platform support that was the main catalyst to beginning work on ADS as the cross-platform option.

Quick Tour and Features

Upon launching Azure Data Studio, users of Visual Studio Code will immediately notice the resemblance and similarities.

ADS is a downstream fork of VS Code and is merged regularly. The version number of ADS as well as VS Code are both visible in the About > Help menu.

Connections

Like any data tool, before data can be accessed, a connection to the relevant data source needs to be established. Azure Data Studio has many options for creating and organizing collections and supports a growing number of data sources. Current support includes SQL Server (on-prem and Azure SQL) Azure Synapse Analytics, and PostgreSQL support is available via extension (Extensions will be discussed later in this article).

Support for MySQL is expected to come via extension in an upcoming release although there is no definitive date at the time of this writing.

Connections are accessed via the “Connections” option in the vertical toolbar:

In addition to the “SERVERS” view which offers the traditional method of setting up a data connection, you will notice additional views including “AZURE” and “SQL SERVER BIG DATA CLUSTERS” that offer the ability to connect to existing Azure accounts and SQL Big Data Clusters, respectively.

The colored groupings are fully customizable and configurable. Server groups are created using the “New Server Group” option:

Adding a Connection

Adding a connection is straight-forward and should be familiar to creating connections in other data tools, such as SSMS.

The connection details present options you would expect, including “Connection type” in which the options may vary depending on any extensions that are installed. “PostgreSQL”, for instance is available with the installation of the PostgreSQL extension. You can assign the connection to any previously created server group by selecting from the “Server group” dropdown. The “Advanced” options include additional settings used to configure the connection further with settings including port number, connection pooling, timeout, encryption, etc.

Connections to Azure SQL can be configured either through the add connections dialog – particularly useful to include relevant connections in a server group collection. Alternatively, the option to directly connect to Azure is available and any relevant Azure SQL databases are easily accessible.

Exploring Databases

The object explorer is used to display a tree view of a server’s databases and associated objects including tables, stored procedures, views, etc. This view should be familiar to users of SSMS or Server Explorer in Visual Studio.

When using the menus and options to work with the objects in the object explorer, it begins to highlight the differences with SSMS and where avid users of SSMS may be expecting to see a myriad of administrative options that are not yet available in ADS.

For example, right-clicking on the database reveals a context menu with limited options. Although the primary options that one would expect such as “New Query” and “Backup/Restore” are available, there are many options missing when comparing to a more mature tool such as SSMS. Options such as “Detach”, “Take Offline”, “Shrink”, etc. are not found in ADS likely because the primary focus of ADS is for accessing data and many of the administrative commands available in SSMS can be utilized using T-SQL commands. It is also worth noting that the tooling void for administrative actions is currently being filled through extensions.

Queries

Clicking “New Query” opens a query editing window that allows for entering SQL statements.

Full IntelliSense and syntax highlighting are included as well as support for code snippets. Start typing “sql” and the code snippets bundled with ADS will be listed. Custom code snippets can also be added.

Running the query will produce the records as rows into the results pane beneath the statements.

Clicking “Explain” and re-running the query, will present the query plan.

Saving the query tab will create a .sql file. As we will see later in the article, saving .sql files or exporting and saving as Notebooks, along with the built-in support for source code management, provide for better organization, version control, and safekeeping of your SQL assets.

While you do not get all the bells and whistles that SSMS offers when creating and running SQL statements, you do get some additional features that are unique to ADS. As an example, the group of menu icons to the right of the results pane offer the following options that can be applied directly against the results of the query:

Each of the “Save as…” options will save the results to a file of the respective format. This is helpful for taking the results and running additional analysis outside of ADS in another analytical tool or for simply saving a snapshot of the data at a point in time.

The “Chart” option will create an interactive chart based on the data in the results pane.

Query History
An option I have really come to appreciate is the “Query History” view (added via extension). I have often closed a file in SSMS or closed SSMS entirely without saving the current query session and regretted not being able to “recall” the unsaved file/query. To help with this, ADS includes a query history pane (accessible from within the View menu [View > Query History]) that lists recently executed queries. It also offers the ability to run the query directly from the history list or load the query into the query editor!

Notebooks

Interactive programming has been exploding in recent years and the proliferation of this approach to producing code has been a staple in the Python community with access to products such as Jupyter Notebook developed and released by Project Jupyter. With Azure Data Studio, interactive programming is available directly in the interface and native support for Jupyter notebooks is a unique feature to ADS. Because this feature is not available in SSMS, the benefits that DBAs have been finding in the usage of creating interactive SQL code and documentation, could lead them to add ADS as a core data tool.

Let’s take a quick walk through of creating and working with a Notebook.

From the main menu, File > New Notebook or Alt+Windows+N on the keyboard will open a new Notebook editor.

With the editor window open, the default Kernel is set to SQL. All your existing connections are accessible from the “Attach to” dropdown or you have the option of creating a new connection to use directly with the Notebook.

The list of available Kernels currently includes SQL, PySpark, Spark with Scala, Spark with R, Python, and PowerShell.

It is interesting to see the mix of the other well-known data analysis languages with SQL inside a data access tool. As mentioned earlier, one of the goals with Azure Data Studio was to support additional user roles such as Data Engineers and Data Scientists. With the addition of the SQL Kernel, it opens even more data analysis possibilities, as well as potential for use cases that are primarily targeted at DBAs.

There are two types of cells that can be created. One is a text cell that is used primarily for describing the interactive coding cells that are interweaved throughout the Notebook. Clicking to add a text cell will bring up the text editor.

An early complaint from DBAs when working with the Notebooks, was the need to have to learn the intricacies of creating formatted documentation using Markdown directly. As such, a simple but effective rich text formatting tool was included to reduce the reliance on having to use Markdown syntax only. However, if one prefers to hand-code text formatting using the Markdown syntax, that option is still available as well.

Here is a simple example of how both text and code (and data!) can be combined to produce interactive documentation more declaratively. It starts with a text cell to build the title of the Notebook, followed by a code cell to insert the SQL statements. The generated results of the SQL statements are displayed directly in the Notebook and the results table includes options found in the standard Query results pane for exporting data and viewing the results in a chart.

Additional text and code cells can be created throughout the Notebook document and saving the file uses the Jupyter Notebook .ipynb extension allowing for this Notebook to be shared with others to load directly into Jupyter Lab or any other data analysis tool with native Jupyter Notebook support.

Saved Notebooks are available via the “NOTEBOOKS” section.

A collection of Notebooks can be grouped together to form a “Book” with the opportunity to link directly to Notebooks within the entire book and provide a robust collection of interactive documentation that is far superior to maintaining a collection of SQL files and sharing them ad-hoc amongst your team.

Microsoft’s internal data team has been making extensive use of Notebooks for sharing key documentation. They have also started to make these Books available outside of Microsoft. As an added benefit within ADS, provided Books can be accessed and downloaded directly from within ADS via the Command Palette.

Type Ctrl+shift+p on the keyboard to bring up the Command Palette. Start typing “jupyter” to view a list of commands that include the “Jupyter Books: SQL Server 2019 Guide” and hit “enter”.

This will load an entire Book of interactive Notebooks related to managing and supporting SQL Server 2019 Big Data Clusters. This is a great example showing the power of using interactive Notebooks for documentation, troubleshooting, and knowledge sharing.

In addition to creating and utilizing Notebooks for operations, they can also be useful to create and share for collaboration, reporting, and data workflows including experimentation, data prep, and data analysis.

Source Code Management

Visual Studio Code offers strong tooling and support for source code management including tight integration with GitHub. Azure Data Studio adopts this feature from VS code and offers source code management for all the created .sql, notebook (.ipynb), and database project files.

Terminal

Another feature that users of VS Code will be familiar with and appreciate in ADS is the easy access to the integrated terminal window. Pressing “Ctrl+`” opens a new terminal window for applying commands directly from a command line. The default here is set to “powershell”, but any installed terminal window can be set to default including the standard Windows Command Prompt or a Linux shell such as the Bash Shell.

Extensions

Sharing similarities to VS Code, Azure Data Studio seeks to shine as a query editor first and promotes access to viewing and analyzing data as a first-class experience. To keep the footprint light and the eye on fast editing of SQL and Notebook files, additional capabilities are handled through the growing list of extensions.

SQL Agent and Profiler as an example, provide extending the capabilities of ADS to bring in more administration functionality. Other notable extensions include the “Query History” view (demonstrated earlier), PostgreSQL for adding support for Postgres databases, Dacpac support, and SandDance for creating enhanced visualizations from the query results pane. There is also an Azure SQL Managed Instance Dashboard extension (currently in preview) that allows for a concise view of your managed instance including statistics on SSD and Premium disk storage in addition to monitoring metrics for CPU usage.

The existing list of available extensions is nowhere near that of VS Code, but this should also improve over time as more extensions are added to the marketplace.

Roadmap

While there is no definitive roadmap available, looking at the upcoming milestones and popular issues in the GitHub project, the next few releases will likely be maintenance releases for ensuring more stability in the product.

There is demand from the community for MySQL support which will likely be added via an extension (similar to support for PostgreSQL). Other high-demand features include a table design editor and increased performance for executing and viewing large datasets. I would personally like to see support for the .NET kernel added as an option in Notebooks – this is apparently in the works, but no timeline has been released for availability.

Which to Use – Azure Data Studio or SQL Server Management Studio?

Obviously, for anyone working outside of a Windows OS, Azure Data Studio is the only option when comparing the two. For ardent users of SQL Server Management Studio, the value proposition to move to ADS may not be apparent at first glance. However, the unique ability to access the SQL kernel in a lightweight editor and create notebooks and notebook collections easily, gives the nod to ADS when running data analysis tasks or building SQL scripts in the form of Notebooks to share with colleagues.

According to Microsoft, “Research has shown that users spend an order of magnitude more time working on query editing than on any other task with SQL Server Management Studio.” That’s an interesting fact considering the power that SSMS has to offer for database management capabilities, but management of the database is always in the hands of a chosen few on most teams and access to the data is relevant for all. If you are a command line user, the built-in terminal with ADS is where you will spend time sending admin commands via utilities like sqlcmd or PowerShell. If you prefer the GUI experience and lots of wizards, SSMS is better. SSMS will also be the preferred choice for security management, live query statistics, and provides tooling for viewing and creating database diagrams and table designers. When speed is a factor? Then stick with SSMS. ADS is behind on rendering performance. Time to render the data is slower in ADS when compared directly to SSMS and scrolling longer lists of data show a noticeable lag. I am sure this will improve as the updates continue to be provided but for now, if you are working with large tables and datasets, SSMS is your best bet.

Summary Comparison:

Conclusion:

As a product, Azure Data Studio is still early in its development and evolution. However, as a lightweight, cross-platform editor and data-viewer, it ticks a lot of boxes for accessing data and running analysis through the integrated Notebooks. For SQL Server users running on Windows, it could be a hard sell initially to buy into using ADS over SQL Server Management Studio. However, using SSMS for heavy administration-related tasks and large datasets, and using ADS for the Notebook support and integrated terminal will make a lot of sense for many users.

I like to work with PostgreSQL, and I have since abandoned other query and data editors, including some commercial options, to work exclusively in Azure Data Studio. I have noticed vast improvements in stability and feature set in the past 12 months, and I am enthusiastic about the continued potential for this product.

This article has been technically reviewed by Gouri Sohoni.

This article has been editorially reviewed by Suprotim Agarwal.

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

Author

Darren Gillis is a Toronto-based software developer and technologist with 20+ years of experience primarily with Microsoft technologies.  He is a big fan of data and Microsoft Azure, having architected numerous cloud-based software projects leveraging many of the features that Azure has to offer.  He is currently developing a SaaS based compliance platform using C#, React, and PostgreSQL.  Feel free to connect with him on LinkedIn, or follow him on twitter @darrengillis.