Creating distributed, dependent data marts from a central data warehouse, or even duplicating the contents of an independent data mart, requires the ability to replicate information reliably. SQL Server 7.0 includes facilities for reliably distributing information from a central publishing data warehouse to multiple subscribing data marts. Information can be partitioned by time, geography, and so on as part of the replication process.
SQL Server 7.0 provides a variety of replication technologies that can be tailored to your application’s specific requirements. Each replication technology produces different benefits and restrictions across these dimensions:
Requirements along and across these three dimensions vary from one distributed application to the next.
In most decision-support applications, data is not updated at individual sites. Instead, information is prepared at a central staging area and pushed to distributed database servers for remote access. For this reason, snapshot replication is often used to distribute data.
As its name implies, snapshot replication takes a picture, or snapshot, of the published data in the database at one moment in time. Instead of copying INSERT, UPDATE, and DELETE statements (characteristic of transactional replication) or data modifications (characteristic of merge replication), Subscribers are updated by a total refresh of the data set. Hence, snapshot replication sends all the data to the Subscriber instead of sending the changes only. If the information being sent is extremely large, it can require substantial network resources to transmit. In deciding if snapshot replication is appropriate, you must balance the size of the entire data set against the volatility of the data.
Snapshot replication is the simplest type of replication, and it guarantees latent consistency between the Publisher and Subscriber. It also provides high autonomy if Subscribers do not update the data. Snapshot replication is a good solution for read-only Subscribers that do not require the most recent data and can be totally disconnected from the network when updates are not occurring. However, SQL Server provides a full range of choices for replication depending on application requirements.
For more information about replication in SQL Server 7.0, see SQL Server Books Online.