Automate Azure VM for SQL Server creation with PowerShell (Part 1)
am 20. Januar 2021
Especially in this time of COVID-19 caused lockdowns, working from home is widespread. Therefore, working together on a developing VM in the cloud may be an attractive solution. In the past few months, I developed a solution to use VMs for such business cases.
Part 1
Especially in this time of COVID-19 caused lockdowns, working from home is widespread. Therefore, working together on a developing VM in the cloud may be an attractive solution. In the past few months, I developed a solution to use VMs for such business cases. We also had in our minds that this VM could be a showcase for customers that prefer having their solution on their premises without Azure. Therefore, a VM in Azure in a Infrastructure-as-a-Service (IaaS) solution can make a lot of sense. This can also be a cost-efficient solution for those who want to try out things for a couple of hours. Even if you need this VM for a longer time, the costs are fairly limited, depending on the hardware specifications you are choosing.
The following article is split in three parts. In the first part, a script sets up a VM from scratch. The second walks you through the code and will setup Azure Bastion. And the last part will walk you through the installation of some software including Azure Data Studio via Chocolatey and the restore of a sample database with dbatools.
The full setup takes a bit more than 1 hour and is set up almost completely automatically.
Pre-requisites
Editor and connection to Azure
I am using Visual Studio Code with some extensions for Azure. In order to connect to your Azure Account, you need to connect your editor of your choice with Connect-AzAccount.
Files in Azure File Store
You will need to upload a backup file to your file Azure File Store. Azure File Store is a quite cheap storage for storing files like database backups. To upload a backup file, I recommend using Azure Storage Explorer with which you can upload your files in a very convenient and secure way.
Azure Bastion
Azure Bastion will be set up this script automatically. However, I at this point, want to give you some resources first why and when you should use Azure Bastion. If you’re using a VM via Internet, it is always a bad idea to use a RDP connection without an encrypted tunnel.
- https://docs.microsoft.com/en-us/azure/bastion/bastion-overview
- https://jussiroine.com/2019/09/building-a-secure-remote-access-solution-using-azure-bastion-host/
dbatools
We are going to install dbatools on the VM after its deployment. At this point, you don’t need to know much about this awesome collection of PowerShell commandlets. It contains 500+ easy and efficient to use scripts that are free and open source.
Chocolatey
We will also install Chocolatey on the VM. Again, at this point, you don’t really need to know much about it as we will just install some software with Chocolatey. It enables you to automatize, download and install many software with few lines of code, making it very quickly for anybody who has to set up a VM (or computers) from scratch. There is also a paid service by Chocolatey, but we will be using the free service offer.
Lets start with the script
As an overview the following script does the following tasks:
- Creates the VM with SQL Server
- Creates Azure Bastion
Following you’ll see the entire script and after this script in the second part of this article, I’ll walk you through it.
# Variables
## Global Settings
$Location = "switzerlandnorth"
$ResourceGroupName = "bloglab"
## Settings for Storage
$StorageName = $ResourceGroupName + "storage"
$StorageSku = "Standard_LRS"
## Settings for Network
$InterfaceName = $ResourceGroupName + "ServerInterface"
$NsgName = $ResourceGroupName + "nsg"
$VNetName = $ResourceGroupName + "VNet"
$SubnetName = "Default"
$VNetAddressPrefix = "10.0.0.0/16"
$VNetSubnetAddressPrefix = "10.0.0.0/24"
$TCPIPAllocationMethod = "Dynamic"
$DomainName = $ResourceGroupName
## Settings for Bastion
$publicIpName = "pip" + $ResourceGroupName
$BastionName = "Bastion" + $ResourceGroupName
$BastionSubnetName = "AzureBastionSubnet"
## Settings for hardware
$VMName = $ResourceGroupName + "VM"
$ComputerName = $ResourceGroupName + "Server"
$VMSize = "Standard_D4s_v3"
$OSDiskName = $VMName + "OSDisk"
## Settings for SQL Server licence
$PublisherName = "MicrosoftSQLServer"
$OfferName = "sql2019-ws2019"
$Sku = "SQLDEV"
$Version = "latest"
# Set credentials for logging into VM
$Credential = Get-Credential -Message "Type the name and password of the local administrator account."
# Creation of Objects
## Resource Group
New-AzResourceGroup -Name $ResourceGroupName -Location $Location
## Storage
$StorageAccount = New-AzStorageAccount -ResourceGroupName $ResourceGroupName -Name $StorageName -SkuName $StorageSku -Kind "Storage" -Location $Location
## Network
$SubnetConfig = New-AzVirtualNetworkSubnetConfig -Name $SubnetName -AddressPrefix $VNetSubnetAddressPrefix
$VNet = New-AzVirtualNetwork -Name $VNetName -ResourceGroupName $ResourceGroupName -Location $Location -AddressPrefix $VNetAddressPrefix -Subnet $SubnetConfig
$PublicIp = New-AzPublicIpAddress -Name $InterfaceName -ResourceGroupName $ResourceGroupName -Location $Location -AllocationMethod $TCPIPAllocationMethod -DomainNameLabel $DomainName
$NsgRuleRDP = New-AzNetworkSecurityRuleConfig -Name "RDPRule" -Protocol Tcp -Direction Inbound -Priority 1000 -SourceAddressPrefix * -SourcePortRange * -DestinationAddressPrefix * -DestinationPortRange 3389 -Access Allow
$NsgRuleSQL = New-AzNetworkSecurityRuleConfig -Name "MSSQLRule" -Protocol Tcp -Direction Inbound -Priority 1001 -SourceAddressPrefix * -SourcePortRange * -DestinationAddressPrefix * -DestinationPortRange 1433 -Access Allow
$Nsg = New-AzNetworkSecurityGroup -ResourceGroupName $ResourceGroupName -Location $Location -Name $NsgName -SecurityRules $NsgRuleRDP, $NsgRuleSQL
$Interface = New-AzNetworkInterface -Name $InterfaceName -ResourceGroupName $ResourceGroupName -Location $Location -SubnetId $VNet.Subnets[0].Id -PublicIpAddressId $PublicIp.Id -NetworkSecurityGroupId $Nsg.Id
## Compute
$VirtualMachine = New-AzVMConfig -VMName $VMName -VMSize $VMSize
$VirtualMachine = Set-AzVMOperatingSystem -VM $VirtualMachine -Windows -ComputerName $ComputerName -Credential $Credential -ProvisionVMAgent -EnableAutoUpdate #-TimeZone = $TimeZone
$VirtualMachine = Add-AzVMNetworkInterface -VM $VirtualMachine -Id $Interface.Id
$OSDiskUri = $StorageAccount.PrimaryEndpoints.Blob.ToString() + "vhds/" + $OSDiskName + ".vhd"
$VirtualMachine = Set-AzVMOSDisk -VM $VirtualMachine -Name $OSDiskName -VhdUri $OSDiskUri -Caching ReadOnly -CreateOption FromImage
## Image
$VirtualMachine = Set-AzVMSourceImage -VM $VirtualMachine -PublisherName $PublisherName -Offer $OfferName -Skus $Sku -Version $Version
## Create the VM in Azure
New-AzVM -ResourceGroupName $ResourceGroupName -Location $Location -VM $VirtualMachine
## Add the SQL IaaS Extension with chosen license type
New-AzSqlVM -ResourceGroupName $ResourceGroupName -Name $VMName -Location $Location -LicenseType PAYG
## Create Azure Bastion Host
Add-AzVirtualNetworkSubnetConfig -VirtualNetwork $VNet -Name $BastionSubnetName -AddressPrefix 10.0.1.0/27
$VNet | Set-AzVirtualNetwork
$publicip = New-AzPublicIpAddress -ResourceGroupName $ResourceGroupName -name $publicIpName -location $Location -AllocationMethod Static -Sku Standard
New-AzBastion -ResourceGroupName $ResourceGroupName -Name $BastionName -PublicIpAddress $publicip -VirtualNetworkId $VNet.id