Using Bicep to Deploy a Microsoft SQL on Azure VM with Cloud Block Store Volumes
This blog will showcase how to deploy an Azure VM that has a configured Microsoft SQL Instance that is connected to a Cloud Block Store (CBS) volume. I have previously showcased VM & CBS deployments with both Powershell and Terraform, but its time to bring it to a new language!
Introduction
The following sections will explain why certain technologies were chosen for this blog, such as why we chose a particular service or storage type.
Why CBS?
Well if you have read any of my previous blogs, CBS is a third party block storage option that makes Cloud Storage better! Just like our on-premises storage, an extremely popular use case is customers wanting to run databases; especially Microsoft SQL. When using something like Azure Native Storage you are bound by what they provide, such as pre-determined disk sizes that happen to include specific IOPs and Throughput. With CBS this no longer becomes an issue, and you gain even more features! You can learn more about Cloud Block Store here.
Why Microsoft SQL on Azure VM
In most cases, customers will deploy an Azure VM and then configure storage and install Microsoft SQL. Using an Azure Microsoft SQL Solution allows it to become a managed solution. is also provides additional benefits such as built in patching and backups as you can see below.
It is also the most flexible offering from Azure in regards to Microsft SQL as there are two other offerings. Azure SQL (SaaS) and Azure SQL Managed Instance (PaaS) are the other options and you can see how they compare and contrast in the following breakdown.
Building a Bicep Template
In my previous blog on Bicep I covered what Bicep is, and how to deploy a standard Azure VM. This template will add two additional resources. The first resource will be using a VM Extension to handle the disk provisioning and configuration, and the second resource will be the SQLVirtualMachine one which goes out and configures the SQL Server to use the newly provisioned disks. The best part is due to the Microsoft SQL IaaS extension, all of the storage metrics get automatically pulled into the Azure Portal.
Bicep File to Deploy Azure Resources
If we look at the below Bicep file, it looks similar to the previous template we used, however it has the two new resources we discussed. The below file will highlight those resources that need to be deployed. You’ll also notice the VM Extension calling another script, we will cover that in the next section.
Powershell Script to Configure Cloud Block Store Resources
The next file you will need is the Powershell script that is run as a VM Extension. This is ran on Windows startup and will execute tasks to Configure iSCSI, Create the Host on CBS, Create the Volumes and Connect them together. Once configured it will configure iSCSI on the Windows host in Azure to connect the volumes
Let’s put all these pieces together and actually run the deployment.
Deploying Resources
Once we log in to Azure using az login we are able to run the command to execute the creation of the Azure Resources. You will need to input things like your Resource Groups, Virtual Network, Subnets and then Configuration for your CBS such as the IP, Username and Password.
The resources will deploy an Azure VM, 3 Cloud Block Store volumes, 1 for TempDB, 1 for Logs and 1 for Data and then Configure SQL Server.
1az deployment group create --resource-group 'MyVMResourceGroup' \
2--template-file deploy_sql_cbs.bicep \
3--parameters virtualMachineName='testvm' existingVirtualNetworkName='MyVNET' \
4existingVnetResourceGroup='MyVNET-rg' existingSubnetName='MySubnet' \
5adminUsername='MyUsername' adminPassword='abcd1234!' PureManagementIP='172.20.1.5' \
6PureManagementUser='pureuser' PureManagementPassword='pureuser'
When the deployment is running you can track it by navigating to the Resource Group and selecting Deployments. Here is where you can track the status of failed or success. When the deployment is completed, if successful you will see everything as green and can provide to the resource group to see what was created.
Closing
As folks are looking to have a fully managed environment, a solution like thats makes sense. With automation, nothing is truly impossible. This solution provides a fully automated solution that will deploy a Microsoft SQL Azure VM that is utilizing Pure Storage Cloud Block Store.
Any questions or comments? Leave them below.
comments powered by Disqus