(Last edited on 9 January 2020)
Note: This was originally written for the book SQL Server 2017 Administration Inside Out, but did not meet the publishing deadline. A revised version is now included in the book SQL Server 2019 Administration Inside Out, but because I wrote both sections, there’s no plagiarism happening here.
A new cross-platform tool for SQL Server and Azure SQL Database
Microsoft has released Azure Data Studio (known as SQL Operations Studio in preview), an exciting addition to the administration and development tooling (DevOps) for database platforms in general, and SQL Server specifically.
Azure Data Studio is a tool that runs on multiple operating systems, supporting multiple database management systems.
Think of Azure Data Studio as the spiritual successor to SQL Server Management Studio (SSMS). It performs many of the same tasks as SSMS; runs on Windows, macOS and Linux; and targets not only SQL Server, but Azure SQL Database and Azure SQL Data Warehouse as well.
Because it is open source, there is a very good chance that more database platforms may be supported in the future.
A comparative example would be Visual Studio Code, which is a cross-platform integrated development environment (IDE) targeting multiple programming languages. In the same way that a software developer can use any operating system for writing code, you can use Azure Data Studio on any operating system for managing and developing databases.
Open source software
Azure Data Studio is an open-source software (OSS) project, forked from Visual Studio Code and based on the Electron shell and Node.js, a JavaScript runtime. It is free for use, and anyone can contribute to the project.
Because it shares a similar development environment to Visual Studio Code, it can have custom themes and is fully extensible.
In other words, you can easily install existing third-party plugins and extensions to improve your workflow, or even write your own and contribute these back to the main product codebase. You should expect the same vendors who write plugins for SQL Server Management Studio to support Azure Data Studio in the near future.
User interface
The main interface for Azure Data Studio is made up of viewlets and tiles, similar to the docked windows in SQL Server Management Studio. These elements present information to monitor and administer your database environment.
The interface is fully customizable, with a dashboard that shows insights, performance metrics and telemetry; recent connections; an Object Explorer (similar to SSMS); and of course a query window for writing queries, complete with a results grid. Results can be exported to CSV, Excel and JSON.
Flyouts and Tiles
Whereas SQL Server Management Studio uses dialog windows that pop up in the center of the application interface, the Azure Data Studio dialog screens, called flyouts, appear from the right of the user interface until the necessary action is performed. This takes some getting used to for people who are more familiar with SSMS.
Take for example a typical database backup. In this example, we are backing up the ReportServer database on this machine. Notice that the main user interface has been greyed out, while the backup options appear on the right-hand side. This is a flyout (you can see a larger version below).
The flyout is scrollable, meaning you can navigate up and down (using your mouse scroll wheel or the keyboard) to see more options. In the following image, the main database backup options can be seen, with a scroll bar on the right.
Notice that you can even script the operation, just like in SSMS.
Feature support
Azure Data Studio includes many of the same core features for administering and developing against SQL Server that you would expect to find in SQL Server Management Studio, including:
- managing registered servers
- viewing server and database reports
- writing queries
- managing security
- generating scripts
- viewing and analyzing query plans
- database consistency checks
- index and statistics maintenance
- backups and restores
Command line interface
One of the breakout features in Azure Data Studio is the built-in command line interface, which allows interacting with the operating system from within the application. This improves productivity by not having to switch away from the Azure Data Studio interface to work with files or execute scripts.
On macOS and Linux, bash is currently supported, while PowerShell is supported on Windows.
Multiple database platforms
Because several platforms are targeted by this product (SQL Server, Azure SQL Database and Azure SQL Data Warehouse), not all features may work on each platform, but the underlying database API (application programming interface) hides most differences from the end-user. In other words, the workflow to manage SQL Server will work the same as it does for Azure SQL Database.
Future roadmap
This is a very new product from Microsoft, with a lot of work ahead of it. Owing to strict publishing timelines for the book, it was not possible to include any information about Azure Data Studio, which is why you’re reading about it on this blog.
If you prefer using macOS or Linux for your day-to-day work, it is definitely worth considering Azure Data Studio as a replacement for SQL Server Management Studio, especially if you already develop against SQL Server on Linux, or Docker on macOS.
The new Microsoft is embracing the people that make technology great. These are exciting times.
Share your thoughts on Azure Data Studio with me in the comments below.