How to Deploy A SQL Server Virtual Machine in Azure – Part 2

For this series, I am focusing on deploying a virtual machine (VM) using three different methods.  Part 1 focused on using the Azure Portal.  Part 2, this post, will go over how to deploy a VM using a PowerShell script.  For the final part, Part 3, I will focus on using ARM (Azure Resource Manager)  templates for deploying virtual machines. For reference, I used Microsoft’s Quickstart article on how to deploy a VM.

To deploy a VM you will need to make sure you have a couple of things setup prior to running the script.

  1. Resource Group
  2. Virtual Network (VNet)
  3. SubNet

First, declare some variables.

$ResourceGroupName = "RG-Name-01"
$VNetName = "vnet-dba-vm-name"
$SubNetName = "snet-dba-vm-name"
$VMName = "VMDBAITCSQL01"
$Location = "North Central US"

You will need to get the VNet information

$VNet = Get-AzureRmVirtualNetwork -Name $VnetName -ResourceGroupName $ResourceGroupName
$SubnetID =  Get-AzureRmVirtualNetworkSubnetConfig -Name $SubnetName -VirtualNetwork $VNet
$InterfaceName = $VMName + "-nic"

$Interface = New-AzureRmNetworkInterface -Name $InterfaceName `
   -ResourceGroupName $ResourceGroupName -Location $Location `
   -SubnetId $SubnetID.Id

Setup and create a data disk on the VM. This disk will be for the database and log files.  For SQL Server database files, you will want to use premium managed disks.  Azure will setup folders for the data and log files automatically.  On a basic install I have done it is usually F:\Data & F:\Log.

$storageType = 'Premium_LRS'
$dataDiskName = $vmName + '_datadisk1'

$diskConfig = New-AzureRmDiskConfig -SkuName $storageType -Location $Location -CreateOption Empty -DiskSizeGB 1000
$dataDisk1 = New-AzureRmDisk -DiskName $dataDiskName -Disk $diskConfig -ResourceGroupName $ResourceGroupName

Next, you will need to define an admin account, or sa account. The sa account is disabled by default in the SQL Server VM in Azure. The account I am creating here will act as the sa account. This is also the account you can use when using RDP to get on the virtual machine.

$SecurePassword = ConvertTo-SecureString 'Change.Me2019' `
-AsPlainText -Force
$Cred = New-Object System.Management.Automation.PSCredential ("sqlvmadmin", $securePassword)

Now, we are ready to configure and create the VM.  When configuring the VM, you specify the size of the server, publisher, offer, SKU and version.  In this case it is the latest version of Microsoft SQL Server 2017.  The SKU is the developer edition of SQL Server.

# Create a virtual machine configuration
$VMConfig = New-AzureRmVMConfig -VMName $VMName -VMSize "Standard_D2s_v3" | `
   Set-AzureRmVMOperatingSystem -Windows -ComputerName $VMName -Credential $Cred -ProvisionVMAgent -EnableAutoUpdate | `
   Set-AzureRmVMSourceImage -PublisherName "MicrosoftSQLServer" -Offer "SQL2017-WS2016" -Skus "SQLDEV" -Version "latest" | `
   Add-AzureRmVMNetworkInterface -Id $Interface.Id | `
   Add-AzureRmVMDataDisk -Name $dataDiskName -CreateOption Attach -ManagedDiskId $dataDisk1.Id -Lun 1

# Create the VM
New-AzureRmVM -ResourceGroupName $ResourceGroupName -Location $Location -VM $VMConfig

In order to automate management of some SQL Server tasks, such as running and managing backups and automatic patching, you will need to setup the SQLIaaS extension. You can find more information here.  Installing the extension enables the SQL Server configuration option under the server blade in the Azure portal.

To do this in Powershell

# Setup the SQL IaaS Agent
Set-AzureRmVMSqlServerExtension -ResourceGroupName $ResourceGroupName -VMName $VMName -name "SQLIaasExtension" -version "1.2" -Location $Location

At this point you can stop here and run the script and it will create the VM in Azure with SQL Server installed. You can take it a step further and install the SQL Server IaaS extension, which will enable Azure to manage the full database backups along with the transaction log backups.

With the New-AzureRmVMSqlServerAutoBackupConfig command you can setup the backup retention period,the frequency and schedule of the backups. It does require a storage account, so in this script, I am creating a storage account first and then running the backup configuration.

#Setup a storage account for DB backups and then enable automated backups in SQL Server
$saName = $VMName.ToLower()
$storage_accountname = "sa"+ $saName

$storage = New-AzureRmStorageAccount -ResourceGroupName $ResourceGroupName `
    -Name $storage_accountname -SkuName Standard_GRS -Location $Location

$autobackupconfig = New-AzureRmVMSqlServerAutoBackupConfig -Enable `
-RetentionPeriodInDays 14 -StorageContext $storage.Context `
-ResourceGroupName $ResourceGroupName -BackupSystemDbs `
-BackupScheduleType Manual -FullBackupFrequency Daily `
-FullBackupStartHour 20 -FullBackupWindowInHours 2 `
-LogBackupFrequencyInMinutes 60 

Set-AzureRmVMSqlServerExtension -AutoBackupSettings $autobackupconfig `
-VMName $VMName -ResourceGroupName $ResourceGroupName

Now you are ready to run the script as you would any other PowerShell script. The script will take a few minutes to deploy the server. Once it is completed you will be able to begin working with SQL Server.

Look for the next post on ARM Templates.