Rajendra Gupta
Reference link :https://azure.microsoft.com/en-in/services/azure-sql/

Deploying Azure SQL Database using Azure Data Studio

August 10, 2021 by

This article guides you to create a Jupyter notebook for Azure SQL Database deployment using Azure Data Studio.

A quick overview of Azure SQL Database and Azure Data Studio

Microsoft offers Azure SQL DB as a relational Database-as-a-Service (DBaaS) with the latest stable SQL Server version. It is a high-performance, secure, reliable cloud database for the data-driven applicable without an organization managing infrastructure.

Additionally, you have options for deploying SQL Server on Azure VM or Azure SQL Managed Instances, as shown below.

Reference link :https://azure.microsoft.com/en-in/services/azure-sql/

Image reference: Microsoft

Azure Data Studio is a Microsoft offering for managing both on-premises and cloud SQL databases for both Windows and Linux operating systems. It has a modern rich editor with IntelliSense, Source control, Code snippets, customized dashboards, and PowerShell, Bash terminal.

  • Note: You can refer to Azure Data Studio for exploring existing articles on SQLShack

Pre-requisites

For this article, you can download and use the latest Azure Data Studio version 1.27.0. Check out Microsoft docs for the latest version.

Download latest release of ADS

Deploy Azure SQL Server using Azure Data Studio (ADS)

You can deploy an Azure SQL DB using the following ways.

  • Azure portal
  • Azure PowerShell
  • Azure CLI
  • Azure ARM templates

Azure SQL Database exists on a logical Azure SQL Server. Suppose we want to utilize ADS for deploying Azure resources in a GUI method. ADS generates a Jupyter notebook with the CLI scripts. You can modify parameter values and deploy a new Azure database quickly and without writing any complex code.

To create a logical SQL Server, Launch the ADS and click on three dots(…), New deployment.

New deployment

In the new deployment, ADS asks you to choose from the following deployment options.

  • On-premises: SQL Server on Windows, SQL Server container Image, SQL Server Big data cluster
  • Cloud: SQL Server Big data cluster, Azure SQL DB, Azure SQL Managed Instance, SQL Server on Azure virtual machine

Deployment options

For this article, click on the Azure SQL Database. It opens a wizard to deploy Azure SQL DB, as shown below.

click on the Azure SQL Database

You get an error message in the ADS deployment: Sign in to Azure Account first. We can ignore this error for now. We require to deploy an Azure SQL Server for hosting the Azure SQL DB. Therefore, modify the resource type as Database Server from the drop-down menu, as shown below.

select Resource type

Accept the Microsoft license terms and click the Create in Azure Portal button.

Microsoft license terms

To open the Azure portal, click on Open.

Click on Open

Provide your Azure credentials, and after authentication, it directly takes you to Create SQL Database Server page. On this basic page, give the following inputs:

  • Subscription
  • Resource group
  • Server name
  • Location
  • Azure region
  • Server admin login
  • Server admin password

Create SQL Database server

We can skip the other sections -Networking, Additional settings and Tags. Click on Review + Create. Review your Azure SQL Server configurations.

Review your configurations

Click on Create. It starts the deployment, and within a few minutes, you get a message – Your deployment is complete.

Deployment status

Click on Go to resource and note down the Azure SQL Server name as highlighted below.

Azure server FQDN

Now, go back to Azure Data Studio. In the new connection window, enter the server name, credentials.

Create a new DB connection

Click on Connect. If your client IP address does not have server access, you need to sign in to the Azure account using Azure Data Studio and add a firewall rule.

Create new firewall rule

Click on Azure Account -> Add an account. Authenticate your Azure account, and you get the following message- Your account was added successfully.

Account message

In the ADS, you get Azure account details. In the firewall rule, either add a client IP or subnet IP range.

Add my subnet IP range

Click on Create, and you get an active connection to Azure SQL Server. By default, you get the master database.

Default database

Deploy Azure SQL Database using Azure Data Studio

We deployed the logical Azure Server in the above section. Now, we require an Azure SQL DB. Therefore, in the ADS, click on New deployment and select the Azure SQL Database. You get a prompt to reauthenticate to access the ARM resource.

Choose resource type as single database

Click on Open and reauthenticate using your Azure credentials. Before you move forward, install Azure CLI tools using the Microsoft docs. Click on the current release of Azure CLI and install it with straightforward steps.

Azure CLI install

In Step 1: deployment prerequisites, click on Install tools. It starts downloading the required Azure CLI commands.

Error messages

Once it is installed, you can see the discovered file path and status as below.

Restart Azure Data Studio and verify path

Select the license terms and click on Next.

In Step 2: Azure SQL Database – Azure account settings page, verify the Azure account, subscription and the Azure SQL Server.

Azure account settings

In Step3: Database settings, specify the Azure SQL DB name, database collation, specify a firewall rule name, IP address range as shown below. In my demo, I have specified the Azure DB name as [myazuredemodb].

Database name and collation

Click Next, Step 4: Summary, review and click on Create.

Review summary

Azure Data Studio creates a Jupyter notebook with Azure CLI scripts for deploying Azure DB. Therefore, it requires configuring Python Runtime. It opens a window to configure Python to run the Python 3 kernel. Click on New Python installation.

Step 1:Configure Python Runtime

Configure Python Runtime

Step 2: Install Dependencies

In step 2, click on Install dependencies. As shown below, it installs Jupyter 1.0.0.

Install Dependencies

It shows the downloaded package details and their installation progress in the Output tab of Azure Data Studio.

Download status python

Once Jupyter and its dependencies are installed, it gives a prompt – Notebook dependencies installation is complete as shown below.

Notebook dependencies

Explore Jupyter notebook for Azure SQL Database deployment

As shown below, you get a notebook to create the Azure SQL Database. This notebook has Azure CLI scripts and instructions as per your defined input. It has the following steps.

  1. Set variable and set up Notebook : This section contains the variable for Azure DB deployment
    • azure_sqldb_subscription: This parameter is for Azure subscription ID
    • azure_sqldb_resource_group_name: Resource group name for Azure DB
    • azure_sqldb_server_name: Azure logical SQL Server name
    • azure_sqldb_database_name: Azure SQL Database name that we want to create
    • azure_sqldb_collation: It has SQL Database collation name. By default, it uses collation SQL_Latin1_General_CP1_CI_AS
    • azure_sqldb_enable_firewall_rule: Enter True for enabling firewall
    • azure_sqldb_ip_start: Minimum IP address
    • azure_sqldb_ip_end: Maximum
    • azure_sqldb_firewall_name: Firewall rule name

    Explore Jupyter notebook for

  2. Notebook set up and connect to Azure account: This step connects to Azure account for subscription ID specified in the variable azure_sqldb_subscription

    Notebook set up and connect to Azure

  3. Setting Azure subscription and create a server firewall rule: In this step, Jupyter notebook defines a firewall rule with the specified IP address

    Azure subscription

  4. Create Azure SQL Database: It uses az sql db create CLI command for database deployment. By default, it makes a serverless compute model with the Gen5 family. You can configure different pricing tiers and additional customization in the script

    You can refer to az sql db to learn more details.

    az sql db create CLI command

To deploy an Azure DB using the Azure SQL Studio Jupyter notebook, click on Run all.

click on Run all.

The Jupyter notebook displays all Azure CLI scripts and their output in the relevant section. The following figure shows az sql db create command output for creating a new Azure SQL Database.

az sql db create command output

To validate the deployment, refresh your ADS connection, and you have [myazuredemodb] Azure DB, as shown below.

refresh your ADS connection

You can view the deployed database logical server name, pricing tier, auto-pause delay using the Azure portal as well.

database logical server name

Conclusion

Azure Data Studio generates Jupyter notebook with Azure CLI scripts for deploying Azure SQL Database. It helps you to automate, customize and deploy the database quickly without worrying about the scripting. To deploy a new database, you can modify the required parameter value and run the notebook for seamless deployment of Azure DB.

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

348 Views