What is database replication?
Replication is the process of copying data from a central database to one or more databases.
The central database is called the publication database because it provides the data for users at other sites. The data in the publication database is copied (replicated) to subscription databases at other locations. All users whether connected to the publisher server (the Publisher) or to a server at one of the remote sites (a Subscriber) see the same data and work on the same records. A Subscriber can be a Subscriber such as a file server or a disconnected Subscriber such as a laptop.
A change that is made in one subscription database is copied to the Publisher (merged), and the Publisher then replicates that change to the other Subscribers. On fixed Subscribers, this is a continuous process and you are not aware of it happening, synchronization usually occurs in a matter of seconds. The effect is that all users on fixed Subscribers have an almost identical view of the data even though they are working on different databases.
This figure shows the main database that is configured as the publication database with three subscription databases on servers that have permanent (fixed) connection to the Publisher:
The situation is slightly different if you are using a disconnected Subscriber, such as a laptop, and the Subscriber is only periodically connected to the Publisher. Your changes can only be merged with the Publisher, and you only receive updates to the data, when the laptop is on the network and connected to the Publisher. For this reason, your view of the data can become progressively outdated, and there is also a risk that your changes might conflict with changes made by others.
Note: Many of the terms that are used in iBase database replication are derived from Microsoft™ SQL Server merge replication on which iBase database replication is based.
Editing and deleting records in a replicated database
When you edit or delete a record, you affect the data in use at other sites. You can, for example:
Edit a record that is "owned" by a user at a different site.
Delete a link that is associated with an entity that is part of another user's area of investigation.
Delete an entity that a user at another site is about to edit.
None of this is new but it is important to be aware of who owns a particular record.
You can find out who last worked on a record by looking at the record properties (in iBase, right-click on the record, for example in a record list, and from the menu, select Properties).
- Record identifier
The record identifier is composed of <table><record number>\<database identifier>. Therefore, SUB in this example identifies the database in which the record was created.
- Created by
The name of the user who entered the data. The optional number is part of the username and can be used to show where the user is located. You can also assign users to locations as part of their contact details as explained in the subsequent information.
- Updated by
The name of the user who last entered the data.
Note: Because updates are made independently by users at each Subscriber, the same data might be updated at the Publisher or by users at more than one Subscriber. Therefore, conflicts can occur when data modifications are merged. However, this should be a rare event, and the iBase administrator can resolve any conflicts that occur.
For information on who created or last modified the record, or who owns the record, you might be able to display their contact details.
Restricted operations in a replicated database
In a replicated database, particular care needs to be taken with:
Batch editing
Batch deleting
Merging entities
Imports
With the first three types of operation, unexpected consequences can occur if you do not carefully analyze the records involved in the operation. For this reason, these operations are restricted to specific users.
Soft delete is always used in a replicated database. This means that deleting a record, marks it as deleted but leaves it in the database so it can be restored if necessary. Soft deleted records can only be restored and purged in the publication database. Soft deleted records are only visible to system administrators. They do not appear in search results, when listing and browsing records or when charting but they do appear, for example, in the Links dialog.
Soft deleted records cannot be purged or restored if there are any conflicts.
Merging entities is a restricted and time-consuming operation in a replicated database (requiring cooperation from all the different sites). For this reason, steps need to be taken to reduce the number of unnecessary duplicates in your data, including duplicates created when you import. See Merging Entities for details.
Note: Bulk Import is not supported with iBase database replication.
Setting up iBase database replication
When you and your SQL Server administrator set up replication, you replicate the iBase:
Entity and link data (including the contents of code lists).
Security data, and the users and groups that it contains.
Audit log and its entries, so that changes to the audit trail can be analyzed for a single site or for the organization as a whole.
Different types of data are held in different SQL Server databases and require separate publications and subscriptions.
For an iBase database, you do not replicate the database schema. The database at each site must have the complete database schema before replication is configured. For more information on what is and is not replicated, see Updating the Database Design.