How Replication Works

Access lets you replicate multiple copies of a database over a LAN, WAN, intranet, extranet, or the Internet. Users can modify their individual copies as their needs dictate. The connection between copies can be inoperable for long periods. You can also require that changes to any copy be replicated in each other copy.

Access calls the collective set of copies a replica set. (You are not restricted to working with a single replica set.) Each copy in the set is a replica. One copy within a replica set must function as a design master. The design master differs from the other replicas in that it can transfer structural changes (such as new forms and reports) and data changes to other members of the replica set. (Other products that offer database replication support data exchange between replicas but do not offer the ability to propagate structural changes across a replica set.)

Database replication occurs between pairs of replicas. Changes propagate throughout a replica set as replicas synchronize with each other. Each replica in a pair can send its changes to the other. If these changes do not conflict, the replicas update themselves with the package of changes they receive from each other. If some changes conflict because both replicas change the same content, one change wins and the other loses. Access saves the losing change in a conflict table. You can use this table to manually resolve conflicts, or your application can apply automatic rules for processing the conflicts. Access has a built-in Conflict Resolution wizard; you can also build your own wizard to replace or supplement it.

Replication errors can occur when a change is valid in a local replica but is invalid in one or more other replicas within a set—for example, if you enter a record with the same primary key in two separate copies in a replica set. When you synchronize the two copies, the Jet database engine rejects the record with the duplicate primary key from the other replica. Other conditions can generate replication errors, such as the introduction of a field validation rule in one replica that creates invalid data in another replica.

Replicas can diverge from one another in one of two ways. First, they might need to complete a synchronization cycle in which all the replicas exchange data with one another. You can remedy this by completing the synchronizations among replica set members. Replicas can also diverge from one another because of replication conflicts and errors—even if they are completely synchronized.

You can replicate databases in five ways:

• Using the Briefcase icon
• Using the Replication command
• Using JRO (or DAO) programming
• Using the Replication Manager
• Using Internet synchronization

Using the Briefcase Icon

Using the Briefcase icon, end users can take a copy of a database off site and make updates to it. When they return it, they can synchronize the replica on a laptop with the one on their desktop computer or department computer. Developers can use Briefcase replication to place a design master on their laptop and build custom forms and reports. Later, they can synchronize the new database objects with the production version.

The Microsoft Briefcase icon is available on the Microsoft Windows desktop in Windows 9x and Microsoft Windows NT 4. (The Briefcase might not be installed on your Windows 9x computer. To install Briefcase, double-click the Add/Remove programs option in Control Panel. Click the Windows Setup tab of the Add/Remove Programs Properties dialog box, select Accessories, and click the Details button. Then, in the Accessories dialog box, select Briefcase, click OK, and follow the prompts.) Dragging an .mdb file from Windows Explorer to the Briefcase icon converts a database from a standard format to one equipped with special tables and fields that support replication. (With other Microsoft Office applications, the Briefcase merely makes copies of whole files without any synchronization between copies.) The Briefcase reconciler leaves the updated original as a design master at the source and places a replica in the Briefcase. You can alter this if you need a design master in the Briefcase.

NOTE

---

When the Briefcase reconciler creates a replica set, it asks whether you want a backup version of the original database. Unless you are sure that you won't need to return to the original, you should accept this option because the conversion adds many new tables and fields. There is no simple, automatic way to remove these with built-in Access tools. At least one third-party source (www.trigeminal.com) offers a utility that strips the replication system fields from user tables.

Using the Replication Command

You choose Tools-Replication to access the commands that make a database replicable, create more replicas, synchronize replicas, reconcile conflicts, and prototype test versions of a replication application. You can create a Prevent Deletes replica (a new feature) only through the Tools menu (although you can control it programmatically after you create it).

To reconcile replication conflicts and errors, you probably have to manually review individual exceptions that appear in the conflict tables. Even if you ultimately program custom reconciliation rules, you probably have to evaluate the rules manually before adopting them for production use.

Using JRO Programming

JRO programming is fundamentally an ADO approach because it relies on ADO connection objects. While ADO is a universal data access technology, the JRO extension functions exclusively with the Jet database engine. If you programmed custom replication solutions in Access 95 or Access 97, now is the time to transition from DAO to JRO programming.

The JRO model supports three general tasks:

• Creating and managing replica sets
• Compacting and encrypting databases
• Refreshing the memory cache to improve apparent performance

The three main objects in the JRO model are shown in Figure 11-1. The first is the Jet Engine object, which supports features specific to the Jet database engine, including compacting and encrypting databases and refreshing the memory cache.

Figure 11-1. The JRO model.

The second main object is the Replica object, which represents a copy of a database. Replica objects are the basic building block of a replica set. You can manage a replica set by manipulating the properties and methods of the Replica objects. Among the functions that you can administer are

• Making a database replicable
• Creating replicas from the design master and other replicas
• Synchronizing replicas
• Reading the replicability of individual objects
• Assigning a new design master
• Managing the retention period for replication history

The JRO model also includes properties for managing many new and revised features, such as visibility, replica type, and priority-based conflict resolution. The third main object is the Filter object. You use this with partial replicas to restrict the contents of a database copy. You can base a filter on a table or a relationship. JRO has a Filters collection for all the filters in a replica. These collectively limit the data that can enter into a partial replica.

Using the Replication Manager

The Replication Manager, which ships with the Office Developer edition, helps you administer a replica set over a network, which can include LAN and WAN connections. In its initial release with Access 97, the Replication Manager supported Internet synchronization via FTP. The new Internet synchronization with Access 2000 has rendered this feature obsolete. However, you must still use the Replication Manager with the version of Internet synchronization in Access 2000.

You use the Replication Manager to make occasional connections to a network or for indirect synchronization, in which changes from one replica to another can go to a drop box if the receiving replica is not open. Later, when the receiving database opens, the Synchronizer agent that the Replication Manager controls passes along the updates. The Replication Manager also offers a graphical user interface for scheduling periodic updates. You must configure the Replication Manager for your server.

You can also use the Replication Manager to coordinate replicas across a WAN. It offers a graphical depiction of your replica set's topology. One common design is a star topology in which a hub replica exchanges data with a related set of spoke replicas that each connects with just the central replica. A fully connected topology links each replica directly with all other replicas. This allows for much faster transfer of data updates throughout the replica set at the expense of increased network traffic. Other typologies offer different design/performance tradeoffs.

Using Internet Synchronization

Using Internet synchronization, you can replicate databases across a World Wide Web (FTP or HTTP 1.1) connection. The connection can be across the Internet, an intranet, or an extranet, and the computers need not be connected all the time. Unlike the indirect synchronization supported by the Replication Manager, Internet synchronization does not require a Synchronizer agent running on the client computer. Furthermore, Internet synchronization can work with anonymous replicas. This is one of the replica Visibility property settings introduced with Access 2000. (See the white paper titled "Internet Synchronization with Microsoft Jet 4.0" at http://support.microsoft.com for details on setting up, administering, and testing an Internet synchronization system.)

As mentioned earlier, Internet synchronization with Access 2000 has made the Access 97 version obsolete. Four main enhancements differentiate Internet synchronization with Access 2000 from its predecessor:

• Support for replication over an HTTP 1.1 protocol connection (Support for Netscape servers is available via this protocol.)
• Performance enhancements relating to encryption
• Support for a new lightweight replica tailored for Web-based replication
• New registry keys to fine-tune synchronizer timeouts

When you select a web server with which to synchronize, Access can automatically determine whether to use HTTP or FTP. Besides permitting operation on Netscape servers, the HTTP protocol lets a replica synchronize from behind a properly configured proxy server to a synchronizer on the Internet. Access 2000 does not explicitly support the reverse configuration. If your originating replica is not encrypted, Internet synchronization does not automatically encrypt the updates that it sends to the synchronizing target replica. The initial version of Internet replication automatically encrypted all updates for transfer over the Web. The new, lightweight anonymous replicas can only replicate with their parent replica on a web server. You must manage that parent replica on the web server using the Replication Manager.

Replication Design Changes

When you make a database replicable by any of the means discussed above, you typically add a collection of system tables as well as a set of fields to each table. These tables and fields help manage the replication project. They can also add substantially to the size of a database. Beyond that, the special replication tables and fields consume resources that slightly lower the maximum number of custom tables per database, the maximum number of custom fields per table, and the number of bytes per record available for custom uses in your database application. Understanding these design changes will help you manage Access replication projects.

NOTE

---

To view most of the special replication fields and tables, choose Options from the Tools menu. Select the System Objects check box, and then click OK.

Replication system fields

Tables in a replication application typically gain four new fields: s_GUID, s_Generation, s_Lineage, and s_Collineage. The s_GUID field uniquely identifies each row in each table in the replica set. The same row in the same table in two different replicas will have different GUID (Globally Unique ID) values. Access constructs the 16-byte GUID strings to be globally unique.

When you design tables for a replication application, you can use an autogenerated GUID as the primary key for a database. If a GUID serves as the primary key field for a table, Access does not create the s_GUID field when you make a table replicable. Instead, it uses the primary key to serve the same purpose. To use a GUID as a primary key, select Replication ID as the FieldSize property for a field with Number or AutoNumber as the data type.

The s_Generation field tracks the generation of a change to a table. This field has a Long Integer data type. A value of 0 represents a new change that unconditionally requires replication. After initially propagating a change to another replica, Access updates the field value so it represents the highest generation of change. The replication process keeps track of the last generation sent to each replica from each replica. When a new exchange commences, Access resumes with the next highest generation of change from the last synchronization with a replica.

The s_Lineage field tracks the history of changes to each row in a table. The field has an OLE Object data type. The field specifies when a row is sent to another replica. It eliminates the possibility of repetitively sending the same changes to another replica.

The s_Collineage field, which has an OLE Object data type, supports column-level replication. This feature is new with Access 2000. Prior versions of Access used row-level replication. (The upcoming section titled "What's New in Access 2000 Replication" explores this further.) The s_Collineage field tracks information that enables the detection of changes at the column level during synchronization.

Each field with the Memo or OLE Object data type also receives a separate generation field. Since such fields can be especially large, they do not necessarily propagate from one replica to another when a field in a row changes value. These fields propagate between replicas only when they actually change value. Their individual generation fields track this for the replication process. A replicable version of the Northwind Category table has one special generation field for the category pictures, but the Employees table has two special generation fields—one each for the Note and Photo fields.

Replication system tables

A number of system tables support the behavior of a replica set, as shown in the table below. Some of these tables, such as MSysTableGuids, can be sparse and basic. MSysTableGuids stores GUIDs for each table name in a replica (excluding the replication system tables and the special hidden conflict tables). Some tables use the GUIDs denoted in MSysTableGuids to identify specific tables in a replica set. Other tables store information about historical or pending operations. The MSysTombstone table stores information on deleted records. The built-in replication logic uses this table to delete records in receiving replicas during synchronization.

Replication System Tables

Table	Description
MSysConflicts	Tracks all conflicts. Replicated to all members of the replica set.
MSysExchangeLog	Tracks synchronization information between a replica and all other members in the replica set.
MSysGenHistory	Stores information about each generation of synchronization. Avoids resending old generations to active replica members and updating replicas restored from backup copies.
MSysOthersHistory	Stores information about generations of updates from other replica members.
MSysRepInfo	Contains a single record with details relevant to the whole replica set. Replicated across the members of the replica set.
MSysReplicas	Stores information about all replicas in a replica set.
MSysSchChange	Stores information about changes to the design master replica for dissemination to other replica set members.
MSysSchemaProb	Stores information about conflicts between replica set members. If there are no unresolved conflicts this table does not appear.
MSysSchedule	Used by the Local Synchronizer agent to manage timing of synchronizations with other replicas.
MSysSideTable	Contains detailed conflict information.
MSysTableGuids	Relates table names to GUIDs. Other replication system tables use these GUIDs.
MSysTombstone	Stores history of deleted records and supports delete updates throughout the replica set.
MSysTranspAddress	Stores information about synchronizers that manage replicas in a replica set.
MSysContents	Stores information about rows for partial replicas. Appears only in partial replicas.
MSysFilters	Stores information about filters for partial replicas. Appears only in partial replicas.

The MSysConflicts table references a set of nonsystem but hidden tables that store details about individual conflicts and errors for each table. The naming convention for these hidden tables is UserTableName_Conflict. For example, if there are one or more conflicts with the information between two replicas for the Employees tables, the replica that loses a conflict has a hidden table named Employees_Conflict. The rows of the tables document the losing record and contain a recommendation about how to proceed. When users resolve conflicts through the built-in logic, Access depends on and manages these tables. If you build custom conflict resolution logic, your custom solutions must also manage these tables. For example, after all the conflicts for a table are resolved, your application should remove the UserTableName_Conflict table. This removes the corresponding row from the MSysConflicts table.

The replication fields and tables place additional constraints on the design of your Access applications. For example, Access permits an upper limit of 255 fields per table. However, replications typically add four fields (s_GUID, s_Generation,s_Lineage, and s_Collineage) plus one additional field for each Memo and OLE Object field in a table. As you plan the fields for a table, you must leave room for the special replication fields. The same type of considerations applies to the maximum byte count per record. See the white paper titled "Database Replication in Microsoft Jet 4.0" at http://support.microsoft.com for guidance on these and other advanced replication design matters.

Backing up and restoring the original database

You must carefully consider whether you want to make a file replicable because there is no built-in feature for restoring your original database. For this reason, you might want to back up the database file before making it replicable.

If you know the names of the replication tables and fields, you can reconstruct a new nonreplicable database file with the current information from any replica within a replica set. You must append tables from the selected replica to the new database copy. You append only the user fields, not any of the special replication fields. You can import all the tables instead (except, of course, the special replication tables) and then run delete queries to remove the special replication fields. You add the relationships between tables and then import the other database objects.