MSMQ and SQL Server integration

At times we have long running SQL procedures and its not always advisable to have the application waiting on it.Especially if your application needs to perform certain set of tasks based on the result returned from the SQL operation.

There are a few approaches that one might consider to address this scenario and using the Service Broker is definitely one of them. But we are not going to look into Service Broker, rather look at how we can use Messaging Queuing with SQL Server to address this requirement.

SQL Server interaction with MSMQ

The following steps depicts the process involved.

• The SQL Server runs a Scheduled Job that does the following
  • Execute the long running Stored procedure
  • Execute a stored procedure that sends a message to a remote Message Queue
• Remote queue on the application server receives the message
• Client application running on the application server polls for messages from the MSMQ. On receiving the message, the message is read from the MSMQ and application can resume its further processing

In this post we will cover all the configuration, components and environment settings including (ports, firewall rules, queue access, SQL configuration etc.) required to facilitate this setup.

.Net Class Library

This class library will simply send the message to a MSMQ. This defines a simple method that takes the queue name and the message to be delivered as parameters.

using System;
using System.Data.SqlTypes;
using System.Messaging;
using Microsoft.SqlServer.Server;

namespace SqlMsmq
{
    public class SqlToMsmqManager
    {
        /// <summary>
        /// Sends a message to the queue
        /// </summary>
        /// <param name="queueName">Full name of the queue</param>
        /// <param name="message">Message to send</param>
        [SqlProcedure]
        public static void Send(SqlString queueName, SqlString message)
        {
            if (queueName == null || string.IsNullOrEmpty(queueName.Value))
                throw new Exception("Message queue name need to be provided");

            var queue = queueName.Value;
            if (!MessageQueue.Exists(queue))
                throw new Exception("Message queue does not exist");
            try
            {
                using (var messageQueue = new MessageQueue(queue, QueueAccessMode.Send))
                {
                    messageQueue.Formatter = new XmlMessageFormatter(new Type[] { typeof(string) });
                    messageQueue.Send(message.Value, MessageQueueTransactionType.Single);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}

SQL Server

To be able to send a message to a Message Queue from SQL Server, first of all we will need to register the System.Messaging assembly and the assembly we created above with SQL Server. Below is the script that defines different steps required in SQL Server configuration

-- STEP1: Enable CLR integration in SQL Server
SP_CONFIGURE 'clr enable', 1
GO
RECONFIGURE
GO

USE [DatabaseName]
GO
ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON
GO

-- STEP 2: Add System.Messaging assembly to the database to enable Message Queuing component
CREATE ASSEMBLY Messaging
AUTHORIZATION dbo
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Messaging.dll' -- Path to the System.Messaging.dll assembly
WITH PERMISSION_SET = UNSAFE
GO

-- STEP 3: Add the external .Net assembly that will send the message to the queue
CREATE ASSEMBLY SqlToMsmq
AUTHORIZATION dbo
FROM 'C:\SqlToMsmq\SqlMsmq.dll' -- Path to the .Net class library
WITH PERMISSION_SET = UNSAFE
GO

-- STEP 4: Create procedure that will calls into the external .Net assembly to send the message
CREATE PROCEDURE [SendMsmqMessage]
	@queueName NVARCHAR(200),
	@message NVARCHAR(MAX)
AS
	EXTERNAL NAME SqlToMsmq.[SqlMsmq.SqlToMsmqManager].Send
GO

--Stored Procedure that needs to be called from the escowing SP at the end
EXEC SendMsmqMessage '<Full queue name to send the message after SP runs>', '<Message><Status>Stored procedure processed</Status></Message>'

Notes:

• You will need to be an admin user to be able to  make the above configurations to SQL server
• Sometimes when registering assemblies in SQL Server you would receive the error:

  "The database owner SID recorded in the master database differs from the database owner SID recorded in database.You should correct this situation by resetting the owner of database using the ALTER AUTHORIZATION statement."

  In this case run the following statement to alter the authorization on the database

ALTER AUTHORIZATION ON DATABASE::[DatabaseName] TO [LoginName]

MSMQ

Because in the class library we have instructed that the message queue is a transactional queue, we need to create a transactional queue on the application server

Environment Configuration

MSMQ communicates over ports 135, 1801, 2103/2105

• Sending Messages

So, since our database server will be pushing the message to MSMQ, we need to have port 1801 on the database server. MSMQ uses this port to establish a network session and then push the message to the destination

• Receiving Messages

MSMQ uses RPC for pulling messages, which requires ports 135, 2103 or 2105 open on the application server.

Environment Testing

To test that you have the required level of connectivity between your database and application server for sending MSMQ messages you should at least be able to telnet from your database server to your application server on port 1801

telnet 1801

And lastly

To send the message from your SQL Server to the MSMQ queue as defined above all we have to do is:

EXEC SendMsmqMessage 'FormatName:Direct=OS:ServerName\Private$\QueueName', '<Message>Any message</Message>'

Notes:

• Since our queue is a private queue we need to specify the message queue name in complete format.
  • We can use either OS or TCP format specifier

And that’s it. The message should appear in the message queue.