BlueXP Blog

Azure SQL & Powershell to Automate SQL Deployment

Written by Yifat Perry, Technical Content Manager | May 10, 2022 9:49:40 AM

More and more enterprises are moving their line-of-business applications and databases to the cloud. Cloud technology allows for the flexibility of deploying these applications in different models, with many organizations opting for PaaS for databases due to the ease of deployment and lifecycle management. In large-scale deployments, automation is also necessary to enable agility and is part of implementing a successful infrastructure-as-code Azure strategy.

In this blog post, we’ll explore the automated deployment of Azure SQL using PowerShell.

In this article, we will cover:

Azure SQL Services

Microsoft SQL is used as the backend database in a large chunk of enterprise applications while the Azure cloud provides customers the choice to deploy it in virtual machines (IaaS) and also as a managed SQL service. Let’s take a look at the different options available to deploy SQL services in Azure.

  • Host in VMs: You can deploy SQL in a virtual machine in Azure. There are SQL images available in the marketplace that can be used for simplifying the deployment approach. During deployment, relevant configuration choices like connectivity, authentication, and backup can be specified and the server will be created with the DB software configured accordingly.
  • Azure SQL Database: Customers who want a more hands-free approach with SQL deployment can choose Azure SQL Database, where the operating system, software, availability, backup, and upgrades are taken care of by Azure. Customers can simply focus on developing and deploying their applications in the cloud using SQL DB.
  • Azure SQL Managed Instance: For customers looking for large-scale migration of applications from on-premises environments to the cloud, Azure SQL Managed Instance offers the best compatibility and benefits of a PaaS service. You can use a “lift and shift” approach to move databases from on-premises servers to SQL managed instances through online replication solutions. Additionally, customers stand to benefit from the features of managed services, such as automated backups, high availability, and upgrades.

Azure SQL Instance Deployment Using Azure PowerShell Commands

In addition to faster deployment times, automation ensures repeatability, adherence to organizational standards, and a reduction in manual errors. This becomes especially important in large-scale enterprise Azure SQL deployments. In Azure, you can enable automation through different tools and services, like Terraform, Azure CLI, ARM templates, and PowerShell, to name a few. Here’s how to achieve automation of Azure SQL deployment using Azure PowerShell.

Prerequisites for Deployment

  • The deployment can be done from Azure Cloud Shell which provides a PowerShell command execution environment. You can access Cloud Shell from https://shell.azure.com/or from the Azure portal.
  • You can also use a local installation of Azure PowerShell.
  • You can run these commands using a role that has contributor permissions at the resource group or subscription levels.

Step-by-Step Commands for Deploying Azure SQL Services

Follow the steps below to create an Azure SQL database and configure firewall rules to allow access to the database from other services and allowed IPs. The following Azure SQL PowerShell commands were executed from Azure Cloud Shell.

  1. In Cloud Shell, switch to “PowerShell” option from the top left drop down menu.

  1. Install Azure PowerShell module using the following command:
Install-Module -Name Az

 

From Azure PowerShell, set the subscription ID of the subscription where the Azure SQL instance will be created. You can get this information from Azure portal → Subscriptions → <target subscription> → Overview page.

$SubscriptionId = '<subscription id>'

 

  1. Create a variable for the target resource group name.
$rgname = "sqltestrg"

 

  1. Set the location of the SQL server.
$location = "eastus2"

 

Set the properties of the SQL server to be deployed, i.e., SQL administrator login, server name, database name, and the IP range from which the server can be accessed.

Note: Update these variables with values relevant to your deployment environment. The IP ranges should be restricted to the IPs from which you need to enable access to the SQL server and the SQL server name should be unique system wide.

$sqladministrator = "SqlAdmin1" 
$password = "P@ssword1" 
$serverName = "testsqlserver743" 
$databaseName = "testDB1"
$iprangestartIp = "0.0.0.0"
$iprangendIp = "0.0.0.0"

 

  1. Connect to your Azure subscription using the following command and follow the device log in instructions:
Connect-AzAccount -UseDeviceAuthentication

 

  1. Create a target resource group name in the subscription.
$rgsql = New-AzResourceGroup -Name $rgname -Location $location

 

  1. Create the Azure SQL server in the newly created resource group.
$SQLserver = New-AzSqlServer -ResourceGroupName $rgname `
    -ServerName $serverName `
    -Location $location `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $sqladministrator, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

 

  1. Create a firewall rule to enable access to the SQL server deployed in the previous step.
$SQLFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $rgname -ServerName $serverName  -FirewallRuleName "AllowedIPs" -StartIpAddress $iprangestartIp -EndIpAddress $iprangendIp

 

  1. Create a sample database in the SQL server instance.
$SQLdatabase = New-AzSqlDatabase  -ResourceGroupName $rgname  -ServerName $serverName  -DatabaseName $databaseName

 

  1. You will now see the Azure SQL server instance and the SQL database created if you browse to the target resource group in Azure portal.

After following these steps, you will have successfully completed the deployment of a new Azure SQL server and database instance using Azure SQL PowerShell commands. You can now connect to it using familiar tools like SQL Server Management Studio for further configuration of the database. When integrated with DevOps and CI/CD pipelines, these Azure SQL PowerShell commands will help automate the Azure SQL deployment process.

Cloud Volumes ONTAP for MS SQL Database in Azure

With Azure SQL Database, the administrative activities of databases are offloaded to Azure. However, in certain circumstances, customers need additional flexibility in deploying SQL databases. For example, they might be using a different version than the latest release, need connectivity to local data centers, want control over DB maintenance and backup schedules, or require more flexibility in storage usage. In such scenarios, customers can opt for a self-managed SQL DB deployed in VMs. NetApp Cloud Volumes ONTAP can also be used to enhance the performance, availability, and agility of storage for such deployments.

Cloud Volumes ONTAP is based on NetApp’s trusted ONTAP technology that delivers an enterprise-class storage management experience for critical workloads such as SQL in Azure. There are several benefits to using Cloud Volumes ONTAP, including:

Databases form the core of the line of business applications. SQL DBs used in such applications can benefit from the advanced capabilities offered by Cloud Volumes ONTAP to deliver consistent and reliable user experience.

To learn more about infrastructure as code on Azure, take a look at our posts on Azure Resource Manager and Terraform on Azure.


FAQs

How do I query Azure SQL Database using PowerShell?

Microsoft provides two PowerShell modules for managing SQL servers including Azure SQL databases. They are SQLServer and SQLPS. The latest, SQL Server, is recommended by Microsoft for SQL administration. The SQL Server module contains cmdlets that can be used to run scripts with Transact-SQL/xQuery. Refer to the Microsoft documentation for SQL Server for step-by-step instructions on installation and usage of SQL Server PowerShell module.

How do I create an Azure SQL database using PowerShell?

PowerShell can be used to create and manage Azure SQL instances and databases. These commands can run either from Cloud Shell or with a local installation of PowerShell. You should also install the ‘Az’ module to run the latest Azure PowerShell commands for creating an Azure SQL database. Step-by-step guidance on how to create an Azure SQL database using PowerShell is covered above in this blog post.

Is PowerShell used in Azure?

PowerShell can be used to create and manage your Azure resources. This comes in handy for administrators who are familiar with PowerShell in a Windows ecosystem and for those organizations who want to automate the administration process through scripts. Azure Cloud Shell provides a preinstalled environment for running PowerShell commands. You can also install the Azure PowerShell module locally and connect to your Azure subscription for managing resources.