Data Replication for Decision Support

Many customers establish data warehouses for their decision support applications. These customers need a way to replicate data from their operational database to their decision support database. MS DTC is not intended for replicating decision support data. Instead, it is recommended that you use the data replication services provided with your relational database application. Typically, these database replication services asynchronously propagate changes from the operational database to the decision support database. This is more efficient than updating both databases synchronously using MS DTC. Using the database replication service may also improve system availability. Database replication services permit the operational database to be updated when the decision support database is unavailable. Changes to the operational database are queued and applied to the decision support database when it becomes available. When MS DTC is used for replication, both databases must be available whenever the operational database is updated. Alternatively, applications that update the operational database must tolerate decision support database failures. There must also be some way to resynchronize the two databases following the failure.

MS DTC is effective for building a reliable queue to send information from one system to another. An application can implement its own replication scheme using this reliable queue. For example, when the application updates the operation database, it queues an update request for the decision support database. A corresponding application on the data warehouse system would then remove the update requests from the queue and update the decision support database, thereby keeping the distributed queue consistent.