InterBase Replication Fundamentals

By: Conference Speaker

Abstract: This session covers the A to Z of replication and demonstrates how it can be used to good effect to help solve the problems developers and project managers are facing when developing and managing enterprise database projects.

This is the technical paper from a talk given at the 11th Annual Inprise & Borland Developer's Conference
By Vince Duggan
Vince Duggan is the technical director of Synectics Software, an Inprise/Borland client/server business partner based in Sandton, South Africa. Over the past 15 years, he has worked with COBOL, BASIC, dBASE, Clipper, and Oracle on DOS, Windows, VMS, and UNIX platforms. Since late 1994, he has worked exclusively with Inprise/Borland products, particularly Delphi and InterBase.

Introduction.

Definition of Replication.

Replication in use within Applications.

Replication methodology within Interbase.

Replication Management tools.

Features of Interbase Replication.

Summary.


Introduction

Modern Relational Database Management Systems have Replication technology in one form or another. Replication was introduced with the release of Interbase 6, and this technology is also backward compatible with Interbase 5, and even Interbase 4.

IBReplicator has been written in such a manner that it is as easy to port to a new platform as Interbase itself is. Currently, ports exist for the major Interbase platforms, and more will be released in the future, as demand dictates.

This paper takes a look at the basics of replication in Interbase 5 and 6. Another paper, entitled "Interbase Replication: Internals" will look at the more complex uses of IBReplicator, and will discuss the specifics of the internal architecture of the product.

back
back to top

Definition of  Replication.

Replication can mean different things to different people. Essentially it means that data and/or changes to data are replicated from a source database, to one or more target databases. Once replication has taken place, the data at all sites is in synchronization. This does not necessarily mean that the source and target databases are mirror copies, nor that the rows in question are identical, nor that the actual values of the data are the same.

In most cases, the source and target database will be identical, but in some cases, only some of the tables will be replicated, and within those tables, only part of the data may be replicated. As well as that, the actual field values may be modified on the way to the target database.

Replication is an architecture that allows an enterprise to maintain multiple copies of data. This is typically done to improve access to the data, by allowing various sites in the organization to have their own copies of the data. Each site then works on their own copy, and the changes made by each must 'automagically' be reflected at any or all of the other sites.

Replication also allows for Branch Office consolidation to Head Office scenarios. The Branch offices will on a periodic basis move their data to head office, either as raw data, or in summary form.

There are quite a few different architectures which can implement replication:

  • Simple copy of the database.
  • Export/Import model.
  • Synchronous model.
  • Asynchronous model

A very simple option is to copy the database to the different target database servers. This is a very manual approach, and the multiple copies will diverge during the period between copies.

One could write a very specific export/import system. In this case, either all the relevant rows or just the changed rows are exported to a file, and the file is sent to the target sites, where another specific import routine imports the data. This option tends not to be very portable, but can work well in certain situations.

The synchronous model is quite widely used. In this model, a change to a row in the source database, is propagated to the target databases synchronously. This means that as changes occur in the source database, they appear simultaneously in all  target databases. The disadvantage to this method is that if one or more of the targets is unavailable, then things quickly get very complex, and usually one has to fall back to an asynchronous method.

The Asynchronous model is very popular, and is the model used by IBReplicator. In this model, changes are 'stored up', and every so often, every few seconds/minutes/hours or days the Replication Server wakes up, and sends the changes to the targets. The advantages to this method is that if one or more of the target databases is off-line, it does not matter, since it will catch up next time it is on-line. There is a disadvantage in that there is a 'lag' time in which the source is out of sync with it's various targets, but this normally can be kept within acceptable levels.

back
back to top
 

Services provided by Replication?

There are a number of reasons why an organization could use replication:

  • Improving data availability and application performance in a distributed data environment.
  • Using a replicated database as a warm standby.
  • Decision support systems which require near-synchronous data consistency with on-line system.
  • Consolidation of data from multiple locations.

Large enterprises are often spread around the country, and although WANs can be very fast and reliable these days, it is often better for each location to have a local copy of it's own data, as well as a certain amount of 'common' data, rather than have a single database at a central location. This usually means that replication is a requirement in order for each location to have the most up-to-date data. Replication can also be used to consolidate data to head office.

In mission critical 7x24 sites, it is imperative that access to data is continuous. In the case of a server shutdown it is important to have access to the same data on another server.  Although Interbase will keep a shadow copy of a database, this shadow copy is not accessible as a working copy of the database without some administrative intervention. As well as that, the shadow copy must be on the same physical machine (though typically on a separate disk drive), or one must use NFS to place it on another machine. This will slow the operation  down somewhat. Replication provides a perfectly usable warm backup database on another server which can be used in an instant should the primary database server fail, and can indeed be used all the time.

Often, those involved in decision making, and accessing a database for decision support purposes, will not access the on-line database, but will rather access a second database, which is periodically synchronized with the on-line database. Replication is the perfect solution to this problem. Depending on volumes and requirements, data in the decision support database can be updated every few seconds up to every few days. Often, the decision support database contains 'consolidated' data, and this consolidation can be done by the Interbase Replication Server,  using the feature which allows one to replicate via a stored procedure.

back
back to top
 

Replication architectures

In an ideal world, any data which has been changed in the primary database, would simultaneously appear in all target databases. This requires that a global commit mechanism (two phase commit) exists. It also means that each transaction that is started must be started across all participating Servers. This must be done from each application (very difficult to maintain), or the DBMS must do it for you. If one of the participating servers is off line, then the problems begin to escalate.

Much easier is an asynchronous architecture. Every now and then a process wakes up, and synchronizes the databases. This means that if one of the participating servers is off-line, it can be ignored for the moment, and the process can try again later.

Deciding what data to send, i.e. sufficient to synchronize the databases, no more and no less, can be done in numerous ways. If the replicating process has access to the RDBMS's write-ahead/transaction log, then it can get the data changes from there. Interbase's architecture eliminates the need for such a log, and so another mechanism must be found.

IBReplicator implements this architecture by way of a change log. In this log is kept a record of each row that is changed. The log consists of a sequencing key, the type of change (insert, update or delete) and the value of the primary key which identifies each row.

This log is read periodically, in the same sequential order that it was created, and the changed rows are looked up on the source database, and the data is transferred to the target database. This is discussed in greater detail in the chapter Replication methodology within Interbase .

Interbase Replication is not built into the database engine, but is an ordinary application which accesses the database via the Interbase client API. This means that administrators and users have total control on how often replication is triggered.
 

back
back to top
 
 

Replication in use within Applications

When replication is used within an organization, it's proper functioning becomes a key link in the success or failure of efficient data access. Therefore it is crucial when databases and applications are designed, that replication is part and parcel of the design process.

It is very easy to design a database which works perfectly in a multi-user, single server environment, but is close to useless in a multi-server replication environment. A simple example using an accounting system:

Machine A (Primary server):
21 Jan 2000 10h00:    Invoice 1234 generated.

Machine B (Regional Server):
21 Jan 2000 10h05:    Invoice 1234 generated.

Replication Process:
21 Jan 2000 10H30: Replicate Invoice 1234 from B to A - fails: Primary key violation.

From this example, it becomes apparent that unique system-wide primary key values are a requirement. Single server, multi-user databases cannot always be transferred into a multi-server replication environment, unless primary key values are unique across all servers.

Triggers can be a source of problems within a replication environment. Triggers are designed to fire when data is modified, usually by a particular application. The actions that the trigger takes are part an parcel of the application in question.

When data is replicated from another database, it must be remembered that the triggers will fire as well. This may not be appropriate, since the work that the trigger is doing has already been done on the original server.

When replication is used to maintain a warm backup copy of the database, the backup copy lags by some amount of time. It must be possible for all applications to switch to the backup database, either automatically or in an easy manner, and a method must exist so that after switch over, one can work out what has gone missing.

One should not think that replication is the universal solution to all problems, and start replicating data all over the place. Like all solutions, replication introduces problems and overheads of it's own. There is an additional load on the Interbase servers involved, as well as additional traffic over the network.

back
back to top
 

What data can be replicated

Essentially, any data which can be uniquely identified by a primary key, can be replicated. The values of the fields that make up the primary key should not change, because that will cause errors to appear in the replication log. When replicating rows in which the primary key value has changed, the data may or may not be correctly replicated, and it may be difficult to decide what is a transient error, and what is a real error.

See the chapter "Problems that can prevent replication"  for a full description of this situation.

The easiest way to prevent problems with changing primary key values is to define, for each table, a single-field 'meaningless' primary key. This field is typically hidden from the end-user and is used to uniquely identify rows as the 'physical' primary key. The application may present another 'logical' primary key (such as an Employee Number) to the user, and this can be modified at will, since it is just another data field as far as replication is concerned.

All Interbase data types can be replicated using the Interbase Replication Server. This includes blobs and arrays. The primary key can be made up of any number of fields of any data type supported by Interbase indexes.

back
back to top
 
 

What data cannot be easily replicated

By and large, all data in well thought out applications can be replicated, and it is usually only when replication is retro-fitted to databases that it is found that problems occur.

The two most common problems are:

  • When an application depends on being able to change the values of the primary key. This is discussed in the previous chapter.
  • When Primary keys are not unique across all databases.

The easiest solution to both of these is to create, populate and manage a hidden unique key for all tables. This key, although not the official primary key, can be used by the replication server as if it was the primary key. This key can usually be managed by triggers and stored procedures in the database itself, and will probably not impact any applications at all.

Secondly, some kinds of 'virtual' data can be quite difficult to replicate. Typically this data is calculated by a view, stored procedure or report, and appears to the end-user as if it is part of the database, but in fact is not.

This problem only arises if the target database has a very different structure from the source database. Typically the target will not contain the fields which the source uses to calculate the virtual data, but will contain fields which should be populated with the virtual data.

A simple example is when the source database contains individual sales entries, while the target database contains only consolidated sales figures.

It is sometimes possible to replicate views as if they were tables, but more often this sort of data is replicated via stored procedures, or by duplicating the target data structures within the source database. This is discussed in more detail in the separate paper entitled Interbase Replication: Internals.
 

back
back to top
 

Replication methodology within Interbase

The Interbase Replication Server consists of a Replication manager and a Replication Server.

The Replication Manager will run on any MS Windows machine on the network, while the Replication server will typically run on the same physical machine as the primary Interbase database, although this is not necessary - it can run on any machine on the network, as long as the Interbase client is installed.

The Replication server is written in the 'C' language, and uses direct Interbase API calls. This means that it can run on any supported Interbase platform, and needs no middleware (such as BDE or ODBC) to connect to Interbase. This exposes the full functionality of Interbase, such as two phase commit, event alerts, blobs and arrays.

The  Replication Manager has a number of functions. These consist of:

  • Replication configuration tool.
    • Set up which databases are involved in replication. Screen Shot
    • Set up which databases are source databases, and which are targets. Screen Shot
    • Define Configuration parameters for each Source database. Screen Shot
    • Define which tables need to be replicated.
    • For each table define which fields need to be replicated. Fields can have different names and types in the source and target database.
    • Define the primary key for each table.
    • Define all usernames and passwords (encrypted).
    • Set up any conditions, for conditional replication.
       
  • Performance monitoring tool.  Screen Shot
    • Monitor replication activity.
    • Shows total number of inserts, updates, deletes, errors, conflicts encountered and time taken.
    • Graphs all available statistics.
    • Will notify operator, either visually or via e-mail if errors have been encountered
       
  • Graphical network management tool
    • Graphically displays all databases in replication network.
    • Shows all links between databases and direction of data flow.

 

  • Manual conflict resolution tool. This is used to manually resolve conflicts which the server cannot do automatically.
    • Displays all rows which failed to replicate due to conflict problems.
    • Administrator can decide to manually replicate the row, or remove the entry.

back
back to top
 
 

How rows are replicated.

Conceptually, replication is pretty simple. The replication server needs to know which rows have been modified since the last scheduled replication event, and it needs to read each changed row, and move the data to the target database.

In practice this requires a fairly complex set of definition tables within a central configuration database, as well as certain tables and triggers on each database which is a source of data.

The configuration database (which could also be one of the production source or target database, but is usually a separate database), contains various tables which contain the names of all tables, key fields and data fields in the replication schema. It also contains the name, location, usernames and passwords of all databases involved.

Each source database has a log table defined (called REPL_LOG). Every time a row is inserted, updated or deleted in a replicated table, and entry is placed in the log. This records the action taken (insert, update or delete) as well as the value of the primary key fields of the row in question. There is another sequencing field, which is there to ensure that rows are replicated in exactly the same order as they where modified. This is imperative for transaction integrity, and foreign key relationships.

Every so often, the Replication Server process wakes up and reads the log table in sequence. For each row in the log, the server fetches the data in the source database, and applies it to the target database.

It is quite possible that rows cannot be applied to the target for a number of reasons, usually because the target row is in use. This row is then left for the moment, and the change will be applied during the next scheduled replication event.

There are other reasons why a particular row cannot be replicated. This is discussed in the next chapter.

back
back to top
 

Problems which can prevent Replication

There are four types of errors that could occur during replication:

  • Unexpected Database errors, which prevent the Replication server from working on a database at all:
    • Invalid Username/Password
    • Database corruption
    • Network problems
    • Operating system crashes
  • Ordinary SQL errors, which prevent the Replication Server from replicating an individual row:
    • Row in use by another transaction
    • Constraint violations
    • User defined exceptions
  • Conflict Errors, which need to be manually resolved. Depending on the setup parameters, most conflict problems can be resolved by the Replication Server:
    • Cannot insert row, because row already exists (Primary key violation)
    • Cannot update a row, because row does not exist.
    • Cannot find the source row since it has since been deleted.
  • Database Design flaws, which either cause intermittent errors, or which cause invisible errors, for example data being silently overwritten, or extra rows appearing with different keys, but the same data:
    • Keys not being unique across all replication databases.
    • Primary key values being changed.

The first category is handled in one of two ways, depending on the severity of the error: Either the error is logged, and the Server carries on with other databases, or the error is logged, and the Server shuts down. In both cases any uncommitted work is rolled back.

The second category, where ordinary SQL errors occur, are easier to handle. The error is logged, and the server carries on with other rows that require replication. The next time the Replication Server activates, the problem row is tried again, and will succeed eventually.

The behavior exhibited by the Server when conflicts are encountered depends on various configuration settings. When a conflict occurs, the server needs to know which database (source or target) has priority. This can be set up in 3 ways:

  • Master/Slave. In this case, the source database always has priority.
  • Database Priority. Databases can be ranked according to priority. Two databases can have the same rank in which case the Server will not be able to resolve conflicts.
  • Time Stamped. Each row must have a time stamp field. The row with the later time stamp will have the higher priority.

When a conflict occurs, and the source database has priority over the target database, then failed inserts will be converted into updates and vice versa. This will be noted in the log file.

If a conflict occurs, and the source database has equal or lower priority compared to the target database, then the server moves the replication log entry into the manual log table, and notifies the operator to resolve this with the manual conflict resolution tool.

If one tries to replicate a database which is not 'Replication Friendly' then many different kinds of errors can occur. If for example applications often change the values of primary key fields (e.g. an employee number is changed when an employee moves to a different division), then a situation can arise when extra rows appear in the target database.

Example:
Employee Table: EmpNo, EmpName

DB1
update: EmpNo 1 - EmpNo 2
update: EmpNo 2 - EmpNo 3

Replication 1st row:
Lookup EmpNo 2 on DB1 - not found

Replication 2nd row:
Lookup EmpNo 3 on DB1
Update EmpNo 2 on DB2 - not found, convert to Insert
Insert EmpNo 3 into DB2

Now we have an Empno 1 and an Empno 3 on DB2
 

back
back to top

Replication Management tools.

Setting up a replication schema is fairly simple. Everything is done in the Replication Manager, and consists of the following steps:

  1. Define all involved databases, whether sources or targets.
  2. Add a Schema, and define the source database.
  3. Add a target database.
  4. Define which tables must be replicated.
  5. Define primary key fields, and data fields for each table.
  6. Create System objects on the Source Database

As soon as the system objects have been defined on the Source database, then data will be inserted into the replication log table, ready for the Replication Server to replicate.


Entering Database information parameters.
 
 


Defining Source and Target Databases, with Table and Field information.
 
 
 

back
back to top
 

Management tools.

The Replication Server has a few extra tools built in which can enhance the Replication experience.

Replication Monitor

This tool allows one to view replication activity for any replication schema on the network. One can see a live graph of the number of insert/update or delete operations that have occurred, as well as the number of errors and conflicts that have occurred.

One can drill down to individual tables, or see the total values for all schemas defined.


 
 

Replication Schema view.

This utility draws a 'map' of the relationships between all databases involved in the replication process. Each database is represented by an icon, and arrows are drawn to represent the flow of data.  This is superimposed on a user defined picture, such as a map, and the icons can be dragged to different positions on the picture, and saved in that position.


 
 
 

Replication Scheduler

This utility allows one to define when replication should take place. One can define specific times, or one can define time intervals (e.g. every hour).

The scheduler can be run from within the Replication manager, or separately as an NT service.

The Replication Server does not need to rely on the Scheduler, as it has a built in timer, which can initiate replication on a regular basis, or replication can be initiated from any application, via an event alert, or one can run the GUI version of the Replication Server, which has functionality which allows one to replicate on demand.

back
back to top
 

The Replication Server.

Most of the time the Replication Server will run as a service on NT, or a daemon under Unix. One can, however, run a GUI version of the Replication Server on the Wintel platforms.

This allows administrators to see exactly what is going on as it happens, and also allows the administrator to initiate replication on demand.

The GUI version will also be used when 'synchronizing' two databases, which can occur when a new database is introduced into a replication schema.


 

back
back to top
 
 

Features of Interbase Replication

  • Fast: Replication occurs directly between servers: there are no intervening layers of processing from database engines or drivers, for example. Our benchmarks indicate that actual replication speeds range from five operations per second over a 28.8 dial-up connection to 100 operations per second between two 200Mhz Pentium machines on a 10BaseT network, where an "operation" is an insert, an update or a delete.
     
  • Small: IBReplicator consists only of the necessary code; there is none of the overhead associated with any form of middleware. The Replication Engine requires 2Mb of disk space on the Server and a further 2Mb on the Replication Manager machine.  In a Windows environment these may run on the same machine.
     
  • Advanced datatypes: IBReplicator can replicate all supported InterBase datatypes, including BLObs and Arrays.  It handles multi-segment primary keys where each segment can be any supported InterBase datatype.
     
  • Event-alerters: Replication can occur in response to database events.
     
  • Two-phase commits: IBReplicator uses InterBase's two-phase commit protocol to ensure that source and target databases are always synchronized.
     
  • Internationalization: International character sets are supported.
     
  • Point-and-click configuration: This tool allows you to select which tables and fields are to be replicated, and to view and edit optional settings; it also generates the required triggers on the source database for you.
     
  • Replication Monitor: This tool provides a real-time graph showing the status of ongoing replications. It also provides information and statistics on all related connections, throughput and activity in the environment.
     
  • Multiple source and target databases: IBReplicator allows you to publish the data in many databases, and to subscribe many databases to each of them.
     
  • n-way replication: Replication can be performed in one or all of the following ways:
    • One-way
    • Bi-directional
    • N-way.

Each target database can also be a source database in its turn, with replication being controlled by different instances of the replication server, or even by the same one.
 

  • Row Level Replication: Also called Domain Replication. This enables the limiting of data sent to a particular target to a particular domain.  For example only replicate rows for Departments 'A' and 'C' to Machine X, and replicate rows for Departments 'S' and 'Q' to Machine Y.
     
  • Replicate to Stored Procedures. The stored procedures must be defined by the user, and must conform to certain rules.
     
  • Sequence of events: All changes are processed in exactly the same sequence on the target database, as they were originally done on the source database.
     
  • Conflict resolution: Each Source/Target database pair can have its own conflict resolution settings. Each Source/Target database pair can have its own error/information logging settings. IBReplicator provides three ways to handle cases where replicated data conflicts with existing data in the target database:
    • Priority-based: Databases can be given priorities, and the database with the higher priority takes precedence.
    • Time-stamped: The latest change takes effect.
    • Master/slave: The source database always takes precedence.
       
  • Precedence: When replicating it often possible to get data conflicts. If the Source database has precedence the following will occur:
    • An Update finding no identical key record in the Target database will be converted into an Insert.
    • An Insert finding a record in the Target database with an identical key will be converted into an Update.
    • A Delete finding no identical key record in the Target database will be ignored.
       
  • Error Logging: All errors (can't ping target, record already exists, no record to delete, record locked etc.) are logged by the Replication Engine.
     
  • Multi-segment primary keys: IBReplicator recognizes primary keys made up of multiple columns, and allows each column to be of any InterBase datatype.
     
  • Complete transactions: Only committed transactions are replicated (and the replication server may bundle multiple transactions into super-transactions for reasons of efficiency).
     
  • Field names: Target tables need not have the same field names and data types.
     
  • Sync Source and Target:  It is possible to synchronize source and target databases and also to build a new target from scratch.
     
  • Passwords:  All passwords are encrypted.
     
  • The following platforms are supported,  others will be dictated by demand and as Interbase 6 is ported to additional platforms:
    • Win95/98
    • WinNT 4.0
    • Solaris 2.6
    • HP-UX 10.20/11.0
    • Linux
  • A replication scheduler has been provided. This Windows NT service can be configured to invoke replication at different intervals, such as daily, hourly, on certain days of the week, etc. Although the service runs on an NT machine, it can invoke replication on any platform. The tool has two components: a manager which is used to configure the timing, and the service which invokes Replication at the appropriate times. The manager can also monitor the times on a Win95 machine.

back
back to top

Summary

Replication is a powerful tool, which allows one to develop complex applications which would otherwise be difficult or impossible to implement. This does not mean that Replication can be seen as a cure for all problems. On the contrary, when one wants to use the power of replication, one must have planned for it's inclusion from the start.

Replication adds to the overall complexity of a project, while at the same time, often proving the difference between the project being a success or a failure.

The ability to replicate to a stored procedure, is extremely powerful, and allows one to do almost anything one likes to the data on the target database. This means that data which normally might not be suitable for replication, can be replicated.

Whether you are using Replication to provide a warm backup, or are splitting the user load across multiple servers, or are consolidating countrywide data to head-office, Interbase 6 has provided a powerful too to broaden your database horizons.
 

back
back to top
 
 

Bibliography:

Data Replication: Tools and Techniques for Managing Distributed Information. Author: Marie Buretta
 
 

Copyright: © 2000 Synectics Software

Server Response from: ETNASC04