By: Edwin Sarmiento | Updated: 2017-05-04 | Comments | Related: > Azure
Problem
Our organization is starting to deploy more virtual machines on Azure for both testing and DR environments. I would like to create and administer Azure virtual machines using Windows PowerShell. How do I get started?
Solution
In a previous tip on Introduction to Azure PowerShell Modules for the SQL Server DBA Part 3, you looked at creating a resource group, a storage account, a virtual network configuration, a virtual network, a public IP address and a virtual network adapter using the Azure Resource Manager-based PowerShell cmdlets. This is in preparation for deploying a SQL Server virtual machine (VM) on Azure. This tip will continue with the creation of an Azure VM running a SQL Server instance from the existing images available in the Azure VM gallery.
You will use the New-AzureRmVMConfig cmdlet to create a configurable Azure VM object, specifying the VM name and the size. In case you don’t know the different Azure VM sizes, you can use the Get-AzureRmVMSize cmdlet and specify the -Location parameter.
Get-AzureRmVMSize -Location "East US"
Proper Azure VM sizing for your SQL Server workload is beyond the scope of this tip and will be covered in a future tip. For this example, you can use Standard_DS3_v2. Assign the output of the New-AzureRmVMConfig cmdlet to a variable as it will be used in subsequent tasks.
$SQLVM = New-AzureRmVMConfig -VMName "HR_DB_TEST" -VMSize "Standard_DS3_V2"
Next, you need to add the Azure virtual network adapter that you created in the previous tip. You use the Add-AzureRmVMNetworkInterface cmdlet, passing the variable for the Azure VM configuration object that contains the VM name and the size. Notice that the same variable name is being used. This simply means that the Azure VM configuration object is being built until you have all that you need to create the Azure VM.
$SQLVM = Add-AzureRmVMNetworkInterface -VM $SQLVM -Id $HR_DB_TEST_vNIC.Id
Next, you need to define the location of the Azure blob storage where the Azure VM virtual hard disk (VHD) will be stored. Let’s call the VHD file SQLVM_OSDisk.vhd and store it inside the vhds container. The PrimaryEndpoints property defines the URLs that are used to retrieve a blob object inside the Azure storage account. The variable below is simply used to define the complete URL endpoint to access the VHD file.
$OSDiskUri = (Get-AzureRmStorageAccount).PrimaryEndpoints.Blob.ToString() + "vhds/SQLVM_OSDisk.vhd"
Continuing to build the Azure VM configuration object, use the Set-AzureRmVMOSDisk cmdlet to set the operating system disk properties on the Azure VM, passing the URL endpoint of the Azure VHD file. The -CreateOption parameter value of FromImage tells the command to create the Azure VM machine from an existing image.
$SQLVM = Set-AzureRmVMOSDisk -VM $SQLVM -Name "SQLVM_OSDisk" -VhdUri $OSDiskUri -Caching ReadOnly -CreateOption FromImage
You, then, need to specify the platform image sourse for the Azure VM using the Set-AzureRmVMSourceImage cmdlet. However, this is a bit tricky. In order to properly define the image source, you need to know the following parameters.
- PublisherName. The available Azure VM images from the gallery come from different vendors – Microsoft, RedHat, F5 Networks, NetApp, etc. To list all of the Azure VM image publishers, use the Get-AzureRmVMImagePublisher cmdlet. We do know for sure that SQL Server comes from Microsoft.
Get-AzureRmVMImagePublisher -Location "East US" | Select PublisherName
- Offer. This specifies the content of the Azure VM image, typically a combination of the SQL Server and operating system version. To list all of the available Azure VM image content, use the Get-AzureRmVMImageOffer cmdlet.
Get-AzureRmVMImageOffer -Location "East US" -Publisher "MicrosoftSQLServer" | Select Offer
- Skus. This is the SKU of the contents of the Azure VM image. For this particular usage, it’s the edition of the SQL Server instance. To list all of the available SKU for a specific publisher given a specific offer, use the Get-AzureRmVMImageSku cmdlet. The example code below displays the available SKUs for a SQL Server 2016 with Service Pack 1 running on Windows Server 2016.
Get-AzureRmVMImageSku -Location "East US" -Publisher "MicrosoftSQLServer" -Offer "SQL2016SP1-WS2016" | Select Skus
- Version. This is the version of the contents of the Azure VM image. For this particular usage, it’s the version of the SQL Server instance. To list all of the available SKU for a specific publisher given a specific offer, use the Get-AzureRmVMImage cmdlet. The example code below displays the available versions for a SQL Server 2016 Standard Edition instance with Service Pack 1 running on Windows Server 2016. You might think that this is unnecessary given that you are dealing with a major service pack version. You might have a requirement to install specific cumulative updates. Knowing what specific version number you need and what is currently available can help you choose the right Azure VM image.
Get-AzureRmVMImage -Location "East US" -PublisherName "MicrosoftSQLServer" -Offer "SQL2016SP1-WS2016" -Skus "Standard" | Select Version
Once you have all the available information to define the source image for the Azure VM image, continue creating the Azure VM configuration object.
$SQLVM = Set-AzureRmVMSourceImage -VM $SQLVM -PublisherName "MicrosoftSQLServer" -Offer "SQL2016SP1-WS2016" -Skus "Standard" -Version "latest"
You need to define the credentials of the local Administrator account as part of defining the operating system properties of the Azure VM. Use the Get-Credential cmdlet, passing the results to a variable that you can use later. You will be prompted to provide the credential. Document this credential as you need it to test connectivity to the Azure VM.
$localAdminCreds = Get-Credential -Message "Type the name and password of the local administrator account."
This is then passed to the Azure VM configuration object using the Set-AzureRmVMOperatingSystem cmdlet.
$SQLVM = Set-AzureRmVMOperatingSystem -VM $SQLVM -Windows -ComputerName "HRDBTEST" -Credential $localAdminCreds -ProvisionVMAgent -EnableAutoUpdate
When you have all the necessary configuration settings that you need, proceed to create the Azure VM using the New-AzureRmVM cmdlet.
New-AzureRmVM -ResourceGroupName "HR_DB_TEST" -Location "East US" -VM $SQLVM
The script below contains the steps outlined in this tip and the previous tip.
#Create an Azure Resource Group
New-AzureRmResourceGroup -Name "HR_DB_TEST" -Location "East US"
#Create an Azure storage account
New-AzureRmStorageAccount -ResourceGroupName "HR_DB_TEST" -Name "hrdbtestvmdisks" -SkuName "Standard_LRS" -Kind "Storage" -Location "East US"
#Create variable for Azure virtual network configuration
$vNETSubnetConfig = New-AzureRmVirtualNetworkSubnetConfig -Name "HR-VNet-Config" -AddressPrefix "172.16.0.0/24"
#Create variable for Azure virtual network
$HR_DB_TEST_vNIC = New-AzureRmVirtualNetwork -Name "HR-VNet" -ResourceGroupName "HR_DB_TEST" -Location "East US" -AddressPrefix "172.16.0.0/24" -Subnet $vNETSubnetConfig
#Create variable for Azure public IP address
$HR_DB_TEST_publicIP = New-AzureRmPublicIpAddress -Name "HR_DB_TEST_publicIP" -ResourceGroupName "HR_DB_TEST" -Location "East US" -AllocationMethod "Dynamic"
#Create variable for Azure VM network interface
$HR_DB_TEST_vNIC = New-AzureRmNetworkInterface -Name "HR_DB_TEST_vNIC" -ResourceGroupName "HR_DB_TEST" -Location "East US" -SubnetId $HR_DB_TEST_vNIC.Subnets[0].Id -PublicIpAddressId $HR_DB_TEST_publicIP.Id
####Create Azure virtual machine configuration
#Define Azure VM name and size
$SQLVM = New-AzureRmVMConfig -VMName "HR_DB_TEST" -VMSize "Standard_DS13"
#Add the Azure virtual network interface
$SQLVM = Add-AzureRmVMNetworkInterface -VM $SQLVM -Id $HR_DB_TEST_vNIC.Id
#Define the Azure blob storage Uri/location of the VHD file
$OSDiskUri = (Get-AzureRmStorageAccount).PrimaryEndpoints.Blob.ToString() + "vhds/SQLVM_OSDisk.vhd"
$SQLVM = Set-AzureRmVMOSDisk -VM $SQLVM -Name "SQLVM_OSDisk" -VhdUri $OSDiskUri -Caching ReadOnly -CreateOption FromImage
#Define the Azure VM image for the SQL Server 2016 Standard Edition with Service Pack 1
$SQLVM = Set-AzureRmVMSourceImage -VM $SQLVM -PublisherName "MicrosoftSQLServer" -Offer "SQL2016SP1-WS2016" -Skus "Standard" -Version "latest"
$localAdminCreds = Get-Credential -Message "Type the name and password of the local administrator account."
$SQLVM = Set-AzureRmVMOperatingSystem -VM $SQLVM -Windows -ComputerName "HRDBTEST" -Credential $localAdminCreds -ProvisionVMAgent -EnableAutoUpdate
## Create the Azure VM
New-AzureRmVM -ResourceGroupName "HR_DB_TEST" -Location "East US" -VM $SQLVM
Once the Azure VM has been created, you can test connectivity via Remote Desktop. Use the Get-AzureRmRemoteDesktopFile cmdlet to download and launch the Remote Desktop file for the Azure VM. You need to pass the Azure VM name that you used with the New-AzureRmVMConfig cmdlet, not the server hostname.
Get-AzureRmRemoteDesktopFile -ResourceGroupName "HR_DB_TEST" -Name "HR_DB_TEST" -Launch
Next Steps
- Read an overview of Azure Resource Manager
- Review the list of available Azure Resource Manager PowerShell cmdlets
- Read the Introduction to Microsoft Azure Storage documentation
- Read about virtual networks in Microsoft Azure
About the author
Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-05-04
