Presented by: Tony Poll
Microsoft Corporation
Redmond, WA
Published: November 1996.
For the latest information, see http://www.microsoft.com/officedev/
The Microsoft® Jet database engine version 3.5 is a 32-bit engine that provides database processing and replication functionality to a variety of applications. This document is intended for experienced Microsoft Access users who want to understand database replication as implemented in Microsoft Jet and use it more effectively in their applications.
Database replication is a technique you can use to support multiple users of an application. Replication is the process of creating multiple copies of an application and its data, to be used at locations that are not always connected to each other. Collectively, the copies are called a replica set. One member of the replica set must be designated as the Design Master; any other copy is a replica. Each replica contains a common set of replicated objects. Any single replica can also contain local objects that exist only in that replica.
A replica set can contain only one Design Master, but can contain as many replicas as needed. The Design Master is the only copy where changes to the database design are allowed. You can designate any replica as the Design Master, but be sure that only one replica is marked as the Design Master at any time. To create a Design Master, you convert an existing database to replicable format and set certain properties to identify it as the Design Master. Some of the replication tools available do this for you automatically.
A database in replicable format includes a number of fields and tables that Microsoft Jet uses to manage the replicated application. For example, one field in each row in a replicated table has a globally unique identifier (GUID) that distinguishes the row from every other row in the replica set. Another field records the lineage (replica ID/version pair) of each row. When a record is updated, the version number for that replica's record in the lineage is incremented.
Any copy-the Design Master or a replica-can update the data. This is called a multi-master data update design; it permits a fully distributed system where data updates are not centralized. This is a significant benefit of using Microsoft Jet, because most other relational database engines support single-master data updates where data can only be updated at a single replica. A row is the most basic unit of information recognized in replication. This means that if any cell in a row is modified then the whole row is marked as changed. When changes are transmitted during synchronization, the whole row is updated. OLE objects and memo fields are exceptions; these items-due to their potential large size-are not transmitted unless they have been changed.
Synchronization, an important part of the replication process, reconciles all these changes and updates the data set in each replica. The file tracking system in Microsoft Jet tracks and records all changes at all replicas, in preparation for updating data during synchronization. Only rows marked as changed are updated when you synchronize replicas. If two replicas simultaneously update the same record at different replicas, Microsoft Jet reconciles the updates. Synchronization can be performed on a regular schedule or as often as necessary to ensure all users have current data. This means that all information will reach all replicas, but there is no guarantee it will reach all replicas within any specified amount of time. When using Microsoft Jet, application designers must allow for this in their designs.
Microsoft Jet uses incremental replication. This means that, during a single synchronization between two replicas, the only updates made are those resulting from changes made since the last synchronization. This provides significant benefits over methods of data distribution that transmit the whole database whenever new data or objects require distribution. Each row in a replicable database has a generation counter; Microsoft Jet uses this field to control incremental exchanges. Microsoft Jet also records the maximum generation in the local database.
Not all applications that support Microsoft Jet use its features in the same way. For example, although Microsoft Excel cannot replicate a database, it can update a database replicated by another product. Microsoft Jet monitors the changes made by Microsoft Excel, Microsoft Visual Basic®, or Microsoft Access to a replica, and updates these changes when you synchronize the replicas.
Microsoft® Windows® 95 and Microsoft Windows NT® Intel platforms support the Microsoft Jet database engine. These products support Microsoft Jet either directly or through Microsoft Data Access Objects (DAO):
In addition, Microsoft Windows NT, Novell® NetWare®, and Windows 95 peer-to-peer networks support Microsoft Jet. Banyan® Vines and LANtastic® do not support the Microsoft Jet Database. Microsoft Jet includes replication code that monitors whether a replica has been copied or moved to a new network location, and determines whether a replica requires a new ReplicaID. Microsoft Jet presumes that files are named in accordance with Microsoft and Novell network file-naming conventions, which are different from the file-naming conventions used by Banyan and LANtastic.
Replication is a feature of the Jet database engine, not of the specific applications that include the Jet database engine. Microsoft Jet performs all database processing for Microsoft Access and Visual Basic, and can provide data to Open Database Connectivity (ODBC) client applications using the Microsoft Access 2.0 ODBC driver.
Replication is well suited for distributed systems that focus primarily on adding new records rather than on updating existing records. Sales representatives who visit customer offices, parcel delivery drivers, and inspectors who visit a variety of construction sites are all examples of users who might benefit from replication. There are many tools and techniques for implementing replication. Some factors to consider when choosing a replication technique are:
The best candidates for replication are applications that can tolerate some latency in data updates in exchange for a robust configuration that can allow updates from any replica and that supports users who are only occasionally connected. This flexibility means the system can work more effectively, potentially improving business performance. Using flexible, low-cost, off-peak asynchronous communication links and asynchronous data duplication provides "real-time-enough" updates without the expense and vulnerability of full-time connection between all nodes. When the application's users are connected, it might be via a direct connection on a local area network (LAN) or wide area network (WAN), or via the Internet. Data can be exchanged on a LAN, a WAN, or the Internet.
Microsoft Jet replication is a good solution if you want to:
If your multiuser application requires very frequent data updates, if it will update a large number of records at one or more sites, or if it is critical for data changes to be very quickly obvious to other users, replication may not be the best solution for you to use. Applications in these categories are better served by two-phase commit solutions. A "two-phase commit" is where replicas are connected all the time, and an update at any one site will only be accepted if agreement is immediately given from all other sites. It's called a two-phase commit because the initial phase is notification of a proposed update sent to all replicas, and the second phase is the actual update only when all sites have agreed (that is, committed) to the update.
There are several tools you can use to implement Microsoft Jet replication. These tools allow you to convert a database to replicable format, identify a replicable database as the Design Master or a replica, initiate synchronization of the replica set, and a variety of other management tasks. You can use the following tools to implement Microsoft Jet replication:
The first three of these tools provide an easy-to-use visual interface, while the last enables programmers to build replication directly into their applications.
Microsoft Access Replication Commands
The Replication submenu on the Tools menu in Microsoft Access provides several commands to help you create a replica, synchronize a replica with another member of the replica set, resolve synchronization conflicts, and recover a replica set's Design Master. For more information about these commands, refer to the Help system provided with Microsoft Access or to Building Applications with Microsoft Access 97.
Briefcase Replication
The Briefcase is an accessory available in Windows 95 or Windows NT version 4.0. When Microsoft Access is installed on your computer, you can use the Briefcase as a replication tool by simply dragging an MDB file from the Windows Explorer onto the Briefcase icon on the Windows desktop. Like magic, your database is converted into replicable format and becomes a member of your replica set. The Briefcase menus include commands to synchronize the replicas.
Behind the scenes, of course, Windows is busy making the magic work. When you install Microsoft Access, the Setup program adds Class ID (CLSID) entries for MDB files and for the Briefcase reconciler to the Windows registry. (The Briefcase reconciler is installed only by Microsoft Access; it is not included with Visual Basic or Microsoft Excel.) The reconciler includes the code required to support replication and synchronization. When you drag an MDB file onto the My Briefcase icon, Windows recognizes the class ID and responds by calling the reconciler. The reconciler converts the database into a replicable form, leaves the Design Master at the source, and places a replica in the Briefcase. The reconciler gives you the option of putting the Design Master in your Briefcase and leaving a replica on the desktop. When you synchronize the replicas, the Briefcase calls the reconciler to merge the replicas. With Briefcase replication, synchronization cannot be scheduled; it occurs only when the Update command is clicked and only between the current member and the specified member.
Note Before converting the database, Microsoft Jet asks if you want to make a backup. If you anticipate that any users will need to use a nonreplicable version of the database, it's a good idea to make this backup.
You can use the Briefcase with files other than MDB files, and with applications other than Microsoft Access. However, doing so will not call the Microsoft Jet replication code; it will call the default Briefcase code instead. If you use the Briefcase when Microsoft Access is not installed or with a non-Microsoft Jet database, dragging a file into the Briefcase is equivalent to simply copying the file into the Briefcase-there is no conversion to replicable format. Therefore, when you update files on your main computer with files from the Briefcase, the Briefcase simply copies over the original file-changes to data and objects are not merged, they are overwritten.
Microsoft Replication Manager
Microsoft Replication Manager is included with Microsoft Office 97, Developer Edition. It is another tool that lets you use replication to administer a distributed application. It offers more functionality and more features than the Briefcase. Key features include:
Synchronizer
The Synchronizer is an agent you can use with the Replication Manager to provide scheduled background exchanges between replicas. These exchanges can be made while two replicas are directly connected, or through a file-system transport that does not require a direct connection for the exchange. In either type of transfer, the Synchronizer collects the changes at one replica and transmits them to other replicas. If the file-transfer system is used, one replica must deposit changes in a temporary file. The Synchronizer, initiated from the target replica, collects the updates at a later time and applies them to the target replica. This is a great benefit for rarely connected users; they can post changes whenever convenient rather than depending on an available connection.
The file-system transport provides an interface to the messaging service, with provision for additional messaging services in later releases.
Data Access Objects (DAO)
In Microsoft Access and Microsoft Visual Basic, DAO provides a programmatic interface to replication functions. Microsoft Jet includes a variety of extensions to the DAO programming interface. These extensions allow developers to convert a database to replicable format, make additional replicas, synchronize replicas, and manage certain properties of a replicated database. These properties include the description of a single replica or of a replica set, the ID of a particular replica or of the replica set's Design Master, the default replica to be used in an exchange, and the local/global property of each object in the database.
Before you can use replication, you must convert the original database to replicable format. Any of the tools listed earlier can help you do this. A database in replicable format includes a number of tables and fields that are not typically present in a nonreplicable database. When you use the tools listed earlier, Microsoft Jet automatically adds the fields and tables it needs to manage your replicated application. A database in replicable format includes:
Additional columns are inserted in the table where there are OLE links to embedded graphics or memo fields. This is an exception to the rule that data changes are tracked on a row level. Because OLE objects may be of a significant size, (a bit image may easily be 1 MB or more) and therefore expensive to send over a communications line, Microsoft Jet replication only sends the OLE object if it has been modified.
System Tables
System tables support code within Microsoft Jet. You should not rely upon the format remaining the same between releases. Treat the descriptions of these tables as "for information only," which may assist you in debugging certain applications.
GUIDs
Just as a fingerprints distinguish one person from all other people, every object in a set of data must have a unique identifier to distinguish it from all other objects. This identifier is called a GUID (globally unique identifier) or universally unique identifier (UUID). Within a database, the primary key serves a similar purpose; it ensures every record in a table has a unique identifier.
When you replicate a database, Microsoft Jet adds several fields to each table in the database. One of those fields, s_GUID, contains a GUID that uniquely identifies a single record. A record, or row, in a database, is the smallest unit of information that replication tools will manage. If you change information in a replicated database, the entire row is marked as changed, and the entire row will be updated when you synchronize the data in the table.
You can use the s_GUID field as the primary key in the database. The advantage of doing so is that it virtually eliminates the possibility of duplicate keys; the potential disadvantage is that the GUID field does not convey any meaning to the user.
If you choose AutoNumber or Number as the data type for a field, you can select Replication ID as the setting for the FieldSize property. The result is that a GUID is assigned for the row and stored in the field. If a table already includes a field with a GUID, the s_GUID field is not added when you replicate the table. Microsoft Jet uses the existing GUID instead.
GUID Generation
The question "How do I know a GUID is really unique?" is a common one. The process of generating a GUID includes numerous checks to ensure its uniqueness. GUIDs are created from:
Here is a sample GUID:
2fac1234-31f8-11b4-a222-08002b34c003
The hyphens make it easier to read and are used only when the GUID is displayed; they are not part of the GUID. A GUID is derived from the following components:
A GUID includes the following fields:
<time_low>-<time_mid>-<time_hi_and_version>-<clock_seq_hi_and_reserved>-<clock_seq_low>-<node>
where:
If a network card is not installed, the node ID is set to a 48-bit number (a 47-bit random number plus 1 bit for local usage). This number is not guaranteed to be unique, even on the generating machine, but is unlikely to be duplicated on another machine. However, because GUIDs are time + sequence this is a reasonable approximation for a local GUID. The node ID returned is explicitly made into a multicast IEEE 802 address so that it will not conflict with a "real" IEEE 802 based node address. The LocalOnly bit contains 1 if the address was cooked up, 0 if it's a real IEEE 802 address. The 48-bit number will be composed of:
GUID Usage
There can be more than one column with coltypeGUID in a table, but there can be only one autogenerated column of coltypeGUID in a table, regardless of whether the table is replicable.
Autogenerated GUID columns are identified by the coltypeGUID and
-or-
If an autogenerated GUID column is added to a table, GUID values are generated for all rows in the table. The value of an autogenerated GUID cannot be changed or deleted.
Generations
When you convert a table to replicable format, Microsoft Jet adds a new field, s_Generation, to every replicable table in the replicated database.
The s_Generation field controls which records are sent during an exchange. When a record is modified, its generation is set to zero (0). In general, all records with generation 0 are sent during an exchange, and then the generation for the record is incremented to one more than the last generation, which now becomes the new highest generation.
When an exchange occurs, the sending replica knows the last generation sent to that specific receiving replica. Only records with generations higher than the previous generations or generation 0 are sent.
The receiving replica will not apply generations received out of sequence.
In some cases Microsoft Jet replication may determine that there are too many records to be sent in a single exchange message. In these situations, the first set of records for the exchange will be of one generation and the following sets of records will be of higher generations. Therefore, it is possible that a single exchange may contain records with different generations.
Generally there is one generation field per record. To optimize exchanges for databases that contain Memo or OLE Object fields (sometimes referred to as a BLOB, or binary large object) an extra generation field is associated with each BLOB. This generation value is set to 0, ensuring it is sent during the next exchange, only if the BLOB is modified. If other fields in a record are modified, but not the BLOB field, then the BLOB generation is not set to 0 and the BLOB is not sent with the exchange.
Lineage
The s_Lineage field is added to every table in the replicated database.
The lineage is used to determine which replicas have already received an update and also to determine the winner when conflicts occur. The lineage consists of a series of entries representing each replica that has changed this row. Each lineage entry consists of a shortened form (2 bytes) of the replica ID and a version number (2 bytes). The shortened version of the replica ID is the replica's nickname. The version number starts at 1 and is incremented each time the record is modified.
Design Considerations
Microsoft Jet enforces a 2048-byte and 255-field limit to any record. These limits include fields and data you add to tables yourself as well as any fields and data Microsoft Jet adds in the course of replication. When you design tables in an application you plan to replicate, make sure you allow for the fields Microsoft Jet is likely to add.
At a minimum, each record will receive GUID, s_Lineage and s_Generation fields, which collectively require 24 bytes per record. This means you should design tables with no more than 252 fields (255 maximum, minus 3) and 2,024 bytes (2,048 maximum, minus 24). However, this is not generally a hindrance to good design; very few well-designed applications use either the maximum allowable fields or bytes in a single record.
If your application uses long binary fields, replication will add an additional 4-byte field per long binary field. This is the result of an exchange optimization, whereby only long binary fields that have been modified are sent in an exchange.
Data Updates
The Microsoft Jet database engine tracks all data updates in the database. This tracking occurs only in replicated databases, and does not impact the performance of the Microsoft Jet engine for nonreplicated databases.
In general, updates are marked on a per-row basis. That is, when a row in a table is updated, the whole row is marked as changed. At the same time, the s_Generation field is set to zero and the version number in the s_Lineage field is incremented.
OLE Object and Memo fields are an exception to this rule. OLE Object and Memo fields are marked separately from the rest of the row, and are sent with the rest of the row only if the OLE Object or Memo field has changed. This is because OLE Object and Memo fields can be large, and therefore expensive and time-consuming to send over a communication link. Therefore, Microsoft Jet doesn't send them unless it is necessary.
Design Updates
Design changes must be made at the Design Master and then distributed to all other replicas.
In the rare event the Design Master is lost, you can designate any other replica as the new Design Master. This action should only be taken when you are certain that the original Design Master will never return, because a replica set with two Design Masters typically corrupts the whole database because conflicting design updates leave the database in an undefined condition.
If you need to make design changes in a replica that is not currently the Design Master, you can transfer the Design Master designation from the current Design Master to that replica.
There are situations where it is desirable to have private objects in the database that are not known to all users in the replica set. For example, one user might have a particular query or report of no interest to other users, or the application designer might want to work on a new feature privately until it is ready to distribute to other users. Local objects can be created at any replica for cases like these. Microsoft Jet ignores local objects during synchronization. If you decide that a local object should actually be part of the replicated application, add it to the Design Master and designate it as a global object by opening its Properties box and selecting the Replicable attribute. At the next exchange, the object will be distributed amongst the other replicas in the replica set.
Note If a local object of the same name already exists in a replica, Microsoft Jet will change its name to OriginalName_Local.
Synchronization is the process of exchanging information between two replicas about data and design changes in the replicated application. Updating an entire replica set is a series of synchronizations between pairs of replicas. A replica can behave in one of three ways during an exchange:
In a push (send-only) indirect exchange, the local replica collects any design changes it has not previously sent to the particular remote replica, and attempts to bundle these changes into a "message" before sending them. (The size of the message may be restricted by physical factors). If there are any generation-zero rows, the generation counter at the local replica is incremented. The changes sent are the combination of all generations larger than the last generation sent, plus any new generations, which are recognized as having a generation of zero. Rows of generation zero are updated with the new, incremented generation number.
If an exchange gets lost between the remote and local replicas, Microsoft Jet's error-correcting protocol rejects the new message and requests a resend of the lost generations.
When running the Synchronizer to schedule exchanges between replicas, you may find it advantageous to disable the Windows 95 System Agents (such as Start, Programs, Accessories, and System Tools). Disk compression and defragmenting can make heavy demands upon your PC that prevent scheduled replication exchanges from completing in a timely fashion. To halt these System Agent tasks, right-click the System Agent icon in the Windows 95 taskbar and click Suspend System Agent.
Internet Synchronization
Synchronization over the Internet is a new feature in Microsoft Jet 3.5/Microsoft Access. When a replica and Replication Manager are both on an Internet server, users can synchronize using a standard HTTP connection. Note, however, that you cannot schedule synchronization over the Internet with Replication Manager, because Replication Manager cannot control the communication link to your Internet service provider. As an alternative, you can write Visual Basic for Applications code to create the link to your Internet service provider, execute a synchronization, and then drop the communications link.
How Internet Synchronization Works
You need the following three things to use synchronization over the Internet: a client PC with a replica (R1), an Internet server that is also configured as a Microsoft Jet replication Synchronizer, and a second replica (R2) on the same PC as the Internet server.
The client PC, where R1 resides, starts the synchronization process with the following actions:
Once the changes are stored on the server, the client PC drops out of the process. The server continues the synchronization by notifying the Synchronizer that there is data in the FTP folder. The Synchronizer then takes the following actions:
If R2 has no changes to send to R1, the synchronization is complete at this point. If R2 needs to send data back to R1, the Synchronizer, Internet server, and client PC complete the process as follows:
Internet synchronization works only between replicas on different machines. If you attempt to synchronize two replicas-one managed with an Internet Synchronizer and one unmanaged-on the same machine, you will get a message saying the exchange was unsuccessful because the Internet is slow or because there is a problem with the Internet server. However, if you have the same two replicas on two different computers, the Internet exchange succeeds.
Preparing for Internet Synchronization
Before you can synchronize over the Internet, you must property configure your Internet server and Replication Manager. For full details, see "Setting Up an Internet or Intranet Server for Replication" in the Help file provided with Replication Manager. If you're new to the Internet, here is the easiest way to configure everything:
Note Some Internet providers may require you to stop and restart Replication Manager and/or the communications connection if synchronization is canceled before it is complete.
The procedure above describes the easiest way to confirm you have set up your Internet server and your Microsoft Access application for replication. Once you have the system working, you may decide you do not need to install the full version of Microsoft Access on the Internet server, but only the run-time version of Microsoft Access that is included with Office 97, Developer Edition (ODE). If you decide to use only the run-time version, you can install it on the Internet server by using the ODE Setup Wizard to create a custom Setup program for the Microsoft Access application you want to replicate. When you're creating the Setup program, make sure you select the Microsoft Replication Manager and Microsoft Access Run-Time Version redistributable components. When you run the custom Setup program on the Internet server, it will extract only the files you need and create the correct registry settings.
Note The ODE Setup Wizard is the recommended tool for installing your application on an Internet server because it automatically updates the system for you. For example, the Setup programs created by the Setup Wizard update registry settings for you. Updating these entries manually is complex and prone to error, particularly with regard to Internet settings and versions of system DLLs that interact with other applications. If you manually copy your application and associated files to the server, you must also update system settings such as registry entries. For this reason, we strongly recommend you use the ODE Setup Wizard to create a Setup program for the application.
Special Considerations When Using CompuServe
Connections through CompuServe may require extra configuration steps. You may receive the error message "Dial-up Networking could not negotiate a compatible set of protocols you specified in server type settings. Check your network configuration in Control Panel then try the connection again." Follow these steps to correct the problem:
If CompuServe is not already an option, click Make New Connection in the Dial-Up Networking window. Type CompuServe as the name of the computer you are dialing. Enter the CompuServe phone number as the number to dial.
If Dial-Up Networking is not on your computer, open Control Panel, double-click Add/Remove Programs, click the Windows Setup tab, click Communications, click the Details button, and then select the Dial-Up Networking check box.
The next step is to configure your Internet connection properties, as follows:
If the Scripting Tool is not installed, open the Control Panel and double-click Add/Remove Program. Click the Windows Setup tab, and then click the Have Disk button. A dialog box appears with a prompt for the location of the manufacturer's files. Set this to the \Admin\Apptools\Dscript folder on your Windows 95 CD and click OK. In the next dialog box, select the SLIP and Scripting for Dial-Up Networking check box and then click the Install button. Click OK twice to install the tool and close Add/Remove Programs.
You should now be ready to go! Now, whenever you attempt a Microsoft Jet replication synchronization across the Internet, you will connect via the CompuServe Internet server. To do this, double-click the CompuServe icon in the Dial-Up Networking window. Enter your CompuServe ID (for example, 12345,1234) in the User name box, and then enter your password in the Password box. You can choose to save the password; if you do, you will not be prompted to enter it each time you connect to CompuServe.
Internet Synchronization with DAO
DAO includes a new parameter, dbRepSyncInternet, that supports Internet synchronization. You cannot use dbRepSyncInternet independently; you must fully specify which type of synchronization you want to use. Here are three statements that illustrate how you can use this parameter:
db.Synchronize "DatabasePathName", _ dbRepImpChanges + dbRepSyncInternet db.Synchronize "DatabasePathName", _ dbRepExpChanges + dbRepSyncInternet db.Synchronize "DatabasePathName", _ dbRepImpExpChanges + dbRepSyncInternet
Note DAO provides dbRepExpChanges as a default parameter only if the user does not specify any parameters at all.
Conflicts and Errors
When two users simultaneously update data, there are three possible outcomes:
The first case is the most common; in well-designed replicable database applications, users can simultaneously update data without any adverse side effects. However, if two or more users modify the same row at the same time, conflicts or errors can occur.
A conflict occurs when two users update the same record. When this happens, Microsoft Jet chooses one replica to win the conflict and one to lose. The record from the winning replica is placed in the table and replicated to the rest of the replica set. The losing record is returned to the loser, and reported in a special table called TableName_Conflict, where TableName identifies the table where the conflict occurred. The user of the losing replica is notified that conflicts exist and is prompted to either reapply the data or delete it. Conflicts do not cause the data in the replica set to diverge, and are usually a minor problem that is easily fixed.
Errors are simultaneous changes that can cause divergence in the data stored in the conflicting replicas. For example, an error occurs if users at two or more replicas simultaneously insert a new record and both records use an index value that was previously defined as unique. When the replicas attempt to synchronize, a duplicate key error will be returned.
Several conditions can cause an error:
For example, a TLV rule may prevent one replica from accepting an update that was successfully inserted in another replica. Consider a replica set with only two members: the Design Master and one other replica. The owner of the Design Master creates a TLV rule on a field (such as Salary > $10,000). Simultaneously a user modifying the replica enters a new record with a value in the Salary field of $9,000, which is accepted since the TLV rule has not been sent to the replica. When the two replica is synchronized with the Design Master, Microsoft Jet attempts to insert the salary record from the replica into the Design Master. The Design Master rejects it and records an error. (Of course, you can avoid this situation by synchronizing the Design Master with all replicas before a new TLV rule is introduced.)
Errors are recorded in the MSysErrors table and replicated to all replicas. Errors must be corrected as soon as possible because they indicate that data in different replicas may be diverging.
Sometimes errors are self-correcting. For example, Microsoft Jet rejects updates on records that are locked when synchronization is attempted. Microsoft Jet records this as an error and attempts the update at a later time. Microsoft Jet removes the error if it later updates the record successfully.
Conflict Resolution
If two users simultaneously update the same record in different replicas, a conflict occurs when Microsoft Jet next attempts to synchronize these two replicas. When a conflict occurs, Microsoft Jet uses the following conflict-resolution algorithm to determine a winner and a loser:
This algorithm is guaranteed to be deterministic. There is no provision to modify this algorithm.
The winning record is placed in the table in both replicas. The losing record is placed in the loser's replica only in a "conflict table". The conflict-table is named TableName_Conflict (where TableName is the name of the table where the conflict occurred). Microsoft Access automatically invokes a wizard to assist the user in resolving entries in conflict tables.
Microsoft Jet uses a merge reconciler that merges individual rows from multiple replicas into a single database; it does not use the default Windows 95 Briefcase reconciler. The default reconciler simply determines which version of the file has the later time stamp and copies that file over the version with the earlier time stamp. It has no provision for record-level conflict resolution; any updates outstanding in the losing file are lost in the process. By merging changes from multiple replicas, the Microsoft Jet reconciler retains changes from the losing replica rather than simply overwriting them with data from the winning replica.
You can enhance the Microsoft Jet algorithm with your own Visual Basic for Applications function. Microsoft Jet will still initially resolve conflicts using its own algorithm but you can use your code to manipulate the results. You must register your function as a database property. To do this, click Database Properties on the File menu, and then click the Custom tab. In the Name box, type ReplicationConflictFunction; in the Type box, select Text; and in the Value box, type the name of your function; for example, Resolve().
Here is a simple function to display the name of any table with a conflict in the Debug window:
Public Function Resolve() 'Find tables with conflicts Dim db As Database Dim tdf As TableDef Set db = CurrentDb For Each tdf In db.TableDefs If (tdf.ConflictTable <> "") Then Debug.Print tdf.Name & " had a conflict" End If Next tdf End Function
To view the Debug window, press CTRL+G.
You can customize this function to resolve conflicts according to your own business rules. For example, if your application includes a special date/time field that is always updated when a record is inserted or edited, your Visual Basic for Applications function could use this field to select the record with the most recent update as the winner. However, your code will replace the Conflict Resolver provided by Microsoft; therefore your function must resolve all errors and conflict situations. It is not sufficient to resolve conflicts and ignore errors; the net result will be inconsistent replicas with unresolved errors.
A partial replica is one that contains a subset of data. Support for partial replicas is a new feature in Microsoft Jet 3.5/Microsoft Access 97. You can use either the Partial Replica Wizard or Visual Basic for Applications code to create a partial replica. The Partial Replica Wizard is available on the Internet; visit the Office Developer web site at http://microsoft.com/officedev/ for more details.
A WHERE clause on a table defines a partial replica. For example, to create a partial replica with customers from California, you would specify "Region = 'CA'". You cannot use user-defined or aggregate functions, nor can you prompt the user at run-time for parameter values. By using Visual Basic for Applications, you can apply restrictions to any number of tables. The Partial Replica wizard limits you to placing restrictions on a single table.
Note Multiple filters are OR'ed together. For example, if you set a filter of "Region = 'CA'" for the Customers table and a filter of "Value > $1,000" for the Orders table, the result will include all records for customers from California PLUS all orders with a value greater than $1,000. You would not get orders of over $1,000 only for customers from California.
The PartialReplica property lets you specify which relationships to follow when creating a partial replica. For example, to select only the orders for the customers in California, set the PartialReplica property for the CustomersOrders relationship to True.
With Visual Basic for Applications, you can create more sophisticated partial replicas by applying restrictions to any number of tables. The following example shows how you can create a partial replica, set a filter on a table, set the PartialReplica filter property, and populate the partial replica with data:
Public Function CreatePartialReplica()
Dim dbsFull As Database 'The source replica
Set dbsFull = OpenDatabase("C:\NWIND.MDB")
'Create partial replica from full replica, description,
'set the dbRepMakePartial option.
dbsFull.MakeReplica "C:\PARTIAL.MDB", _
"Replica of NWIND.MDB", dbRepMakePartial
dbsFull.Close
'You have a partial replica, but no data rows.
End Function
Public Function CreatePartialFilter()
Dim dbsPartial As Database
Dim tdfCustomers As TableDef
'Open the partial replica in exclusive mode.
Set dbsPartial =OpenDatabase("C:\PARTIAL.MDB", True)
'Open the Customers table.
Set tdfCustomers = dbsPartial.TableDefs("Customers")
'Set a filter on the Customers table.
tdfCustomers.ReplicaFilter = "Region = 'WA' "
dbsPartial.Close
End Function
Public Function CreatePartialRelationships()
Dim dbsPartial As Database
Dim Rel As Relation
'Open the partial replica in exclusive mode.
Set dbsPartial = DBEngine(0).OpenDatabase("C:\Partial.MDB", True)
For Each Rel in dbsPartial.Relations
If (Rel.Table = "Customers") And _
(Rel.ForeignTable = "Orders") Then
'Set CustOrd relationship PartialReplica property to True.
Rel.PartialReplica = True
Exit For
End If
Next Rel
dbsPartial.Close
End Function
Public Function PopulatePartial()
Dim dbsPartial As Database
Dim strFull As String
'Open the partial replica in exclusive mode.
Set dbsPartial = OpenDatabase("C:\PARTIAL.MDB", True)
'Point to the full replica.
strFull = "C:\NWIND.MDB"
'Populate the partial replica from the full replica.
dbsPartial.PopulatePartial strFull
dbsPartial.Close
End Function
You should call PopulatePartial under any of the following circumstances:
Note that PopulatePartial works only with direct connections; it does not support indirect synchronization such as drop box (file transfer) synchronization, Internet synchronization, or FTP folder synchronizations.
Synchronizing between full and partial replicas may require careful attention. Say you create a replica set with three members: Full_1, Partial_1, and Full_2. Now assume Full_1 synchronizes with Partial_1, and because Partial_1 has a filter, it only gets a subset of the changes made at Full_1. Now Partial_1 synchronizes with Full_2. Obviously only the subset of the changes stored in Partial_1 can be sent to Full_2. It would be a mistake to assume that Full_2, having successfully completed the exchange, has all the updates from Full_1. The only way to guarantee that Full_2 has all the changes is to synchronize with another full replica. When synchronizing full and partial replicas, the best process is to treat partial replicas as 'leaf' nodes. That is, designate them as the end of a synchronization chain. This also increases the efficiency of synchronizations, because the protocol that ensures correct propagation of updates between partial and full replicas may result in redundant data exchange.
Partial replicas introduce a number of subtleties for deleting or updating records when referential integrity and cascading updates or deletes are used in the same application. Consider what might occur in a simple database with two tables: Customers and Orders. Referential integrity is enforced between these tables, and cascading updates or deletes are not enabled. Consider Customer A who has Orders records in the full replica. Now assume there is a partial replica with only the Customers table. If Microsoft Jet allowed the records pertaining to Customer A to be deleted at the partial replica, then this delete would fail when it was sent to the full replica, because there would be existing records for Customer A.
To prevent this, Microsoft Jet traps attempts to update or delete primary keys in the parent table in partial replicas, and permits them only if
Or if, for each child table:
-or-
For partial replicas, Microsoft Jet looks only at the immediate child table to decide whether to permit an update or delete. However, it's important to remember the effect that cascading updates and deletes can have. Consider an example of three related tables (Customers, Orders, and OrderDetails). If cascading updates and deletes are enabled, an attempt to update a Customer record in a partial replica will also attempt to update any Order records, which in turn will attempt to update any OrderDetails records. If cascading updates and deletes are not enabled, an update to a Customer record would check the Order records and ignore the OrderDetails records.
Replicated databases use the same security model as nonreplicated database. The permissions assigned to a user's logon ID control the actions that user can take on the database.
The application designer must ensure that the same security information is available in each replica. There are two ways to do this:
Microsoft Jet 3.5 includes a new security permission, Administrator, for a database object. By default, this permission is granted to the Users and Admins groups, as well as to the user logged on when a new database is created or converted from an earlier version of Microsoft Access. It is up to the application developer to restrict this permission to selected users if security is to be enforced.
A user with Administrator permission can do the following in the Design Master:
In addition, a user with Administrator permission can execute the Recover Design Master command from any replica.
Note Make sure there is always at least one user with Administrator permission on the database. If the Design Master and the associated System.mdw file are destroyed (for example, through a hard disk failure), it is possible to designate another replica as the new Design Master-but only a user with Administrator permission can do this.
Parameters for Microsoft Jet 3.5/Microsoft Access 97 replication components are stored in the system registry. In addition to the entries listed here, the registry includes many minor entries for the Synchronizer, such as the log file location, the last viewed replica, the security database, and so on, under the following key:
HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\3.5\Transporter
Briefcase Reconciler
HKEY_CLASSES_ROOT\CLSID\{ClassID of Access.Database.8} \Roles\Reconciler = {DB5B8C90-7B62-11CF-A9E4-00AA00B676FC}
HKEY_CLASSES_ROOT\CLSID\{ClassID of Access.Database.8} \Roles\NotifyReplica = {DB5B8C90-7B62-11CF-A9E4-00AA00B676FC}
HKEY_CLASSES_ROOT\CLSID\{DB5B8C90-7B62-11CF-A9E4-00AA00B676FC}
\InProcServer32 = "full path of msRCLR35.dll"
\ResourceDll = "full path of msRECR35.dll"
\SystemDb = "full path of system.mdw"
\ThreadingModel = "Apartment"
HKEY_CLASSES_ROOT\CLSID\{ DB5B8C90-7B62-11CF-A9E4-00AA00B676FC} \SingleChangeHook
Replication Manager Registry Entry
HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\3.5\Replication Manager "Path" path to Replman.exe
Synchronizer Registry Entry
HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\3.5\Replication Manager\3.5 "SynchronizerPath" path to mstran35.exe
Here is a list of publications you can refer to if you want more information about database replication, the Microsoft Jet database engine, or using replication in Microsoft products.
Bidirectional exchange-An exchange between two replicas, in which both replicas send and receive updates.
Cascade update/cascade delete-Referential integrity options which specify that changes or deletions to the primary key in one table will be propagated to any other tables that reference that primary key value.
Conflict-An attempt by two replicas to simultaneously update the same row in a table. One replica "wins" the conflict and its value is propagated to the rest of the replica set. The losing replica is notified that it lost and is offered the chance to resubmit its update.
Data Access Objects (DAO)-The programming language-independent object-based data access language used to manipulate Microsoft Jet engine data.
Database engine-A program, or part of a program, that serves as the link between a database management system (DBMS) or application and the data. Specifically, the part of a DBMS program that reads and writes data records.
Design Master-The single member of a replica set in which schema changes may be made for propagation around the whole replica set.
Error-In some situations an update received from one replica causes an error in the receiving replica (for example, it violates a unique key rule, or a TLV rule). In these cases, an error is registered and the user must manually correct the error.
Exchange-The process of sending schema and data updates between replicas.
Foreign key-A reference to the primary key in another table.
Generation-A counter, specific to each replica, which is incremented each time an exchange occurs with any other replica.
Global object-An object that is replicated around the replica set. Global objects can only be created in the Design Master.
GUID-Globally unique identifier. A primary key in a database that is always guaranteed to be unique. Also known as a UUID (universal unique identifier).
Lineage-A record of each nickname/generation pair. Used to optimize exchanges between replicas and resolve conflicts.
Local object-An object that is not replicated around the replica set. A local object can be created in any replica.
Method-An action that can be applied to an object. For example, to move to the first row of a recordset, you apply the MoveFirst method to the Recordset object.
Multi-master-The ability to modify any data at any replica.
Nickname-A shortened name for of the ReplicaID GUID, used in the lineage.
OLE-A standard method of linking and embedding objects created by one program to another program. Also, a type of field in Microsoft Jet used to store complex objects created by other programs.
Objects-A package of things created and manipulated by programs. In Microsoft Jet, Tables, Users and Query Definitions are all examples of objects.
Open database connectivity (ODBC)-A standard way to connect to and read and write records from other databases, usually server databases.
Primary key-A column (or multiple columns) in a table that ensures that a record can be uniquely identified.
Property-An attribute of an object that can be retrieved and (sometimes) set. For example, the Index property of a table can be set to the name of one or more columns in the table.
Pull exchange-An exchange between two replicas where one replica only receives, or pulls, update from the other replica.
Push exchange-An exchange between two replicas where one replica only sends, or pushes, its update to the other replica.
Read-only replica-A replica that is not permitted to update either data or the schema.
Referential integrity-A relational database rule that requires that all foreign key values reference valid primary key values.
Replica-A special copy of a database that is created in such a way to allow changes made in the replica to be exchanged at a later time with other replicas in a replica set, eventually bringing all the replicas in the replica set into a consistent state.
Replication-The process of creating special copies of a database, where the copies have a special relationship to each other.
Replica ID-A GUID that uniquely identifies a replica.
Replica set-Replicas that share a common heritage and are able to synchronize their data and schema. Replicas can synchronize only with other replicas in the same set.
Retention period-The amount of time, measured in days, that a replica set retains details of deleted records, schema changes, and other system-specific information. This value can be modified only by opening the Design Master in Microsoft Replication Manager and changing the value.
Synchronization-The process of bringing two replicas into a consistent state.
Transaction-A sequence of actions that must occur as a single unit.
Transaction processing-A mode of database processing that supports the creation, and saving (CommitTrans) or undoing (Rollback) of transactions.
Two-phase commit protocol-A system used in some distributed databases systems whereby each database either agrees to or rejects a proposed change, and only if every database in the system agrees is the change actually made.
Validation rule-An expression that can be linked to a change of data so that it is always evaluated when a certain type of data modification is made.
© 1997 Microsoft Corporation. All rights reserved.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Microsoft, Visual Basic, Windows 95, Windows NT, and Visual C++ are registered trademarks of Microsoft Corporation.
Intel is a registered trademark of Intel Corporation. Novell and NetWare are registered trademarks of Novell, Inc.
Other product or company names mentioned herein may be the trademarks of their respective owners.