Getting Started with Azure Data Studio and SQL Server 2019 Preview

I recently wrapped up an exciting and informative week on the Microsoft campus with my fellow Microsoft Most Valuable Professional (MVPs) at MVP Summit 2019. Throughout the 10 years that I’ve been awarded MVP status, MVP Summit has been invaluable in getting insight into new features, products and even the occasional moon shots that have evolved in the Microsoft data platform.

The week was set aside for the 300-plus (global) Data Platform MVPs (and some of the 3,000-plus other award areas' MVPs) to spend time with their respective product groups in order to engage in bidirectional learning: MVPs get insights into what is in the pipeline for their foci of interests, and Microsoft mines the MVPs for feedback on the direction the product teams are headed toward for the next couple years.

As one would expect, 99 percent of what’s discussed is under non-disclosure agreement (NDA). That doesn’t mean we can’t discuss that 1 percent of goodness, as well as information that has been released but that we can now add additional insight to. This article comes directly from conversations with various engineers, developers and executives. It will certainly not be the last.

Here Comes SQL Server 2019

SQL Server 2019 is currently in the final rounds of Community Technology Previews (CTPs), and if I wasn’t excited before last week, then I definitely am now. Microsoft is continuing on the road to embracing open source and data-adjacent technologies. Did you ever expect to see a release of Microsoft SQL Server with the following features built-in and fully supported by Microsoft?

• Integrated technologies: Hadoop Distributed File System (HDFS), Spark and other analytics tools
• Availability Group platform choices: It’s not just about Windows OS anymore; Linux containers using Kubernetes-managed clusters are just the start. Want to run Java code on SQL Server? No one is stopping you.
• Data Virtualization: Expansion of PolyBase, which allows data professionals to query data not only in Hadoop directly from SQL Server without moving any data, but also MongoDB, Teradata, and even Oracle.

It’s not just about integrated open source technologies: Instant rollback and recovery through accelerated database recovery, improvements to encryption and security with Always Encrypted secure enclaves, Intelligent Query Processing, Automatic Plan Correction, Data Discovery & Classification labeling to facilitate better GDPR compliance, and vulnerability assessment mechanisms are also part of the newest release of Microsoft SQL Server.

I strongly advise you to start becoming acquainted with SQL Server 2019, and I recommend using the newest tool in the Microsoft Data Professional toolbox to do so: Azure Data Studio. This article is intended to help you get all the extensibility you need for Azure Data Studio to be your portal for exploring SQL Server 2019.

Prepare Your Azure Data Studio Implementation

If you have not heard of Azure Data Studio (ADS), here is a primer. The tool is not intended to replace SQL Server Management Studio (SSMS). Quite the contrary, if I understand correctly: Vicky Harp, Principal Product Manager Lead, and Ken Van Hyning, SQL Client Tools Engineering Manager, have said that ADS is intended to complement SSMS and SQL Server Data Tools (SSDT). While ADS is ready to be your administrative/development portal for SQL on prem and Azure, it’s not necessarily ready to handle the new features that SQL Server 2019 has to offer. ADT makes use of an extension marketplace that allows you to customize your experience.

To start, you’ll need to download the SQL Server 2019 extension from the marketplace, install it, and then restart ADS.

The typical install of extensions occurs automatically: Navigate to the Extensions Marketplace, select your extension and install. There are some larger extensions that require a download and install of the extension’s .vsix file first and a more traditional install.

 

Once you’ve downloaded the extension file, navigate to the File tab in your ADS menu and select “Install Extension from VSIX Package.” Follow directions from there. You’ll be prompted to reload ADS, and you’ll be ready to explore SQL Server 2019 inside of ADS.

Getting the Latest CTP of SQL 2019

Becoming familiar with the latest release of SQL Server is easier than ever before thanks to Docker containers:

1. Navigate to the SQL Server 2019 web page.
2. Select “Download Now,” which takes you to the landing site of choices for your trial of SQL Server 2019:

 

 

3. I’m choosing the Docker route to install SQL fastest. If you choose to take advantage of Docker, you’ll first need to create a Docker account, then download and install Docker.
   1. Pulling the latest latest SQL 2019 Developer Edition CTP, on Ubuntu image, and downloading it to your workstation is as simple as a single command. I chose Ubuntu, but a RHEL is also available:

        docker pull mcr.microsoft.com/mssql/server:2019-CTP2.3-ubuntu

1. 2. Execute the following bash command to create a Docker container from this image. I’ve set the port mapping of container:host to use the SQL default port of 1433. If this port is already in use on the host, select an open port to map to port 1433. I’ve named the instance sql1. Feel free to set the name to something that suits you. Be sure to replace the with an appropriate password that is at least eight characters long and includes at least three out of the four following classes of characters: uppercase, lowercase, numeric, symbol. Note: You can also run this same command from PowerShell if you substitute double-quotes (“) for single-quotes (‘):

 

        docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=' \

        -p 1433:1433 -name sql1 \

        -d mcr.microsoft.com/mssql/server:2019-CTP2.3-ubuntu

 

4. Connect to your new SQL instance from Azure Data Studio^*:

 

^You can use the same connection information to connect to this containerized instance from SQL Server Management Studio.

5. Open a new query window and execute the following command and receive the appropriate results to verify

 

SELECT @@VERSION;

GO

Conclusion

At this point, you now have a working SQL 2019 CTP 2.3 instance you can connect to from either Azure Data Studio or SQL Server Management Studio. As of this writing, however, only ADS provides you with preview support for all three of the most anticipated features forthcoming in this release: SQL Server 2019 Big Data Clusters, the expansion of Polybase, and the integration of Jupyter Notebooks. Containerized SQL CTPs are the quickest and cleanest way to get familiar with what I anticipate to be a pivotal release of SQL Server, setting a path that could not have been anticipated even a few years ago.