How to Get Started with Azure Data Studio

Published:7 September 2022 - 11 min. read

Nicholas Xuan Nguyen Image

Nicholas Xuan Nguyen

Read more tutorials by Nicholas Xuan Nguyen!

Meet Active Directory and Windows Server auditing, security and compliance needs with ManageEngine ADAudit Plus. Download Free Trial!

If you’ve been used to managing databases via the command-line interface, that’s good enough. But admit it, that method requires a lot of typing, and remembering all the different commands can be challenging. Luckily, with Azure Data Studio, you can manage your databases in a GUI.

Azure Data Studio is one of the most popular solutions for data professionals working with local and cloud platforms on Windows, macOS, and Linux. And in this tutorial, you’ll learn how to install Azure Data Studio and create a connection to your database.

Read on and get started with the basic tasks in Azure Data Studio’s database management!

Prerequisites

This tutorial will be a hands-on demonstration. If you’d like to follow along, be sure you have the following.

  • A Windows 10 computer.
  • A Linux computer – This tutorial uses Ubuntu 20.04, but other Linux distributions will work.
  • SSH access to your Linux system.
  • A non-root account with sudo privileges – This tutorial uses a root account for simplicity. But using a non-root user with sudo access is always recommended.

Downloading and Installing Azure Data Studio on Windows

Azure Data Studio is a cross-platform database tool. This tool lets you manage SQL Server, Azure SQL Database, and Azure SQL Data Warehouse on Windows, macOS, and Linux.

Azure Data Studio provides you with several features, including all SSMS (SQL Server Management Studio) features you’re familiar with. In addition, a modern user interface, IntelliSense code-completion aid, built-in source control, integrated collaboration tools, etc.

To take advantage of Azure Data Studio on Windows, you’ll first have to install this tool:

1. Open your favorite web browser and navigate the Azure Data Studio download page.

2. Next, click the User installer (recommended) hyperlink to download the User installer.

This package installs Azure Data Studio for the current user only. No admin rights are required, and there’s no need to worry about affecting other tools installed on the same machine.

Downloading the User installer (recommended)
Downloading the User installer (recommended)

3. Double-click the Azure Data Studio executable you downloaded in step two to launch the setup wizard (step four).

4. When the setup wizard opens, select the I accept the agreement option to accept the license terms and click Next.

Accepting the software license terms
Accepting the software license terms

5. Now, keep the default install location, and click Next.

But if you like, you can also click on Browse to set a different location to install Azure Data Studio.

Selecting Azure Data Studio’s install location
Selecting Azure Data Studio’s install location

6. Click Next or click on Browse to set a location for Azure Data Studio’s shortcut.

Setting Azure Data Studio’s shortcut location
Setting Azure Data Studio’s shortcut location

7. Select additional tasks for the setup to perform as follows, and click Next:

  • Tick the Register Azure Data Studio as editor for supported filetypes box. This option sets Azure Data Studio as the default editor for files with supported extensions, such as .sql and .json.

Why is this option important? Having a consistent editor can be helpful for all the different file types you work with.

  • Tick the Add to path box to add Azure Data Studio to your PATH environment variable. Doing so lets you launch Azure Data Studio from the command line. This behavior comes in handy whenever you get in a situation where the GUI is unavailable.

Like Ubuntu, Windows has an environment variable called PATH that specifies where the operating system should look for executables.

Selecting additional tasks for the setup wizard to perform
Selecting additional tasks for the setup wizard to perform

8. Next, click Install to install Azure Data Studio.

Installing Azure Data Studio
Installing Azure Data Studio

9. Finally, click Finish once the installation completes closing the setup wizard.

Optionally, tick on the Launch Azure Data Studio option, and click on Finish. Once you do, Azure Data Studio opens for the first time.

Finishing Azure Dat Studio installation
Finishing Azure Dat Studio installation

You will see the main Azure Data Studio window, as shown below. From this point on, you can connect to the database you want to manage.

Viewing the Azure Data Studio’s main window
Viewing the Azure Data Studio’s main window

Downloading and Installing Azure Data Studio on Linux

Linux, by far, is the most popular platform after Windows for running Azure Data Studio. But like in Windows, you must first install Azure Data Studio on Linux.

The recommended way to install Azure Data Studio on Ubuntu is to use the .deb package. The .deb package is a container format that packages an application and all its dependencies.

1. Login to your Linux machine, and open your favorite web browser.

2. Navigate to the Azure Data Studio download page, as you did in step one of the “Downloading and Installing Azure Data Studio on Windows” section.

3. Next, right-click the .deb file hyperlink and select Copy link address to copy the download address.

Copying the .deb file’s download link
Copying the .deb file’s download link

4. Open your Terminal, and run the wget command below to download the Azure Data Studio .deb file. Ensure to replace the URL below with the download link you copied in step three.

If you’re on a non-root user, always prepend sudo when you run commands that require elevated privileges throughout this tutorial.

wget https://sqlopsbuilds.azureedge.net/stable/d904740d93d7df76a0ba361f20e4351813b57645/azuredatastudio-linux-1.37.0.deb
Downloading Azure Dat Storage’s .deb file
Downloading Azure Dat Storage’s .deb file

5. Now, run the following ls command to verify that the Azure Data Studio .deb file has been downloaded.

Change azuredatastudio-linux-1.37.0.deb to the name of the .deb file you downloaded when you refer to the .deb file throughout the tutorial.

ls -la azuredatastudio-linux-1.37.0.deb

Below, you can see the .deb file exists. But, if you get an HTML file instead, repeat step four to redownload the .deb file.

Verifying the Azure Data Studio .deb file has been downloaded
Verifying the Azure Data Studio .deb file has been downloaded

6. Run the apt update command below to update the list of available packages and their versions.

apt update -y
Updating the list of available packages
Updating the list of available packages

7. Once updated, run the dpkg command below to view the contents (-I) of the Azure Data Studio .deb file. Before installing any software on your computer, you should verify the file’s contents.

dpkg -I azuredatastudio-linux-1.37.0.deb

See the Depends section? Below is a list of dependencies required to run Azure Data Studio.

Viewing the contents of the Azure Data Studio .deb file
Viewing the contents of the Azure Data Studio .deb file

8. Now, run the below command to install (-i) Azure Data Studio on your Linux computer.

dpkg -i azuredatastudio-linux-1.37.0.deb
Installing Azure Data Studio
Installing Azure Data Studio

9. Finally, launch the Azure Data Studio from your Application Finder, as shown below.

Launching Azure Data Studio from the Application Finder
Launching Azure Data Studio from the Application Finder

Like when you installed Azure Data Studio on your Windows computer, you’ll get the same main window shown below.

Viewing the Azure Data Studio’s main window
Viewing the Azure Data Studio’s main window

Verifying the SQL Server Instance is Running

After installing Azure Data Studio, it’s time to explore how to manage databases with Azure Data Studio. But first, you must ensure that the SQL Server instance you want to connect is running and accessible.

Open PowerShell as administrator, and run each command below to verify the SQL Server instance is running on your machine with the following:

  • Queries all files on your computer for the string SQL Server is now ready for client connections. This string is written to the SQL Server Error Log when the SQL Server instance starts.
  • Gets a list of all services (Get-Service) running on your computer. The Where-Object cmdlet filters the output to include only services that have SQL Server in their name and are running.
Get-ChildItem -Path "c:\\program files\\microsoft sql server\\mssql*" -Recurse -Include Errorlog |select-string "SQL Server is now ready for client connections."
Get-Service | Where {$_.status -eq 'running' -and $_.DisplayName -match "sql server*"}

Below, you can see the MSSQLSERVER is running, which indicates you can now connect to your SQL Server instance.

Verifying the SQL Server instance is running
Verifying the SQL Server instance is running

Connecting Azure Data Studio to the SQL Server Instance

Now that you have verified that the SQL Server instance is running, you’re only a few steps away from managing your databases on Azure Data Studio. But first, you need to connect to your SQL Server instance.

1. Launch Azure Data Studio from the Start menu.

2. Click on Create a new connection on the main window to open the Connection Details window.

Initiating creating a new connection
Initiating creating a new connection

3. Configure the Connection Details with the following:

  • Connection type – Select the Parameters option, which is familiar to users of SQL Server Management Studio (SSMS).

If you choose the Connection String radio button, you’ll need to provide a connection string, which is out of scope for this article.

  • Server – Input the name of the computer running the SQL Server. In this case, it’s localhost since you’re connecting to a local instance.
  • Authentication type – Select Windows Authentication from the dropdown field to use your Windows credentials to authenticate to SQL Server (recommended).
  • Keep all other default options, and click Connect to connect to your SQL Server. A new tab opens in Azure Data Studio.
Connecting to the SQL Server
Connecting to the SQL Server

4. Lastly, click Databases (left-panel) under the localhost tab to see a list of databases of the connected SQL Server instance.

At this point, you can now run queries and perform other tasks in Azure Data Studio.

Checking databases of the connected SQL Server instance
Checking databases of the connected SQL Server instance

Creating a New Database

After connecting to your SQL Server instance, you can freely manage databases with Azure Data Studio. And what better way to get started with this tool than by creating a new database?

To manage a database, you need a database first. So your first task is to create a new database in Azure Data Studio.

1. On Azure Data Studio, click Home → New Query to open a new query editor.

Opening a query editor
Opening a query editor

2. Next, copy and paste the below T-SQL snippet into the query editor. This T-SQL snippet checks if a database named AZDATADEMO exists on the connected SQL Server instance.

Azure Data Studio creates the database and sets the Query Store feature to ON if the database doesn’t exist. This feature captures query execution plans and runtime statistics, which can be helpful when troubleshooting performance issues.

The Query Store feature is available in SQL Server 2016 and higher.

USE master
GO

-- Checks if the AZDATADEMO database exists
IF NOT EXISTS (
 SELECT name
 FROM sys.databases
 WHERE name = N'AZDATADEMO'
)
 -- Create the AZDATADEMO database if it doesn't exist
 CREATE DATABASE [AZDATADEMO];
GO

-- Sets the Query Store feature ON
IF SERVERPROPERTY('ProductVersion') > '12'
 ALTER DATABASE [AZDATADEMO] SET QUERY_STORE=ON;
GO

3. Now, click Run to execute the query.

You’ll see a message that says, ” Commands completed successfully, ” indicating the database has been created.

Executing the query
Executing the query

4. Lastly, click on Databases again (left-panel) under the localhost tab, and you’ll see the newly-created database in the list.

Viewing the newly created database.
Viewing the newly created database.

Creating a Table and Inserting Data

You’ve just created a database, and that’s a great way to start getting used to managing databases in Azure Data Studio. But as you know, a database is useless when it’s empty. So why not use Azure Data Studio to create a table and insert data into the table?

1. On your query editor, click on the database name in the Change Connection dropdown and change the connection to the newly-created database (AZDATADEMO).

Changing the database connection
Changing the database connection

2. Next, replace the current snippet in the query editor with the below T-SQL snippet, and click Run to execute the new query.

The snippet below creates a table named Users in the current database (AZDATADEMO). The table comprises four columns: CustomerId, Name, Location, and Email, where the CustomerId column is the primary key column.

-- Check if the Users table exists
IF OBJECT_ID('dbo.Users', 'U') IS NOT NULL
 -- If the Users table exists, delete the table
 DROP TABLE dbo.Users;
GO
-- If the Users table doesn't exist, create the table with four columns
CREATE TABLE dbo.Users
(
 CustomerId int NOT NULL PRIMARY KEY, -- primary key column
 Name nvarchar(50) NOT NULL,
 Location nvarchar(50) NOT NULL,
 Email nvarchar(50) NOT NULL
);
GO
Creating the Users table
Creating the Users table

3. Navigate to the Databases page and double-click on your new database (AZDATADEMO) database to see all data in the database.

Accessing the AZDATADEMO database
Accessing the AZDATADEMO database

Below, you can see the newly created Users table.

Viewing the newly-created Users table
Viewing the newly-created Users table

4. Lastly, replace the snippet in the query editor with the below T-SQL snippet, and click Run to execute the new query.

The snippet below inserts four rows into the Users table.

INSERT INTO dbo.Users

VALUES

 ( 1, N'Adam', N'US', N''),
 ( 2, N'Listek', N'US', N'[email protected]'),
 ( 3, N'Donna', N'US', N'[email protected]'),
 ( 4, N'Janet', N'US', N'[email protected]')

GO
Inserting data into the Users table
Inserting data into the Users table

Querying Table Data

Yay! Your database is no longer empty after adding data to the Users table. But how sure are you that the data went through? Luckily, querying table data in Azure Data Studio isn’t as complex as it may sound. How?

Replace the snippet in the query editor with the one below, and click Run to SELECT and return all (*) rows FROM the Users table (dbo.Users).

SELECT * FROM dbo.Users

GO

If successful, you’ll see all rows of data from the Users table displayed under the Results pane below.

Querying rows of data from the Users table
Querying rows of data from the Users table

Perhaps you plan to run more specific queries. If so, execute the snippet below instead to query data of the rows only from the Users table (dbo.Users), WHERE the user’s Name is Adam.

SELECT * FROM dbo.Users

WHERE Name = 'Adam'

GO

As you can see below, only one item returns with the name Adam.

Returning all rows from the Users  table where the Name is Adam
Returning all rows from the Users table where the Name is Adam

Conclusion

You’ve experienced firsthand how powerful Azure Data Studio is in managing your SQL Server databases. And in this tutorial, you’ve learned how to install Azure Data Studio and start with basic tasks in managing your databases. Like creating a new database, table and inserting data.

What you’ve seen in this tutorial is just the tip of the iceberg. Azure Data Studio can do much more. Why not explore more and create a Jupyter notebook in Azure Data Studio to run your Python code?

Hate ads? Want to support the writer? Get many of our tutorials packaged as an ATA Guidebook.

Explore ATA Guidebooks

Looks like you're offline!