Is it possible to add SignalR messages directly to the SQL Backplane?

Question

I'd like to know if I can add SignalR messages directly to the SignalR SQL Backplane (from SQL) so I don't have to use a SignalR client to do so.

My situation is that I have an activated stored procedure for a SQL Service Broker queue, and when it fires, I'd like to post a message to SignalR clients. Currently, I have to receive the message from SQL Service Broker in a seperate process and then immediately re-send the message with a SignalR hub.

I'd like my activated stored procedure to basically move the message directly onto the SignalR SQL Backplane.

Answer 1

I was inspired by this post and wrote up a version in an SQL stored proc. Works really slick, and wasn't hard.

I hadn't done much work with varbinary before - but SQL server makes it pretty easy to work with, and you can just add the sections together. The format given by James Haug above is accurate. Most of the strings are just "length as byte then the string content" (with the string content just being convert(varbinary,string)). The exception string is the payload, which instead is "length as int32 then string content". Numbers are written out "least significant byte first". I'm not sure whether you can do a conversion like this natively - I found it easy enough to write this myself as a recursive function (something like numToBinary(val,bytesRemaining)... returns varbinary).

If you take this route, I'd still write a parser first (in .NET or another non-SQL language) and test it on some packets generated by SignalR itself. That gives you a better place to work out the kinks in your SQL - and learn the right formatting of the payload package and what not.

Answer 2

Yes and no. I set up a small experiment on my localhost to determine if possible - and it is, if formatted properly.

Now, a word on the [SignalR] schema. It generates three tables:

[SignalR].[Messages_0] 
--this holds a list of all messages with the columns of 
      --[PayloadId], [Payload], and [InsertedOn]
[SignalR].[Messages_0_Id]
--this holds one record of one field - the last Id value in the [Messages_0] table
[SignalR].[Scehma] 
--No idea what this is for; it's a 1 column (SchemaVersion) 1 record (value of 1) table

Right, so, I duplicated the last column except I incremented the PayloadId (for the new record and in [Messages_0_Id] and put in GETDATE() as the value for InsertedOn. Immediately after adding the record, a new message came into the connected client. Note that PayloadId is not an identity column, so you must manually increment it, and you must copy that incremented value into the only record in [Messages_0_Id], otherwise your signalr clients will be unable to connect due to Signalr SQL errors.

Now, the trick is populating the [Payload] column properly. A quick look at the table shows that it's probably binary serialized. I'm no expert at SQL, but I'm pretty sure doing a binary serialization is up there in difficulty. If I'm right, this is the source code for the binary serialization, located inside Microsoft.AspNet.SignalR.Messaging.ScaleoutMessage:

public byte[] ToBytes()
{
  using (MemoryStream memoryStream = new MemoryStream())
  {
    BinaryWriter binaryWriter = new BinaryWriter((Stream) memoryStream);
    binaryWriter.Write(this.Messages.Count);
    for (int index = 0; index < this.Messages.Count; ++index)
      this.Messages[index].WriteTo((Stream) memoryStream);
    binaryWriter.Write(this.ServerCreationTime.Ticks);
    return memoryStream.ToArray();
  }
}

With WriteTo:

public void WriteTo(Stream stream)
{
  BinaryWriter binaryWriter = new BinaryWriter(stream);
  string source = this.Source;
  binaryWriter.Write(source);
  string key = this.Key;
  binaryWriter.Write(key);
  int count1 = this.Value.Count;
  binaryWriter.Write(count1);
  ArraySegment<byte> arraySegment = this.Value;
  byte[] array = arraySegment.Array;
  arraySegment = this.Value;
  int offset = arraySegment.Offset;
  arraySegment = this.Value;
  int count2 = arraySegment.Count;
  binaryWriter.Write(array, offset, count2);
  string str1 = this.CommandId ?? string.Empty;
  binaryWriter.Write(str1);
  int num1 = this.WaitForAck ? 1 : 0;
  binaryWriter.Write(num1 != 0);
  int num2 = this.IsAck ? 1 : 0;
  binaryWriter.Write(num2 != 0);
  string str2 = this.Filter ?? string.Empty;
  binaryWriter.Write(str2);
}

So, re-implementing that in a stored procedure with pure SQL will be near impossible. If you need to do it on the SQL Server, I suggest using SQL CLR functions. One thing to mention, though - It's easy enough to use a class library, but if you want to reduce hassle over the long term, I'd suggest creating a SQL Server project in Visual Studio. This will allow you to automagically deploy CLR functions with much more ease than manually re-copying the latest class library to the SQL Server. This page talks more about how to do that.