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