Azure Data Studio (formerly SQL Operations Studio)
Azure Data Studio is a free Microsoft desktop tool, initially called SQL Operations Studio, that can be used to manage SQL Server databases and cloud-based Azure SQL Database and Azure SQL Data Warehouse systems. The lightweight software is designed to make routine database development, querying and administration work easier than it is with Microsoft's more-functional SQL Server Management Studio (SSMS) software. As a result, Azure Data Studio is aimed not only at database administrators, but also at software developers and other non-DBAs who manage databases as part of their jobs.
Also referred to as SQL Ops Studio in its original incarnation, the tool offers a consistent user experience across Windows, Linux and macOS clients. It first became available as a public-preview technology in November 2017 and remained in preview mode until September 2018, when Microsoft made the product generally available and changed its name to Azure Data Studio. The company has issued new releases with added features on a monthly basis since launching the SQL Operations Studio preview, a practice it plans to continue with the GA version.
Features of Azure Data Studio
Azure Data Studio uses Microsoft's Visual Studio Code source code editor as its foundation; the management tool was forked from Visual Studio Code, and as in the latter technology, the open source Electron framework serves as a cross-platform application building block. An integrated terminal window enables users to execute database management commands with Bash, PowerShell, sqlcmd and other command-line tools from within Azure Data Studio's UI.
The tool includes built-in features like multi-tab windows, a T-SQL editor, smart code navigation and integration with the Git open source version control system for tracking source code. In the T-SQL editor, users can save the results of database queries as text, JSON or Excel files; in addition to query development, the editor supports the creation of stored procedures, scripts and other database objects.
Azure Data Studio also provides a set of T-SQL code snippets that can be used to streamline the process of writing T-SQL statements. The code snippets are templates that generate the T-SQL syntax needed to perform different administrative actions, from updating existing database objects to creating new databases, tables and views. Users can also create custom code snippets for actions that aren't covered by the built-in ones.
Another key feature in Azure Data Studio is the ability to create insight widgets that visualize the results of T-SQL queries to make it easier for DBAs and other users to see what's happening in their databases. An insight widget uses JSON code to automatically generate a chart or graph that displays information about an individual database or a SQL Server system, depending on the T-SQL query. Users can drill down into the underlying data and initiate management actions based on what they find.
Azure Data Studio supports SQL Server from the 2014 release to the SQL Server 2019 preview that Microsoft also made available in September 2018. Both Azure SQL Database and Azure SQL Data Warehouse are also supported, and as of November 2018 the tool included preview support for Azure SQL Database Managed Instance, a version of the cloud database system that's almost functionally equivalent to SQL Server.
Benefits of using Azure Data Studio
Users of Azure Data Studio can create customizable dashboards to monitor and troubleshoot performance bottlenecks and other issues in databases and at the server level. The tool includes separate server and database dashboards, which can be configured with insight widgets to provide at-a-glance views of performance levels, system usage and other metrics. In addition to viewing that information, users can back up and restore databases, edit the data in database tables and perform other management tasks from inside the dashboards.
A server groups function enables users to organize the servers and databases they work with into different groups to help streamline management processes, with color coding to distinguish between the groups. The group configurations are saved in the tool's user settings and can be modified via an editing feature that includes the ability to drag and drop servers from one server group to another.
Azure Data Studio provides APIs and authoring tools to extend the tool's functionality beyond what's in the base software. Users can create their own extensions or take advantage of ones made available by third-party developers. Microsoft also is developing extensions, including ones that incorporate functionality from SSMS.
For example, the company added support for its SQL Server Agent job scheduling and execution tool via an extension in the April 2018 update of the SQL Operations Studio preview; an SSMS wizard used to import flat files was similarly added in the August 2018 update. And an extension for querying and managing the preview version of SQL Server 2019 was introduced as part of the first Azure Data Studio GA release; that extension supports the big data clusters enabled in the database update and offers the first notebook-style development environment for SQL Server.
While Microsoft says Azure Data Studio wasn't built to replace SSMS, the newer tool does offer similar types of functionality in a more lightweight package. Its cross-platform client support also gives users more flexibility than the Windows-only SSMS software does. And it's designed to be easy to use regardless of skill level. As a result, there's less of a learning curve for users who aren't full-time DBAs than there is with SSMS, according to Microsoft.
Microsoft recommends that workers use Azure Data Studio instead of SSMS if they primarily edit and run queries against databases and need to quickly chart or visualize query results; in addition, connecting to and querying a SQL Server 2019 big data cluster is supported only in Azure Data Studio at present. On the other hand, SSMS should be used by DBAs and other IT pros who spend most of their time on database administration tasks, including deep configuration and security management work, and need to use performance tuning advisor tools and dashboards, according to Microsoft.
How to get started with Azure Data Studio
To install Azure Data Studio, users download the version of the program for their client platform from Microsoft's website or GitHub and then either start the software directly or extract it from a .zip file, depending on which download option they've chosen. A Connection dialog box automatically opens when the tool is first run to prompt users to connect it to SQL Server or Azure SQL data stores, with authentication and password protections built in to ensure that only authorized users can do so.
The Azure Data Studio UI is divided into three sections: an Activity Bar with a set of clickable icons at the left of the screen, a wider rail with links to various folders and features, and the main window, which is where dashboards and the T-SQL editor display. Multiple tabs can be open in the main window, enabling users to run different dashboards and the query editor at the same time.
The top bar in the software allows users to execute or cancel queries; connect to and disconnect from servers and databases; and show estimated query execution plans. Azure Data Studio also includes a bottom bar that provides access to the integrated terminal window and other features.
Two types of configuration settings are supported: user settings that are applied universally, and workspace settings that override the universal ones in individual folders. To modify the default settings, they can be opened from the File menu or by pressing Ctrl + Shift + P. For example, after doing so, users can change the color scheme in Azure Data Studio or the SQL Operations Studio preview releasae if they're still running that by choosing Preferences and then Color Theme from the drop-down menu.
Microsoft recommends that new users enable a "preview features" setting by default when they download Azure Data studio so they can access functionality that's available only in preview mode if they so desire. Meanwhile, existing users of the SQL Operations Studio preview release who want to retain custom settings when they upgrade to Azure Data Studio can copy the files in their user settings folder, save the files locally on their computer and then paste the contents into the same folder in Azure Data Studio.