Azure Data Studio: Initial thoughts and walk-through

At Microsoft Ignite, Azure Data Studio was made Generally Available. Previously known as SQL Operations Studio, Azure Data Studio is a cross-platform database tool that you can use to manage on-prem and cloud data sources.

When I first installed and ran Azure Data Studio, my inital impression of it was that it was pretty much a Visual Studio Code for Data. It comes with Intellisense, code snippets, integration for source control and an integrated terminal.

There’s support for Git in Azure Data Studio, however you need to install version 2.0.0 or later before you can use it.

Much like Visual Studio code, you can have multiple integrated terminals running at the same time rather than switching in out of your editor each time you want to do some command line work:

All the terminals!

As a default, Terminals in Windows 10 use Powershell, while Linux and macOS use $SHELL. You can learn more about configuring the terminal here.

There’s also support for extensions. Extensions in Azure Data Studio allows us to add more functionality to our base installation much like Visual Studio Code. You can also develop your own extensions which is pretty awesome!

Let’s go through a simple tutorial for connecting and querying a Azure SQL Database. I’m going to assume that you know how to create a Database in Azure. If not, you can follow this tutorial to get started.

Open Azure Studio to get started. If this is the first time you’ve run Azure Data Studio, you’ll automatically see a Connection page open. If not, you can add a new connection by clicking on the icon in the SERVERS sidebar (see below):

Both SQL Login and Windows Authentication is supported by Azure Data Studio. I’ve used SQL Login, so I need to provide my server name, username and password.

(Note: Make sure you enable afirewall rule for your server in Azure. If you don’t do this, you are able to do this in Azure Data Studio by following a simple UI wizard. Pretty cool!)

Once you’re logged in, let’s create a database and a table to work with. Right click on your server and select New Query. In the query editor, run the following SQL code:

IF NOT EXISTS (
    SELECT name
    FROM sys.databases
    WHERE name = N'PersonDB'
)

CREATE DATABASE [PersonDB]
GO

ALTER DATABASE [PersonDB] SET QUERY\_STORE=ON
GO

Once our database has been created we want to change our database to PersonBD. We can do this using the drop down menu:

We can then create a Person table by running the following code:

IF OBJECT\_ID('dbo.People', 'U') IS NOT NULL
DROP TABLE dbo.People
GO

CREATE TABLE dbo.People
(
   PeopleId INT NOT NULL PRIMARY KEY,
   Name [NVARCHAR](50) NOT NULL,
   Age INT NOT NULL,
   Email [NVARCHAR](50) NOT NULL
);
GO

We can see our new table in the Server sidebar:

Now let’s insert some rows into our new table:

INSERT INTO dbo.People
 ([PeopleID], [Name], [Age], [Email])
VALUES
  (1, N'Will', 27, N'[e-mail]'),
  (2, N'Isaac', 7, N'[e-mail]'),
  (3, N'Sam', 4, N'[e-mail]'),
  (4, N'Jan', 33, N'[e-mail]'),
  (5, N'Jane', 57, N'[e-mail]')
GO

And finally, we can query it to view its contents:

SELECT * FROM dbo.People;

We should see the following result:

Now that we’ve created our table and we’ve inserted some data in it, let’s now manage our database by creating an insight widget that provides us a view of how much space our tables are using in the database.

Much like Visual Studio Code, we can find things quickly using the Command Palette. To open this up, press Ctrl+Shift+P. Once open, type in settings and select Preferences: Open User Settings.

We’ll see a JSON file open along with a search input box. In here, type dashboard and find the dashboard.database.widgets setting:

Once you found this setting in default user settings, you want to click the pencil icon and select Copy to Settings. If it says Replace in Settings please don’t click it!

When you copy the settings into your User Settings file, you want to add the following JSON:

{          
   "name": "Space Used by Tables",
   "gridItemConfig": {
           "sizex": 2,
           "sizey": 1          
    },          
    "widget": {              
        "table-space-db-insight": null          
    }      
},

Save your settings and open your database dashboard by right clicking your PeopleDB and clicking Manage. You should see the following widget in your dashboard:

You can now use this insight chart for different insights through the use of filtering etc.

Once you’re done with your resources, feel free to head back into Azure and clean up any resources that you don’t need anymore.

As you can see, Azure Data Studio is a pretty awesome tool for Data Engineers to manage on-prem and cloud data sources. Personally, I can’t wait to see how this product evolves with input from the community.

If you want to check out the source code, here’s the GitHub repo. Likewise you can also see the release notes here.