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: