To deploy a VM with Bicep, the resource Microsoft.Compute/virtualMachines can be used. If the image from SQL Server is used, a Microsoft SQL Server is installed accordingly. However, a sample database or firewall rule is often required. These steps can be easily carried out using the resource Microsoft.Compute/virtualMachines/runCommands.
The script for this installation is deployed on a resource group and creates the SQL VM, a VNet with subnet, an NSG and performs the corresponding setup steps. Below is the part of the script with the VM and the Run-Command:
targetScope = 'resourceGroup'
param adminUsername string = 'adminUser'
@secure()
param adminPassword string
...
resource sqlserver 'Microsoft.Compute/virtualMachines@2021-03-01' = {
properties: {
storageProfile: {
imageReference: {
publisher: 'MicrosoftSQLServer'
offer: 'SQL2017-WS2016'
sku: 'Standard'
version: 'latest'
}
}
osProfile: {
computerName: 'SQLServer'
adminUsername: adminUsername
adminPassword: adminPassword
}
...
}
}
resource sqlServerInstall 'Microsoft.Compute/virtualMachines/runCommands@2024-07-01' = {
parent: sqlserver
name: 'sqlServerDbInstall'
location: resourceGroup().location
properties:{
source:{
script: '''
$zipUrl = "https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks-oltp-install-script.zip"
$destinationFolder = "C:\AWSample"
New-Item -ItemType Directory -Path $destinationFolder
$zipFilePath = "$destinationFolder\file.zip"
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
Invoke-WebRequest -Uri $zipUrl -OutFile $zipFilePath
Add-Type -AssemblyName System.IO.Compression.FileSystem
[System.IO.Compression.ZipFile]::ExtractToDirectory($zipFilePath, $destinationFolder)
Remove-Item -Path $zipFilePath
sqlcmd -S $Env:COMPUTERNAME -E -i "$destinationFolder\instawdb.sql"
New-NetFirewallRule -Name "Allow SQL Server" -DisplayName "SQL Server" -Enabled True -Profile Any -Action Allow -Direction Inbound -LocalPort 1433 -Protocol TCP
'''
}
}
}
In lines 34-42, the installation script of the Adventure Works database is downloaded and unpacked into the folder C:\AWSample. The archive was deleted afterwards.
In line 43, the command sqlcmd is used to start the Adventure Works installation script (-i "$destinationFolder\instawdb.sql"). The local Windows account is used to establish a trusted connection (-E) to the server (-S $Env:COMPUTERNAME).
Line 44 creates a firewall rule to access the SQL Server port 1433. This line changes the firewall of the VM, it does not affect the deployed NSG.
The deployment can then be carried out as usual (here into the existing resource group rg-sqlserver). Keep in mind, that you need to secure your password, because the parameter is marked as @secure() in line 3:
New-AzResourceGroupDeployment -ResourceGroupName rg-sqlserver -TemplateFile sqlvmwithadventureworks.bicep -adminPassword (ConvertTo-SecureString "P@ssw0rD!" -AsPlainText -Force)
The runCommands resource is an easy way to run scripts on VMs. However, I think it is not very well documented and there are not many examples of it yet.
1 Pingback