open database southwind
alter table example add constraint;
 (title set check inlist(alltrim(title),"Miss","Mr","Mrs","Ms"))

In this article Barry Mavin, CEO and Chief Software Architect for Recital provides details on how the Recital Database Server can be used to provide a solution for Universal Data Integration.

Overview

The Recital Database Server handles universal cross-platform data access to a wide range of data sources. The database server natively handles full remote SQL data access to Recital, Visual FoxPro, FoxPro, FoxBASE, Clipper and older dBase data. Using Bridges, it handles full remote SQL data access to C-ISAM and OpenVMS RMS. Using gateway connections, it handles full remote SQL data access to Oracle, MySQL, PostgreSQL, SQL Server, server-side ODBC, server-side JDBC and server-side OLE DB data sources. With its ability to access data using server-side ODBC, JDBC and OLE DB drivers from clients on all supported operating systems (Windows, Linux, Unix, OpenVMS), the Recital Database Server is an ideal Data Integration Solution for applications of all sizes and complexity.

Universal Data Integration Solutions

There are several ways in which data may be accessed by the Database Server.

Table 1:

Client Universal Data Access solutions for accessing local or remote data.

Table 2:

Windows Server Universal Data Access solutions accessible from any remote client running on Windows, Linux, Unix or OpenVMS:

Table 3:

Linux and Unix Server Universal Data Access solutions accessible from any remote client running on Windows, Linux, Unix or OpenVMS:

Table 4:

OpenVMS Server Universal Data Access solutions accessible from any remote client running on Windows, Linux, Unix or OpenVMS:

Supported Data Sources

Native Data Access

The Recital Database Server has native built-in support for the following data sources:

• Recital
• Visual FoxPro
• FoxPro
• FoxBASE
• Clipper
• dBase

You can setup tables to work with using the Database Administration Tool in Recital Enterprise Studio.

Bridges

Using Bridges, you can access the following data sources as if they were standard Recital/FoxPro tables:

• CISAM
• OpenVMS RMS

You can setup bridges using the Database Administration Tool in Recital Enterprise Studio.

Gateways/Connections

Using Gateways, you can transparently access the following local or remote data sources:

• Recital
• Oracle
• ODBC (Server-side ODBC data sources)
• JDBC (Server-side JDBC data sources)
• OLEDB Use this to connect to SQL Server and other Windows OLE DB data sources)
• MySQL
• PostgreSQL

Remote Data Object functions

Recital 10 includes a complete and robust set of data source independent functions for accession MySQL, Oracle, DB2 and Postgres. This article explains how to use them.

Client Data Access drivers

Included with the Recital Database Server are three Client drivers. These Client drivers can access any data sources supported by the Recital Database Server. They are not restricted to accessing only Recital data. They can be used to access server-side ODBC, JDBC and OLE DB data sources also.

Recital Universal .NET Data Provider

Use this client driver when building .NET applications with Visual Studio .NET. A data provider in the .NET Framework serves as a bridge between an application and a data source. A data provider is used to retrieve data from a data source and to reconcile changes to that data back to the data source.

Key features of the Recital Universal .NET Data Provider:

• Fully Internet enabled
  The Recital Universal .NET Data Provider works across the internet providing access to a wide range of data sources located on remote servers running Windows, Linux, Unix and OpenVMS.
• SQL Server compatible
  The Recital Universal .NET Data Provider is plug compatible with the .NET Framework SQL Server Data Provider.
• Cross-platform Data Integration
  Using the Recital Universal .NET Data Provider, you can connect to remote Windows, Linux, Unix or OpenVMS servers and access any data source supported by the Recital Database Server.
• Managed code
  The Recital Universal .NET Data Adaptor written in C# is 100% .NET Framework managed code.
• Runs on Windows Mobile
  The Recital Universal .NET Data Adaptor runs under the .NET Compact Framework on Windows Mobile.

Recital Universal JDBC Driver

The JDBC API is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases. The JDBC API provides a call-level API for SQL-based database access. JDBC technology allows you to use the Java programming language to exploit "Write Once, Run Anywhere" capabilities for applications that require access to enterprise data.

Key features of the Recital Universal JDBC Driver:

• Fully Internet enabled
  The Recital Universal JDBC driver works across the internet providing access to a wide range of data sources located on remote servers running Windows, Linux, Unix and OpenVMS.
• JDBC 3.0 API
  The Recital Universal JDBC driver supports the JDBC 3.0 API.
• Pure Java Type 3 Driver
  The Recital Universal JDBC driver is a 100% pure Java Type 3 driver.
• Full Access to Metadata
  The JDBC API provides metadata access that enables the development of sophisticated applications that need to understand the underlying facilities and capabilities of a specific database connection.
• Cross-platform Data Integration
  Using the Recital Universal JDBC driver, you can connect to remote Windows, Linux, Unix or OpenVMS servers and access any data source supported by the Recital Database Server.
• No Installation
  A pure JDBC technology-based driver does not require special installation; it is automatically downloaded as part of the applet that makes the JDBC calls. The Recital Universal JDBC Driver is 100% java.

Recital Universal ODBC Driver

Connect to remote data from Microsoft Office or other applications that support ODBC data access. The Recital Universal ODBC Driver is also available for Linux and Unix.

Key features of the Recital Universal ODBC Driver:

• Fully Internet enabled
  The Recital Universal ODBC driver works across the internet providing access to a wide range of data sources located on remote servers running Windows, Linux, Unix and OpenVMS.
• Works with Crystal Reports
  The Recital Universal ODBC driver supports the SQL syntax generated by Crystal Reports.
• Works with Microsoft Office
  The Recital Universal ODBC driver works with Microsoft Office products.
• Works with PHP on Linux
  The Recital Universal ODBC driver is available for Linux and works with PHP.

DRBD:
DRBD (Distributed Replicated Block Device) forms the storage redundancy portition of a HA cluster setup. Explained in basic terms DRBD provides a means of achieving RAID 1 behavoir over a network, where whole block devices are mirrored accross the network.

To start off you will need 2 indentically sized raw drives or partitions. Many how-to's on the internet assume the use of whole drives, of course this will be better performance, but if you are simply getting familar with the technology you can repartition existing drives to allow for two eqaully sized raw partitions, one on each of the systems you will be using.

There are 3 DRBD replication modes:
• Protocol A: Write I/O is reported as completed as soon as it reached local disk and local TCP send buffer
• Protocol B: Write I/O is reported as completed as soon as it reached local disk and remote TCP buffer cache
• Protocol C: Write I/O is reported as completed as soon as it reached both local and remote disks.

If we were installing the HA cluster on a slow LAN or if the geogrphical seperation of the systems involved was great, then I recommend you opt for asyncronous mirroring (Protocol A) where the notifcation of a completed write operation occurs as soon as the local disk write is performed. This will greatly improve performance.

As we are setting up our HA cluster connected via a fast LAN, we will be using DRBD in fully syncronous mode, protocol C.
Protocol C involves the file system on the active node only being notified that the write operation was finished when the block is written to both disks of the cluster. Protocol C is the most commonly used mode of DRBD.

/etc/drbd.conf

global { usage-count yes; }
common { syncer { rate 10M; } }
resource r0 {
protocol C;
net {
max-buffers 2048;
ko-count 4;
}
on bailey {
device    /dev/drbd0;
disk      /dev/sda4;
address   192.168.1.125:7789;
meta-disk internal;
}
on giskard {
device    /dev/drbd0;
disk      /dev/sda3;
address   192.168.1.127:7789;
meta-disk internal;
}
}

drbd.conf explained:

Global section, usage-count. The DRBD project keeps statistics about the usage of DRBD versions. They do this by contacting a HTTP server each time a new DRBD version is installed on a system. This can be disabled by setting usage-count no;.

The common seciton contains configurations inhereted by all resources defined.
Setting the syncronisation rate, this is accoimplished by going to the syncer section and then assigning a value to the rate setting. The syncronisation rate refers to rate in which the data is being mirrored in the background. The best setting for the syncronsation rate is related to the speed of the network with which the DRBD systems are communicating on. 100Mbps ethernet supports around 12MBps, Giggabit ethernet somewhere around 125MBps.

in the configuration above, we have a resource defined as r0, the nodes are configured in the "on" host subsections.
"Device" configures the path of the logical block device that will be created by DRBD
"Disk" configures the block device that will be used to store the data.
"Address" configures the IP address and port number of the host that will hold this DRBD device.
"Meta-disk" configures the location where the metadata about the DRBD device will be stored.
You can set this to internal and DRBD will use the physical block device to store the information, by recording the metadata within the last sections of the disk.
Once you have created your configuration file, you must conduct the following steps on both the nodes.

Create device metadata.

$ drbdadm create-md r0
v08 Magic number not found
Writing meta data...
initialising activity log
NOT initialized bitmap
New drbd meta data block sucessfully created.
success

Attach the backing device.
$ drbdadm attach r0

Set the syncronisation parameters.
$ drbdadm syncer r0

Connect it to the peer.
$ drbdadm connect r0

Run the service.
$ service drbd start

Heartbeat:

Heartbeat provides the IP redundancy and the service HA functionailty.
On the failure of the primary node the VIP is assigned to the secondary node and the services configured to be HA are started on the secondary node.

Heartbeat configuration:

/etc/ha/ha.conf

## /etc/ha.d/ha.cf on node1
## This configuration is to be the same on both machines
## This example is made for version 2, comment out crm if using version 1
// replace the node variables with the names of your nodes.

crm no
keepalive 1
deadtime 5
warntime 3
initdead 20
bcast eth0
auto_failback yes
node bailey
node giskard

/etc/ha.d/authkeys
// The configuration below set authentication off, and encryption off for the authentication of nodes and their packets.
//Note make sure the authkeys file has the correct permisisions chmod 600

## /etc/ha.d/authkeys
auth 1
1 crc

/etc/ha.d/haresources
//192.168.1.40 is the VIP (Virtual IP) assigned to the cluster.
//the "smb" in the configuration line represents the service we wish to make HA
// /devdrbd0 represents the resource name you configured in the drbd.conf

## /etc/ha.d/haresources
## This configuration is to be the same on both nodes

bailey 192.168.1.40 drbddisk Filesystem::/dev/drbd0::/drbdData::ext3 smb