SQL Server 2000 DTS Part 11 – DTS Designer Tasks – the Message Queue task

It is finally time to complete our coverage of DTS tasks – the only
remaining one that we have not presented yet is the Message Queue task. However,
before we describe its features and provide an example demonstrating their use,
let’s first spend some time discussing the concept of message queuing and its
Microsoft-specific implementation.

The main purpose of message queuing is providing reliable data exchange
between distributed (potentially heterogeneous) systems that might not be able
to operate simultaneously at the same capacity. This way you can accommodate
scenarios where some type of information from a data source needs to be
processed, but you might not be able guarantee its immediate delivery due to
connectivity problems or temporary unavailability of a data target. Similarly,
you can allow for differences in processing ability between both systems,
without being concerned about overwhelming the destination with message
overflow. Such functionality can also be beneficial in load balancing
configuration, where multiple targets can monitor a single queue and process
any awaiting message.

Message queuing first became available on the Windows platform as part of
Windows NT 4.0 Option Pack add-on, but starting with Windows 2000 it was
included as a standard operating system component (available from the Add/Remove
Programs applet in the Control Panel). Its Windows XP installation consists of
the following elements:

  • Active Directory Integration – (applicable when a computer is a
    member of a Windows 2000/2003 Active Directory domain) – provides
    representation of public message queues (as opposed to private ones, which are
    not represented in the Active Directory) as subnodes of Active Directory msmq
    objects.

  • Common – implements core functionality and allocates local
    storage for messages.

  • MSMQ HTTP Support – allows sending and receiving messages over
    HTTP protocol (proxy settings are configurable with the proxycfg.exe utility).
    This option relies on Message Queuing ISAPI extensions being present on
    Internet Information Server (managed with IIS Administrator MMC snap-in).

  • Triggers – enables triggering execution of a code in COM
    components (or executables) in response to arrival of a message.

Full installation creates two services – Message Queuing and Message Queuing
Triggers, and registers Message Queuing in the Active Directory database. (You can
view it by launching Active Directory Users and Computers, selecting
"Advanced Features" and "Users, Groups, and Computers as containers"
options in the View menu, and displaying content of a computer object
representing the system on which Message Queuing has been installed). You will
notice that the computer object contains msmq sub-object. Every public message
queue you define on the system running Message Queuing service will have a
corresponding public queue object residing in the msmq node.

To install Message Queuing on Windows 2003 server, launch Windows Component
Wizard (by clicking on the Add/Remove Windows Components button) from Add or
Remove Programs in the Control Panel. On the first page of the wizard, locate
the Application Server entry, use Details button to display its subcomponents,
and select the Message Queuing option. In addition to the choices available for
Windows XP, you will also be able to install:

  • Downlevel Client Support – provides Active Directory support for downlevel
    clients. This might require modifying Active Directory permissions if the
    Message Queuing services on downlevel clients are operating under local system
    account security context.

  • Routing Support – allows store-and-forward mechanism and routing
    of messages using Active Directory based information.

The most basic scenario in which Data Transformation Services utilize
message queuing involves one DTS package serving as a Queue Sender, another as
Queue Receiver, and an intermediate system providing Message Queuing services.
While typically each of these components resides on a separate physical system,
you can also co-locate all of them on the same SQL 2000 Server. Regardless of
how you configure your solution, you have to install Message Queuing components
on the intermediate server. In order to take advantage of Active Directory
Integration and use public queues, this computer needs to be a member of Active
Directory Domain. Once the installation of the Message Queuing has completed,
open local Computer Management MMC snap-in. You will find Message Queuing subnode
listed under the Services and Applications node. From here, you can create
private and public (if you installed Active Directory integration) messaging
queues. For the purpose of our exercise, create one public and one private
queue, named SQLPublic and SQLPrivate, respectively. This is done
by right clicking on the one of the Queues top-level folders (Public or
Private) and selecting New from the context sensitive menu. Notice that a
public queue is referred to simply by the name you assigned to it (prefixed
with the computer name), while the private one has an additional private$
element. For example, assuming that our server is called MRBIG, the
queues will be referred to as MRBIGSQLPublic and MRBIGprivate$SQLPrivate.
You also have an option of making each queue transactional, although in our
case, this is not required. Creation of public queue will also result in the SQLPublic
entry appearing under msmq subnode of MRBIG computer object in the Active
Directory.

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles