The ERP Hacker

CLR Integration: Wrestling Microsoft to the Ground

August 10th, 2011

The code resulting from this misadventure can be found at https://github.com/agglerithm/ClrIntegration .

The whole thing started innocently enough. In our efforts to implement Syteline as a viable ERP system (now 4 years past due), I learned about an event system that was available (for a modest configuration fee, of course), one which could be used to broadcast MSMQ messages whenever a database record was changed. This was potentially game-changing for us; it would allow us to achieve true command/query separation without having to write our own ERP system from scratch.

There was only one problem: We were using MassTransit to handle MSMQ traffic, and the message format used by Syteline was unlikely to be compatible.

I weighed several options, such as writing some sort of anti-corruption layer to convert from one message format to another. I soon realized it would be necessary to look into the guts of Syteline’s event system to see how it worked.

From what I could tell, the system made use of triggers to communicate with MSMQ. I cringed at the thought of adding yet another layer of triggers to an already over-burdened design (each table already had three to five triggers attached to handle business rules). Then I decided that, if I was going to use triggers anyway, I should implement my own and at least have control over the performance. In addition, I could ensure that the messages were in the correct format from the beginning and the anti-corruption layer would be unnecessary.

It seemed like a good idea, especially when reading the glowing descriptions of Microsoft’s great new technology to replace the old C-style extended procedures: CLR integration.

CLR integration provides an interface between SQL Server and the .NET runtime. According to Microsoft, it is a wonderfully efficient and elegant dance of synchronization between two members of their family, each with an intimate understanding of the other, and with great care taken to use only the resources required for a given operation, and no more.

According to Microsoft. I suspected the truth was somewhat different.

It’s no secret that the needs of the .NET team at Microsoft are rarely considered by the teams working on such projects as Office, Windows, and SQL Server. There are .NET assemblies to interact with these things, but one gets the feeling that all the interaction is a result of hacking on the part of the .NET team, and is not borne from a sense of mutual cooperation. My work on this project has done little to dispell that impression.

So here’s how I needed to use CLR integration: Anytime a DML command is run on a table I need to keep track of, a .NET assembly should be called with the inserted or deleted record. The handler should then run a simple routine that builds a message from the affected record and sends it to the appropriate queue. From there, it’s another application’s responsibility to post the changes to the reporting database.

It’s as simple as that. Communication is completely asynchonous, so SQL doesn’t have to wait for any sort of response before it continues on to do whatever it is that it does. It therefore should be less of a resource-hog than if it were a normal Transact-SQL trigger that simply updates the report database. It would also make constantly polling the tables for changes, an even greater waste of resources, unnecessary.

All that was needed was to implement CLR integration on the application (.NET) side, then make the .NET assembly available on the SQL side. The first part was easy, the second part less so.

At first I simply wrote a trigger that would do nothing more than return a message back along the connection pipe saying that the assembly had been successfully called. There was a bit of a learning curve in making this happen, but I soon was able to demonstrate that it was at least possible.

Then, I had to make the assembly actually do something. Knowing that it should be as lightweight as possible, I decided to dispense with all my usual dependencies, including MassTranit, and using only the standard GAC assemblies and a reference to one additional dll: The Microsoft message queuing assembly, System.Messaging. Sure, it would require a little hacking to simulate the behavior of MassTransit, but the functionality I needed was not that extensive.

It was here that the fun began. I soon learned that, in order for SQL to recognize the latest changes to my own assembly, I would have to constantly blow away the trigger or triggers with dependencies on that assembly, re-install the assembly, then re-create the trigger or triggers. I also learned that since System.Messaging was not considered part of the core .NET runtime, it would have to be added to the SQL Server assemblies list.

When I added that, I learned that there were further dependencies that would not allow me to install System.Messaging until THEY were installed. It was about this time that I was hit with an epiphany similar to the sudden realization by a child that Santa isn’t real.

The wonderful engineers at Microsoft, the lofty geniuses responsible for the .NET system, were suddenly revealed as being mere mortals. As the different dependencies added up, I realized that they had made…let’s say, architectural decisions that were indistinguishable from careless blunders.

System.Messaging could not be installed without first installing System.Windows.Forms. The Forms assembly, in turn, required various assemblies related to drawing rectangles and other aspects of the GUI environment. These, in turn, required references to still more dependencies.

My only question was..Why? For God’s sake, WHY?

What kind of bumbling incompentent includes dependencies on the user interface in a mid-level assembly which, by definition, operates BELOW the user interface level? And WHY is that bumbling incompetent making major architectural decisions at Microsoft?

It was, of course, POSSIBLE to include System.Windows.Forms in my ever-growing list of depencies in SQL, but I was beginning to feel increasingly uncomfortable with this. Especially when my attempts to add the last few assemblies I needed failed with an out-of-memory error.

At first I thought this error was an issue with SQL Management Studio, but it quickly became apparent that it was the actual server that was out of memory. After a little research, I found that this was a well-known problem with CLR integration. On one message board, the comment was made: “Microsoft really boned us good on this one.”

Great. Wonderful. Now I wasn’t sure what I was going to do, beyond hacking into System.Messaging itself and try to implement its logic without the crazy dependencies.

That was, of course, not an option at all. It was crazy to go to this much trouble to work around what was clearly a poorly-designed system.

I decided to do it anyway.

I used JetBrains’ dotPeek to peer into the inner workings of System.Messaging. Soon, I discovered that this fairly complex library was actually a wrapper for a few calls to an old C-style DLL called “mqrt.dll”, located in the system32 folder.

Soon, I had a renewed feeling of hope. All I needed to do was to figure out what the different methods of the DLL did, then co-opt the ones that I needed to create messages, open a connection to a queue, and send the messages there. I didn’t have to read queues or even deserialize messages.

As it turned out, all I needed were the following methods:

MQOpenQueue(), which provided a handle to a message queue;

MQSendMessage(), which puts a message into a queue with a given handle; and

MQCloseQueue(), which closes the queue.

Microsoft’s assembly included two main objects for dealing with queues and messages: The Message object and the MessageQueue object. I copied these classes verbatim to my project and began systematically hacking away at the parts I didn’t need…which was most of them. At first I pared them down a little too much…it turned out that I needed to include a security context before I could send a message…but eventually I ended up with a lean, mean facsimile of System.Messaging that did what it needed to do.

Now all I had to do was emulate MassTransit. This wasn’t as difficult as it sounds, since I didn’t have to bother with subscriptions or any of the other higher functionality of the framework. I simply had to ensure that the messages I was sending were in a format recognizable by MassTransit.

There was another advantage to the lean and simple approach I was taking, which was that the messages were hierarchichally flat. There were to be no complex members, only primitives, and it was simple enough to create a tool to build a message header defining these types. I created a number of extension methods for building the attributes I needed, such as:

public static string GetBooleanDefinition(this object spec)
{
return “System.Boolean, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″;
}

I then created an XmlMessageEnvelope class to serve as both a container and builder for the actual Xml that would go into the message. The class made use of reflection to take an arbitrary class and build a message from it that could be recognized by MassTransit. My system would eventually have many message classes to accommodate the changes I needed to handle, and I needed it to be flexible.

So that’s it, so far. I’m putting quite a bit of thought into this part of the process, so that when I add the back-end logic I don’t have to worry about what layer had introduced which bug at whatever point in time. I still have quite a bit of work ahead of me, and I anticipate more wrestling with the Microsoft stack, but there is at least a light at the end of the tunnel.

Entry Filed under: Uncategorized