Storage
Mobility
Protection
Analysis and control
TOPICShare
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
- Azure SQL Instance Deployment Using Azure PowerShell Commands
- Cloud Volumes ONTAP for MS SQL Database in Azure
- FAQs
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.
- In Cloud Shell, switch to “PowerShell” option from the top left drop down menu.
- 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>' 
- Create a variable for the target resource group name.
$rgname = "sqltestrg" 
- 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"
- Connect to your Azure subscription using the following command and follow the device log in instructions:
Connect-AzAccount -UseDeviceAuthentication 
- Create a target resource group name in the subscription.
$rgsql = New-AzResourceGroup -Name $rgname -Location $location 
- 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))
- 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 
- Create a sample database in the SQL server instance.
$SQLdatabase = New-AzSqlDatabase -ResourceGroupName $rgname -ServerName $serverName -DatabaseName $databaseName 
- 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:
- Lowers Azure cloud storage costsusing storage efficiencies such as thin provisioning, deduplication, and compression.
- Protects SQL data using point-in-time application consistent backups using NetAppSnapshot™ technology.
- The high availability configurationgives storage layers the resiliency to ensure minimal RTO (<60 seconds) and RPO (zero data loss).
- Quick deployment of new environments using low storage footprint writable clones created with NetApp FlexClone® data cloning technology.
- Data mobility across multi-cloud and hybrid deployments using SnapMirror® data replication.
- Centralized and consistent management of MS SQL volumes across multiple environments through NetApp Cloud Manager.
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.
