Rajendra Gupta
Jupyter notebook

Deploy SQL Server on Azure VM using Jupyter notebooks in Azure Data Studio

August 16, 2021 by

This article will deploy SQL Server on Azure VM using a Jupyter notebook in Azure Data Studio.

Introduction

The SQL on Azure VM is an Infrastructure-as-aService(IaaS) for migrating or deploying SQL Server in Azure cloud Infrastructure. You get complete control over SQL Server, OS similar to an on-premises SQL Server.

You can use the following feature that improves the working experience over a traditional SQL VM.

  • Automated patching
  • Automated backup
  • High Availability
  • Different VM configuration and easy to upgrade resources
  • Automated storage configuration
  • Different licensing models such as Pay-as-you-Go, Bring your Own licenses, Azure Hybrid

You can refer to the article, Azure SQL Database vs SQL Server on Azure VMs to compare SQL on Azure VM (IaaS) and Azure SQL Database (PaaS).

In the article, Deploy SQL Server on Azure VM using the Azure Portal, we explored the Azure portal for configuring SQL on Azure VM.

Deployment of SQL Server on Azure VM using Azure Data Studio

Azure Data Studio is a cross-platform data development application for SQL Server. We can use it for deploying Azure resources such as Azure SQL Database, Azure SQL Managed Instance and SQL Server on Azure VM. After going through the GUI deployment wizard in ADS, it generates a Jupyter notebook with Azure CLI scripts and instructions. This notebook comes in handy for deployments, automation. You can also share these notebooks with peers for using them or view scripts, their outputs.

For this article, you will require the Azure Data Studio latest version. This article uses ADS version 1.27.0. You can download ADS on Windows, macOS or Linux operating systems.

Download Latest Azure Data Studio

Launch ADS and click on New deployment.

ADS deployment

Click on SQL Server on Azure Virtual Machine suitable for migration and applications requiring OS-level access. Let’s explore the steps for configurations of Azure VM for SQL Server.

Step 1: Deployment pre-requisites

The first step is to download Azure CLI tools. You can install it using the Install theAzure CLI or click on Install tools on this page.

  • Note: The current version of Azure CLI is 2.21.0

Deployment pre-requisites

Currently, the following figure shows status as Not Installed in my lab environment.

Azure CLI tools

Once the Azure CLI is installed on your system, restart Azure Data Studio, and you can view its status as installed and directory.

Restart ADS post Azure CLI installation

Now, put a tick for accepting the Microsoft privacy statement, Azure SQL VM license terms and azdata license terms and click Next.

Step 2: Azure settings

In step 2, connect to your Azure account using your credentials using the Sign-in button. For the sign-in, enter your Azure credentials and authenticate for use in the ADS. Once you have signed in, it shows the account name from which you are signed in.

After the authentication, you get a subscription ID and resource groups. Select the resource group, Azure region for deploying SQL Server on Azure VM.

In this article, for demonstration, I use the East US location, as shown below.

Azure settings

Step 3: Virtual Machine settings

In this step, we specify the VM configurations for SQL Server using the following.

  • Azure virtual machine name
  • Administrator account user name
  • Administrator account password

Virtual Machine settings

  • Image: Select the SQL Server image with the OS using the drop-down list. It has various images for deployment on both Windows and Linux OS. In this lab environment, I choose SQL Server 2019 on Windows Server 2019
  • Image SKU: Select the edition such as Standard, Enterprise, developer. For the developer, choose sqldev, as shown below

Image and Image SKU

Scroll down and select the VM size. Azure provides various machine sizes to choose from depending on the vCPU, RAM, supported disk type and pricing. It gives a brief description of each VM machine. However, you should refer to Azure VM for reference purposes before deploying SQL Server.

SQL VM machine type

Step 4: Networking

In the networking, select the virtual network, subnet and public IP configurations. If you don’t have any existing VM networks, choose the option – -New virtual network.

Also, put a tick on the checkbox – Enable Remote Desktop (RDP) inbound window.

Networking

Step 5: SQL Server Settings

In the next step 5, SQL Server settings, we specify the following values.

  • SQL Server connectivity: Private (within Virtual Network)
  • Port: 1433 (default). If you want a non-default port, specify the port number
  • Enable SQL Server authentication: Yes
  • User name and password: Here, specify the administrator credentials for SQL Server authentication

SQL Server Settings

Click Next and review your SQL on Azure VM configurations before deployment.

SQL on Azure VM configurations

Click on Select, and it opens a SQL notebook with Azure CLI scripts in the Azure Data Studio.

Explore Jupyter notebook for deploying SQL Server on Azure VM

Azure Data Studio Jupyter notebook uses Python 3 kernel for running the script in the notebook. If you do not have Python 3, it automatically installs it for you.

At the top of the Jupyter notebook, it gives the summary and steps for implementing Azure SQL Virtual Machine.

It uses the following steps for overall deployment:

  1. Set variables and set up a notebook
  2. Connect to Azure account and subscriptions
  3. Configure Network settings
  4. Provision virtual machine resources in Azure
  5. Provision SQL VM resource in Azure

Jupyter notebook

The Jupyter notebook contains the following steps for deploying SQL Server on Azure VM.

Step 1: Set variables and set up the notebook

Step 1 collects variables information based on user input during the Azure deployment wizard in Azure Data Studio.

  • Set variable: You can review variables and their values for Azure VM deployment

    Set variable

  • Notebook setup

    Notebook setup

Step 2: Connect to Azure account and subscriptions

In step 2, the Jupyter notebook connects with the Azure account using the CLI script and fetches the subscription information.

Connect to Azure account and subscriptions

Next, it uses the az account set CLI command for setting the Azure subscription.

Setting Azure subscriptions

Step 3: Configure Network settings

The network settings section creates a new virtual network, subnet, and public IP address based on user inputs. The public IP address helps connect Azure resources over the internet.

Configure Network settings

Second, it creates a network security group and configures rules to allow RDP (port 3389) and SQL Server connections (port 1433).

Network IP config

In the third step, the CLI script creates the network interface using the az network nic create command.

creates the network interface

Step 4: Provision of virtual machine resources in Azure

In step 4, the CLI script creates an Azure VM based on the configurations specified in Steps 1-3. Here, it uses the az vm create CLI command for Azure VM deployment. Later, it creates a SQL VM with the default set of connectivity settings.

Provision of virtual machine resources in Azure

Step 5: Provision SQL VM resource in Azure

In this step, the Jupyter notebook uses az sql vm create CLI script for deploying SQL Server on Azure VM. It also configures SQL Server authentication based on the specified credentials.

Provision SQL VM resource in Azure

You can modify the configurations using the Azure CLI scripts in the Azure Data Studio and quickly deploy SQL Server on Azure VM without going through Azure portal steps. Click on Run all to run the Jupyter notebook.

Click on Run All

The Jupyter notebook embeds scripts and their outputs in a single console. For example, the following figure gives Azure CLI script as well as its JSON output.

Connecting to Azure

Once the Jupyter notebook script executes successfully, you can refresh the resource group in the Azure portal. It shows deployed resources such as virtual network, virtual machine, SQL virtual machine, public IP address, disk.

Check deployed resources in Azure portal

Conclusion

In this article, we explored Jupyter notebooks for deploying SQL Server on Azure VM deployment wizard. It uses Azure CLI scripts for quick configurations, deployments without the knowledge of CLI scripting.

Rajendra Gupta
Azure, SQL Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

133 Views