Sending messages to Azure from SQL Server - Part 1

One of the projects I’m currently working on is caching contract related data in Azure Cosmos DB. To be able to cache the data I need to get some kind of notification when contract related data changes. Unfortunately, as this is for the most part a large legacy ecosystem, there are multiple application services that modify the contract data in the underlying SQL database directly.

Current Setup

The ideal solution would be to add some sort of service layer between the application services and the database to handle both the SQL operation and to send out events on an Azure Service Bus or some other mechanism which allows me to act on data changes.

Ideal Setup

Unfortunately, since this is the real world, I don’t have the time or budget to create a new service layer and modify the existing legacy application services so that all data modifications run through this new service layer.

So, I needed to find another solution: I’m going to send out the events to the Azure Service Bus directly from within SQL server.

That sounds complicated and scary, but there are a few things that are in my favor:

  • Azure Service Bus has a REST API which can be used to post messages to.
  • I’m running an on-premise instance of SQL Server 2016 which allows me to use CLR integration.
  • I know that the only time I need to modify my cached contract data is when an insert, update or delete occurs on my Contracts table, or any of the related tables. This means I can use a DML trigger to perform an action when the data changes. For the simplicity of this article I’m just going to continue with the Contracts table, but I will add triggers on every table.

Sending messages to a Service Bus

I’m going to send a message to an Azure Service Bus topic. There is an excellent tutorial on the Microsoft Docs website to Get started with Service Bus topics. The reason I’m using a topic and not a queue is because I will eventually have multiple listeners on the same event. Rather than sending out one event to multiple queues, Service Bus topics already handle this for me. If I add another application which should act when the contract data changes, I can simply register a new subscription on the topic and I’m good to go.

Another thing to note is that the CLR integration in SQL server is somewhat limited. You can’t use the entire .NET framework and I advice against the use of third party libraries.

Start by creating a new project in Visual Studio, and make sure to use a .NET framework class library. I’m using .NET 4.6.2 but any full framework version should suffice. To my knowledge .NET core is not yet supported by SQL Server. I named my project AzureStoredProcedures. Rename the existing Class1 to StoredProcedures. If a reference to System.Web exists in the project, it’s best to remove this as this is not a registered assembly in SQL Server. I will doing everything using the System.Net library which is sufficient for what I need to do.

The following using statements are required:

using Microsoft.SqlServer.Server;

using System;
using System.Data.SqlTypes;
using System.Globalization;
using System.IO;
using System.Net;
using System.Security.Cryptography;
using System.Text;

Next, I’m going to define some class based constants:

  • Namespace: The name of the Azure Service Bus namespace you created.
  • KeyName: The name of the shared access key. This can be found in the Shared Access policies screen of your Azure Service Bus. The default name should be RootManageSharedAccessKey.
  • AccountKey: The primary key required to connect to your Azure Service Bus. Note that this is just the key, not the entire connection string.

    private const string Namespace = "<name-of-the-azure-namespace>";
    private const string KeyName = "RootManageSharedAccessKey";
    private const string AccountKey = "<service-bus-primary-key>";
    

In order to be able to post messages to the Azure Service Bus REST API, I need a shared access token which I can generate from the primary key.

private static string GetSasToken()
{
  // Set token lifetime to 20 minutes. 
  var origin = new DateTime(1970, 1, 1, 0, 0, 0, 0);
  var diff = DateTime.Now.ToUniversalTime() - origin;
  var tokenExpirationTime = Convert.ToUInt32(diff.TotalSeconds) + (20 * 60);

  var stringToSign = WebUtility.UrlEncode(Namespace) + "\n" + tokenExpirationTime;
  var hmac = new HMACSHA256(Encoding.UTF8.GetBytes(AccountKey));

  var signature = Convert.ToBase64String(hmac.ComputeHash(Encoding.UTF8.GetBytes(stringToSign)));

  return string.Format(
    CultureInfo.InvariantCulture,
    "SharedAccessSignature sr={0}&sig={1}&se={2}&skn={3}",
    WebUtility.UrlEncode(Namespace),
    WebUtility.UrlEncode(signature),
    tokenExpirationTime,
    KeyName);
}

If the call to the REST API fails, I will get a WebException which will contain the actual error message. Since I’m interested in this for monitoring purposes, I created a helper method to extract the actual error message from the exception.

private static string GetErrorFromException(WebException webExcp)
{
  var exceptionMessage = webExcp.Message;

  try
  {
    var httpResponse = (HttpWebResponse)webExcp.Response;
    var stream = httpResponse.GetResponseStream();
    var memoryStream = new MemoryStream();

    stream.CopyTo(memoryStream);

    var receivedBytes = memoryStream.ToArray();
    exceptionMessage = Encoding.UTF8.GetString(receivedBytes)
      + " (HttpStatusCode "
      + httpResponse.StatusCode.ToString()
      + ")";
  }
  catch (Exception)
  {
    // If this fails we are beyond all hope and I have other mechanisms
    // to handle this.
  }

  return exceptionMessage;
}

Finally, we have the actual method which will be invoked from our SQL Server stored procedure. It’s important to note that this method is decorated with the SqlProcedure attribute and that the method parameters are SQL Server data types, not standard .NET data types.

[SqlProcedure]
public static void SendMessage(SqlInt32 contractId, SqlString operation, out SqlString returnValue)
{
  var sasToken = GetSasToken();
  var webClient = new WebClient();

  webClient.Headers[HttpRequestHeader.Authorization] = sasToken;
  webClient.Headers[HttpRequestHeader.ContentType] = "application/vnd.microsoft.servicebus.yml";

  // Create a basic JSON string
  var message = $"{{ contract: {contractId}, operation: '{operation}' }}";
  var body = Encoding.UTF8.GetBytes(message);

  try
  {
    webClient.UploadData(
      "<your-topic-url>" + "/messages?timeout=60",
      "POST",
      body);

    returnValue = "success";
  }
  catch (WebException ex)
  {
    returnValue = GetErrorFromException(ex);
  }
  catch (Exception ex)
  {
    returnValue = ex.Message;
  }
}

Another important thing to do is to make sure our library will be signed. Go the the project properties, click on Signing and check the Sign the assembly checkbox. Select <New...> from the drop-down. Given the key a name, and uncheck the password box as this is not needed.

Sign the assembly

In the next part we will load the assembly in SQL Server and add the triggers to send out the events.

comments powered by Disqus