<%dim crumb crumb="Replication of Database" crumb2="Database Replication Issues and Discussion" %> Database Replication
Home  Fees/Services  Access Templates  Tutorials  Tutorial Downloads  Articles  Search  Forums  Contact  Privacy  Links

Data Processing Articles

Home > Access Programming Articles <% response.write "> " & crumb %>

Disaster Recovery Plan
Access Forms Help
MS Access Versus
Replication of Database
Software Risk Assessment
Choose a Consultant
Faster Access Database
Access Web Database
ODBC Access Examples
Microsoft Access, Why?
Repair Corrupted Table
Access to QuickBooks
Access Cloud Database

Image Gallery

<% dim Gnum,Ynum Gnum="4705670955" Ynum= "26695" %>

Database Replication in MS Access

Learn the Uses, Advantages and Pitfalls of Database ReplicationDatabase 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:

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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):

  1. Using the Tools menubar choice:

  2. replication tutorial example problems

  3. Using the Briefcase Option:

replication issues do' don'ts

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 and above.

Contact Information

<% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>