If you need to send data from one application to another and time isn't a factor, check out Microsoft Message Queue Server (MSMQ). Although many people overlook MSMQ in their application development strategies, this powerful messaging technology can give your applications a simple, robust way to interact with SQL Server.
Introduced with the Windows NT Server 4.0 Option Pack, MSMQ uses message queues to transmit data between applications. MSMQ creates a queue that lets an application send data by calling MSMQ methods to put data in a message queue. The target application then calls another MSMQ method to pull data from the queue.
If you have many users, for example, you can use MSMQ to feed user requests to the database asynchronously to avoid bottlenecks between the database and the user applications. You can also use MSMQ to distribute parts of an application across servers. For example, if you have your Web server on one side of your firewall and your application server on the other side, you can use MSMQ to transmit data between the two servers.
This architecture opens interesting possibilities for database application development. For example, let's say you have an order entry system on the Web that lets people select items, then place their orders. During holidays and other busy times of the year, however, users may overload the system. To manage the heavy order traffic, you could use MSMQ to place orders in the message queue so that the back-end application can retrieve them from the queue as it has time and update the database. In this case, using MSMQ to eliminate bottlenecks instead of trying to handle the orders in realtime will result in better performance for the entire application.
You can use MSMQ across LANs and across the Internet. And as I noted earlier, you can use MSMQ to move data through firewalls. Several Microsoft articles describe how to use MSMQ with firewalls (see Q183293 and Q178517, for starters). By using only MSMQ to transmit data between a server outside the firewall and one inside the firewall, you eliminate all other data considerations from your firewall implementation. And because the application inside the firewall pulls data from the queue, you control exactly which data gets through and what processing occurs on that data.
Originally with MSMQ, you had to create applications on each side of the queue to handle all the functions you needed to perform, including placing data in the queue and extracting data from the queue. In Windows 2000, however, Microsoft introduced a new COM+ feature called Queued Components that lets developers create an application that runs on one system but can execute an object's method on another system.
The Queued Components functionality is similar to Distributed COM (DCOM) but doesn't involve the DCOM protocol and its associated overhead. Instead, MSMQ serves as the message bus for the queued component call. And because Win2K executes the code that your application requested in the queued method call, you don't have to build a "listener" object that runs continuously and extracts data from the queue. Now, you can build a data-driven application that uses a message queue to access the components that talk to the database. You can even distribute to other servers the components that call the queued components, as long as MSMQ can reach the queued component server.
This Queued Components architecture simplifies the process of building applications that use MSMQ because developers don't have to build applications that manage the queue. And the Queued Components feature also provides a more robust application development environment by supplying an easy way to distribute components in our applications.