A few years ago, database replication was a high-end capability that few companies considered. Now, more organizations are interested in database replication for two main reasons: Businesses are becoming more geographically dispersed, yet employees still need access to a single set of coherent data; and many companies are working with massive data sets that they can't quickly or easily restore in the event of a system failure.
Database replication is different from file replication, which essentially copies files. Database-replication products log selected database transactions to a set of internal replication-management tables. The software then periodically checks these tables for updated data and moves the data from the source to the target systems while guaranteeing data coherency and consistency.
Database replication helps handle many of the problems you can encounter with distributed systems. Replicating databases to systems in branch offices lets branch-office users access a local copy of the data instead of accessing a central server over WAN links. Database-replication products also let you transfer selected data sets to a reporting server so that you can move your processor-intensive—reporting processes off of your main transactional database.
In addition, database replication can supplement your disaster-recovery plans by duplicating the data from a local database server to a remote database server. If the primary server fails, your applications can switch to the replicated copy of the data and continue operations. Many database-replication products even have built-in tools to let you update the primary database with any changes that users made to the backup database while the primary database was offline.
SQL Server includes three methods of database replication that you can use with other SQL Server systems: snapshot, transactional, and merge. As its name implies, snapshot replication is a point-in-time copy of the data. Transactional replication provides a near-realtime copy of individual database transactions. Merge replication lets you replicate updates from one or more systems to target systems at a later time. Although SQL Server's built-in replication methods work well between SQL Server systems, they're limited when the source or target database system is, say, Oracle8i or IBM DB2. For example, SQL Server's built-in replication supports only snapshot and one-way transactional replication to Oracle systems—it won't perform merge replication to an Oracle system. To replicate to DB2 systems, you must use Host Integration Server (HIS).
Third-party database-replication products specialize in heterogeneous replication and offer homogeneous replication capabilities that are similar to SQL Server's internal replication capabilities. Some products even offer additional features. When comparing database-replication products, you need to ask many questions. Which database platforms does the product support? What types of replication does the product provide (e.g., snapshot, near realtime, bidirectional)? Does the product support scheduling replications? What mechanisms trigger replication? Is the replication process program controlled? What database object types can you replicate? Does the product support data transformations? And does the product support computing new fields in replicated tables?
In addition to having the basic product features that you need, the database-replication product you choose must be compatible with your application environment. Some database-replication products might require system changes that might make your database incompatible with your applications. For example, some products might require changes to the database schema that could result in application errors.
Database-replication products can provide a powerful mechanism to distribute your data and to maintain backups that you can use if a disaster occurs. However, you must test and evaluate database-replication products inside your environment because each environment is different and might react in ways that you don't expect. To get started in your search, take a look at the Buyer's Guide. This list summarizes third-party database-replication products that support SQL Server. Note that this list doesn't contain every available product. We've included the product information that vendors submitted to us by our deadline.
