Database replication is a useful technology. You can use it to maintain multiple-failure, hot-standby databases and read-only, Web-enabled databases; you can use it to propagate lookup data throughout a distributed organizationıs databases; and you can even use it to populate and maintain data warehouses and data marts. Moreover, having written a database replicator for Ingres that is still in production use today, I also know replication is a very complex technology.
A database replicator must be transaction based, asynchronous, serializable, unobtrusive, robust, configurable, manageable, and, above all, efficient and easy to manage. In an ideal world, it should operate transparently across heterogeneous hardware platforms, operating systems, network protocols, and even database systems.
In this monthıs column, I investigate how the PeerDirect replicator release 3 from Current Network Technologies Corp. (based in Mississauga, Ontario Canada) measures up to these requirements. PeerDirect release 3 was in beta testing at press time, but it should be generally available by the time you read this.
When you use any database replication product, you must understand the underlying design philosophy (and accompanying terminology), because it can have a big impact on how your data is distributed and replicated. With PeerDirect, a database is maintained at a site. The database is considered a collection of work sets. A work set is a set of closely related tables, such as customers and customerdetails, or invoices and invoicelines. A work set is further divided into slices, each of which represents an instance of the work set. For example, a particular customer, with its details, is one slice. A base record in a base table uniquely identifies each slice. For example, each customer row in the customers table is a base record. The choice of base table depends on your business rules. The only thing that PeerDirect requires is that each work set have a base table. A work set can be nested under another work set, and the nested tables are then replicated with the parent work set.
One of PeerDirectıs design goals is that each site stores all and only the data the users require. Users who have been granted the da_subscribe right by the initial admin user must subscribe to the slices in which they (and other users at the site) are interested. The users with da_subscribe rights at any site can subscribe to any grouping of slices. For this purpose, all the nonnested base records are replicated to each site so that users can select the records of interest. Their sites will only store and maintain the data of the nested rows to which they subscribe. When the users with the da_subscribe rights unsubscribe from a slice, the sliceıs data is deleted from the local site. However, it is still maintained at all the other subscribing sites. It is important to unsubscribe from a slice only after all operations on the slice have been successfully replicated. A table can be designated as global, which means it will be replicated in its entirety to every site. The "admin" user, who is automatically created when PeerDirect is loaded at the first site, can create new users and grant and revoke rights to them.
Furthermore, you can group table columns in "fragments," or sets of nonprimary-key columns that are updated together. PeerDirect replicates only the updated fragments, rather than the entire record, to the interested sites. In some scenarios this replication can eliminate update collisions. It can also reduce network traffic and replication processing loads. However, PeerDirect requires each fragment to have a stamp column, which is a 26-character field, whose name must begin with the letters stamp. PeerDirect uses the stamp column to store the ID of the user who last updated the fragment, the site and time at which this update was made, and an encrypted integrity check value. PeerDirect automatically adds a stamp column to each replicated table, but if you want to use fragmentation, you must add a stamp column for each fragment. Because the primary key is considered a nonupdatable part of each fragment, its values cannot be changed ı you must delete and insert rows to change the primary key values in a fragmentation scheme. PeerDirect supports key columns of byte, short, long, string, Boolean, and timestamp datatypes.
When you "PeerDirect-enable" a database (as they phrase it), you run a utility that creates several system tables in the database, which are used to maintain the replication configuration. The names of these tables are all shorter than eight characters, and they all start with "d," as in dSite, dUsr, dMsg, and so on. Application programs should not access these tables, and your database design should not contain similarly named tables. Most replicators I have worked with use system tables to store configuration data. Imagine how unfortunate it would be if the packaged application you use happens to have tables with the same obscure naming convention. Improbable, but not impossible!
It is difficult with any replication system to define the entire data replication scheme. With PeerDirect, you use a C-like language called Distribution Control (DC) to define how your databases should be replicated, shared, and secured. You use DC to define the work sets, fragments, encrypted columns, operations, and replication rules in script files. A DC script file can include other DC script files. The script is compiled using PeerDirectıs DC compiler, DCC, which ensures that the scriptıs rules match the databaseıs physical structure. The distribution information is then considered part of the database schema and is stored in a DC file associated with the database.
Database administrators accustomed to GUI-based configuration and management tools may object to the use of scripts, but personally I prefer this linguistic approach. Scripts can be ported between systems easily, and they can be generated from CASE tools and other similar systems. GUI tools, on the other hand, often require you to redefine the replication configuration interactively ı and manually ı against each source database. A GUI-based administration tool is planned for a subsequent release of PeerDirect.
Users can subscribe and unsubscribe to slices using the PeerDirect Administrator or by calling the PeerDirect APIs. With the PeerDirect Administrator, you can select base tables and subscribe to individual slices. However, the user must have the necessary administrative and subscription rights. Subscriptions, as well as subscription rights, can also be maintained through the PeerDirect API calls. The alternative to subscriptions is to designate a site as a Full Site, meaning it will contain a copy of the entire database. With PeerDirect 3.0, users can automatically subscribe to work sets. This ensures that their sites will regularly receive all the new data allocated to that work set on the source database. For example, if you have auto-subscribed to the customer work set, any new customers will automatically be replicated to you.
Before an application can access a database replicated by PeerDirect, it must be registered as a PeerDirect project. You can use the PeerDirect Administrator to register a project, or you can use a command-line utility called dnewproj. The registration occurs against the development database. Each projectıs unique name is used to add information about the application to the PeerDirect development environment. This includes the "organization" (department or team) developing the project, optionally with a password. I thought that a replicator only needed to focus on changes to the database contents, not necessarily the applications that access it, but in the PeerDirect environment projects are registered to ensure that only the operations of legitimate applications are replicated. However, if you follow the correct protocols, such as setting the stamp values correctly, you can update the database through an interactive interface, and these changes will also be replicated.
For each project you must also register editions and networks. You can use the PeerDirect Administrator for these tasks, or you can use command-line utilities called dnewedn and dnewnet, respectively.
An edition, in PeerDirect nomenclature, is a version of a database. Tables in different editions can be replicated to each other, as long as they have compatible formats (the same table and column names and closely matched datatypes). Editions are further categorized by releases, which help keep track of the individual databases that make up the edition. A network, in PeerDirect terms, is a related group of databases. For example, the databases used during development form one network, while the databases used together during production form another network.
Before running your applications against the replicated databases, you must create a release package that is installed at the relevant sites. A release package consists of a database, a configuration file, and the application executables. Similar to an edition, a release package is specific to a database version. PeerDirect uses encrypted certification to validate the releases. You can create release packages through the PeerDirect Administrator or through the dnewrel command-line utility. You first install a release at one site and populate the system tables with all the necessary configuration data. From here it is then replicated to the subsequent installation sites.
You can implement PeerDirect in one of many replication configurations. It currently supports Oracle7 and 8, Microsoft SQL Server, Informix, Sybase SQL Anywhere (with plans for Adaptive Server Anywhere), Microsoft Access, and Corel Paradox databases on any platform, with support for Sybase SQL Server, IBM DB2, Pervasive, and others planned for later in 1998. The PeerDirect Replication Server runs on Windows 95 and Windows NT, and it accesses the database as a client application, so the database itself can reside on a Windows, Unix, or mainframe server.
With autonomous standalone sites such as mobile laptop computers, each site runs your application, the application database, and the PeerDirect Replication Server. One of these sites must be designated a "master" site for installation and running the PeerDirect Administrator.
In an autonomous office workstation configuration, you can use autonomous workstations, each with its own applications, application databases, and replication servers, very similar to the standalone configuration I just described. Administration and installation would be performed on the central database server, from where it is replicated to the workstation databases. In this configuration, each workstation works autonomously while sharing data with the central database and, optionally, with other workstations, through the PeerDirect replication mechanisms. This configuration is illustrated in Figure 1.
In an autonomous office with a single, shared-database database configuration, the workstations only run the application programs. They access data stored in a central database, which, through the PeerDirect Replication Server, can be replicated to other autonomous offices, and/or to a hot-standby site. The PeerDirect Replication Server and Administrator can be run on the database server or, for improved load sharing, on a specialized replication server platform.
The PeerDirect Replication Server also scales very well. It can be deployed on a single-processor, low-memory notebook machine, but it will also use the extra processors and resources on a multiprocessor machine with extra memory and a fast network connection without requiring any additional configuration or administration changes. It has a multithreaded architecture, running two threads for each active replication link ı one for database transactions and the other for network communications. Because of the replication engineıs multithreaded architecture, it can scale on any multiprocessor hardware that can run individual threads of a single process on multiple processors. At this stage, it supports Windows NT or Intel SMP machines, with support for MPP and NUMA planned for a future release.
The replication configuration options with PeerDirect are very flexible. It does not force you into a particular replication configuration or data allocation scheme because of replication software or available platform limitations. It supports bidirectional peer-to-peer replication among heterogeneous databases without requiring that you nominate master and slave sites. In essence, you can replicate your data in a configuration that suits your business model.
Delete operations are a bit more complex than insert and update operations when using PeerDirect replication. When PeerDirect detects a deleted row, it assumes the row was deleted accidentally and tries to restore the row from another site. As a result, you must delete rows using the PeerDirect API (calling the procedure DSECDeleteRecord) or by inserting rows in the PeerDirect system table (called dDel). When you call the DSECDeleteRecord procedure, PeerDirect ensures that all related records are also deleted. With the system table approach, you write the key of the row to be deleted in the dDel system table using a specified syntax. This can be done through a trigger, for example. During the next replication cycle, PeerDirect calls DSECDeleteRecord for every entry in the dDel table. However, this approach can only be used with the A1 security mode. (Iıll describe PeerDirectıs two security modes later.)
This implementation of deletions can have a severe impact on applications running against the replicated database. Each application has to be changed to use one of these two forms of deletion. Alternatively, you have to define a trigger on each table to perform the necessary operations on the dDel table. I can appreciate the problems caused by deleted rows ı the replication software can hardly ever detect if the deletion was intentional or accidental. However, I personally would prefer a less intrusive implementation. In some OLTP applications (which are often replicated to hot-standby sites), delete operations are a fact of life. Although the delete problems can be hidden in trigger code, some poor DBA must still write, check, and implement these delete triggers.
You can use an A1- or B3-level security scheme to protect the data in the databases participating in a PeerDirect replication scheme, but the transmissions across the network are always encrypted. With the default A1 level of security, the data is stored in the normal format and applications can access the data without using the PeerDirect API calls. In this scheme, each rowıs stamp field must initially be set to null or left empty, and the PeerDirect replicator will maintain its contents.
With B3-level security, the application must fill in the stamp field of each row, which means it must make the necessary PeerDirect API calls. If the data columns are encrypted ı which you specify in the DC script ı your application needs to make PeerDirect API calls to decrypt the data before using it. Similarly, the application must call the PeerDirect API to encrypt data before storing or updating it. When you use encryption, PeerDirect uses a different key at each site, and it performs its own key management functions. You have to initialize the PeerDirect library before running your application, and terminate it before completing the application. In addition, the encryption functions are only available to valid PeerDirect users, which means the application has to log on using a valid username/password pair. Before completion, it has to log off again.
You can specify which users may access which information by restricting the operations they may perform in the DC script. During compile time, these definitions are stored in the PeerDirect system tables. In the first replication cycle their contents are replicated to each PeerDirect site. During run time, however, administrators can use a PeerDirect administration function to grant access rights on specific slices to specific users. The default rights are create, read, write, and delete, but you can also define your own rights in a DC script. During run time, an application can query the userıs access rights through a PeerDirect API call.
PeerDirect 3.0 does not support transactions. It replicates the operations performed on the work sets and fragments. I can appreciate the difficulty in replicating a database such as Microsoft Access, which doesnıt support the atomicity property of transactions. However, when you want to replicate the other databases supported by PeerDirect, you definitely want this functionality.
Fortunately, Current Network Technologies designed a scheme to replicate transactions across all the supported databases. This scheme will be implemented in PeerDirect 3.1, scheduled to be released later this year. The database administrator has to design so-called transaction sets, which designate tables whose operations should be replicated together as a transaction. This solution does not compromise their full peer-to-peer, update-anywhere replication scheme, but it may have a number of interesting implications. First, for databases not supporting transactions, the operations will now be replicated in the context of a transaction. This is useful, for example, when replicating from Microsoft Access into a transactional database such as Oracle. Second, this approach may be used to partition large transactions in subtransactions. Some replication products do not perform as well when replicating huge transactions. The buffer space required, as well as the amount of processing that has to take place, can severely degrade replication performance. The transaction sets can be used to partition huge transactions into subsets consisting of nonoverlapping tables. My last observation is that designing transaction sets can be complex in some environments. For example, I have a client running a replicator in a complex, high-volume OLTP environment. A transaction can easily consist of 10 to 20 operations that update five to eight tables, but each different type of transaction does not necessarily update the same sets of tables. Designing nonoverlapping transaction sets in such an environment may be tough.
Considering the criteria for a good replicator, namely transaction-based, serializable, asynchronous, unobtrusive, robust, configurable, manageable, efficient, and transparent, PeerDirect fares pretty well. It replicates asynchronously, it is robust, and it can handle different types of problems. It is highly configurable. It can replicate peer-to-peer, update-anywhere operations in many directions, which some competing products cannot do. It can replicate fragments, which few other replicators can do. It is easy to manage, especially when scaling up to a large number of databases. Most important, it can replicate transparently between Oracle7 and 8, Microsoft SQL Server, Informix, Sybase SQL Anywhere, Microsoft Access, and Corel Paradox databases, which very few other replication products can do.
However, in my opinion PeerDirect has two problem areas. First, it doesnıt replicate within transaction boundaries, which means that you can have the effects of partial transactions replicated to some sites. As I mentioned already, this problem should be addressed in the subsequent release. Second, PeerDirect 3.0ıs implementation of delete logic is not always unobtrusive. Some applications may require PeerDirect API calls to delete rows, while other databases may require additional triggers to implement its delete procedures. If you plan to use B3 security, you must implement calls to the PeerDirect API to obtain the encrypted stamp values. You may also argue that the requirement to add additional stamp columns for fragmentation is not unobtrusive, but you must agree that fragmentation is very difficult to implement, and few replicators even attempt it. The price of these database changes for fragmentation may not be so high if you need that kind of logic.
Finally ı and this is true of any replication product ı a replicator is a tool you use to implement a business solution. The replicator itself is not the business solution. You still have to design and implement a replication scheme, which in some cases may be pretty complex. The replication product only gives you the capabilities to put your business solution into action. It can influence how well your business solution is implemented, but you first have to design the business solution.
Figure 1. The PeerDirect replication mechanism configuration. (Source: Current Network Technoloiges Corp.)
| Current Network Technologies Corp., Mississauga, Ontario, Canada; 416-805-9088 or fax 905-822-3824; www.peerdirect.com. |