Database Replication in MS Access
Learn the Uses, Advantages and Pitfalls of Database Replication
Database replication provides a method to database designers and users to enter and update database records at more than one location
while the DBMS manages the synchronization of each copy of the database.
Database shadow, database mirror or real time database backup are also considered as
database replication for the article.
Database replication is typically tightly coupled with database transactions.
At the transaction level data can be duplicated to the replica database.
The result is greater data integrity and availability. However, the
increased availability is dependent on how
independent the database replica is from the primary database. Replica
independence must be considered in
terms of disk spindles, disk
controller, power supplies, system, room, building and city. The further
away the replica is when considering the above list determines just how robust
and available your replica will be under different circumstances. Ideally the further
the replicate database is removed from the
primary database the better. Along with an increase in distance comes
added complexity and
expense.
The cost benefit of independence of the replica must be
evaluated. Common replication concepts include (from wikipedia.com):
-
Master/Slave Replication: All write requests are performed on the master and
then replicated to the slaves
-
Quorum: The result of Read and Write requests is calculated by querying a
"majority" of replicas.
-
Multimaster: Two or more replicas sync each other via a transaction identifier.
Database replication can serve several purposes. Here are some of them:
-
Remote Access and Update of Data: For examples, a sales person out in the field could come back to a motel at night and
connect into the home office computer and accomplish two-way synchronization of database records.
-
Distribution of Database Design Changes: A database programmer can implement changes to the Master
Database - such as addition/change of tables, forms and reports. The replication system will propagate these changes to all
replica databases. This method can greatly reduce of maintenance overhead of a database system.
-
Database Backup and Disaster Recovery: A replica copy of the master database can be housed in another
office, building, or city. This method can provide nearly instantaneous backup of critical information.
-
System Load Balancing: Replicas of a database can reside on more than one computer and, when combined
with load balancing software, can distribute users evenly to balance workload across several computers.
-
Database Load Segregation: Database functions can be separated across computer systems in order to insulate
key functions from potential slowdown. For instance - you may want to isolate time critical transaction processing from user
browsing and report generation.
All the major (and many minor) database management software companies provide database replication with varying
degrees of robustness and flexibility - including Microsoft Access.
Microsoft Access Replication
With all Access databases it is best to start with the most efficient relational table design you can construct.
Extensive use of lookup tables with index pointer and defined relationships between tables along with no data duplication from
record to record all combine to improve the performance and reliability of an Access database. Efficiency and performance are
even more important for a replicated database.
In a replica setup there can be one Design Master and as many replica databases as needed. Note that changes
to replicated objects (tables, forms, reports, queries, etc) can only be made to the design master and not to the replicas.
Not all objects within a database need be replicated. Local objects may exist in the design master and in each
replica. You might have some special case queries or temporary tables in the replicas which don't get synchronized.
Before replication you need to decide which database objects will be part of the replica object set and which will
be local.
Autonumber ID Field Consideration: If you will be routinely adding more than 100 records to a replicated
database between synchronization occurrences then you should consider changing Autonumber Long Integer fields to Autonumber
Replication ID for the data type. Replication ID datatype (128 bit) provides more numbers for the autonumber field and thus
will reduce the likely hood of autonumber conflict.
If you have a database password set then remove the password before creating the replication.
There are two methods ( plus programmatically) to create a replicated database (reproduced from Microsoft Access
help):
-
Using the Tools menubar choice:
-
Using the Briefcase Option:
Synchronization Conflicts
When you have multiple database replicas and it comes time to
synchronize them either to each other or to another main database synchronization conflicts occur if the same field and record were
changed on more than one replica. Database replication engines have sophisticated algorithms for determining the correct value
for a field but obviously there are occasions when a conflict can not be resolved. In this case human intervention is required
to resolve the conflict.
The Microsoft Replication Conflict Viewer is a tool which you use to resolve conflicts manually.
To reduce or eliminate the occurrence of conflicts you may want to make design changes to your database
tables or modify you work practices so that there exists a segregation of duties and thereby avoiding having two people work on the
same tables and records between synchronization instances.
Design changes - for
example you may have a work request database which has a status comment field which can get updated by several people during the
day. In this case you may want to break the comment field out into a separate table with a date/time stamp to store each
version of the comment when it is changed by more than one user.
For a comprehensive set of questions and answer about Microsoft Access database replication please consult
Microsoft's page via the following:
Microsoft Access Replication
FAQ Download and the
Database Journal.
Review the database replication information above in conjunction with
Software Disaster Recovery Planning
and
Software Risk Assessment.
Note: Database replication is not directly support in Microsoft Access 2007.
