Using Spatial Data Types / DBGeography In Azure Worker Role

9 September 2015

I'd recently built some stuff that runs in Azure on a Worker role that interacts with some Spatial Data from a SQL Database via EntityFramework; having tested it locally, everything worked great. However, when published to the actual Azure environment, none of the data was being returned and my code wasn't working as expected.

It turns out that the reason behind this is that in Azure you're missing the SqlServer.Types (which is what DbGeography uses in the background). It worked flawlessly for me locally as I have Sql Server installed, which deploys the required .dll to the GAC.

The fix is pretty simple; all you need to do is add the Microsoft.SqlServer.Types (Spatial on Azure) nuget package to your WorkerRole project (ie the one that normally contains WorkerRole.cs). After you've added it, a little readme will pop up in Visual Studio; you'll want to follow the instructions for "desktop applications" (as you're not in a ASP.NET context on an Azure Worker Role).

I put the LoadNativeAssemblies call in the OnStart() method in WorkerRole.cs, but I'm not 100% if that's the only place you have to include it, or if you just have to make sure they're loaded prior to using DbGeography, or if you even really need this step / the CopyLocal of Microsoft.SqlServer.Types assembly that nuget package includes is enough.

EDIT: I noticed that it seemed to unload / stop working once, so I followed the instructions here as well https://alastaira.wordpress.com/2011/08/19/spatial-applications-in-windows-azure-redux-including-denali/ to explicitly add the SQLServerSpatial110.dll - it's unmanaged code so you don't add it as a reference; instead, just add it to the project and set the Output to Copy Always.

EDIT 2: Still seems to stop working about 30mins after deployment / if the worker role spins down...

EDIT 3: It turns out there's actually nothing wrong with how I'm including the SqlServer types and my worker role issue was something completely different (it was a race condition with EntityFramework), so all the stuff in this post should be good to go as is! (Until it gets changed in the future, invariably)

Tags: DbGeography, Azure, Microsoft.SqlServer.Types

Add a Comment

No Comments