HowTo: Send Notifications from SQL Server using SignalR

Overview

In our current project we’ve to push some notifications directly from the database server (SQL Server 2014) to different apps. For example, we’ve scheduled some SSIS (SQL Server Integration Services) projects that pre-calculates large amounts of data. And well, should be nice notify all the apps when new data is available without unnecessary round trips to database. And you know? we’re already using SignalR in our apps… so, why not try to build a lightweight client for sending SignalR notifications?

Dear architects, please, don’t judge me :P

The idea

I finally decided to create a small project in C# that connects to our SignalR (which is self-hosted in a Windows Service). That project contains a single method for sending notifications with a message (just a string for simplify).

Once the project is ready and tested, the next step is create an ASSEMBLY, that is, a module that contains class metadata and managed code as an object in an instance of SQL Server. By referencing this module, common language runtime (CLR) functions, stored procedures, triggers, user-defined aggregates, and user-defined types can be created in the database.

And finally, create a Stored Procedure that use our previous assembly CLR function (for isolation purposes and decoupling), and then use it in our SSIS projects with a simply ‘Execute SQL Task‘ when the process finalizes.

Note: Before continue, take a look to the last parameter ‘args’. This parameter allows you to pass a different number of arguments to this method, which is nice because when we invoke the hub’s method we can use the same argument type. Unfortunately this argument type is not compatible with SQL CRL and will crash when creating the Stored Procedure in the database :( So we need to change this argument to a simple string.

This code is just an example, maybe would be a better idea split it into different methods (remember the ‘S’ of SOLID?) :)

Let’s assume the code is ok and is able to connect and send the notification. Now…

Create the SQL Assembly

In order to deploy your assemblies to a SQL Server you need the same version of the .NET Framework installed on the server. Yep, it’s too obvious but…

Create a folder in the server called ‘C:\SQLServerAssemblies’ (or wherever you want) and copy all the assemblies generated by the previous project.

But we’ve not finished yet… Now we’ve to add some related assemblies (assemblies dependencies). Otherwise, assembly creation will fail.

You can find these assemblies in the same server, in the correct version of .NET Framework. In my case: