Azure Data Studio is a cross-platform database tool to allow data professionals a single user interface to work with databases on premise and in the cloud. This is a free new tool that is available for Windows, Mac, and Linux users. Azure Data Studio is not a complete replacement for SQL Server Management, but is a complementing tool that provides a whole new experience for data professionals. Azure Data Studio provides a modern editor experience, with IntelliSense, code snippets, customizable dashboards, source control integration, etc..
I was attracted to the Azure Data Studio tool because of its customizable dashboard capability. In this article the dashboards that come with Azure Data Studio will be explored, and you will see how to customize one of these dashboards to include a new widget.
Installing and Initial Execution of Azure Data Studio
Before you can explore modifying the default dashboards that come with Azure Data Studio, you first need to install this new tool. To install Azure Data Studio, first download the software. You can obtained the bits for installation from the following location: https://docs.microsoft.com/en-us/sql/azure-data-studio/download?view=sql-server-2017
From this link you can download a zip file for a Windows installation. After downloading the zip file, extract it to a folder. If you then browse to the extracted folder for the “azuredatastudio.exe” file, you can double click on this exe. When I did this, the following screen was displayed:
Connecting to a Data Source
On the right side of the screenshot above there are basic connection options, like Connection type, server name, type of authentications, etc.. There is also an “Advanced” button which provides other connection options, like initialization, security, and source connection. To test out modifying the default dashboards I connected to one of my local on-premise instances of SQL Server (MSI\MSSQLSERVER01). I didn’t need to use any of advanced options to test out and modify the new default dashboards.
Once connected to my instance I found there where two different management dashboards that came with the installation: Server and Database. As the names suggest the Server dashboard is used to gain insight at the server level and the Database dashboard is used to provide insight at the database level. By default, these dashboards provide a few default items. To help you look at each of these dashboards a little closer, I will show you an example of how you could modify one of dashboards to provide you additional information.
Once I connect to my instance, I was displayed the default SERVER DASHBOARD, shown below:
By default the SERVER DASHBOARD displays some nicely formatted server level information. You can see the version and edition of SQL Server that you’ve connected to. You can also see the computer name on which the instance is running, as well as the OS Version that supports the instance. Also displayed are a list of tasks that can be performed, a list of databases on the instance, an overview of the databases backups, and databases sizes for each database.
The server dashboard provides a lot of good information by just connecting to an instance. This dashboard is a good start, but the real beauty of this tools is that you can customize these dashboards (more on customization later on in this article).
With just a double click on one of the databases on the SERVER DATABOARD you can bring up the DATABASE DASHBOARD. Below is the DATABASE DASHBOARD that is displayed when I double clicked on my WorldWideImportersDW database:
Once again, this dashboard has some common tasks that you might want to take on, as well as shows a list of the tables on the database. This dashboard has a lot of white space to the right of the list of tables. I’m going to customize this dashboard to contain some additional information I obtained using some TSQL code.
Querying Data Using Azure Data Studio
If you review the server and database dashboards above, then you will see that there is a “New Query” icon in the Task item on the dashboard. You can bring up the query editor by clicking on either one of these icons or by using the “New Query” item on the “File” menu, or by using the CNTL+N short cut. When you use one of these options, a new blank query window will open, that will look like this:
This new query window looks much different then the new query window in SQL Server Management Studio. As you can see in my figure, I only have the run, cancel, disconnect, and change connections options. Additionally, the database context in which TSQL will run can be identified.
To test out this new query editor I decided to write some TSQL code that will display the number of records in each table. Here is that TSQL code:
QUOTENAME(SCHEMA_NAME(SO.schema_id)) + '.' + QUOTENAME(SO.name) AS [TableName]
, SUM(SP.Rows) AS [RowCount]
sys.objects AS SO
INNER JOIN sys.partitions AS SP
ON SO.object_id = SP.object_id
index_id < 2 -- 0:Heap, 1:Clustered
ORDER BY [RowCount] DESC;
When this code is pasted into a new query window and the run icon is clicked (or F5 is used) to execute this code the following is displayed:
Here you can see the TSQL code, the results generated when my TSQL code was run against the master database and the messages that were generated when the code was executed. It looks a little like SSMS. The TSQL code above can be used to customize the DATABASE DASHBOARD.
Customizing the DATABASE DASHBOARD
It is fairly easy to modify the Azure Data Studio dashboards to display more dashboard items using your own custom code. To demonstrate this, I will show how to add another item to the DATABASE DASHBOARD. The new dashboard item will create a new database insight widget using the TSQL code used in the prior section.
The definitions for these dashboards are stored in a JSON configuration file. In order incorporate the output of the TSQL code in the prior section, into the DATABASE DASHBOARD, the following different steps will need to be performed.
Step 1: Save TSQL Code
In the JSON code the TSQL code that needs to be run will need to be referenced. Therefore, the TSQL code will need to e named and saved into a file location where Azure Visual Studio can find it. In this case I stored this code in the following location: C:\temp\RowCount.sql.
Step 2: Display Results in Chart View
After the TSQL code is saved, the query will need to be ran again. Once the results are displayed they will need to be displayed in a “chart” view. In order to do that you can click on the Chart icon in the RESULTS pane by clicking on the button pointed to by the red arrow shown in the screen shot below:
Step 3: Set Chart Type
When the Chart icon I clicked on, a new CHART tab is opened. A bar chart is displayed by default. The bar chart isn’t the correct chart format I wanted for my custom dashboard item. To make the CHART look like a table chart, the “Chart Type” needs to be changed to “Table”. When this is done, the CHART tab now looks like this:
Step 4: Create Insight Code
In order to modify the dashboard configuration file, JSON code based on the chart defined above will need to be created. To do that you can click on the “Create Insight” icon (this icon can be seen in prior step right above the list of tables). Once this icon is clicked, the JSON code for the new dashboard item will be generated.
The generated JSON code will be placed in a new tab and will initially be created as a single line of text with no formatting. I don’t know about you, but I find it hard to read unformatted code. It is a good thing Azure Data Studio makes it easy to format the code. You can simply right click on the code that was generated, and then pick “Format Document” from the drop down or use “ALT+SHIFT+F” to format the JSON code. The formatted JSON code looks like this:
Step 5: Bring up the USER SETTINGS to edit configuration file
In order to modify the default user settings for the DATABASE DASHBOARD, the USER SETTING need to be brought up. There are a couple of options for how the current user settings can be displayed. The configuration settings can be displayed by clicked on the “Settings” wheel that appears in the lower left hand side of the DATABASE DASHBOARD screen, or the “CNTL+,” keyboard shortcut can be used. When this is done, the following default USER SETTING screen will be displayed:
As you can see nothing has been done yet to set any user overwrites to the default settings. You can tell this because there is no information contained within the USER SETTINGS items above (see red box).
Step 6: Add default “dashboard.database.widgets” section in dashboard configuration file
To accomplish this the default dashboard.database.widget code will first need to be found. To find this default code just type in “database.widgets” in the search text box. When this is done, the database.widgets code is displayed in the left pane.
This code can be captured by clicking on the pencil icon that I have pointed to in the above screenshot. When the pencil icon is clicked, the database.widget code is highlighted in the left pane. Once it is highlighted, you can then select the “Copy to Settings” button which paste this code into the user settings as shown below:
Step 7: Add new Row Count Insight to dashboard configuration
Now all that needs to be done is to add the row count JSON widget code that was created in step 4 to the end of the new dashboard.database.widgets code that was created in the prior step and then save the configuration file.
Once my configuration file is updated, testing can be done to see how this change affects the database dashboard. To do this test, you first need to go back to the SERVER DASHBOARD and click on one of the databases. I choose the WorldWideImporters database, again. When I do this my new customized database dashboard view is displayed as shown below:
You can see my new dashboard item named “My-RowCount-Widget” in the above screenshot.
As you can see it was simple and easy to add a new dashboard item. What I really like is this new dashboard item was created off some TSQL code I created. Because I was able to use TSQL code to modify this dashboard I did not need to learn a new programming language to customize the database dashboard. The only other coding language used was JSON, but I didn’t have to write that code, it was generated for me based off my TSQL code.
I only looked at one small aspect of Azure Data Tool. From my quick exploration I verified that it is easy add new custom dashboard widgets to the existing database dashboard. I really like I TSQL code can be used to create a new dashboard widget. Being able to use TSQL for widget development will make it really nice for those people that have been coding TSQL for years. I can see someone creating a bunch of their own cool new dashboard widgets to highly customize the SERVER and DATABASE dashboards. As more people in the user community adopt this tool, I hope to see lots of people sharing their custom cool widget code.