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:
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.
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.
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.
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>'
$rgname = "sqltestrg"
$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-AzAccount -UseDeviceAuthentication
$rgsql = New-AzResourceGroup -Name $rgname -Location $location
$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))
$SQLFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $rgname -ServerName $serverName -FirewallRuleName "AllowedIPs" -StartIpAddress $iprangestartIp -EndIpAddress $iprangendIp
$SQLdatabase = New-AzSqlDatabase -ResourceGroupName $rgname -ServerName $serverName -DatabaseName $databaseName
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.
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.
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.
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.
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.