Azure Data Studio Introduction
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.
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
Azure Data Studio provides various options for installation, as stated below.
- 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].
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.
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
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.
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.
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
- 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
View all my tips
Article Last Updated: 2022-06-07