Microsoft.SqlServer.Types not functioning properly on Azure Worker Role

Question

I'm looking to use DbGeography via EntityFramework communicating with a SQL Server database, within an Azure Worker Role. As I understand it, DbGeography uses Microsoft.SqlServer.Types / SqlServerSpatial110.dll in the background, so in order to get it to work in Azure, I've followed:

http://blogs.msdn.com/b/adonet/archive/2013/12/09/microsoft-sqlserver-types-nuget-package-spatial-on-azure.aspx and installed the nuget package, then I've specified loading the SQL Server types in the OnStart method in WorkerRole.cs:

public override bool OnStart()
        {
            // Load SQL Server Types
            SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory);

I also then followed this blogpost https://alastaira.wordpress.com/2011/08/19/spatial-applications-in-windows-azure-redux-including-denali/ where I explicitly added SqlServerSpatial110.dll to the project and set it to Copy always.

So the real issue is - immediately after deployment, everything works as expected. However, if I leave the Azure Worker role alone for a bit (~30 mins) and it receives no requests, the DbGeography portions of my code no longer function.

Is there something else I need to do?
Have I put the LoadNativeAssemblies call in the wrong place (should it be on Run() ?)
Do Azure Worker Roles get recycled?
Has anyone come across this before / managed to use DbGeography with an Azure Worker Role, or have any insight as to what might be happening?

Another thought - is it anything to do with how LoadLibrary functions within an Azure Worker Role? — Henry C
I've tried using it on Run(), which also has not helped; when you go into Client.OnMessage(), is the AppDomain possibly different between instances? — Henry C

Euan Gordon Euan Gordon · Accepted Answer · 2018-01-16T15:05:02

Very late reply, but I found your question while looking for a solution. How to load SQL Server Types in Azure Functions.

The major issue is that the path that the code is running from is not the same as with other applications types. So you cannot use the suggested:

SqlServerTypes.Utilities.LoadNativeAssemblies(Server.MapPath("~/bin")); or
SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory);

Within your Azure Function Run params, add in ExecutionContext context which allows you to retrieve the working folder.

By navigating up one level you can use this path for loading the SQL Server Types:

public static async Task<HttpResponseMessage> Run([HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)]HttpRequestMessage req, TraceWriter log, ExecutionContext context)
{
    string path = context.FunctionDirectory;
    string newPath = Path.GetFullPath(Path.Combine(path, @"..\"));
    SqlServerTypes.Utilities.LoadNativeAssemblies(newPath);

Microsoft.SqlServer.Types not functioning properly on Azure Worker Role

2 Answers