Introduction

Azure Data Studio is the next iteration in Microsoft’s development of a cross-platform client tool for developing code and managing databases on-premises and in the cloud. Azure Data Studio was originally released in preview mode as SQL Operations Studio. The primary edge Azure Data Studio has over SQL Server Management Studio is the availability of installers that will run natively on Windows, Linux, and MacOS. Azure Data Studio is compatible with any version of Windows Desktop Operating systems greater than Windows 7 (SP1 with KB2533623) and any version of Windows Server greater than Windows Server 2008 R2. Azure Data Studio is also compatible with MacOS 10.12 and 10.13, RHEL 7.3 and 7.4 as well as Ubuntu 16.4. Alan Yu announced the availability of the November edition of Azure Data Studio on 5^th November 2019.

Expectations

With the initial projections of Azure Data Studio as a cross-platform tool I was hoping with would be able to use the tool to connect to Oracle and MySQL databases among others, but the tool is limited to data-related server products both on-premise and in the cloud. I also thought it was the beginning of the end of SQL Server Management Studio, but it really doesn’t match the power SQL Server Management Studio provides for deep database administration. It does have some great features for coding and lends itself to the new paradigms of managing data beyond the typical relational database, beyond the traditional data center and beyond the Windows Operating System. Besides, Microsoft does not intend to discontinue SQL Server Management Studio in the foreseeable future.

Installation

Figures 1 to 7 show screenshots of the installation process for Data Studio on a Windows Desktop. The installation process is quite straightforward and much less time consuming than SQL Server Management Studio.

Fig. 1 License Agreement

 

Fig. 2 Destination Directory

 

Fig. 3 Start Menu Folder

 

Fig. 4 Desktop Icon and Others

 

Fig. 5 Installation Summary

 

Fig. 6 Installation Execution

 

Fig. 7 Installation Completion

 

Connections

Connecting to the database engine using Azure Data Studio is similar to SQL Server Management Studio. As you can see below, you can display recent connections and explicitly enumerate the databases in an instance before connecting. Data Studio also allows you save connections and reuse them at a later time.

Fig. 8 Establishing a Connection

 

Fig. 9 Enumerating Databases

 

Once connected, the server dashboard is immediately visible. You can also navigate the left pane much like Object Explorer in SQL Server Management Studio. (Fig. 10). Also observe the additional panes for connecting directly to Azure and Big Data Clusters respectively. The narrow section highlighted with a yellow box points out useful buttons such as the first (labelled connections) which toggles between displaying or not displaying the connections pane when clicked. Other icons display the search functionality, the notebook, SQL Editor, Source Control and Extensions respectively.

Fig. 10 Object Explorer Look-Alike

 

Simple Database Operations

One aspect of the philosophy behind Azure Data Studio is the rise of DevOps as a practice in many organizations. Developers are increasingly merging their roles with Database Administration particularly as more and more tasks in database systems are automated or automatable. Data Studio provides the simplest approach to taking a database backup I have seen to date. Simplification of such operations gives developers who use the tool free time to do creative work. Database restores are equally easy to do.

Fig. 11 Performing a Backup Operation I

 

Fig. 12 Performing a Backup Operation II

 

Fig. 13 Performing a Backup Operation III

 

Fig. 14 Performing a Restore Operation

 

IntelliSense and More

The SQL Editor in Azure Data Studio has an amazing intellisense feature. It is much faster in displaying suggestions that SQL Server Management Studio. I also noticed interesting code snippets to writing SQL statements identified as sqlInsertRows, sqlSelect etc. These snippets display the correct syntax for writing statements. (Fig. 15-17). There are code snippets for almost anything you want to accomplish in the database engine thus making it simple for non-database administrators to use Data Studio to interact with databases.

Fig. 15 Code Snippets with Descriptions

 

Fig. 16 sqlInsertRows

 

Fig. 17 sqlSelect Code Snippet

 

Just like SQL Server Management Studio, Azure Data Studio provides the Explain Plan feature. (Fig. 18) Data Studio has an Integrated Terminal feature which allows you to write code in PowerShell and Bash (when installed on Linux). This allows you to work in these environments without switching windows. You can open an Integrated Terminal in three ways:

1. Type Ctrl + `
2. Open View > Terminal
3. Type Ctrl + Shift + P and select “View: Toggle Integrated Terminal” from the List

Figure 18 shows the output of the Explain Plan feature in the lower part of the screenshot. In essence it is an execution plan as it is traditionally called in SQL Server.

Fig. 18 Explain Plan

Figure 19 shows the set of View: Toggle command which allows the user switch between views in Data Studio. One very useful option here is the View: Toggle Integrated Terminal which opens up an external terminal such as Command Prompt within Azure Data Studio.

Fig. 19 Integrated Terminal (View: Toggle Integrated Terminal)

The Terminal can also accessed via the View menu as shown in Figure 20. Figure 21 shows that PowerShell can also be used within Data Studio.

Fig. 20 Integrated Terminal (Terminal)

 

Fig. 21 Integrated Terminal (PowerShell)

 

Fig. 22 Integrated Terminal (SQLCMD)

 

Conclusion

Data Studio is another interesting tool in the developer and engineers arsenal. It also shows Microsoft’s support for emerging trends in IT such as DevOps. While it my not be the DBAs tool of choice, it makes it easier for those who work with data to embrace non-Windows platforms, cloud and Big Data without having to install multiple tools.

References

1. What is Azure Data Studio
2. November 2019 Release of Azure Data Studio
3. Integrated Terminal
4. Download Azure Data Studio