Database Distribution: Introduction

Contents

• Overview
• DBMauto: The Automated Distribution System
• Duplicate SEQNO Handling
• Master/Slave Cron Job Synchronisation

Overview

The flow of data is shown schematically in diagram. At the heart of the system is the Master Database at Fermilab. Most database updates enter the database realm here. At regular intervals the DBM job dbmjob is used to export all recently updated data and these export files are distributed to all other databases where the data is imported if not already present. This is done by the local database manager again using dbmjob. These primary data flows are shown in red.

Smaller amounts of data come from secondary databases e.g. at CalDet and these are exported up to the Master Database where they join other up

This system relies on the ability to:-

• Record the insertion date so that updates can be incremental.
• Uniquely identify data so that it is not accidentally duplicated if attempting import more than once. For example updates to a secondary database might be reflected back if exporting all recent changes. However such data is ignored as duplicated data when resubmitted to the Master.

dbmjob can only distribute Dbi compliant database tables i.e. ones that come in pairs, the main data table and an auxiliary validity range table.

Along with the data, dbmjob exports table descriptions in the database update file. The Slave database ignores these descriptions for any table it already has otherwise it uses them to create empty tables before importing the associated data. This allows new tables to be added the Master database and for the Slave databases to automatically create them when they first receive data for them.

The system also distributes updates to the DBILOGENTRY table which records significant changes to the database i.e. those that could affect production running. These updates are written to the import log files and also broadcast to the list of names contained in:-

    $SRT_PUBLIC_CONTEXT/DatabaseMaintenance/scripts/logentry_mail_list 

DBMauto: The Automated Distribution System

The DatabaseMaintenance package provides a perl driver script to automate the system described above. The key points are:-

• At a site with access to the Master Database a cron job runs the script with a configuration file that specifies what data to export and where. Each time the cron job runs dbmjob it creates a new update file in a work directory, although these will be empty if no data has been added to the database since the last update. The driver script just removes empty updates and moves non-empty ones into a publish directory.

• At Slave sites the same script is configured to act as an importer. The driver script checks to see what update file was last imported and then looks for further updates, in order, in the publish directory.

• For Slave sites that don't have direct access to the Master's publish directory some type of mirror software has to run to provide a locally accessible mirror. One possible way to mirror is to use rsync. This is described below

• Note that the Master and Slave don't need to be in synchronisation. For example the Master might attempt to export every hour but a particular Slave site might run a cron job only once a week to import all the updates generated.

• The scripts mails errors, and regular summaries to a contact list specified as part of the configuration, so if all is working normally the contacts have only to ensure that they receive the summaries.

• As the scripts are configured locally its possible to build a distribution web with databases acting both as masters and slaves. The reflection logic described in the Overview ensures that updates don't get re-imported if a pair of databases both export to, and import from, one another.

Duplicate SEQNO Handling

Master/Slave Cron Job Synchronisation

• Cron jobs run no more frequently than once an hour.
• When they run:-
  • Master exports run on the half hour
  • Mirroring jobs run at 45 mins past the hour
  • Slave imports run one the hour