Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 9, 2004

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

By Marcin Policht

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 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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM