Rajendra Gupta

Provisioning Azure SQL database using Azure PowerShell

March 10, 2021 by

This article will show how to create an Azure SQL database using Azure PowerShell.

Introduction

Microsoft Azure is the leading cloud infrastructure platform. You implement database solutions on both on-premises and cloud servers. In the cloud, you can use Amazon Web Services (AWS), Azure, Google Cloud, or any other private cloud. Being a SQL Server DBA, you should be familiar with various Microsoft offerings for SQL Server.

Azure SQL provides the following products for the SQL Server database engine.

  • Azure SQL Database
  • Azure SQL Managed instance
  • SQL Server on Azure VM: It is similar to running SQL Server on an on-premises virtual machine (VM).

Azure databases

In the below Microsoft docs image, we get a comparison for the costs and administration tasks of on-premises and cloud databases.

costs and administration tasks

Azure SQL database

Azure Database offers a platform as a service (PAAS) or relational database-as-a-service (DBaaS) in Microsoft Azure. It provides the latest stable edition for the fully managed database engine service.

It has the following deployment options for the Azure SQL Database.

Azure single database

It is a standalone managed database, and it has its dedicated resources. It is similar to a contained database in SQL Server. It is best suited for applications that require dedicated resources, database scoped developments.

Elastic pools

It is a collection of databases and provides a cost-effective solution for managing multiple databases. The database can move in and out from the elastic pools. It is suitable for Saas applications that shared multiple databases and share costs for cost efficiency. Refer to the article, Elastic pools for more details.

Elastic pools

In the SQL Azure category on SQLShack, we learned deployment of Azure SQL DB using the Azure Web Portal. In this article, we cover the PowerShell cmdlets for Azure SQL DB deployment.

Install Azure PowerShell

We can use Azure PowerShell for configuring and managing the Azure resources. You can use the PowerShell version 6.2.4 or later on both Windows and Linux platforms.

In this article, we install the Windows PowerShell 7.x for using the Azure PowerShell cmdlets. If you have PowerShell 5.1, you need to follow the article for additional steps.

Run the following command to verify the Window PowerShell version in your environment.

Azure PowerShell

Navigate to Microsoft docs, choose your supported platform and download the Windows PowerShell 7 package.

supported platform

For my Windows environment, I downloaded the setup and started the installation wizard. It is a straightforward installation, as shown in the below sequence of images.

installation wizard

Launch PowerShell 7(x64) from the start menu.

Launch PowerShell 7(x64)

Verify the Windows PowerShell version. As shown below, we have the 7.1.0 version installed in our environment.

Verify the Windows PowerShell version

Now, install the Az module for Windows PowerShell. It downloads the package over an internet connection and installs it.

Install Az module

The below cmdlet returns the name, version of the Az module for Microsoft Azure.

version of the Az module

Steps for creating an Azure SQL Database using Azure PowerShell

Step 1: Connect to Azure account

Connect to the Azure portal using your credentials and navigate to Azure Active Directory. In the default directory, it shows the tenant ID and primary domain.

Connect to Azure account

Note-down the Tenant id and specify in the Connect-AzAccount cmdlet.

Note-down the Tenant id

It opens a web portal for your Azure credentials.

Azure credentials

It validates the Azure credentials, and the web portal shows the following message

validates the Azure credentials

The Connect-AzAccount cmdlet returns the account details, as shown below.

>Connect-AzAccount cmdlet

Step 2: Create an Azure Resource Group

You can consider the Azure resource group as a container that combines the various resources for your deployment. Before we create an Azure SQL DB, we create the resource group using the New-AzResourceGroup cmdlet.

In the below command, we create the [MyAzureSQL] resource group in central India.

If you are familiar with Azure locations, you can run the Get-AzLcoation cmdlet to display the location code and name.

Create an Azure SQL Server

Once the SQL database is configured, you can refresh the Azure Web Portal, and it shows you configured SQL Server in the Central India location.

SQL database configurations

Click on the SQL Server name, and you get another page with detailed information, settings, and configurations. On this page, you can note down the server name as the FQDN name of your SQL instance.

server name as the FQDN name

You can also use the Azure PowerShell cmdlet Get-AzSqlServer and retrieve the fully qualified name.

Azure PowerShell cmdlet

Step 4: Configure the Server Firewall Rule

We need to configure the server firewall rule for connecting to SQL instances. In this example, we specified an IP range that should be able to connect with the Azure SQL DB.

It uses New-AzSqlServerFirewallRule cmdlet and creates the firewall rule named AllowedIPs for the start, end IP addresses range.

Configure the Server Firewall Rule

Step 5: Connect to Azure SQL database and creates a new database

Connect to the Azure Server using FQDN using the SQL Server Management Studio. It shows version 12.0.2000.8. Is it SQL Server 2014?

Wait! Hold your horses. I will explain it in the later part of the article.

creates a new database

To create a new database, we use the New-AzSqlDatabase cmdlet. In this cmdlet, we specify the servername, database name from the variable we configured earlier.

In the RequestedServiceObjectiveName, we define the service tier based on the DTU’s, storage, max concurrent sessions. It is a critical parameter for an Azure SQL Database. Therefore, you should review the resources carefully.

In this article, we use the standard service tier S0 that has the following compute resources. You can follow the article DTU purchasing model for reference purposes.

service tier based on the DTU’s

In the sample name, we specify the sample schema name for the database.

sample schema name

Refresh your object explorer, and it shows the database name as shown below.

Refresh your object explorer

You can use the Get-AzSqlDatabase cmdlet to retrieve the database configurations.

In the output, it retrieves properties for the master and newly created Azure SQL database [sampledatabase].

Get-AzSqlDatabase cmdlet

Get-AzSqlDatabase cmdlet output

Step 6: Query the Azure SQL database for validations

We can either use the SSMS or Azure Web Portal Query editor (preview) for validation purposes. In the Azure portal, navigate to databases and open Query editor (preview).

Connect with your SQL login credentials.

Query the database

Run a select statement to view data from the tables. In the sample database, we have [SalesT] as a database schema.

Run a select statement

Step 7: Remove resources if not required

You should perform resource cleanups if it is not in use. To remove the resources, you can use the Remove-AzResourceGroup cmdlet, and it removes all resources inside it. It requires confirmation before proceeding further.

Remove resources if not required.

Azure SQL Database version

To check the SQL Server version, we use either of the following SQL queries.

It returns the output as Microsoft SQL Azure ( RTM) – 12.0.2000.8

Azure Database version

Azure SQL DB and SQL Server versions are not comparable with each other. It uses the internal build numbers for these. As we described earlier, Azure SQL Database uses the latest stable edition.

However, if we check the database compatibility level, it returns 150 for both master and user database. The compatibility level 150 is for SQL Server 2019.

database compatibility level

It shows the Azure SQL database is having the latest version 12. It is compatible with 150, 140, 130, 120, 110, 100.

Conclusion

In the article, we configured an Azure SQL database using the Azure PowerShell cmdlets. It is a quick and friendly way to configure databases and automate the process as well.

Rajendra Gupta
Azure, Installation, setup and configuration, PowerShell

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

1,065 Views