Wednesday, 30 March 2016

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.

Tuesday, 29 March 2016

SQL Server News–29th March 2016

This past weekend is one of my favourite holiday weekends - the 4 day Easter weekend :-)  Although I have had to work through most of it…With the holiday and the fact I've been busy with a new system go-live this post is slightly  late as a result.

On Thursday last week the Agenda for SQLBITS 2016 was annoucned. Our very own David Postlewaite has been selected to speak on the Saturday at SQLBITs this year. Congratulations David. That Saturday is free to attend. f you would like to see David deliver his session – sign up! Its free.
Anyway news posts and other bits that have caught my attention this week include:

SQL Server

From January Microsoft want you apply  cumulative updates (CUs) like you would a a service pack. CUs now get tested like service packs apparently so we should take them more regularly as and when they come out. Interesting.

Continuous Integration - this is a subject and area that I have been looking at  recently. This a good article by David Atkinson first published in May 2013 but updated on 9th April 2015 that discusses continuous integration and also looks at doing continous integration  with Redgate tools. I have been a Redgate fan for many years and used a variety of their tools in that time. If you are looking for continuous integration this is well worth a read

Then there's this too on database deployments  also by redgate disucssing  the different approaches you can take

Erik Darling of looks at Extended Events in SQL Server 2016 and some the really useful new features that have been in included

Big Data and Analytics

A very interesting read on how Uber use big-data to optimize its customers experience.


SQL Server in Windows Container in Azure -

Comparing cloud providers - How do Azure and AWS (Amazon) stack up?

Other Stuff

An interesting read on marketing and SEO using social media experiments -

Thursday, 24 March 2016

SQLBITS 2016 Agenda has been Published

The agenda for this year’s SQLBITs has been published you can find it here

Our very own David Postlethwaite has been selected to give one of his talks.

Congratulations to David on getting selected to speak at the event.

The event takes place in Liverpool between 4th – 7th May

You will find David in the 2:30pm – 3:30pm slot on Saturday 7th May and he’s in TR8. 

He  will be delivering his talk titled “A Beginner’s Guide to Cloud Computing”

Monday, 21 March 2016

Weekly Round-up 21st March 2016

Here are some of the more interesting links that I have found this week

SQL Server

SQL Server 2016 RC1 has been released. I've not had a chance to look at RC0 in too much detail yet. Although I did notice that Microsoft have split the SQL Server client tools installation from there server features. I'll hopefully get a chance to download install and setup RC1 this week. With the release candidates coming thick and fast RTM can't be too far away.

Queries to monitor replication backlog

Support for SQL Server 2005 runs out on April 12th - Less than a month from now. If you need help in upgrading to the latest version of SQL Server or perhaps the cloud is now a viable and cost effective solution for you check out our consulting page for more information on how we can help

SQL Server 2014 Failover Cluster installation guide -

Business Intelligence and Analytics

With the up and coming release of SQL Server 2016 and the integration with R and the Polybase feature for integrating SQL Server with other technologies, I found this article by fellow Learning Tree instructor Andrew Tait call an Introduction to Microsoft R Server particularly interesting.

This is a useful article looking at editing DAX queries in DAX studio from Dan Buskirk

An  article depicting some useful and free data modelling tools can be found over on Simple Talk. This article has five online data modelling tools

Got a problem SQL Server, need help fixing it, checkout our consulting page and feel free to contact us I'm sure we can help

Wednesday, 16 March 2016

SQL Server 2016 RC0–Management Studio install option has disappeared

If you are trying to install SQL Server 2016 RC0 – which I’m sure you are all doing looking at the new features that are coming our way soon and wondering how all this cloud stuff passed you by? You might be also be sat there wondering where has the option to install the client tools gone.


Where has the Management Studio install option gone?

Well the answer is simple. It now has a separate option in the SQL Server Installation centre


As you can see from the screen shot above, there are two option for installing the client tools. Install SQL Server Management Tools and Install SQL Server Data Tools.

It’s quite nice to see the client being separated from the server.

Tuesday, 15 March 2016

Creating a Domain Controller

The following is a short guide for promoting a Windows Server 2012 Machine to be a domain controller. This assumes that there is no existing forest or domain. My reason for doing this is to create a test enviornment using virtual machines on Oracle Virtual Box. I'm planning to setup an AlwaysOn Availability group.

One of the prerequisite to creating an Always on Availability group is a Windows Server Failover Cluster (WSFC). To set up a WSFC I need a domain. Hence why I’m creating a domain controller.  In future posts we will look creating an availability group

I have used Oracle Virtual box to create a server in my domain. I have used the same process previously using VMWare Workstation.

I'm going to assume you have run through create Virtual Machine  wizard and generated the VM configuration. With the Windows Server ISO  mounted and you have run through the install.

First you will configure the Windows Server. Set and Administrator Account and click Finish.


Admin Account


If your using a Virtual box you can press the host key + del (my host key is the right-hand Ctrl button) to send a Ctrl+Alt+Del to the VM guest

Log in to the VM using the password entered during setup.

Dcpromo has been deprecated, so if you have done this before using that command, you have a couple of options.

In this post we will be using Server Manager (UI).

Change the Computer Name

Click on the Server Manager Button in the start bar

Server Manager


Then click on the Local Server Option in the left-hand menu

Click on the Computer Name

Local Server

Click on the Change button on the Computer Name tab and enter a meaningful name for the Domain Controller. I have called my GREDC

Change computer name

Changing the name requires a server restart, click Restart Later if prompted. We will change the IP address first

Set IP address

Right-click on the Network icon in the tool bar and click Open Network and Sharing Centre.

Click on Change adapter settings.

Right-click the Network adapter and chose Properties

Click on the Internet Protocol Version 4 (TCP/IPv4) option and then click the Properties button

I set the IP Address to be accepted the default settings for subnet mask and set the preferred DNS Server to be then click OK

Restart the VM

Reboot the VM

Install the AD DS Role

From the Server Manager Dashboard, click on Add roles and features.

Click next on the first screen of the wizard

Ensure that the Role-based or feature-based installation option is selected (it should be the default) and click Next

Ensure that the Select a server from the server pool option is selected and the server we have been working on is highlighted. Click Next

Add role and features

Click on the Active Directory Domain Services role. You will prompted with a message that says other roles and features need adding. Click Add Features

Add additional roles

You can accept the default on the Features tab and click Next.

On the AD DS page click Next

On the Confirmation screen click Install Wait patiently while the wizard runs.

You'll get a screen that look like the following when the install succeeds

Add roles and features complete

You will see a yellow explanation warning on the Server manager toolbar. Click the warning and you will see there is a post-deployment configuration needed. Click the link to promote this server to a domain controller


This will start the Deployment Configuration Wizard


Click on Add a new forest and give your root domain a valid name


You can then set the functional level of the domain. I accepted the defaults here which is Windows Server 2012 R2. If you have a reason to choose a different functional level you can do so using the drop-down list. Click Next


On the DNS Option page, as this is a root level domain you will  get warning, you can just click Next here


The wizard will show you the NetBios name which it would have established from the domain name we entered earlier in the wizard.


I am happy to accept the default location for the database, log and SYSVOL locations. You can change these if you wish. Click Next


You will get an opportunity to review the options here and view the PowerShell script that you can reuse to recreate the DC if needed. Click Next


The pre-requisites review can take a few seconds to run. You will get a few warnings that for the purposes of demo domain I'm happy with.


Click Install and the virtual machine will become a domain controller.

The purpose of this post is to create a domain controller, which will enable me to create a Windows Server Failover Cluster (WSFC) later on. Using the Windows Server Failover cluster I will then implement an AlwaysOn Availability Group.

View our SQL Server and Cloud consulting pages for information on our SQL Server Consulting page.

Tuesday, 8 March 2016

SQL Server on Linux

When I started working with SQL Server many years ago, SQL Server on Linux was a regular April fool’s day joke that bloggers used to write about.

With the ever changing SQL Server database engine or just data platform these days. The integration of R and Polybase planned for SQL Server 2016, allowing SQL Server users to work with and use other analytic platforms and scalable data process technology. The integration with technologies such as Hadoop  capable of handling large amounts of structured and unstructured data. It’s no surprise that what used to be an old April fool’s joke is now going be a reality.

Microsoft are targeting mid 2017 for this to be available.  You can check out Scott Guthrie’s blog post here for more information.

Featured post

Creating a Linked Server to an Azure SQL Database

Why would you create a Linked Server to an Azure SQL Database? If you work in a hybrid environment with some databases stored on your on ...