Azure Data Studio Introduction

By: Rajendra Gupta   |   Updated: 2022-06-07   |   Comments   |   Related: > Azure Data Studio

Problem

Traditionally SQL Server has been a Windows based relational database system, but it is also available on Linux. To manage SQL Server, SQL Server Management Studio (SSMS) has been used on the Windows platform. In this article we look at Azure Data Studio which is another tool you can use to manage SQL Server both on Windows, Linux and the cloud.

Solution

Azure Data Studio is a cross-platform data management tool built on Visual Studio Code. It has several exciting features such as IntelliSense, Code Snippets, Source Control using GitHub, keyword completion, Built-in, and Customizable Server and database scoped dashboards, extensions, smart SQL code snippets, Server Groups, built-in terminal (Bash, PowerShell, sqlcmd, BCP), visualization of query result sets, built-in Jupyter notebooks and Jupyter books. With SQL Server on Linux, Microsoft introduced this tool for installing on Windows, Linux, and macOS.

The Azure Data Studio is an open-source client tool, and its source code is available on GitHub under a source code end-user license agreement(EULA). Therefore, you get the rights to modify and use the ADS software, but you cannot redistribute or host in a cloud service.

You can install SQL Server Management Studio on Windows and remotely connect to the SQL Server Linux instance. However, if you want to install a client tool on a Linux server, Azure Data Studio plays a vital role. You can connect cloud SQL and on-premises SQL Servers with this tool.

SSMS Supported Platforms

SSMS documentation lists the supported operating system like Windows.

Azure Data Studio Supported Platforms

Azure Data Studio documentation lists Windows, macOS, and Linux platform support.

Azure Data Studio Features

Installation options

Azure Data Studio provides various options for installation, as stated below.

• Windows:
  • User installer: The user installer does not require you to have administrator privileges for installation. It uses the LOCALAPPDATA folder for the installation location.
  • System installer: The system installer requires administrative privilege for ADS installation.
  • ZIP file: You can extract a ZIP file for ADS installation.
• Linux: ADS supports Debian(.deb), RPM and compressed .tar.gz file.
• Server Groups: You can create separate groups in the Azure Data Studio connection manager. For example, we can create separate groups for Production, Staging, and Development instances.

Each server group can have a dedicated color assigned to it.

While adding the servers, you can define a customized name as well. For example, the below connection page defines a local instance(.) as ProductionInstance.

The following image shows two server groups [Production], [Development] with local instances added as [ProductionInstance1] and [MyDevServer].

Dashboards

Azure Data Studio shows the default server dashboard with the following details.

• SQL Server, Version, Edition, Hostname, and OS version
• Backup status
• Individual database size chart for both data and log files

Similarly, the database dashboard shows its recovery model, last full backup, last log backup, compatibility level, database owner, and an option to search database objects.

Marketplace Extensions

Azure Data Studio extensions add new functionalities and features to the ADS. It can be provided by Microsoft or individuals. Here are a few popular extensions:

• Admin Pack for SQL Server
• Database Administration Tool Extensions for Windows
• SQL Server Agent
• SQL Server DACPAC
• whoIsActive

You can also design your extensions and publish them in the marketplace.

Modern Development Editor

The Azure Data Studio is enriched with a modern development editor with the following features.

IntelliSense: As soon you start typing in the editor, IntelliSense tries to help with supported objects and actions. For example, the below image gives a list of objects available in the corresponding database schema.

SQL code snippets guide you through creating a database and its objects, such as tables, views, stored procedures, logins, users, and roles. For example, as you write Create, it gives options as followings.

If we want to create a new temporary table, choose it and press Enter. You can replace the highlighted fields for user inputs and create the table.

Similarly, the below code snippet is for an INSERT statement.

Peek Definition or Go to Definition Functionality

Suppose you have a stored procedure that contains multiple tables joins. To check the table columns, you need to go to a different window and switch back between windows. Azure Data Studio provides a peek definition or go-to definition functionality to solve this issue.

Peek definition: Peek definition opens the object definition such as Create Table in the query editor.

Go to Definition: It opens the object script in the new query window.

Export Query Results

Once you execute a query, you can export its results into CSV, JSON, XLSX, and text files. You can also visual query results into Bar, Grid, Scatter, TreeMap, Density, Strips, Columns, Stacks, SandDance charts.

Integrated Terminal

The Azure Data Studio has an integrated terminal for executing PowerShell, command-prompt script, or Bash scripts without switching over to different consoles.

Integrated Jupyter Notebooks

Azure Data Studio has integrated Jupyter notebooks, an open-source web application for creating and sharing the live code, visualizations, script results, narrative text, and query Azure Monitor Logs. These notebooks can be used for machine learning, statistical modeling, data visualization, and transformation. You can execute SQL Server, Python, Spark, and PySpark code using its kernels.

HDFS Integration

You can query HDFS data in a SQL Server 2019 Big Data Clusters using Azure Data Studio.

Integrated Source Control

Azure Data Studio has integrated Git repositories as source control management.

Integrated Deployment Options

Azure Data Studio provides different deployment options for implementing on-premises and Azure resources. These deployment options are:

• SQL Server on Windows
• SQL Server container Image
• SQL Server Big data cluster
• Azure SQL Database
• Azure SQL Managed instance
• SQL Server on Azure Virtual Machine

Next Steps

• Watch for new articles on installing Azure Data Studio on Windows, Linux, and macOS.
• Stay tuned for exploring the features of Azure Data Studio in detail.
• Refer to existing published tips on Azure.

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips