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).
In the below Microsoft docs image, we get a comparison for the costs and administration tasks of on-premises and cloud databases.
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.
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.
|
1 |
$PSVersionTable.PSVersion |
Navigate to Microsoft docs, choose your supported platform and download the Windows PowerShell 7 package.
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.
Launch PowerShell 7(x64) from the start menu.
Verify the Windows PowerShell version. As shown below, we have the 7.1.0 version installed in our environment.
Now, install the Az module for Windows PowerShell. It downloads the package over an internet connection and installs it.
|
1 |
Install-Module -Name Az |
The below cmdlet returns the name, version of the Az module for Microsoft Azure.
|
1 |
Get-InstalledModule -Name Az | select Name, Version |
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.
Note-down the Tenant id and specify in the Connect-AzAccount cmdlet.
|
1 |
Connect-AzAccount -Tenant '2b9eb124-502e-4c37-b5d4-d50a2dbd472e' |
It opens a web portal for your Azure credentials.
It validates the Azure credentials, and the web portal shows the following message
The Connect-AzAccount cmdlet returns the account details, as shown below.
>
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.
|
1 |
New-AzResourceGroup -Name 'MyAzureSQL' -Location 'centralindia' |
If you are familiar with Azure locations, you can run the Get-AzLcoation cmdlet to display the location code and name.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
Get-AzLocation |Select Location, Displayname | Format-Table </p> <p> As shown below, the location code for Central India is <strong>centralindia</strong>. </p> <p> <a rel="lightbox [15]" href="/wp-content/uploads/2021/03/create-an-azure-resource-group.png"><img src="/wp-content/uploads/2021/03/create-an-azure-resource-group.png" alt="Create an Azure Resource Group" /></a> </p> <p> The Azure Resource Group cmdlet returns the status as <strong>Succeeded</strong> as shown below. </p> <p> <a rel="lightbox [16]" href="/wp-content/uploads/2021/03/azure-resource-group-cmdlet.png"><img src="/wp-content/uploads/2021/03/azure-resource-group-cmdlet.png" alt="Azure Resource Group cmdlet " /></a> </p> <p> Now, you can validate the newly created resource group in the Azure Web Portal. </p> <p> <a rel="lightbox [17]" href="/wp-content/uploads/2021/03/validate-newly-created-resource-group.png"><img src="/wp-content/uploads/2021/03/validate-newly-created-resource-group.png" alt="Validate newly created resource group " /></a> </p> <h3>Step 3: Create an Azure SQL Server</h3> <p> In the step, we use the <a href="https://docs.microsoft.com/en-us/powershell/module/az.sql/new-azsqlserver?view=azps-5.1.0" target="_blank" rel="nofollow noopener">New-AzSqlServer</a><strong> </strong>for a SQL database server. Firstly, we defined the following variables and set their values so that we can use these variables in Azure PowerShell cmdlet. </p> <ul> <li> $resourceGroupName: Specify the Azure resource name we created in step 2 </li> <li> $location: It stores the value for Azure resource location </li> <li> $adminSqlLogin: Specify a login name that we will use to connect with the SQL authentication </li> <li> $password: Specify the password for the $adminSqlLogin login </li> <li> $databaseName: Specify a database name </li> <li> $servername: Specify the SQL server name </li> </ul> <p> In the New-AzSqlServer cmdlet, we pass the variables information in the [ResourceGroupName], [ServerName] and [Location] argument. </p> <p> The [SqlAdministratorCredentias] defines the SQL Server administrator using the $adminSqlLogin and $password for the username and password. </p> <p><pre lang="PowerShell">$resourceGroupName = "MyAzureSQL" $location = "centralindia" $adminSqlLogin = "SqlAdmin" $password = "admin@123" $databaseName = "SampleDatabase" $serverName = "sqlshackdemo" $server = New-AzSqlServer -ResourceGroupName $resourceGroupName ` -ServerName $serverName ` -Location $location ` -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force)) |
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.
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.
You can also use the Azure PowerShell cmdlet Get-AzSqlServer and retrieve the fully qualified name.
|
1 |
>Get-AzSqlServer | select FullyQualifiedDomainName |
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.
|
1 2 3 4 5 |
$startIp = "10.0.2.40" $endIp = "10.0.2.45" $serverFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName ` -ServerName $serverName ` -FirewallRuleName "AllowedIPs" -StartIpAddress $startIp -EndIpAddress $endIp |
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.
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.
In the sample name, we specify the sample schema name for the database.
|
1 2 3 4 5 |
$database = New-AzSqlDatabase -ResourceGroupName $resourceGroupName ` -ServerName $serverName ` -DatabaseName $databaseName ` -RequestedServiceObjectiveName "S0" ` -SampleName "AdventureWorksLT" |
Refresh your object explorer, and it shows the database name as shown below.
You can use the Get-AzSqlDatabase cmdlet to retrieve the database configurations.
|
1 |
Get-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName |
In the output, it retrieves properties for the master and newly created Azure SQL database [sampledatabase].
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.
Run a select statement to view data from the tables. In the sample database, we have [SalesT] as a database schema.
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.
Azure SQL Database version
To check the SQL Server version, we use either of the following SQL queries.
|
1 2 |
select @@VERSION SELECT SERVERPROPERTY('ProductVersion') |
It returns the output as Microsoft SQL Azure ( RTM) – 12.0.2000.8
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.
It shows the Azure SQL database is having the latest version 12. It is compatible with 150, 140, 130, 120, 110, 100.
- You can follow the article ALTER DATABASE (Transact-SQL) Compatibility Level for more information
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023