Why Use Database Replication?

Imagine that a client has asked you to develop a contact-management application that the company’s field sales staff can use to monitor sales and orders. Each sales representative has a laptop computer that can be connected to the company’s network.

A traditional approach to building this application is to separate the tables from the other objects in the database so that the data can reside in a back-end database on a network server, or on the Internet or an intranet, while the queries, forms, reports, macros, and modules reside in a separate front-end database on the user’s computer. The objects in the front-end database are based on tables that are linked to the back-end database. When sales representatives want to retrieve or update information in the database, they use the front-end database.

Database replication enables you to take a new approach to building this application by creating a single database that contains both the data and objects, and then making replicas of the database for each sales representative. You can make replicas for each user and synchronize each replica with the Design Master on a network server. Sales representatives update the replicas on their computers during the course of a work session, and users synchronize their replicas with the Design Master on the server as needed.

In addition, you can choose to replicate only a portion of the data in the Design Master, and you can replicate different portions for different users by creating partial replicas. In the scenario involving sales representatives using replica databases, each individual salesperson typically needs only the sales data related to his or her own territory. Replicating all sales data for all sales representatives would involve unnecessary processing and duplication of data. By using partial replicas, you can duplicate only the data that each sales person actually needs. A complete set of data is still contained in the Design Master, but each replica handles only a subset of that data.

Database replication is well suited to business applications that need to:

• Share data among remote offices You can use database replication to create copies of a corporate database to send to each satellite office across a wide area network (WAN). Each location enters data in its replica, and all remote replicas are synchronized with the replica at corporate headquarters. Individual replicas can maintain local tables that contain information not included in the other replicas in the set.
  
  
• Share data among dispersed users New information that is entered in the database while sales representatives are out of the office can be synchronized any time the sales representatives establish an electronic link with the corporate network. As part of their workday routine, sales representatives can dial in to the network, synchronize the replica, and work on the most current version of the database. Because only the incremental changes are transmitted during synchronization, the time and expense of keeping up-to-date information are minimized. By using partial replicas, you can synchronize only specified parts of the data.
  
  
• Make server data more accessible If your application doesn’t need to have immediate updates to data, you can use database replication to reduce the network load on your primary server. Introducing a second server with its own copy of the database improves response time. You determine the schedule for synchronizing the replicas and can adjust that schedule to meet the changing needs of your users. Replication requires less centralized administration of the database while offering greater access to centralized data.
  
  
• Distribute application updates When you replicate your application, you automatically replicate not only the data in your tables, but also your application’s objects. If you make changes to the design of the database, the changes are transmitted during the next synchronization; you don’t have to distribute complete new versions of the software.
  
  
• Back up data At first glance, database replication might appear to be very similar to copying a database. However, while replication initially makes a complete copy of the database, thereafter it simply synchronizes that replica’s objects with the original objects at regular intervals. This copy can be used to recover data if the original database is destroyed. Furthermore, users at any replica can continue to access the database during the entire backup process.
  
  
• Internet or intranet replication You can configure an Internet or intranet server to be used as a hub for propagating changes to participating replicas.

Although database replication can solve many of the problems inherent in distributed-database processing, it is important to recognize the situations in which replication is less than ideal. You may not want to use replication if:

• There are a large numbers of record updates at multiple replicas Applications that require frequent updates of existing records in different replicas are likely to have more record conflicts than applications that simply insert new records in a database. Record conflicts occur when any changes are made to the same record by users at different locations at the same time. Applications with many record conflicts require more administrative time because the conflicts must be resolved manually. This is true even if different fields are updated within the same record.
  
  
• Data consistency is critical at all times Applications that rely on information being correct at all times, such as funds transfer, airline reservations, and the tracking of package shipments, usually use a transaction method. While transactions can be processed within a replica, there is no support for processing transactions across replicas. The information exchanged between replicas during synchronization is the result of the transaction, not the transaction itself.

See Also For more information about replication, see the Microsoft Access and Microsoft Replication Manager Help files, and see the white paper “Database Replication in Microsoft Jet,” located in the Papers folder on the companion CD-ROM.