Blog Archives

Azure SQL Virtual Machine for Development and Testing

Though Microsoft Azure provides virtual machine images with SQL server installed, during development and testing, I may not need those optimizations. All I might need is a virtual machine with database engine installed. Also, from a cost standpoint, using an evaluation version of SQL server on a virtual machine might be preferred over a SQL VM image, for development and testing purposes. Providing how I prepare my SQL experiment virtual machine (most of the time, I use the machine for a week or two and once the experiment is complete, I delete the virtual machine).

Azure Virtual Machine Images with SQL

Build a Virtual Machine with SQL Database Engine

Step 1: Create a new ‘Windows Server 2012 R2 Datacenter’ virtual machine with required configuration. Once virtual machine is up and running continue with next steps.

Step 2: SQL Server requires .NET 3.5 to be installed on server. This is not installed by default on Azure virtual machines.

Install-WindowsFeature -Name NET-Framework-Core
Install-WindowsFeature -Name PowerShell-V2

Step 3: Download SQL server installer from an Azure storage account. You should run the following commands from Microsoft Azure PowerShell window.

CD C:\

$context = New-AzureStorageContext `
                -StorageAccountName '<todo>' `
                -StorageAccountKey '<todo>'

Get-AzureStorageBlobContent -Container '<todo>' `
                            -Context $context `
                            -Blob 'SQLServer2014-x64-ENU.iso'

Step 4: Mount downloaded ISO image to a drive

$sqlIso = "C:\SQLServer2014-x64-ENU.iso"

$sqlDrive = (Mount-DiskImage -ImagePath $sqlIso `
                            -PassThru `
                            -WarningAction Ignore | 
                            Get-Volume).DriveLetter + ":"

Step 5: Silently install SQL Server from command line. For more details around command line based install check product documentation.

$domainName = Get-Content env:USERDOMAIN
$userName   = Get-Content env:USERNAME
$adminUser  = $domainName + "\" + $userName
$saPassword = "Secret@2015"


Invoke-Expression $installCommand 

Step 6: Once installation is complete, open SQL Management Studio and test with both Windows as well as SQL authentication.

Upload SQL installer to Storage

You can use the following script to upload SQL installer to blob storage for future use.

$context = New-AzureStorageContext `
                -StorageAccountName '<todo>' `
                -StorageAccountKey '<todo>'
Set-AzureStorageBlobContent `
			-Blob "SQLServer2014-x64-ENU.iso" `
			-Container '<todo>' `
			-File "SQLServer2014-x64-ENU.iso" `
			-Context $context `

Cleaning up Azure Storage Accounts

I wanted to put some structure around the experiments that I was doing on Microsoft Azure. I noticed that I had lots and lots of storage accounts created in the past. I used to create new virtual machines from UI and shut it down whenever I am done with the required purposes. Whenever it shows me that I ran out of quota on cores or cloud services, I used to delete the virtual machine and cloud services. However, since most of the time, I used the option to create a new storage account as part of virtual machine creation, there were lot of storage accounts left out.

How do I go about finding which ones are still used vs. which ones are not in use. I can go through the portal click through each of the storage accounts, go to containers tab and look if there are files inside containers. But I thought it will be fun to write a PowerShell script that can traverse all storage accounts, containers and list the files and their sizes. If I see a storage account with no files, or storage account with only a ‘vhd’ container with couple of .status files only, then they are good to be delete.

Script Downloads

Getting started with Microsoft Azure PowerShell

I will be writing a series of blogs around Microsoft Azure & Hybrid Cloud. As a quick start, wanted to write up on how to setup our machines with PowerShell to work with Microsoft Azure.

Setup machine with Microsoft Azure PowerShell

  1. If you haven’t installed Web Platform Installer, you can download and install it from
  2. Download and install ‘Microsoft Azure PowerShell’ from Web Platform Installer. If you do not find that in ‘Spotlight’ you can perform a search for ‘Microsoft Azure PowerShell’

Set your default subscription and storage account

Download Azure Publish Settings file , configure default subscription and configure default storage account. There is a very good blog article from Michael Walsham explaining this step-by-step. I strongly recommend you read through that post. There is one minor change in Azure PowerShell cmdlet usage (based on a cmdlet update from Microsoft), from the post you see in the blog. I have written a PowerShell script with minor updates, which you can download from here.

Script Downloads

# Ref:
# Step 0: Define all variable values
$publishSettingsFile = "C:\Users\Manesh\Documents\AzureSubscriptions.publishsettings";
$location = "West US";
$subscriptionName = "Visual Studio Premium with MSDN";
# Sometimes, you might have multiple MSDN subscriptions
# If someone else add you as a co-admin on their MSDN subscription etc.
# If so, choose the subscription Id option
$subscriptionId = $null;
# Sometimes, this account is created by someone else.
# So if the script fails, you might want to change this.
$storageAccountName = "manesh";
# Step 1: Import Azure Publish Settings File
Import-AzurePublishSettingsFile $publishSettingsFile
# Step 2: Set current subscription
if ($subscriptionId -eq $null) {
Select-AzureSubscription -SubscriptionName $subscriptionName -Current
} else {
Select-AzureSubscription -SubscriptionId $subscriptionId -Current
# Display only current subscription
Get-AzureSubscription -Current
# Step 3: Set current storage account
$isStorageGood = $false;
try {
Get-AzureStorageAccount –StorageAccountName $storageAccountName -ErrorAction Stop | Out-Null
if ((Get-AzureStorageAccount –StorageAccountName $storageAccountName).Location -eq $location) {
$isStorageGood = $true;
Write-Host "'$storageAccountName' storage account already exists, skipping creation"
} else {
Write-Host "'$storageAccountName' storage account already exists, but in a different location. Try another storage."
catch {
if (!(Test-AzureName -Storage $storageAccountName)) {
Write-Host "Creating Storage Account $storageAccountName"
New-AzureStorageAccount -StorageAccountName $storageAccountName -Location $location
$isStorageGood = $true;
else {
Write-Host "'$storageAccountName' storage account already exists and is owned by some other subscription. Try a different name."
if ($isStorageGood) {
if ($subscriptionId -eq $null) {
Set-AzureSubscription -SubscriptionName $subscriptionName -CurrentStorageAccountName $storageAccountName
} else {
Set-AzureSubscription -SubscriptionId $subscriptionId -CurrentStorageAccountName $storageAccountName
# You should see the provided subscription and storage account name while executing following command
Get-AzureSubscription -Current
Write-Host "All is well"
} else {
Write-Host "Try a different storage account name and re-run the script"


My views on technology

Virtualization and some coffee

My views on technology

Thomas Maurer

Cloud and Datacenter Blog focusing on Microsoft Azure

Cloud Administrator in Azure World

Begin Your Azure Management Journey with the Cloud Administrator