Microsoft recently announced that SQL Azure will support working with native spatial data in June of this year. This is great news, and significantly enhances the usability of SQL Azure as a geographic data respository. As a result, many folks may want to start utilizing spatial data in their Azure hosted Web applications. Working with SQL Server spatial data in a .NET application usually involves working with the SQL Server Spatial (SqlServerSpatial.dll) and Types (Microsoft.SqlServer.Types.dll) libraries included with the SQL Server System CLR Types feature pack. The technical capabilities of the SQL Server Spatial library have been established in numerous blog posts, conference sessions, forums, and in the product documentation. Basically the library offers a standard, light-weight set of spatial operators built on geometry and geography spatial data types that are relatively easy to use and integrate in a .NET application. You can download, install, use, and distribute these libraries as needed. The latest edition of the SQL CLR Types was released in November 2009 for SQL Server 2008 R2. If the .NET application that uses the SQL spatial libraries is installed on a local machine or hosted on a local server, you have complete control over the environment in which it operates. However, if you choose to deploy an ASP.NET application as a Windows Azure web service, you will encounter a limitation. The platform for Windows Azure is a flavor of 64-bit Windows Server 2008 prior to the R2 release. The Nov 2009 SQL Spatial library is dependent on the system library MSVCRT80.dll which is not included with this operating system. As a result, when you attempt to use the SQL Spatial library in your Azure hosted ASP.NET web service, you'll likely see the following error:
Unable to load DLL 'SqlServerSpatial.dll': The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log for more detail. (Exception from HRESULT: 0x800736B1)
MSVCR80.dll is required by the SQL Spatial library and must be installed/configured on the host operation system, it cannot simply be dropped in the same directory. In addition, you cannot install features and components in your Windows Azure workspace. So how can you use the SQL Spatial library in an Azure hosted application? Simple, include a SQL Spatial library from a SQL Server 2008 (not R2) edition of the CLR Types. The functionality is basically unchanged.
Here are the steps:
1) Download the 64-bit SQL Server 2008 edition of the SQL Server CLR Types feature pack. I used the October 2008 download successfully.
2) If you don't want to install the feature pack, or you don’t have a 64-bit system on which to install, you can extract the 64-bit version of this library using the following command:
msiexec /a SQLSysClrTypes_64bit.msi /qb TARGETDIR="C:\MyFolder"
3) Copy the Microsoft.SqlServer.Types.dll and SqlServerSpatial.dll into the bin of your Web application. I was able to successfully deploy and use SQL Server Spatial logic in Windows Azure service using the February 2010 edition of the Azure SDK.
Now let's change gears and chat about ESRI's foray into the SQL Server Spatial and Azure world. Currently ESRI has a product named MapIt which includes an ASP.NET Web service, the Spatial Data Service, that uses the SQL Server Spatial library to work with native spatial data in SQL Server 2008. The Spatial Data Service can be deployed as a Windows Azure service and work with spatial data in SQL Azure or any third party that hosts spatial data in SQL Server 2008 (e.g. Discount ASP.NET). You can see the SDS in Windows Azure in action here: http://mapit.cloudapp.net/. To deploy your own SDS as a Windows Azure service, use the Azure deployment utility on the MapIt resource center.
As a parting thought, keep in mind that Microsoft will likely update the Windows Azure platform to include the foundational libraries necessary to support the SQL Server 2008 R2 edition of the spatial library. Hopefully the information in this post will suffice until that time.
