This blog post has been a long time coming. I’ve sat on IaaS research since the morning of the 8th June. Truth is I love it. Forget the comparisons with EC2 and the maturity of Windows Azure’s offering. IaaS changes everything. PaaS is cool, we’ve built a stable HPC cluster management tool using web and worker roles and plugins – we’ve really explored everything PaaS has to offer in terms of services over the last four years. What IaaS does is change the nature of the cloud.

  1. With IaaS you can build your own networks in the cloud easily
  2. You can virtualise your desktop environment and personally benefit from the raw compute power of the cloud
  3. You can hybridise your networks by renting cloud ready resources to extend your network through secure virtual networking
  4. Most importantly – you can make use of combinations of PaaS and IaaS deployments

The last point is an important one because the coupling between the two can make use of well groomed applications which need access to services not provided out-of-of-box by PaaS. For example, there is a lot of buzz about using IaaS to host Sharepoint or SQL Server as part of an extended domain infrastructure.

This three part blog entry will look at the benefits of hosting SQL Server 2012 using the gallery template provided by Microsoft. I’ll draw on our open source library Azure Fluent Management and powershell to show how easy it is to deploy SQL Server and look at how you can tweak SQL should you need to set up mixed mode authentication which it doesn’t default to.

So the first question is – why use SQL Server when you have SQL Azure which already provides all of the resilience and scalability you would need to employ in a production application? Well … SQL Azure is a fantastic and a very economical use of SQL Server which provides a logical model that Microsoft manages but it’s not without it’s problems.

  • Firstly, it’s a shared resource so you can end up competing for resources. Microsoft’s predictive capability hasn’t proved to be that accurate with SQL Azure so it does sometimes have latency issues which are kinds of things that developers and DBAs go to great pains to avoid in a production application.
  • Secondly, being on a contentious shared infrastructure leads to transient faults which can be more prolific than you would like so you have to think about transient fault handling (ToPAZ is an Enterprise Application Block library which works pretty much out-of-the-box with your existing .NET System.Data codebase).
  • Thirdly, and most importantly, SQL Azure is a subset of SQL Server and doesn’t provide an all-encompassing set of services. For example, there is no XML support in SQL Azure, certain system stored procedures, synonyms, you can’t have linked servers so have to use multiple schemas to compensate. These are just a few things to worry about. In fact, whilst most databases can be updated, some can’t without a significant migration effort and a lot of thought and planning.

IaaS offers an alternative now. In one fell swoop we can create a SQL Server instance, load some data and connect to it as part of our effort. We’ll do exactly that here and the second part using our fluent management library. The third part will describe how we can use failover by configuring an Active-Passive cluster between two SQL Server nodes.

Let’s start with some powershell now.

If you haven’t done already download the WAPP powershell CmdLets. The best reference on this is through Michael Washam’s blog. If you were at our conference in London in June 22nd you would have seen Michael speak about this and will have some idea of how to use Powershell and IaaS. Follow the setup instructions and then import your subscription settings from a .publishsettings file that you’ve previously downloaded. The CmdLet to do the import is as below:

> Import-AzurePublishSettingsFile

This will import the certificate from the file and associate its context with powershell session and each of the subscriptions.

If you haven’t downloaded a .publishsettings file for any reason use:

> Get-AzurePublishSettingsFile

and enter your live id online. Save the file in a well-known location and behind the scenes the fabric has associated the management certificate in the file to the subscriptions that your live id is associated with (billing, service or co-admin).

A full view of the CmdLets are available here:

In my day-to-day I tend to use Cerebrata CmdLets over WAPP so when I list CmdLets it gives me an extended list. Microsoft WAPP CmdLets load themselves in as a module whereas Cerebrata are a snap-in. In order to get rid of all of the noise you’ll see with the CmdLets and get a list of only the WAPP CmdLets enter the following:

> Get-Command -Module Microsoft.WindowsAzure.Management

Since most of my work with WAPP is test only and I use Cerebrata for production I tend to remove most of the subscriptions I don’t need. You can do this via:

> Remove-AzureSubscription -SubscriptionName

If there is no default subscription then you may need to select a subscription before starting:

> Select-Subscription -SubscriptionName

Next we’ll have to set some variables which will enable us to deploy the image and set up a VM. From top to bottom let’s describe the parameters. $img contains the name of the gallery image which will be copied across to our storage account. Remember this is an OS image which is 30GB in size. When the VM is created we’ll end up with C: and D: drive as a result. C is durable and D volatile. It’s important to note that a 30GB page blob is copied to your storage account for your OS disk and locked – this means that an infinte blob lease is used to lock out the drive so only your VM Image can write to it with the requisite lease id. $machinename (non-mandatory), $hostedservice (cloud service), $size and $password (your default Windows password) should be self-explanatory. $medialink relates to the storage location of the blob.

 > $img = "MSFT__Sql-Server-11EVAL-11.0.2215.0-05152012-en-us-30GB.vhd"
 > $machinename = "ELASTASQLVHD"
 > $hostedservice = "elastavhd"
 > $size = "Small"
 > $password = "Password900"
 > $medialink =

In this example we won’t be looking at creating a data disk (durable drive E), however, we can have up to 15 data disks with our image each up to 1TB. With SQL Server it is better to create a datadisk to persist this and allow the database to grow beyond the size of the OS page blob. In part two of this series we’ll be looking at using Fluent Management to create the SQL Server which will be creating a data disk to store the .mdf/ldf SQL files.

More information can be found here about using data disks:

What the article doesn’t tell you is that if you inadvertantly delete your virtual machine (guilty) without detaching the drive drive first you won’t be able to delete it because of the infinite lease. You can also delete via powershell with the following CmdLets if you get into this situation which is easy to do!

> Get-AzureDisk | Select DiskName

This gives you the name of the attached data or OS disks. If you use Cerebrata’s cloud storage studio you should be able to see these blobs themselves but the names differ. The names are actually pulled back from a disks catalog associated with the subscription via the Service Management API.

The resulting output from the above CmdLet can be fed into the following CmdLet to delete an OS or data disk.

> Remove-AzureDisk -DiskName bingbong-bingbong-0-20120609065137 -DeleteVHD

The only thing necessary now is to be able to actually run the command to create the virtual machine. Michael Washam has added a -verbose switch to the CmdLet which helps us understand how the VM is formed. The XML is fairly self-explanatory here. First setup the VM config, then setup the Windows OS config and finally pipe everything into the New-AzureVM CmdLet which will create a new cloud service or use an existing one and create an isolated role for the VM instance (each virtual machine lives in a seperate role).

> New-AzureVMConfig -name $machinename -InstanceSize $size -ImageName $img -MediaLocation $medialink -Verbose | Add-AzureProvisioningConfig -Windows -Password $password -Verbose | New-AzureVM -ServiceName $hostedservice -Location "North Europe" -Verbose

By default Remote Desktop is enabled with a random public port assigned and a load balanced redirect to a private port of 3389.

Remote Desktop Endpoint

Remote Desktop Endpoint

In order to finalise the installation we need to setup another external endpoint which forwards request to port 1433, the port used by SQL Server. This is only half of the story because I also should add a firewall rule to enable access to this port via TCP as per the image.

SQL Server Endpoint

SQL Server Endpoint

Opening a firewall port for SQL Server

Opening a firewall port for SQL Server

You would have probably realised by now that the default access to SQL Server is via windows credentials. Whilst DBAs will be over the moon with this for my simple demo I’d like you to connect to this SQL instance via SSMS (SQL Server Management Studio). As we’ll need to update a registry key to enable mixed mode authentication via regedit:


And update the LoginMode (DWORD) to 2.

Once that’s done I should be able to startup SSMS on the virtual machine and create a new login called “richard” with password “Password_900” and remove all of the password rules and expiries so that I can test this. In addition I will need to ensure that my user is placed in the appropriate role (sysadmin is easy to prove the concept) so that I can then go ahead and connect.

Logging into SQL Server

Logging into SQL Server

I then create a new database called “icanconnect” and can set this as the default for my new user.

When I try to connect through my SSMS on my laptop I should be able to see the following as per the image below.

Seeing your database in SSMS

Seeing your database in SSMS

There’s a lot of seperate activites here which we’ll add to a powershell script in second part of this series. I realise that this is getting quite long and I was keen to highlight the steps involved in this without information overload. In part two we’ll look at how Azure Fluent Management can be used to do the same thing and how we can also script the various activities which we’ve described here to shrink everything into a single step.

Happy trails etc.