다음을 통해 공유

Replacing an MSMQ queue with a SQL Server 2005 queue

  • 아티클

Today, we did a short presentation on Service Broker for a customer who is interested in using the Service Broker technology. Their current application uses a large backend SQL Server database that is accessed from several mid-tier servers. In one of their scenarios, requests are queued to an MSMQ message queue and an NT service periodically reads these requests from the queue and performs some database operations on the backend. Switching from MSMQ to SQL Server 2005 Service Broker would give them the immediate advantages of persisting messages along with the data in the database and thus provide a single backup/restore solution. And they also get the exactly once in-order semantics of the conversations, internal activation to get rid of the polling NT service, etc.

MSMQ and Service Broker being fundamentally different in their architecture, we realized that proposing a solution from a Service Broker angle seems like a huge change for someone thinking in terms of a single MSMQ queue. So here is a look at incrementally migrating a simple spooling MSMQ application to a Service Broker.

Our application consists of a single queue that is being used for spooling requests. Two processes 'reader' and 'writer' respectively dequeue and enqueue messages to it. There could potentially be multiple writers enqueuing requests. Implementing multiple readers in the MSMQ case can be tricky is messages are correlated and must be fetched in order (But in Broker land, this is trivial due to our conversation semantics).

To implement this solution using SQL Server 2005, both the reader and writer programs connect to a database (for example using ADO.NET). So we begin by creating a database 'spoolerdb':

-- create the database

--

use master;

go

if (exists (select * from sys.databases where name='spoolerdb'))

      drop database spoolerdb

go

create database spoolerdb;

go

Next we create a queue (which is a new database object in SQL Server 2005) that will hold our request messages:

use spoolerdb;

go

create queue spool;

go

In order to identify endpoints that can initiate or be the target of messages, we create yet another type of database objects called services and bind them to the same queue. The services currently only support the [DEFAULT] contract for sake of simplicity, but can be extended in the future:

create service writer on queue spool (

      [DEFAULT] );

go

create service reader on queue spool (

      [DEFAULT] );

go

The overall architecture of the system would look something like the figure below:

The red dashed lines represent the operations that we want to achive -- the writer wants some way to enqueue messages into the queue and the reader wants to dequeue messages from the queue. The layer that sits between the programs and the physical queue is a logical layer comprising of Service Broker constructs.

The writer can enqueue messages by executing the following T-SQL batch:

-- Writer logic (DML)

--

declare @dh uniqueidentifier;

begin transaction;

    begin dialog @dh

        from service writer

        to service 'reader', 'current database'

        with encryption = off;

    send on conversation @dh ('My first message');

    send on conversation @dh ('My second message');

    send on conversation @dh ('My third message');

    end conversation @dh;

commit transaction;

The reader can loop to receive messages from the queue. Here is a loop entirely in T-SQL that receives messages into a temp table and runs a cursor over it. If the message type is not the special end_dialog message type then it prints the message to the output.

-- Reader logic (DML)

--

declare

@qview table (

      message_type_name sysname,

      dh uniqueidentifier,

      message_body varchar(max));

declare @message_body varchar(max);

declare @message_type_name sysname;

declare @dh uniqueidentifier;

declare qc cursor for select * from @qview;

while (1=1)

begin

      begin transaction;

            delete from @qview;

            waitfor (

                  receive message_type_name, conversation_handle, message_body

                  from spool into @qview), timeout 2000;

            if (not exists (select * from @qview))

            break;

            open qc;

            fetch next from qc into @message_type_name, @dh, @message_body;

            while @@fetch_status = 0

            begin

                  if (@message_type_name = 'https://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')

                        end conversation @dh;

                  else

                        print @message_body;

                  fetch next from qc into @message_type_name, @dh, @message_body;

            end

            close qc;

      commit transaction;

end

deallocate qc;

commit transaction;

Of course, the logic can be broken into multiple batches and executed from the reader program, for example a CLR program that uses ADO.NET. It can be seen that we use abstractions such as services and dialogs for SENDing and RECEIVing messages. Services are named endpoints that can initiate or be the target of a message exchange called conversations. A two-way peer-to-peer conversation is known as a dialog.

While this simple solution works great for quickly migrating an MSMQ app to Service Broker, you will soon realize that having separate queues for the initiator and target service is a good idea even if you don't initially perceive the writer to read messages or the reader to write messages. But that is optional.

Comments