Automate Azure VM for SQL Server creation with PowerShell (Part 3)
am 13. April 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. This is the final part of a series of total 3 blog posts.
In this third part the blog series (see Part 1 and Part 2), I will show you how to set up the generated VM with a SQL Database and various software in an automated way.
Starting and setting up the VM
Now the VM is deployed and already started. Now is also the moment you’ve been waiting for – you want to access to your VM. In order to do so, go to your Azure portal and find your resource and your VM in there. Once you’re clicked on your VM, you’ll see a screen like below. Here, click on “Connect” as below and choose the option Bastion.
After this, you’ll see a site with a button “Use Bastion” on which you click. In the next window, you need to enter the credentials you’ve set for this VM during the running of the script. Your browser starts to connect to your VM. During that time, you will see a small window asking if you want to allow copy & paste. If you allow it, you can copy & paste text onto the VM. It is not possible to copy files into the VM on this way. However, like I’ve already mentioned in the prerequisites, you can use the Azure File Storage Explorer with which you can use drag and drop to upload files to this File Storage. You can map this storage as a network drive so you can access even larger files very conveniently that way. The next few steps will allow you do so.
After your VM started and you see Windows, you might want to have a full screen view on your VM. To do so, click on this >> on the left side
This will slide open a small window like this. In here, you can choose full screen:
And add a File Share
# Enabling execution of PowerShell scripts:
set-executionpolicy remotesigned -Force
# Mounting Azure File Store as cheap data storage
$connectTestResult = Test-NetConnection -ComputerName $YourDataStorageName.file.core.windows.net -Port 445
if ($connectTestResult.TcpTestSucceeded) {
# Save the password so the drive will persist on reboot
cmd.exe /C "cmdkey /add:`"$YourDataStorageName.file.core.windows.net`" /user:`"Azure\sqlsrvbaks`" /pass:`"$YourPassword`""
# Mount the drive
New-PSDrive -Name Z -PSProvider FileSystem -Root "\\YourDataStorageName.file.core.windows.net\wideworldimporters" -Persist
}
else {
Write-Error -Message "Unable to reach the Azure storage account via port 445. Check to make sure your organization or ISP is not blocking port 445, or use Azure P2S VPN, Azure S2S VPN, or Express Route to tunnel SMB traffic over a different port."
}With this, we are now able to install chocolatey:
# Install Chocolatey:
Set-ExecutionPolicy Bypass -Scope Process -Force; [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))Chocolatey makes it extremely easy to install a lot of software with just one PowerShell line as following:
# Install Chocolatey GUI and other software:
choco install chocolateygui microsoft-edge tabular-editor daxstudio azure-data-studio git dbatools vscode -yThis installs:
Of course, you could add / remove the software as you like in that line. The last -y means that you agree to the installs of all listed software. dbatools takes quite some time to be installed (ca. 10 minutes) so don’t be surprised if it seems to be stuck – it is not.
With this Tabular Editor, .Net Framework 4.8 gets installed. Therefore we need to reboot the VM:
# Rebooting Computer to complete installs
Restart-ComputerYou just can keep the browser tab for the VM open until the reboot is done and you can continue.
With dbatools being installed, we would like to restore our databases. But, remember – we need to have the backup files first which we need to bring to our VM first? Now it is the time to map Azure File Store to our VM. If you go to Azure Portal, go to
And add a File Share
And click on the created file share next. In the next window, click on Connect which will open a side window. In here, you can define a mapping letter (eg. Z) and it shows a some code with which you can map your file store.
Copy this code (attention: this has a password in it, keep this secret). Paste this into PowerShell ISE or put it into a file and run it on your VM. As a result, you should see something like this:
If you’ve uploaded a file via Microsoft Storage Explorer, you’ll see the file here. In order to restore this file or even more files, you can use the following code:
# Restore Parameters
$SQLBackupSource = "Z:\*.bak"
# Restore databases
Copy-Item $SQLBackupSource -Destination "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup" -Force -Verbose
Restore-DbaDatabase -SqlInstance localhost -Path "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\"In order to shut down your VM via Code or removing it or even deleting the entire resource group with all its objects in it, those few lines may help you out:
# For stopping this VM:
Stop-AzVM -Name $VMName -ResourceGroupName $ResourceGroupName
# # For removing this VM:
Remove-AzVM -ResourceGroupName $ResourceGroupName -Name $VMName
# For removing resource group (and all its objects, including this VM):
$ResourceGroupName = "bloglab"
Remove-AzResourceGroup -Name $ResourceGroupName -ForceThis was just a small dive into this topic. Of course it would be possible to automatize this even more in a wholesome DevOps way. It also might be interesting to know that Microsoft offers some tools that may support this idea with:
If you have a question, feel free to contact us!
