Sql2msmq - How To

  1. Sql2msmq - How To
    1. Introduction
      1. Introducing Sql2msmq to MS SQL Server
    2. Really using Sql2msmq
      1. Configuring a trigger
      2. Testing your configuration
    3. Programmatic Example
    4. Caveats
      1. Trivial solution

Introduction

The first thing you need to understand is that Sql2msmq relies upon:

"Microsoft .NET Framework common language runtime (CLR) integration with Microsoft SQL Server, and the SQL Server database engine process execution environment."

That's a bit of a mouthful, but it's a pretty simple concept. Microsoft has linked the .NET runtime into their SQL server product. This allows developers to create assemblies which can be called from within the context of SQL Server itself. This is very similar to the old fashioned extended stored procedure API that used to be available for the same purpose. The advantage of this new functionality is that it is managed code, so it's more robust (it probably won't bring down your SQL Server), and developers can take advantage of the rich .NET runtime class library for functionality.

Introducing Sql2msmq to MS SQL Server

In order to be able to use Sql2msmq the SQL Server needs to know it exists. In order to inform SQL Server we need to register the assembly with it explictely. Before we can do that we need to register a few of Sql2msmq's dependencies. In fact there is really only one dependency that SqlServer? doesn't already know about - the MS Message Queue assembly named "System.Messaging". There's another hurdle before we get to that stage though.

In order to register System.Messaging we'll need configure SQL server in such a way that it will allow us to register it. If you are not a seasoned Microsoft SQL Server Database Administrator a lot of this is going to look like so much gibberish; it certainly does to me, even now. There is a huge amount of documentation which may help you to discover a better way of doing this, but these are the steps I took when developing the assembly.

In this example, the database which I wish to register the assembly against is named 'fortitude', don't ask why an assembly would be registered against a single database, and not the entire server - it's loaded into the server address space, after all. Here I am assuming that you are logged on to your Sql Server, and are capable of running SQL statements.

1.) make sure we're not doing anything to anyone elses database environment 

use [fortitude]

2.) Ensure that CLR integration is enabled (there seems to be some confusion about this) 

            sp_configure 'clr enable', 1

3.) Now - we address a strange issue which I simply do not understand, but know how to mitigate: Sql Server seems to require enormous amounts of memory in order to register assemblies. In this case my server has 4 Gig of RAM, so we'll enable it all 

            sp_configure 'max server memory', 4096
            RECONFIGURE

4.) Then we inform SQL Server it can trust the fortitude database. 

             ALTER DATABASE 
                 fortitude 
             SET 
                 trustworthy ON

5.) Now we can register the MS Message Queue assembly - note we need to declare it unsafe for some inexplicable reason. 

             CREATE ASSEMBLY 
                 Messaging
             AUTHORIZATION 
                 dbo
             FROM 
                 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'  -- obviously path may vary
             WITH 
                 PERMISSION_SET = UNSAFE

6.) Finally, we can register Sql2msmq - don't ask why we don't need the AUTHORIZATION part 

             CREATE ASSEMBLY 
                 sql2msmq
             FROM 
                 'C:\SQL\TriggerAssemblies\sql2msmq.dll'  -- obviously path may vary
             WITH 
                 PERMISSION_SET = UNSAFE

If you feel uncomfortable with all that, or you would simply prefer to use another method of enabling the registration of the assemblies, you might wish to read  this

I haven't used that method, but it seems like it might be a better way of doing things. One can only hope that some day Microsoft will simplify the amazing muddle that is their security model. If you encounter loads of weird errors which indicate permission issues, please blame Microsoft, not me. Sql2msmq does not use any unsafe code - it's all managed C#.

I must confess that I missed a step - that's becuase I'm not sure what its purpose is, but it overcame a weird permission error. I am pretty sure this would normally not be required by a user with dbo type permissions 

EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false

Really using Sql2msmq

Now we're ready to actually use the assembly. To use the assembly we need to associate one or more triggers against it. By default, I register the trigger for three even types: insert, modify, delete

Configuring a trigger

Here I assume a table name of 'dummy_t' 

create trigger 
	msmq_trig
on 
	[dummy_t]
for 
	insert, update, delete  -- we want to know about all events
NOT FOR REPLICATION 
as external name sql2msmq.msmqTriggers.msmqTrigger  -- DO NOT CHANGE THIS - it's the code that runs inside the assembly

GO

Note the sql2msmq.msmqTriggers.msmqTrigger - do not change this. sql2msmq.msmqTriggers.msmqTrigger is the exported class from the sql2msmq assembly that will be called when the trigger is fired by Sql Server.

Testing your configuration

Now, you probably would like some indication that things are as they should be. Luckily, the trigger assembly can communicate back to a client through the magic of pipes.

All that is needed is a simple insert, update or delete operation on the table

Now for something slightly confusing: The FIRST time you run a data manipulation operation against dummy_t, the Message Queue will need to be created. DONT MAKE IT YOURSELF sql2msmq will make one for you.

Here is some sample output from the trigger assembly for trivial update on my dummy_t table 

Enter Trigger
Open Connection
Get Instance Details
ServerName = [MY-SQL-SERVER]
DBName = [fortitude]
TableName = [dummy_t]
Trigger Type [Update]
Load INSERTED
Load DELETED
In SendToMQ with quene name - MY-SQL-SERVER\fortitude.dummy_t.Update
Create Queue
DANGER - Failed to create Message Queue [MY-SQL-SERVER\fortitude.dummy_t.Update] within specified timeout

(1 row(s) affected)

First of all it's great that we don't see any errors on the output. That's a good indication that everything went well. Now let's look at what got sent back.

Enter Trigger - this is the first indication that sql2msmq has been called as a result of the trigger against the table.

Open Connection - this indicated that sql2msmq has opened a connection to the Message Queue server.

The next few lines are sql2msmq's discovery of the server name, database name, table name and table operation

Then we see this: In SendToMQ with quene name - MY-SQL-SERVER\fortitude.dummy_t.Update - The queue name is composed of the server it resides upon, then the database name followed by the table name, then finally the data operation that caused the trigger to be called.

Now here comes the confusion: Create Queue DANGER - Failed to create Message Queue [MY-SQL-SERVER\fortitude.dummy_t.Update] within specified timeout

What you are seeing here is that sql2msmq has tried to create the queue but an internal timeout has expired while waiting for the queue to be visible. This seems to happen almost no matter how long the timeout is, and because we don't want to have the SQL operation abort, we simply report the fact the timeout expired and report success.

Now if you run the same SQL statement again, you should see something very similar except in place of the 'DANGER - .... message you will see 

With Queue
<NewDataSet>
  <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="Update" msdata:UseCurrentLocale="true">
      <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
          <xs:element name="Update">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="Id" type="xs:int" minOccurs="0" />
   -- snip --
                <xs:element name="DateCreated" type="xs:dateTime" minOccurs="0" />
                <xs:element name="Updated" type="xs:dateTime" minOccurs="0" />
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:choice>
      </xs:complexType>
    </xs:element>
  </xs:schema>
  <Update>
    <Id>9648</Id>
-- snip -- 
    <DateCreated>2008-09-29T11:18:41.553+01:00</DateCreated>
    <Updated>2008-09-29T11:18:41.57+01:00</Updated>
  </Update>
  <Update>
    <Id>9648</Id>
-- snip -- 
    <DateCreated>2008-09-29T11:18:41.553+01:00</DateCreated>
    <Updated>2008-09-29T11:18:41.57+01:00</Updated>
  </Update>
</NewDataSet>
Send Message

(1 row(s) affected)

Notice the 'With Queue' - this indicates that the queue was successfully opened.

Then what you see is the trigger data set.

In this case, because we performed an update we see the original row, then the new row. (this is how an Update trigger behaves, it provides a inserted and a deleted table each with a single row)

The Xml that is sent is trivially converted into a System.Data.DataTable? by way of this code: 

System.IO.StringReader sr = new System.IO.StringReader(text);
DataTable tbl = new DataTable();
tbl.ReadXml(sr);

where text is the XML payload

Programmatic Example

Here is a trivial sample C# code snippet which illustrates how to receive trigger messages from sql2msmq 

const int timeout = 120 // 2 minutes

public void Func()
{
  MessageQueue queue = new MessageQueue("MY-SQL-SERVER\fortitude.dummy_t.Update", QueueAccessMode.Receive);
  ReceiveCompletedEventHandler cb = new ReceiveCompletedEventHandler(MessageHandler);
  queue.ReceiveCompleted += cb;
  queue.BeginReceive(TimeSpan.FromSeconds(Convert.ToDouble(timeout)), queue);
  queue.ReceiveCompleted -= cb;
  queue.Close();
}


// Provides an event handler for the ReceiveCompleted event.
private static void MessageHandler( object sender, ReceiveCompletedEventArgs e)
{
  try
  {
    // we stored the queue in the state object parameter of the constructor
    MessageQueue queue = (MessageQueue)e.AsyncResult.AsyncState;
    // End the asynchronous receive operation.
    Message msg = queue.EndReceive(e.AsyncResult);
    // sql2msmq sends the message as XML
    msg.Formatter = new XmlMessageFormatter( new Type[]{typeof(string)} );
    // get the buffer out of the message
    String text = (String)msg.Body;
    // and convert it to a DataTable
    System.IO.StringReader sr = new System.IO.StringReader(text);
    DataTable tbl = new DataTable();
    tbl.ReadXml(sr);
    // Display the data in the DataTable
    foreach(DataColumn col in tbl.Columns)
    {
        Console.Write("{0}\t\t", col.ColumnName);
    }
    Console.Write("\r\n");
    foreach(DataRow row in tbl.Rows)
    {
      foreach(DataColumn col in tbl.Columns)
      {
        Console.Write("{0}\t\t", row[col.Ordinal].ToString());
      }
      Console.Write("\r\n");
    }
    // reinitialise the async callback so we can receive another message
    queue.BeginReceive(TimeSpan.FromSeconds(Convert.ToDouble(timeout)), queue);
  }
  catch (Exception x)
  {
     Console.WriteLine(x.ToString());
   }
}

Caveats

Don't leave triggers associated with message queues that you are not monitoring. Failure to perform destructive reads or purges on messages in the queue may lead to reduced server performance, or ultimately more severe resource errors which may ultimately result in your server being unavailable or even crashing. MSMQ allows limits to queue size to be set; it is essential that you understand the impact of accumulating messages on the queue.

Trivial solution

Unregister the Sql2msmq assembly trigger when no readers are operational.