Azure Data Studio First Look: Awesome editor and useful backup and profiling tools

Azure Data Studio is a new open source, cross-platform database tool from Microsoft that offers a modern editor experience with IntelliSense, code snippets, source control integration, and an integrated terminal. In this first look I'm going to try out the new editor and two of my most common data related tasks: Backup and Profiling.

Setup

To get ready download and install Azure Data Studio. Then launch it and you'll see a VS Code like editor, go to the Extensions tab and there is a list of Recommend extensions waiting for you.  Find Admin Pack for SQL Server extension, for me it's the first one on top of that list of extensions. Installing this extension gives you 4 more extensions, in this post I'm taking a look at the second one and last one.

To complete the setup, I add connections to my local development database and my production Azure SQL database.

Editor

I find Azure Data Studio a breeze to work with! First it only shows me the databases I care about and these databases could live on different servers local or remote on Azure. Before Azure Data Studio like everyone else I use SSMS (SQL Server Management Studio), and boy is it a pain anytime I need to write a query to first login then dig into a tree of nodes, click click click to get the your database. Oh did I mention compare to SSMS, Azure Data Studio also launches way faster!

When I open up a new query window the IntelliSense is pretty much with me every step of the way from the moment I start typing "select", and it's very responsive. I also had a nice surprise after I typed in this simple query, I thought to myself what happens if I press F12 key with the cursor on Blog_Category?

select * from Blog_Category

Try it and bam! You get the table definition in a new query window.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Blog_Category](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](max) NULL,
	[Slug] [nvarchar](256) NOT NULL,
	[Title] [nvarchar](256) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I'm probably just scratching the surface when a tool looks like it's the VS Code's sibling.

Profiling

The SQL Server Profiler extension allows me to profile my app to see what what queries EF Core generates for me. To start a new session, right click on your server, choose Launch Profile or shortcut Alt + P.

Launch a profiler session
Launch a profiler session

Then I head over to my website and refresh the home page, after that I see a long list of sql events fill up my screen. So I click on Filter on the session window's toolbar and apply the following filter to see only the queries I care about.

Add a filter to the Profiler session
Add a filter to the Profiler session

This is what I end up seeing, there are 4 queries run when my home page is hit. Refreshing the page again I do not see more queries run because the page uses caching, and going to a different page will yield more rows into the session window. Clicking on a row will display the entire query in the Text pane below.

See my queries
See my queries

Backup SQL Database

I'm running this site on Azure App Service with a Basic plan because this is what I can afford at the moment. But one of the unfortunate aspects with the Basic plan is that it does not include the Backup/Restore feature, you need a Standard plan for that. There are a couple of essential features I wish could be included in the Basic plan instead and this is one of them. Check out feature comparisons of all the App Service plans.

The Backup feature of the Azure App Service backs up both your website and SQL Database in a zip and saves it in your Azure Blob Storage. Your SQL Database backup is saved into a .bacpac file, and really for me this file is all I need because I'm not saving any data such as images on the web server's file system so I can live without backing up my website. I'm happy to say that the SQL Server dacpac extension allows me to back up my SQL Database into this bacpac file with ease.

Right click on the server and choose Data-tier Application wizard and just follow the steps. For my production database it took 1:58 minutes to complete the backup to my local hard drive and the file is 129k.

Azure Data Studio - Backup your SQL Database
Backup my SQL Database in Azure Data Studio

Note Azure Data Studio allows you to back up into either .bacpac or .dacpac format. These are the two primary types of export file. The difference is that a bacpac includes the schema and data from the database while the dacpac contains only the schema not the data.

With the bacpac file I can then import it through SSMS to my local server, yes for this step you need SSMS, the SQL Server Import extension is for copying data from a flat file (.csv, .txt, .json) to a SQL Server table. The import is straight forward, after which I can simply change my project connection string and run my app against this new database with production data.

Import bacpac in SQL Server Management Studio
Import bacpac in SQL Server Management Studio

Interestingly during the restore I notice that SSMS can import .bacpac file from local file system or from an Azure Storage account, but when I back up my database in the Azure App Service I do not see an option that allows me to save the file to Azure Blob Storage. 

Final Thoughts

  • Azure Data Studio supports other database providers, like PostgreSQL, but it does not work with SQLite yet, there is an open issue on GitHub, please go up vote it. 
  • And Azure Data Studio is not here to replace SSMS, here is a guide on when to use which and their feature comparisons. I feel as a developer I'll definitely spend more time here from now on. 
  • There are a couple more extensions that may worth a look for next time, the SQL Server Schema Compare and Redgate SQL Search.
  • Here is the Azure Data Studio github issues to report any issues.