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

imageimage

Denali CTP3

imageimage

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

image

2. Add a web role to the project

image

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

image

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

image

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

image

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.

image

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

image

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.

image

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.

image

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

image

This entry was posted in Spatial, SQL Server and tagged , , , . Bookmark the permalink.

12 Responses to Spatial Applications in Windows Azure Redux (including Denali)

  1. rbrundritt says:

    Next time I’m going to follow this article line by line. spent a day debuging my application to find out that the issue was the wrong version of the dll’s.

  2. Hi Alastair,

    Just to say I am moving an application from VS2010/SQL 2008 to VS2012/SQL 2012 localdb only and had problems finding Microsoft.SqlServer.Types.dll.

    On my system its in C:\Program Files\Microsoft SQL Server\110\Shared\.

    Hope that helps someone else.

  3. Jonathan Kallay says:

    Be aware that your sample will work, but what won’t work is a web application that retrieves geometry from SQL Azure, because SQL Azure is currently running Denali (SQL Server 2012) at compatibility level 100; hence it will return instances of the 10.0 spatial types and the web role will throw an exception when trying to cast them to 11.0.

  4. Pingback: Il blog di Kleeng | Gestire dati spaziali in un Cloud Service di Azure

  5. Pingback: Unable to use geography data type with Azure Cloud Service - Windows Azure Blog

  6. Just an FYI for anyone trying this with an Azure Cloud Service project: In order to get the .Distance method to work (not throw an exception) in the ASP.NET MVC4 / Entity Framework 5 / SQL Azure project i’m working on (in which SQL’s geography type is mapped by EF to DbGeography type in C#), I had to use the 64-bit versions of all assemblies noted on this blog entry, and further, I had to use the (64 bit) version of SqlServerSpatial.dll from Program Files\Microsoft SQL Server\110\LocalDB\Binn folder, and *not* the one in SysWOW64. Additionally, I ended up needing to use the (64 bit) version of Microsoft.SqlServer.Types.dll from Program Files\Microsoft SQL Server\110\Shared\ folder.
    This is now working on our Azure site, hopefully this saves somebody else the 4 or 5 hours of mucking about that it took me….

  7. Pingback: DB Best Chronicles » Blog Archive Server Back-End for Mobile Apps: How to Develop and Deploy to Azure

  8. Pingback: Хроники DB Best » Blog Archive Серверный back-end для мобильного приложения: разработка и переход в облако Azure

  9. Dan Miser says:

    @Kirk,

    I don’t see SqlServerSpatial.dll in Program Files\Microsoft SQL Server\110\LocalDB\Binn. The only places I see it on my system is system32 and syswow64. I do see a SqlServerSpatial110.dll in the directory you mention.

  10. Dan Miser says:

    As of today, the only thing I needed to deploy was the 32-bit version of SqlServerSpatial.dll – even to a 64 bit Azure web site.

  11. Rowan Miller says:

    Hi,
    I work on the Entity Framework team at Microsoft. We now have a NuGet package to make deploying both the managed and native assemblies much easier. See this post for details – http://blogs.msdn.com/b/adonet/archive/2013/12/09/microsoft-sqlserver-types-nuget-package-spatial-on-azure.aspx.
    ~Rowan

  12. Barack says:

    NuGet PM> Install-Package Microsoft.SqlServer.Types

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s