Set Up Multiple Azure VM Servers with PowerShell

We recently learned how to set up a Microsoft Azure virtual machine (VM) with SQL Server, but in a "real world" environment, there's a lot more configuration you need to do to set up SQL Servers. You also don't want to put your data and log files on the operating system disk, so you need to add disk drives to properly place your data, and you need to enable TCP ports so you can communicate with the Azure VM just like you'd do with a local server.

The Add-AzureDataDisk cmdlet allows you to add disks easily to your VM. If you use the -CreateNew argument you can add new disks to the VM, or you can use the -Import argument to import a previously created Virtual Hard Disk (VHD) file into the VM. In fact, this latter method is often the easiest way to migrate some of your on-premises machines into Azure. There's an argument called -LUN and each new data disk must have a unique number, starting with 0 and going up to 15. You can specify a location, but I think you probably want to keep them in the same location as your VM.

Add-AzureDataDisk -CreateNew -DiskSizeInGB 100 -DiskLabel 'data1' -LUN 0

You can use the Add-AzureEndpoint cmdlet to add a TCP endpoint for SQL Server access. This cmdlet has arguments to set both the local and the public port so you can set them to match your company's security guidelines. For my purposes, I set them both to 1433.

Add-AzureEndpoint -Protocol tcp -LocalPort 1433 -PublicPort 1433 -Name 'SQL'

Another thing you can do is add parameters to the Add-AzureProvisioningConfig to provision your VMs in your own domain, using the -JoinDomain, -Domain, -DomainPassword, -DomainUserName and -MachineObjectOU arguments. You can also specify your subnet using the Set-AzureSubnet cmdlet.

For this example I want to create five new servers, all using the same image I did in Create a New Azure VM with PowerShell. PowerShell makes that really easy, and is one of the reasons I love using PowerShell. It's the avoidance of repetition, and the assurance that I'm not making mistakes, because I can check the script carefully before running it.

First, you'll set up a collection of server names. You'll also set up an empty collection for the VM objects you'll create.

$nms = 'NEWSQL01','NEWSQL02','NEWSQL03','NEWSQL04','NEWSQL05'$vms = @()

You also want to set up a single Service Name to manage the VMs using the New-AzureService cmdlet.

$svcnm = 'AVMSQL'$svc = New-AzureService $svcnm -Location 'Central US' -Description 'Service for Azure VM Creation'

Now, you'll iterate through the $nms collection and create entries in the $vms collection with the VM configuration and properties for all five servers, then one call to the New-AzureVM cmdlet will create all five servers.

foreach ($nm in $nms) {$vm = New-AzureVMConfig -Name $nm -InstanceSize Basic_A3 -ImageName $imgnm |             Add-AzureProvisioningConfig -Windows -AdminUsername '[mywindowsadminuser]' -Password '[strongpassword]'  |             Add-AzureDataDisk -CreateNew -DiskSizeInGB 200 -DiskLabel 'data1' -LUN 0 |             Add-AzureDataDisk -CreateNew -DiskSizeInGB 50 -DiskLabel 'log1' -LUN 1 |             Add-AzureEndpoint -Protocol tcp -LocalPort 1433 -PublicPort 1433 -Name 'sql'$vms += $vm}

Finally, you'll call the New-AzureVM cmdlet and pass the collection of VMs in the -VMs argument.

New-AzureVM -Location 'Central US'  -ServiceName 'AVMSQL' -VMs $vms

Once they're created, you can use the Get-AzureVM to see what VMs you have configured.

Related: PowerShell the SQL Server Way