Technical Blog
Manually Create an Azure SQL Virtual Machine
Add Azure SQL Virtual Machine functionality to an existing SQL Server hosted on an Azure VM
The Azure SQL Virtual Machine resource provides an easy way to interact with SQL Server directly from the Azure Portal. You’re able to view disk usage and extend drives, as well as configure backups and patching schedules.
When creating a new SQL VM in Azure using one of the predefined SQL images, this is automatically created and configured for you. But if you already have a VM running SQL Server, how can we add this extra functionality?
Creating the Azure SQL VM resource
At present, the only way to create a SQL Server VM resource for an existing VM is via the command line. For SQL Server 2012 and later, there are two management modes; Lightweight and Full.
Lightweight Mode
Lightweight mode is the default when a VM is added using the automatic registration feature, or when a management type is not specified during manual registration. This mode only supports changing the license type and edition of SQL Server and provides limited ability to manage the instance from the Azure portal. This mode is best suited for SQL Server VMs running multiple instances of SQL Server, or those that are participating in a failover cluster instance (FCI). There is no downtime required to install this mode.
Azure CLI
az sql vm create --name [vm_name] --resource-group [resource_group_name] --location [vm_location] --license-type [license_type]Powershell
#Store existing compute VM details in variable
$vm = Get-AzVM -Name [vm_name] -ResourceGroupName [resource_group_name]
New-AzSqlVM -Name $vm.Name -ResourceGroupName $vm.ResourceGroupName -Location $vm.Location -LicenseType [license_type] -SqlManagementType LightWeight For license type, the available options are:
- PAYG – Pay Per Usage
- AHUB – Azure Hybrid Benefit
- DR – Free DR Replica License (for use on secondary servers in Availablity Group only)
Full Mode
Full mode installs the SQL IaaS Agent to the VM in order to deliver all functionality, but this does require a restart of the SQL Server instance and system administrator privileges. It’s recommended to install Lightweight Mode first, and schedule the upgrade to Full mode during a maintenance window. This mode provides all available functionality in the Azure Portal.
#Store existing compute VM details in variable
$vm = Get-AzVM -Name [vm_name] -ResourceGroupName [resource_group_name]
New-AzSqlVM -Name $vm.Name -ResourceGroupName $vm.ResourceGroupName -Location $vm.Location -LicenseType [license_type] -SqlManagementType FullConfiguring the VM
Azure VMs are created with an ephemeral temporary disk; the recommended best practice is to use this disk for TempDB.
Creating a SQL VM in Full Mode will automatically install the required libraries and executable, but it does not automatically create the scheduled task and registry keys required for the agent to register itself with the SQL VM, or automatically configure the ephemeral disk for use by TempDB which can cause issues with SQL Startup after a VM undergoes a full reboot or is stopped and deallocated. You will also be limited in the changes you can make from the Azure Portal, and will not have insight into the disks.
Without these, when SQL Server starts, the service account will not have permissions to create the tempdb files on the ephemeral temporary disk and the following error will be in the event log.
Event 5123, MSSQLSERVER
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'D:\tempdb.mdf'xTEN have created this script below to run on a system after installing the Full Management IaaS Agent to automatically create the required scheduled task and registry keys.
#checks if the IaaS extension executable exists; if it does, it creates the scheduled task
$filecheck = Test-Path -Path "C:\Program Files\Microsoft SQL Server IaaS Agent\Bin\SqlIaaSExtension.SqlServerStarter.exe"
if (!$filecheck){
Write-Output "The IaaS Extension executable cannot be found. Please confirm that the SQL VM has been created and the IaaS Extension installed"
}
else {
$action = New-ScheduledTaskAction -Execute 'C:\Program Files\Microsoft SQL Server IaaS Agent\Bin\SqlIaaSExtension.SqlServerStarter.exe'
$trigger = New-ScheduledTaskTrigger -AtStartup
Register-ScheduledTask -Action $action -Trigger $trigger -TaskName "SqlStartUp" -User "SYSTEM" -RunLevel "Highest"
}
#checks if the registry keys already exist; if they don't, it creates them
$regkeycheck1 = Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\SqlIaaSExtension\CurrentVersion" -Name "TempDbData" -ErrorAction Ignore
if (!$regkeycheck1) {
Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\SqlIaaSExtension\CurrentVersion" -Name "TempDbData" -Value "D:\Tempdb\Data"
}
$regkeycheck2 = Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\SqlIaaSExtension\CurrentVersion" -Name "TempDbLog" -ErrorAction Ignore
if (!$regkeycheck2) {
Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\SqlIaaSExtension\CurrentVersion" -Name "TempDbLog" -Value "D:\Tempdb\Log"
}
Following these steps will allow you to leverage the Azure SQL VM resource and all of its functionality for a VM that has been created with a non-SQL image, or that predates Azure SQL VM. Gain greater insights into your SQL disk utilisation, automate backups and updates and review security settings directly from the Azure Portal, and all at no additional cost – xTEN ensures that all of their Azure customers running a SQL VM have this resource available.
