Skip to main content

SQL Server 2016 Azure Virtual Machines

With the release candidates coming thick and fast for SQL Server 2016 downloading, uninstalling the previous version and installing the latest can be a little bit tedious.
A less timing consuming option to trying the latest is to use Microsoft Azure. If you have an Azure subscription you can spin up a VM using one of Microsoft’s pre-built images in a very short amount of time.
There is a cost to using Microsoft Azure but a Windows vm on the basic tier is only a few pounds per month.
If you have an MSDN subscription you receive a monthly allowance that is bundled into your subscription. This is what I use for my Azure testing work.
If you company has embraced the cloud already then you may have access to that or you may have to spend a few € or £ or whatever your currency to use. Unless of course you can get a free trial which if you are new to the cloud and Azure Microsoft are normally happy to let your trail it for free for a while.
Once you have your Azure account sign in at and sign in

From the portal home page click on the + New link, then Virtual Machines and scroll down to SQL Server.

Click on the SQL Server option and you will be presented with a list of servers offering different versions of Windows and SQL Server.
To get to SQL Server 2016 you might need to click on the “Load More” option. At the time of writing SQL Server 2016 RC1 has been released (it was released for download yesterday 21/3/2016) although it's not yet available in the gallery of VMs.

I chose SQL Server 2016 RC0 Evaluation on Windows Server 2012 R2. Accept the default settings and click create

On the basic settings screen you need to enter the following details
•    A Name for the server
•    An administrator Username - used to login to the vm
•    Password
•    Your Subscription – This will be prefilled with your subscription.
•    Resource Group - This is used for managing resources. I used the default resource group for the purpose of this demo
•    Location - accepted my default which was Northern Europe
Click OK
The size screen allows you to specify the size of the server.
Basically allow you to choose the resources available to your VM. Just remember the more you choose here, the more the VM will cost!
I clicked View all and scrolled to the Basic section I chose A1 basic - the cheapest and least resources Azure VM size.
Click Select to continue

On the settings page you can select

o    Disk type
o    Storage account
o    Virtual account
o    Subnet
o    Public IP address
o    Network security
o    Diagnostics - enabled by default
o    Diagnostic storage account - where to store the diagnostic data
o    Availability

I accepted the defaults on this screen and clicked OK. For a demo these are fine.

The next section is the SQL Server Settings page
SQL Connectivity - you have three options
o    Local - inside the VM only
o    Private - with a virtual network
o    Public - connect from the internet
o    I left this as the default 1433 you can change this if you wish
SQL Authentication
o    So I can connect from SSMS and because I don't have AD setup I will enable SQL Authentication
o    Specify a username and password
Storage configuration
o    This was disabled for me - I think because of the spec of VM that I chose above
Automated Patches
o    This can be enabled or disabled. If it enabled to get to specify your patching window

Automated Backups for me are disabled, again down to the spec of the VM selected. However you can get your database backed up automatically
Azure Key Vault Integration I will leave disabled
Finally validation will run and you will get a summary screen click OK and the deployment will built. Allow a little bit of time for this to complete.


Popular posts from this blog

SQL Server 2012 and Virtual Service Accounts

This post is written by David Postlethwaite
If you are using SQL Server 2012 you will probably have noticed that the default account for the SQL services has changed from that used in previous versions. With SQL 2005 and 2008 the default account for SQL service and SQL Agent service was “NT Authority\System”. This is one the built in accounts on a Windows machine, managed by the machine and selectable from a dedicated dropdown list

The Network Service account was introduced in Windows 2003 as an alternative to using the LocalSystem account, which has full local system privileges on the local machine, a major security concern.
The Network Service has limited local privileges easing these security concerns but when many services on a machine use the Network Service account it becomes harder to track which service is actually accessing resources and performing actions, because all the services are using the one Network Service account.
Also, this account, by default, has sysadmin per…

Always Encrypted

By David Postlethwaite

Always Encrypted is new features in SQL Server 2016 and it is also available in Azure SQL Database. Here you can encrypt columns in a table with a master key and a certificate so that they will appear as encrypted strings to those who don’t have the required certificate installed on their pc.
Once the certificate is installed on the computer then the unencrypted data can then be seen as normal.

The data passes from database to your application as the encrypted value, only the application with the correct certificate can unencrypt the data so it is secure across the wire. This will go some way to resolving the concern of people worried about putting their sensitive data on a shared server in the cloud such as Microsoft Azure and accessing the data across the Internet.

At the time of writing Always Encrypted is only supported with ADO.NET 4.6, JDBC 6.0 and ODBC 13.1 but expect other driver to become available.

The calling application (including SSMS) must also hav…

New in SQL Server 2017: Graph Databases

David has recorded and published a video of his presentation on SQL Server Graph Database. In his video which you can watch below, David provides an excellent introduction into SQL Server 2017 Graph Databases. In his presentation he looks at Tennis results at tournaments for  his favourite player "The Fed"  Rodger Federer.

David  shows how to set up graph database and work with them in SQL Server 2017.

Graph Database is not new. Other vendors have had graph database capabilities for some time so Microsoft are quite late to the market. In David presentation it appears that Microsoft have done a reasonable job of implementing some of the graph database features but he does point some of the limitations of the Microsoft product too and suggests that it is not ready for production yet but Microsoft seem serious about this feature.

Please watch the video and feel free to leave a comment or feedback - David is delivering a version of this talk on Graph databases in SQL Saturday Ka…