SQL Server 2000 DTS Part 11 - DTS Designer Tasks - the Message Queue task
March 9, 2004
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:
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:
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 MRBIG\SQLPublic and MRBIG\private$\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.