Database Replication

Basically three kind of actors are involved in the replication process:

The Source, which also can be identified as the Master , is the data which will be replicated. The xmlBlaster is the MOM responsible for the transport and delivery of the data to the Destinations. The later are also called Sinks of the replication. We will normally be dealing with Single Master / Multiple Slaves replication, meaning there is a single source but there can be multiple destinations. The destinations can be plugged in and out dynamically. This approach excludes conflicts and eliminates this way the danger of using error prone and complex conflict resolution algorithms.

	Note
	It is possible to use this replication in a Multimaster approach but this can only be safely done if the business logics are such that no conflict can occur in the replication of the data. This is the case for situations where the data for which individual masters are responsible never overlap each other.

The following are the basic steps in our Replication Workflow:

Step 1 and 2 are executed in the thread which initiated the change on the source database. This is normally the thread of the application making a change on the database. The code executed in these steps is PL/SQL. It is basically a set of functions, triggers, tables, views and sequences which we further will call the DB Resources . Particularly important in this context is the table which works as the outgoing queue. This is the interface to the publishing step (step 3). This table is called ${replication.prefix}items where ${replication.prefix} is a configuration parameter identifying the replication source. This table contains all the changes having to be replicated:

Step 3 occurs outside the database. It is an asynchronous step meaning some application polls and checks if changes have occurred on the ${replication.prefix}items table. If such a change occurred it will be published. More specifically this misterious application is an xmlBlaster Client called DbWatcher . Such client can either run inside the same JVM as the XmlBlaster server as a native client plugin, or it can run as a standalone application.

The distribution of the generated messages (Step 4) is handled by the XmlBlaster based on the Publish/Subscribe paradigm. More simply on this approach: if you want a message you have to manifest your interest by subscribing the same way as you would do when you subscribe to a magazine. The publisher, as the author of an article in that magazine, has not the slightest idea about who will read what he published.

The Subscribers get the replication data in an update event. They analyze and process the data according to the needs finally store it in the slave destination database. These clients are called DbWriter and can -as for the case of the DbWatcher- either run inside the server's JVM or as standalone application.

Figure 2.2. Replication Steps

Replication DB Resources, DbWatcher XmlBlaster and DbWriter are the components used for the replication.

When starting a new system you need to trigger an Initial Update

	Note
	This step is performed for each Destination. It implies processing on the master-, on the xmlBlaster- and on the slave-side. In case you get inconsistencies on your replicas or for other reasons you can always invoke an initial update again.

	Note
	The Master is a lazy fellow which does not perform any work until it is explicitly asked for. Until somebody really triggers a first initial update, nothing happens.

Since there are cases where it can be difficult for clients to communicate to the server, and for monitoring and administration purposes, it is convenient to centralize the most of the logics involved in replication on the server side. For this reason every Slave client has a proxy on the server side. Such proxies are called ReplSlave . All ReplSlaves are owned and administered by a ReplManagerPlugin . Whenever an administrator requests an operation on the a Slave, it is not the real slave (The DbWriter) which works, but this server side proxy object.

When using our replication each XmlBlaster instance contains one and only one ReplManagerPlugin. Each such object has exactly one MBean representing the replication: the ReplManagerPluginMBean. One ReplManagerPlugin can administer several replication sources: n DbWatcher and several replication destinations: m DbWriter .

Figure 2.3. Relationships between entities

Every DbWriter is always represented on the server side by a ReplSlave proxy. This is true even if the DbWriter is running as a native client in the XmlBlaster's jvm. We can therefore consider this as a one to one relation. Every DbWriter can receive its data from one or more DbWatchers. Normally it only gets data from one single DbWatcher but if conflicts are avoided it can receive data from several DbWatchers. On the other hand, every DbWatcher can feed one or more DbWriters so we have a many to many relation here. In the drawing this is represented by a p..q relation. Here p is a subset of n (for the DbWatchers) and q is a subset of m (for the DbWriters).

The central part of the replication is the ReplManagerPlugin. This is the instance the administrator interacts with. The ReplManagerPlugin knows all active DbWatchers, i.e. it holds a map of all masters implied in replication. Every such DbWatcher is identified with a key defined in its configuration as replication.prefix . Not all DbWatchers are must necessarily be replication-masters, they could serve other purposes. If they have to serve as replication-masters however, they need to have such a replication.prefix property defined.

Every DbWatcher serving as a replication master registers itself to the ReplManagerPlugin shortly after having connected or reconnected to the XmlBlaster and is unregistered when it is disconnected or when it temporary looses the connection to the XmlBlaster. The registration and un-registration happens by means of a point-to-point message sent to the ReplManagerPlugin. For this, and other reasons, the DbWatcher must be available when starting or restarting a replication.

	Note
	Currently the ReplManagerPlugin has an hardcoded session name which is known to the DbWatcher. So this is currently not configurable.

The DbWriters serving as slaves in the replication are also known to the ReplManagerPlugin. As for the DbWatchers, also here a map is hold. There are however some differences: First the ReplManagerPlugin does not really communicate with the real slave. It communicates with its server-side proxy, that is the ReplSlave associated to the involved DbWriter.

The second difference is the key: here the key is the relative session name of the DbWriter (for example client/Fritz/1 ).

The third difference is the way the registration is made: This occurs when the DbWriter connects to the xmlBlaster for the very first time. Since it is a persistent connection it never disconnects (unless it will leave the replication definitively). All DbWriters serving as replication slaves must define as the dispatch plugin the ReplManagerPlugin.

The ReplManagerPlugin listens to the Connection events and the subscription events and knows thereby when a new replication slave is available. When this is the case, it creates the proxy object (the ReplSlave). When the DbWriter subscribes to receive replication data, the ReplManagerPlugin detects it and initializes the proxy object. From now on the proxy is ready to work.

Note

In reality it is not the DbWriter which makes the subscription. Subscriptions are done on behalf of the relatively stupid DbWriter when triggering an initial update: the proxy object makes the subscription. Since the subscription is persistent, the persistence layer also replays the subscription during restart of the xmlBlaster. This way it is ensured that the receiver of the replication data is always available even if the real client is currently unavailable. Of course when the real client is unavailable, replication data is queued on the server-side until the real client is available again.

Once they have connected, the DbWriters are always known and registered in the ReplManagerPlugin even if they are temporary unavailable. This means that you will be able to start or restart an initial update even if the DbWriter is currently off-line.

There are several tables involved in the replication. The following is a short explanation. The tables are sorted in their importance order:

There is currently only one view used for replication purposes: COLS_VIEW. This is only needed for database vendors which do not have any possibility to add triggers which detect a table alteration. For such databases detection of these actions must occur by polling. Since the DbWatcher already polls, this detection is done by him too, before the ITEMS table is checked, a comparison between this view and a snapshot table of the same parameters taken in the previous sweep detects changed occured between the two sweeps. An example of a database where this view is used is postgres.

Currently the sole sequence used is SEQ which is used every time a unique number is needed. Its main purpose is to create the REPL_KEY which is the unique key for the ITEMS table. It is also used for the sequence of the TABLES table and for the DEBUG_TABLE table.

The functions used are several and can be different for different database vendor. We will shortly describe here the most important ones and such which are common to all vendors.

The following prerequisites must be fulfilled previously to trigger an initial update:

	Note
	It is not necessary that the DbWriter is currently online, it suffices that it has connected, i.e. it must have logged in once.

	Important
	When connecting the DbWriter to the XmlBlaster you must specify that you want to use the DispatchPlugin[ReplManager][1.0] as a dispatcher plugin.

The following image shows the initial update workflow graphically:

Figure 2.4. Initial Update

When the DbWriter connects to the XmlBlaster, the ReplManagerPlugin gets an event. If the connecting client is a replication slave, then it must have chosen the ReplManager as the dispatcher plugin. If this is the case, the ReplManagerPlugin creates a new instance of a proxy object: the ReplSlave. It is also registered in a map. It is however not initialized yet.

	Note
	The ReplManagerPlugin gets this event even when starting up the xmlBlaster if there are persistent connections. This way it is ensured that a slave is always represented server-side by a proxy even if it is currently off-line.

In cases where it is not indicated to perform an initial update over the wire, for example in cases where the initial data is huge or the network connection slow, there is the option to transfer these data by means of files.

Per default this feature is switched off. To activate it you have to edit the customize.xsl file which resides in org/xmlBlaster/contrib/replication/monitor. You edit the line:

  <xsl:param name="show.initialFilesLocation" select="'no'"/>
  to be
  <xsl:param name="show.initialFilesLocation" select="'yes'"/>
          
          

	Note
	It is important to keep the single quote wrapping 'yes' or 'no' inside the double quote. Omitting these will fail to work.

Once you have activated this field and restarted xmlBlaster you have one more field when choosing the initial update page looking like the following:

Figure 2.5. Initial Update with manual file transfer

After having initiated the replication with the store initial data checkbox enabled, the initial workflow in the background is basically the same as for a normal initial update with some slight differences. All data is queued as usual, but:

In the second phase of the initial update, i.e. when the dispatcher is enabled again for delivery to the slave, the property mentionned above is recognized and the message is not sent to the slave, instead it is written locally on disk at the location you specified.

The two end-of-update messages serve the following purpose: When the first is detected at the delivery step it is not delivered, instead the dispatcher closes again. At this time the first message in the queue will be the second end-of-update message.

The administrator now has to manually copy the files for example on a CD-ROM and deliver it to the slave. On the slave side it has to be copied from the CD-ROM to the location specified by the replication.initialFilesLocation property of the slave.

When the files are on location, the administrator simply activates the dispatcher for that particular slave again and the second end-of-update will be delivered to the slave. On the slave side, when this message arrives, it is detected. Thereby the slave knows it has to search for the messages on the file system and starts to load these files. When the process is successfully ended, the message is acknowledged and the initial update phase is completed.

If an error occurs, the message is not acknowledged, instead an exception is thrown back to the server, the dispatcher is closed again and the error message is visualized in the details pages of that particular slave.

The structure of the data on disk

In a normal initial update, the initial data is transfered in chunks to avoid to exceed the maximum size allowed for example if the underlying communication protocol has some restrictions. When using the manual file transfer these chunks are written to file once they are coming in the destination queue. Each of these chunks is then a file on the filesystem. All these files are collected in a subdirectory. The name of the subdirectory is assigned each time an initial update is triggered and is ensured to be unique. In fact it is a unique timestamp.

The name of the chunks is constituted as ${prio}-${timestamp} where ${prio} is the priority of the message and ${timestamp} is the timestamp used to identify the entry in the queue. This way the messages are in the correct order when sorted alphabetically.

Figure 2.6. File structure of Initial Update with manual file transfer

On initial update, information about tables to be watched was gathered. Depending on the configuration a trigger was added to the table to be replicated.

When a change occurs on a table to be replicated, the trigger on that table is fired. On Insert, update or delete the trigger ensures that the change is serialized as an xml literal to be stored either in the content or in the oldcontent column of the ITEMS or in both. The entry is then stored in the ITEMS table. Such an approach allows to gather replication data even if the XmlBlaster should be down for some reason. This means that if the xmlBlaster is not available this only delays the replication until the xmlBlaster is available again, but no data will be lost. Now the replication data is temporary queued in this table until it is picked up by the DbWatcher.

As we saw in the previous section, the replication data has been queued in the ITEMS table. It is now the job of the DbWatcher polling thread to detect that something new is available on that table, pick it up, process it, publish it and finally remove the processed data from the queue.

The prepared messages are published by the DbWatcher to the xmlBlaster. All messages are handled persistently, so if a DbWatcher is running standalone and the XmlBlaster is currently unavailable, such messages are queued in a persistent storage. When the XmlBlaster becomes available again queued messages are published. Even for cases where the DbWatcher is running embedded the messages are persistent. This to avoid loss of messages on runlevel changes. When messages are published, i.e. are stored in the persistent queues of all interested slaves, the DbWatcher thread returns and removes the processed messages from the ENTRIES table. On an XmlBlaster crash happening after having successfully published the messages but before removing the entries from the table it could occur that the same change entry is published twice. This is detected later by the DbWatcher and the message is marked to avoid double delivery.

When the DbWatcher is publishing the messages it is not necessary for the real slave to be available since the data is stored persistently on a serverside queue (also called the callback queue). If it is available, or when it becomes available, the message is delivered to the slave if and only if the message is not obsolete. A message can be obsolete if it was already delivered, i.e. if it was marked by the DbWatcher as a double. Messages are also obsolete if they are older of an ongoing initial update. In other cases the message is delivered.

On the slave side, the responsible class taking care of the storage is the DbWriter. The DbWriter is a general purpose class used to store messages on a database. It is a minor plugin framework which makes use of interfaces which can be implemented differently for specific purposes. One of such specific purposes is indeed replication. Therefore the class making the job is the ReplicationWriter.

The ReplicationWriter checks what kind of action it is, performs the necessary filtering and modifications and executes the change on the replica database as a jdbc call. There is the possibility to ignore certain actions, the data content can be modified or the name of the destination tables or the columns can be modified. All this can be configured, and where configuration does not suffice the user can implement its own business-specific code for the available interfaces.

When an exception occurs a check is made to determine what the problem is. On INSERT or CREATE, if an entry was already found, the action is ignored and a warning is written to the logs. If an entry to be updated or deleted is not found, a warning is also issued. Other errors are logged and the exception is thrown back to the server. The server, on receiving the exception temporary stops the delivery to that specific slave client. At this point it is the responsibility of the administrator to analyze the error, fix it, and manually reactivate the delivery.

The configuration of the MX4JAdaptorPlugin is done as for the other xmlBlaster plugins in the xmlBlasterPlugins.xml configuration file. The following is an example of such a configuration. The files used for the xsl transformation reside in the directory: ${XMLBLASTER}/src/java/org/xmlBlaster/contrib/replication/monitor.

   <plugin id='MX4JAdaptorPlugin' className='org.xmlBlaster.contrib.jmx.MX4JAdaptor'>
      <attribute id="xsltProcessor">XSLTProcessor</attribute>
      <attribute id="host">localhost</attribute>
      <attribute id="port">9999</attribute>
      <attribute id="adaptorName">HttpAdaptorMX4J</attribute>
      <attribute id="xsltPath">org/xmlBlaster/contrib/replication/monitor</attribute>
      <attribute id="xsltCache">false</attribute>
      <attribute id="authenticationMethod">basic</attribute>
      <attribute id="replication.monitor.user.mike">usrPwd:user</attribute>
      <attribute id="replication.monitor.user.james">initPwd:initiator</attribute>
      <attribute id="replication.monitor.user.fred">adminPwd:admin</attribute>
      <action do='LOAD' onStartupRunlevel='4' sequence='4' onFail='resource.configuration.pluginFailed'/>
      <action do='STOP' onShutdownRunlevel='3' sequence='66'/>
   </plugin>
          
          

The Monitor has three main pages plus some other pages used to display results and errors. Every page has tooltips shortly describing the item's function.

You get this page per default. For example if you point your browser to http://localhost:9999 you will see this page. This page displays the status of each ongoing replication in a table. It is a monitoring page, that is, control operations can not be performed here. This page refreshes automatically with a frequency which is customizable. On this page you don't see replications which have not been initiated yet.

Figure 3.1. Replication List Page

The data is displayed in a table having 6 columns. The first column Destination Name displays the name of the slave associated to that replication. This name is normally either the session name of the slave or a subset of it. This name is customizable. You can click on these names to go to the details of that particular replication.

The Holdback Messages column displays how many entries are still to be delivered. These entries are in the Callback Queue for that client. Under normal operation it should display 0, meaning the holdback queue is empty. There can be three reasons why you have holdback entries:

The Counter column shows the current status of the counter. This is a monoton increasing unique positive integer identifying the message containing the data to be replicated. Be aware of the fact that also internal administrative messages use this counter. This means that in some circumstances there may be entries in the holdback queue which are not containing data to be replicated.

The Status column contains a semaphore having four statuses. One color for each status:

The Active / Standby column shows the status of the dispatcher in charge of delivering the messages from the server side to the slave. It has two statuses, either active, meaning the server tries to deliver the messages, and standby meaning it will not deliver even if the connection to the slave is OK.

The Connection column displays the status of the connection. It also has two statuses: either the communication is OK, meaning the slave can be reached from the server, or not OK meaning either that there is a communication problem (for example no route or disconnected ethernet cable), or the slave is not running. Since monitoring is done on the server side there is no way to distinguish among these two cases.

On the bottom of the page there are two buttons. The left button is used to manually refresh this page. The right Initiate Repl. button takes you to the page where you can initiate a new replication.

This page is used to start a replication for the first time. It basically has two display modus which are configurable in the customize.xsl stylesheet. The default is a simple modus which is determined by the parameter:

  <xsl:param name="show.cascading" select="'no'"/>
           
           

Or for the advanced case:

  <xsl:param name="show.cascading" select="'yes'"/>
           
           

The Simple Modus

Figure 3.2. Initial Update Page (simple)

In simple modus there are two multiple choice fields:

• The Source field lets you choose which master to use. The Master is identified by the replication.prefix attribute of the DbWatcher acting as master.

• The Destination field lets you choose which slave to use. The Slave is identified by the session name of the DbWriter .

As a source you only will see Masters which are registered.

Note

When a Master is not accessible, for example in case it is running as a remote application and the connection is temporary broken, it is automatically unregistered. It will be registered again once it will be able to access the XmlBlaster Server again. While unregistered it is not possible to initiate a replication where it is supposed to act as a master. In other words: the master needs to be available at the moment an initial update is triggered.

As a destination you only will see such DbWriters which have already connected to the XmlBlaster.

	Note
	In contrast to the Master -which needs to be available when initiating a replication- the slave does not need to be available all the time. It suffices that it has connected once to be known (or registered). It will not be unregistered every time the connection is broken. You can start a replication involving a DbWriter even if the connection to this is currently broken.

Once you have made the choice of a Master and a Slave you can click on the button Initiate Repl. which will start the replication and take you back to the replication list page.

The Advanced / Cascaded Modus

Figure 3.3. Initial Update Page (cascaded)

Cascaded modus means you can optionally choose a replication to be automatically initiated once the replication which you trigger now has reached its NORMAL operation status.This can be useful for example for cases where you have a back and forth replication and you don't want to manually trigger the back-replication everytime you want to initiate the replication. If you don't need the cascaded replication leave these fields empty.

	Important
	Once you have made the choice and pushed the Initiate Repl. button the cascaded option is persistently memorized. Subsequently, if you start again the same replication, you don't need to re-specify the cascaded replication: it will be remembered and executed again.

• The Source field lets you choose which master to use. The Master is identified by the replication.prefix attribute of the DbWatcher acting as master.

• The Destination field lets you choose which slave to use. The Slave is identified by the session name of the DbWriter .

As a source you only will see Masters which are registered.

Note

When a Master is not accessible, for example in case it is running as a remote application and the connection is temporary broken, it is automatically unregistered. It will be registered again once it will be able to access the XmlBlaster Server again. While unregistered it is not possible to initiate a replication where it is supposed to act as a master. In other words: the master needs to be available at the moment an initial update is triggered.

As a destination you only will see such DbWriters which have already connected to the XmlBlaster.

	Note
	In contrast to the Master -which needs to be available when initiating a replication- the slave does not need to be available all the time. It suffices that it has connected once to be known (or registered). It will not be unregistered every time the connection is broken. You can start a replication involving a DbWriter even if the connection to this is currently broken.

Once you have made the choice of a Master and a Slave you can click on the button Initiate Repl. which will start the replication and take you back to the replication list page.

If you click on the Cancel button you will be sent back to the replication list page without initiating a replication.

This page shows the details about a single entry. This is not only a monitoring page but also a control page. You can perform several operations on the particular replication.

Figure 3.4. Details Page

The first column on the left displays the property to monitor/control. The second column from the left displays the current status of that property. This is basically the same information which is also displayed in the replication list page. The rest of the colums display controls used to perform some operations, for example change the status of the property.

On initial update several messages are sent from the master to the slave. These messages are always intercepted by the ReplManagerPlugin which delegates processing to the ReplSlave.

This message is sent by a ReplSlave as a PtP to the session of the associated master to tell him to start a new initial update. The ReplSlave knows the session of the master since it knows his complete configuration. This message is published on the topic replRequestInitialData. The message is persistent and will be queued if the master is not available.

This message is sent by a ReplSlave as a PtP to the session of the associated master to tell him to cancel the previously triggered initial update. The ReplSlave knows the session of the master since it knows his complete configuration. This message is published on the topic replRequestCancelUpdate. The message is persistent and will be queued if the master is not available.

This message is sent by the ReplManagerPlugin as a PtP to the session of the master to tell him to recreate the triggers used to detect changes on the source database. This message is published on an anonymous topic. The message is transient and will be queued if the master is not available. No attributes are used for this command message. This is normally a request triggered on the JMX Console. No confirmation messages are sent back to the publisher of this message.

This message is sent by the ReplManagerPlugin as a PtP to the session of the master to tell him to execute a Statement (an SQL Statement) on the master and all the associated slaves. The statement can either be a read- or write statement (select or update). This message is published on the topic replRequestBroadcastSQL, is persistent and will be queued if the master is not available. A response of the result of the operation on the master is published on the topic specified in this request via a Data Message with command STATEMENT. Also is replicated to all associated slaves using the replication mechanism of the master (normally via its REPL_ITEMS Table. The message can be published as a high prio message (priority 8).

This message is sent by the ReplManagerPlugin as a PtP to the session of the master to tell him to start collect initial update commands. This message is published on the topic replSimpleMessage, is transient and will be queued if the master is not available. It contains no attributes.

This message is sent by the ReplManagerPlugin as a PtP to the session of the master to tell him to start the initial update for the collected requests (all INITIAL_UPDATE requests collected after the last INITIAL_UPDATE_START_BATCH). This message is published on the topic replSimpleMessage, is transient and will be queued if the master is not available. It contains no attributes.