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.

NoSQL, NoGIS… What’s with all the Negativity?

Databases are dull. So is geography. As a result, professionals in these fields have a habit of desperately trying to make themselves sound more interesting by inventing exciting memes – something that they can turn into a twitter #hashtag or submit a session at a conference and trick people into thinking that there’s been some radical, exciting developments that have suddenly made these topic areas “cool” and “in with the kids”….

The current fad is to take some of the most familiar acronyms associated with each topic area and add the word “No” in front of them – presumeably in the hope of distancing the subjects from their tired, historic associations. So in the database world, instead of SQL (Structured Query Language), we now have NoSQL (sometimes “Not Only Structured Query Language”). And, if you work with spatial data, we now have not only the GIS but also the NoGIS.

Of course, the problem with both NoGIS and NoSQL is that they are in some way misleading and deliberately obtuse as to what they stand for, instead favouring to come up with a natty name (Michal Migurski of Stamen design, who proposed the first “NoGIS” meetup even acknowledged that it started as a joke, as quoted here). So what do these terms really mean?

According to http://nosql-database.org/, NoSQL databases are non-relational, distributed, and horizontally-scalable. Rather than joining between relational tables, they typically use schemaless key-value stores, often for document databases. Examples of “NoSQL” databases are MongoDB, CouchDB, and Microsoft’s Azure table storage. Note that NoSQL has not really got anything to do with SQL at all (but nor is it the antithesis of it) – if anything, NoSQL should be called “Non-relational”, but I guess that would require more characters in Twitter. Also note that non-relational document store databases are hardly a new idea; I remember working with Lotus Notes databases 10 years ago that today would be classed as “NoSQL”.

NoGIS is a reflection of the fact that spatial data and geographic analysis is becoming increasingly more ubiquitous. Rather than being confined to dedicated GIS analysts with specialist (read “ESRI”) tools, it is now possible to have spatial applications running on Windows Phone 7 applications, via Bing Maps in a webbrowser, in a Silverlight application, or in any Reporting Services report, for example, and accessed and interpreted by users with no specific geographic interest. “Spatial is no longer special”. In common with NoSQL, the NoGIS movement is interested in (primarily) web-based approaches to analysing high-volume, dynamic data in efficient ways for non-expert users. But, once again, this is hardly a new idea – even ESRI themselves have a lightweight Silverlight viewer making geographic analysis available to the masses, and I personally know of many scientific organisations that have been sharing geographic research data using Google Earth for several years.

Today, I’m creating a Bing Maps application that will display and interrogate multiple layers of dynamic spatial data retrieved from Microsoft Azure storage. Inadvertently, I’m combining NoSQL and NoGIS. How cutting-edge am I?!

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:

Spatio-temporal Event Processing with StreamInsight, SQL Server Denali, and Bing Maps – Part 5

Yesterday, fellow Bing Maps MVP Nicolas Boonaert (blog) kindly tweeted a link to my series of posts about analysing spatio-temporal data using StreamInsight and Bing Maps. In addition to the generous words he said publicly, he also sent me a private message teasing me about the graphical presentation of the data. I believe the term he used was “moving pixelised potato on a map”…

Spurred on by Nicolas’ comments, I thought I’d take the opportunity to improve the visual impact of my StreamInsight output by making use of my own HeatMap library to plot the outbreak as a heatmap rather than as a convex hull. To do so required remarkably few changes to the code:

First, instead of creating a (Polygonal) convex hull around all the events in the Stream Insight window at a  given point in time, I added a UDA that would instead create a (MultiPoint) union of all those points.

public class MultiPointUnion : CepAggregate<string, string>
  {
    public override string GenerateOutput(IEnumerable<string> eventData)
    {
      var gb = new SqlGeographyBuilder();
      gb.SetSrid(4326);
      gb.BeginGeography(OpenGisGeographyType.MultiPoint);
      foreach (var d in eventData)
      {
        SqlGeography point = SqlGeography.Parse(d);
        gb.BeginGeography(OpenGisGeographyType.Point);
        gb.BeginFigure((double)point.Lat, (double)point.Long);
        gb.EndFigure();
        gb.EndGeography();
      }
      gb.EndGeography();

      return gb.ConstructedGeography();
    }
  }
}

Then, I amended the javascript that updated the map in the browser control. Instead of plotting a single polygon as a vector shape on the map, I imported the heatmap library and added a new HeatMapLayer as follows:

heatmapLayer = new HeatMapLayer(
  map,
  new Array(),
  { intensity: 0.4,
    radius: 25,
    colourgradient: {
      0.0: 'rgba(255, 255, 255, 0)',
      0.5: 'rgba(255, 255, 120, 100)',
      0.8: 'yellow',
      0.95: 'red',
      1.0: 'white'
    }
  }
);

(Colours were chosen somewhat arbitrarily – white hot spot right at the centre of each point, followed by red, and gradually diminishing yellow). Then, each time the WCF listener received a new event summary from StreamInsight, rather than plot it as a Microsoft.Maps.Polygon, it called a method that would split the WKT of the MultiPoint instance into an array of Microsoft.Maps.Locations, and supply these to the heatmap layer:

heatmapLayer.SetPoints(locationArray);

The result is shown in the video below, complete with my rather vague commentary (based on this evidence, I don’t think I’ll ever realise my childhood dreams of being a radio DJ… ). One thing to notice is that, while the convex hull approach I used before emphasises the overall geographic spread at any point in time, the heat map approach emphasises instead the intensity of events in certain areas.