SQL Azure – Keeping up with a Moving Target

Moving your data to the (public) cloud necessarily involves relinquishing some control over the setup and maintenance of the environment in which your data is hosted. Cloud-based hosting services such as Microsoft Azure are effectively just scalable shared hosting providers. Since parts of the server configuration are shared with other customers and (to make the service scalable) there is to be a standard template on which all instances are based, there are many system settings that your cloud provider won’t allow you to change on an individual basis.

For me, this is generally great. I’m not a DBA or SysAdmin and I have no interest in maintaining an OS, tweaking server configuration settings, installing updates, or patching hotfixes. The thought of delegating the tasks to ensure my server remains finely-oiled and up-to-date to Microsoft is very appealing.

However, this also has its own down-sides. One advantage of maintaining my own server is that, even though it might not be up-to-date or have the latest service packs applied, I know nobody else has tweaked it either. That means that, unless I’ve accidentally cocked something else up or sneezed on the delete key or something, a database-driven application that connects to my own hosted database should stay working day after day. When an upgrade is available I can choose when to apply it, and test to ensure that my applications work correctly following the upgrade according to my own plan.

Not so with SQL Azure.

Two examples of breaking changes I’ve recently experienced with SQL Azure, both seemingly as a result of changes rolled out since the July Service Release:

Firstly, if you use SQL Server Management Studio to connect and manage your SQL Azure databases, you need to upgrade SSMS to at least version 10.50.1777.0 in order to connect to an upgraded SQL Azure datacentre. This same change also broke any applications that rely on SQL Server Management Objects (including, for example, the SQL Azure Migration Wizard, resulting in the error described here). The solution to both these issues is thankfully relatively simple once diagnosed – run Windows Update and install the optional SQL Server 2008 SP1 service pack.

A more subtle change is that the behaviour of the actual SQL Azure database engine has changed, making it more comparable to Denali on-site SQL Server rather than SQL Server 2008 R2. Whereas, normally, upgrading SQL Server wouldn’t be a breaking change for most code (unless, of course, you were relying on a deprecated feature that was removed), the increase in spatial precision from 27bits to 48bits in SQL Denali means that you actually get different results from the same spatial query. Consider the following simple query:

DECLARE @line1 geometry = 'LINESTRING(0 11, 430 310)';
DECLARE @line2 geometry = 'LINESTRING(0 500, 650 0)';

SELECT @line1.STIntersection(@line2).ToString();

Previously, if you’d have run this query in SQL Azure you’d have got the same result as in SQL Server 2008/R2, which is POINT (333.88420666910952 243.16599486991572).

But then, overnight, SQL Azure is upgraded and running the same query now gives you this instead: POINT (333.88420666911088 243.16599486991646), which is consistent with the result from SQL Denali CTP3.

Not much of a difference, you might think… but think about what this means for any spatial queries that rely on exact comparison between points. How about this example using the same two geometry instances:

SELECT @line1.STIntersection(@line2).STIntersects(@line1);

SQL Azure query run in July 2011: 0. Same SQL Azure query run in August 2011: 1. Considering STIntersects() returns a Boolean, you can’t really get much more different than 1 and 0….

So, a precautionary tale: although SQL Azure hosting might have handed over the responsibility for actually performing any DB upgrades to Microsoft, the task of testing and ensuring that your code is up-to-date and doesn’t break from version to version is perhaps greater than ever, since there is no way to roll back or delay the upgrade to your little slice of the cloud.

Spatial Applications in Windows Azure Redux (including Denali)

Several other people have already written on the subject of writing an Azure application that makes use of the SQL Server spatial datatypes. See, for example, Johannes Kebeck’s or Rex Hansen’s articles. However, having tried to answer a question on this subject today, I noticed that both of these articles are a little out of date, or are not quite complete. So I thought I’d summarise the current steps involved in creating and deploying an application that makes use of the geometry and geography datatypes in an Azure application, including the steps involved if you want to use the new spatial features in SQL Server Denali. Note that I’m talking about Azure here, not SQL Azure (although that too can make use of spatial datatypes) – so, this is about using the same spatial functionality as in SQL Server but in a cloud-based application layer rather than in a cloud-based database layer.

What library(s) to use?

The spatial functionality in SQL Server comes provided courtesy of two libraries:

• Microsoft.SqlServer.Types.dll – this is a (managed) .NET library that is installed in the /Program Files(x86)/Microsoft SQL Server/100/SDK/Assemblies subdirectory of a SQL Server installation and is registered in the GAC of any computer on which SQL Server has been installed.
• SQLServerSpatial.dll – this is an (unmanaged) C++ library that can be found in the /Windows/System32 directory. In SQL Server Denali, the equivalent library is called SQLServerSpatial110.dll.

You need both the managed and unmanaged libraries to use spatial features, but getting them to work on Azure can be a bit tricky. The first thing is to make sure you use the 64-bit version of the libraries (since Azure runs on a 64-bit OS). Secondly, you need to make sure that you use the correct edition of the libraries. Although both SQL Server 2008 and SQL Server Denali x64 libraries work correctly, it appears that SQL Server 2008 R2 libraries have “a few compatibility problems” with Azure (as reported my MS staff in http://social.msdn.microsoft.com/Forums/en-GB/windowsazuretroubleshooting/thread/d5f3f43e-a5bf-4c44-9e99-4593e6f812fd). However, the R2 libraries offer no additional functionality over the SQL Server 2008 libraries anyway, so if you don’t want to use Denali you can just use the original SQL Server 2008 libraries instead.

Getting the Libraries

If you’ve already got a 64-bit version of either SQL Server 2008 or Denali on your machine, you can use the libraries installed with it in the locations described above. If not, the easiest way to get hold of compatible libraries is to download an x64 version of the SQL Server Feature Pack. Note that you can get hold of and use these libraries from the feature pack without ever needing to install SQL Server.

• You can get the Oct 2008 x64 Feature Pack for SQL Server 2008 from here.
• Or, if you want to use latest features such as support for curved geometry types, you can download the latest (CTP3) x64 Feature Pack for SQL Server Denali from here.

Once downloaded, extract the dll files from the appropriate .msi file using the following command:

msiexec /a SQLSysClrTypes.msi /qb TARGETDIR="C:/Temp"

Within the C:\Temp folder you should now find subfolders matching the directory structure described above each containing one of the dll files. You can check that you’ve got the right version of the files by comparing them to the details below:

SQL Server 2008

Denali CTP3

Note that, in addition to the libraries above, to make the Denali libraries work on Azure you’ll also need the msvcr100.dll and msvcp100.dll files installed in the Win/System64 directory.

Trust and Unmanaged Code

Prior to Windows Azure v1.2, the default trust level for Windows Azure applications was medium trust. That meant that, in order to use native unmanaged code libraries such as SqlServerSpatial.dll in an Azure application, you had to explicitly grant access by including the following property in the ServiceDefinition.csdef file:

enableNativeCodeExecution="true"

This step is described in Johannes’ article (which, remember, is 2 years old). However, since v1.2 (released June 2010) every Azure web role now runs under full trust, and can run native code by default (source: MSDN). Therefore no additional configuration changes are currently required to access the spatial libraries from your Azure application.

Step-By-Step Guide

Having covered the points above that may have been missing from other guides, here’s a complete step-by-step guide to creating a spatial Azure application using the SQL Server Denali CTP3 spatial library. So, first of all, get hold of a copy of the 64-bit Microsoft® System CLR Types for SQL Server® code name ‘Denali’ CTP 3 from the CTP3 Feature Pack, and extract the dlls from it (or just install the .msi) as described above. All set? Then let’s go…

1. From Visual Studio 2010, create a new Windows Azure Project

2. Add a web role to the project

3. Select Project –> Add Reference and click the Browse tab to navigate to the directory in which you unpacked the spatial libraries. Add a reference to Microsoft.SqlServer.Types.dll, which can be found in \Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Types.dll

4. Change the properties of the Microsoft.SqlServer.Types library to “Copy Local = True”

5. Select Project –> Add Existing Item and navigate to the /Windows/System32 directory. Then, add the SqlServerSpatial110.dll file.

6. Select Project –> Add Existing Item again. This time, navigate to the /Win/System64 directory and highlight both the msvcp100.dll and msvcr100.dll files.

7. Set the properties of msvcp100.dll, msvcr.dll and SqlServerSpatial110.dll to “Copy to Output directory = Copy always”

8. The libraries are now ready to use, so write some code that makes use of them. Here’s a silly demo just to prove that I’m making use of a Denali spatial function, BufferWithCurves(). Firstly, edit the Default.aspx file as follows:

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeBehind="Default.aspx.cs" Inherits="WebRole1._Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <h2>SQL Server Denali Spatial and Azure Demo</h2>
    <p>
        The area of a circle of radius 100 is... (uses SQL Server Denali's BufferWithCurves() method).
    </p>
      <div id="area" style="font-size:x-large;" runat="server"></div>
</asp:Content>

Then edit the Default.aspx.cs file to be as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using Microsoft.SqlServer.Types;

namespace WebRole1
{
  public partial class _Default : System.Web.UI.Page
  {
    protected void Page_Load(object sender, EventArgs e)
    {
      Calculate_Area();
    }
    private void Calculate_Area()
    {
      SqlGeometry point = SqlGeometry.Point(0,0,0);
      SqlGeometry circle = point.BufferWithCurves(100);
      area.InnerText = circle.STArea().ToString();
    }
  }
}

9. Right-click on the Windows Azure project and select to Publish. In the following dialog, choose to Create Service Package only.

10. Logon to the Windows Azure Management Portal. Create a New Hosted Service and enter the details of the service. In the Package Location and Configuration File boxes, select the .cspkg and .cscfg files created by Visual Studio in the bin/Debug/Publish directory of your project.

11. Once the service has been created, navigate to the DNS of your new service and enjoy:

Cloud Hack

I spent a very enjoyable day yesterday in London at The Cloud Hack – a one day coding fest in which developers were invited to combine data from two or more APIs and create a cloud-based app, hosted on Windows Azure, with the chance to win cash prizes.

The event was sponsored by Microsoft, and the APIs made available to the developers for the day included Huddle, Paypal, National Rail Enquiries, and Bing Maps. Prizes were awarded for the best use of these APIs, the best overall app, and the most ambitious app.

I wasn’t competing; rather, I was there to provide technical support for anybody who wanted to use the Bing Maps APIs in their application (which, as it turned out, was almost everyone). I do find it slightly odd that, at a Microsoft-sponsored event designed to promote Windows Azure, Microsoft didn’t supply any of their own technical staff for this role… don’t get me wrong, I had a great time, and it’s always flattering to be recognised as a technical expert in the field, but I found myself standing alongside product managers and technical leads from the other partner APIs as the sole technical contact for the Bing Maps product and also basically providing a sales pitch on their behalf. Microsoft Bing Maps marketing department – I’ll be sending you my bill!

Anyway, there were some pretty good apps came out of it – although (and I’m not surprised) many teams struggled to get all the way from concept design, getting to grips with 5 different APIs, and then deploying to Azure all in a compressed day event. Some ideas included:

• Combining Bing Maps walking directions with National Rail departure information to determine if you would make it to the train station before your train left
• A charity event planning website that provided event organisers with event management, donation and payment options, and a map to the venue
• A holiday planning app that created a personalised itineraryand map of activities to do in a particular location.

One other application that made me smile was “iParcel – a social parcel delivery network”.  Travellers could log on to the site and provide details of a journey they were making by train, using the National Rail API. People who wanted to send a parcel somewhere could also register and say from and to where they’d like to deliver their parcel. Huddle was used to register the details of senders and deliverers and put them in contact with each other. The sender could monitor the progress of the package on a Bing Map, and then pay the deliverer using Paypal. So that’s all 4 APIs used, in a mostly cohesive app, leading this application to win the “most ambitious” prize, even if it didn’t have fully working functionality on the day. Also, I suspect that, if such an anonymous parcel-delivery service were ever publicly released, there might be some interesting concerns to address – “…a social networking site for drugs-runners”…?!

Anyway, thanks to all the organisers and attendees – and I look forward to attending the next hack event!