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"

$installCommand = "$sqlDrive\Setup.exe /Q /IACCEPTSQLSERVERLICENSETERMS /ACTION=install /UpdateEnabled=0 /FEATURES=SQLENGINE,CONN,SSMS,ADV_SSMS /INSTANCENAME=MSSQLSERVER /SECURITYMODE=SQL /SAPWD=$saPassword /SQLSVCACCOUNT='NT Service\MSSQLSERVER' /SQLSYSADMINACCOUNTS=$adminUser"

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 `
			-Force 

About Manesh

Manesh is a software consultant and solutions architect specializing in cloud, data, Linux and devops in the azure realm with key focus on hybrid workloads. He has been working on Azure technologies since its inception and has helped many enterprises to onboard and adapt to Azure cloud, build solutions for datacenter scale / high consumer applications and services. Currently, he is Microsoft certified for Developing Microsoft Azure Solutions (70-532), Implementing Microsoft Azure Infrastructure Solutions(70-533) and Architecting Microsoft Azure Solutions (70-534).

Posted on 2015/02/07, in Microsoft Azure, SQL Server and tagged , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

hyper-v.nu

My views on technology

yungchou

witnessing a clear cloudy day

Virtualization and some coffee

My views on technology

Thomas Maurer

Cloud and Virtualization

Cloud Administrator

Begin Your Journey to the Cloud with the Cloud Administrator

%d bloggers like this: