Cross-Platform and Multi-Database

In this lesson, you'll learn about how Azure Data Studio can work across multiple platforms and work with multiple databases.

We'll cover the following

The new options provided by cross-platform computing

The see-saw battle for computer and software dominance between Microsoft and Apple, and by extension between Windows and macOS, while still on-going, will perhaps end in a whimper, not a bang. This is because these behemoths and the growing platform agnostic cloud computing options are making the reminiscent “I’m a PC, I’m a Mac” choice less relevant. Add to this the increasing popularity of Linux, which is also free and open source (anyone see a pattern?), and you have several platform options for running Azure Data Studio.

What about the database platform

When you are using ADS, there is a great likelihood that you are querying, developing, managing, or analyzing the contents of one or more databases. Here as well, you have many options for where your databases can reside. Despite a common misconception (probably due to having “Azure” in its name), Azure Data Studio provides full connectivity to both cloud and on-premise database systems. However, your database choices do not stop there. If we just consider SQL Server, for now, we have several platform options, including:

  • Windows
  • Linux
  • Docker Containers (for Windows, Linux, and macOS)
  • Azure Cloud
  • AWS Cloud
  • Google Cloud

While not a comprehensive list of platform options, it is clear that SQL Server is no longer just a Windows product. So, read on because ADS is designed for more than just Microsoft’s flagship SQL Server database.

What “multi-database” means for your SQL experience

Not only one content be cross-platform, but ADS is designed to connect beyond SQL Server. At the time of this writing, ADS directly supports SQL Server and PostgreSQLFor PostgreSQL support, you first need to add the free PostgreSQL extension from Microsoft. This can be found in the ADS Extensions ‘Marketplace’ as first-class citizens. This is the case whether your target database system is on-premise, in the cloud, in a container, or on bare metal. Soon (perhaps by the time you read this), two additional databases could be added to this list: MySQL, and MariaDB.

But there is more to the multi-database story due to the language (Kernel) options that are baked into ADS, such as PowerShell, Python, and Spark. In short, ADS can be used for any database that is within reach of these supported languages. They are technically considered second -class database connections (due to the intermediate host language), but perhaps they are also stronger for the same reason.

Postgre and SQL

For example, let’s say you would like to connect to the cloud-based Snowflake database while using ADS. A good language choice for this would be Python since (a) it is a directly supported ADS language, and (b) it has a native ‘snowflake’ connector. When using Python in ADS, you can invoke scripts from the Terminal Window or within a Juypter Notebook. In either case, you now can use Python language constructs, such as variables, arrays, loops, and branches to implement complex logic that is ultimately rendered as SnowSQL statements (Snowflake’s SQL dialect).

What about Management Studio?

When asked by Microsoft and early adopters if Azure Data Studio is a replacement for SQL Server Management Studio (SSMS), the most common responses are “not yet if you are a Database Administrator,” or, “yes if you are primarily a SQL Developer.” However, I think these replies are a bit short-sighted since ADS is much broader in scope than SSMS (a Windows-only management application for only SQL Server). As startling as it may sound, Azure Data Studio, at its core, is largely database, platform, and language agnostic. While it is true that SQL Server was the first ADS-supported database (and will likely continue to enjoy priority status), Microsoft is implementing support for third-party databases, despite the aforementioned SSMS functionality gaps for DBAs.

However, the bigger picture is based on the very architecture of ADS, which puts the user community in the driver’s seat, whether creating simple enhancements or developing highly functional extensions and placing them in the integrated Extensions Marketplace. Azure Data Studio is truly a new and open breed of software, which will certainly complement, if not eventually replace multiple programs that sit prominently on many of our desktops. Even the word “replace” in this context seems insufficient since ADS integrates formerly disparate applications under a single roof. And this is precisely where I think things will get interesting for all data professionals.

Opening Azure Data Studio

On Educative, you will be presented with an instance of Azure Data Studio when needed. If you wish to run this locally, you can enter, “Azure Data Studio” into any search engine or use this link. Next, then click on the Download Azure Data Studio link, and follow the instructions to install it on your computer’s operating system.

Note that Azure Data Studio does not include a Database Server within the download. If needed, you can download SQL Server Express Edition, which is a lightweight and Free version of SQL Server. All the examples in this course will work with SQL Server Express 2019.

As a quick guide, once you open ADS, you can refer to the figure below, which displays the regions of Azure Data Studio that we will commonly reference:

Hit the RUN button to start Azure Data Studio.

Hitting the run button will open up a small window
with Azure Data Studio running in it. You can also
click on the URL once the app has loaded to open it
in a new browser tab.

Here’s a short quiz to test your knowledge.

Q

Which of the following options are ways to enhance and extend ADS?

A)

Highly versatile Juypter Notebooks

B)

Integrated Terminal Window command-line options

C)

Customizable keyboard shortcuts

D)

All of the above