With Windows Server 2016 just been released, now is the perfect time to build an Azure VM with SQL Server 2016 on Windows Server 2016. In a matter of minutes you can be playing and learning both platforms. Below I will document the steps I took to build the VM along with the additional software I installed. This is a fully-loaded VM that I use for demo’s and to build small projects:
(Software updates as of 11/4/2016)
- Go to the Azure Portal, choose “New”, type in “SQL Server 2016”, and choose “SQL Server 2016 RTM Enterprise on Windows Server 2016”. This will install SQL Server 2016 CU2 (13.0.2164.0)
- Follow the prompts to enter the info needed to build the VM. I kept the default Azure “Resource Manager” (ARM) deployment model. I chose the “East US” region, picked “Standard DS3” for the virtual machine size, created a resource group called “SQLServer”, used an existing storage account I called “serrastoragessd” which is premium-LRS and located in the East US region, enabled R Services, and created one data disk (under “Storage configuration” in SQL Server settings – create more disks for faster performance – please read Storage configuration for SQL Server VMs)
- After about 5 minutes your new VM will be ready. I then connect to the VM and check for windows updates and install them
- On the Azure Portal, click on the VM and under “Support + Troubleshooting” you will see “Boot diagnostics”. This will show you the boot screen of the VM so you can see if it is still performing windows updates
- I then login to SSMS and for the server properties change the server authentication to “SQL Server and Windows Authentication mode”. I then create a SQL login with sysadmin server role
- Install the latest CU if needed: see SQL Server 2016 build numbers
- Get the latest SSMS version if needed: see Download SQL Server Management Studio (SSMS). Latest is 16.5 (13.0.16000.28)
- Install Visual Studio Enterprise 2015 with Update 3 (14.0.25424.00 Update 3)
- Install SQL Server Data Tools (SSDT) 2015 GA for VS 2015 (14.0.61021.0)
- Install Office Professional Plus 2016 (make sure to choose 64-bit version)
- Install Visio Professional 2016 (make sure to choose 64-bit version)
- Install Chrome (Version 53.0.2785.143)
- Install Adobe Reader (Version 2015.020.20039)
- Install Java 8 (Update 111)
- Install Azure Data Lake Tools for Visual Studio (Version 2.2.21)
- Install Power BI Desktop (October update)
- Install Microsoft Data Migration Assistant (Version 2.0)
- Install Microsoft Database Experimentation Assistant Technical Preview (Version 1.0)
- Install Azure Storage Explorer (Version 0.8.5). I use a Shared Access Signature to connect to my Azure storage
- Install Roboform (Version 7.9.22)
- Install DocumentDB Studio (Version 0.71)
- Install DocumentDB Data Migration Tool (Version 1.7)
- Install Data Warehouse Migration Utility Preview (Version 1)
- Install Azure SDK for .NET VS 2015 (Version 2.9.5)
- Install Microsoft Data Management Gateway (Version 2.4.6137.1)
- Install Red Gate Azure Explorer (Version 1.1.0.43)
- Install Chrome Postman (Version 4.8.1)
- Install Fiddler (Version 4.6.3.44034)
- Install Narratives for Power BI
- Install ZoomIt (Version 4.5)
- Install Microsoft R Open (Version 3.3.1). Installs RGui
Install RStudio (Version 1.0.44)
- Install R Tools for Visual Studio 2015 (RTVS) (Version 0.5)
- Download and restore the Wide-World-Importers sample databases (Version 1.0)
- Download and restore the AdventureWorks Sample Databases and Scripts for SQL Server 2016 CTP3
- Download and restore the Northwind database
- Download and restore samples databases from SQLskills
- Use Site Recovery to backup your VM daily
- When not is use, manually stop your VM, or use an Azure Marketplace solution or a graphical runbook (both use Azure Automation) to save money. Note you will still be charged for storage if you created a data disk (see Azure Storage Pricing)