Azure Data Studio Introduction

By:   |   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.

SSMS Supported Platforms

Azure Data Studio Supported Platforms

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

Azure Data Studio Supported Platforms

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.
azure data studio connection details

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

azure data studio add server group

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

azure data studio connection details

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

azure data studio server groups

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
azure data studio dashboards

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.

azure data studio dashboards

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.

azure data studio Marketplace Extensions

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.

Azure Data Studio Modern Development Editor

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.

Azure Data Studio sql code snippets

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.

Azure Data Studio sql code snippets

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

Azure Data Studio sql code snippets

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.

Azure Data Studio peek definition

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

Azure Data Studio peek definition

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.

Azure Data Studio 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.

Azure Data Studio Integrated Terminal

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
Azure Data Studio Integrated Deployment Options
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.



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



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

View all my tips


Article Last Updated: 2022-06-07

Comments For This Article





download














get free sql tips
agree to terms