DSS-R approaches to replication usually are built on various technology variations of table copying. Tables at the target location are created one at a time drawing from one or more source tables or files. DSS-R copies are inherently read-only. Most approaches provide for transaction consistent data within a table, but are not concerned with transaction consistency across sets of target tables. A common environment is for tables to be updated after the close of business, so fully consistent environments are established by the morning.
The typical decision support application has a requirement for consistent period data sources and not necessarily for data that is up-to-the-minute current. DSS-R approaches, then, don't typically worry about keeping the data current (daily or less often, is typical for updates). Consistent, stable data for a given period is the highest requirement for these types of applications. The decision support systems are tuned for query processing, typically by adding more indexes. In this case, then, continuous propagation of updates would interfere with the ability of the query tool to provide reasonable performance (above and beyond the additional load that is created on the replication server).
The replication server should provide various timing options which can create copies based on timed events (clock or interval), on application events (e.g. end of day reconciliation completed), or on manual request.
Other important requirements for decision support include the ability to access legacy production system data from sources such as IMS, RMS, VSAM and flat files and to provide sophisticated data manipulation/enhancement to that data.
An example of data enhancement is what IBM has implemented in its Information Warehouse - a sort of three schema architecture for decision support purposes. Recognizing that operational systems frequently aren't correctly structured for supporting queries, IBM offers reconciled copies and derived data which summarize and add calculation value to the copies of data offered for decision support. The copies can be updated at any time and according to criteria established by the d.b.a.
DSS-R approaches are very useful in situations where companies are downsizing and the distributed applications need to share data with host legacy systems. The assumption of DSS-R is that updates will be made at the single source sites, not at the data copy sites. Sometimes, source data is in a central host, but other times it can be located in remote locations which own distinct data fragments. The data copies, however, are "read-only".
The predominant technology for DSS-R replication is some form of extract, manipulate, and further processing. These runs are typically batch jobs that occur after on-line transaction processing has ceased. It is much simpler to insure consistent transaction data is copied when the source table(s) are not being updated.
Alternatively, DSS-R may be provided through propagation of source table changes to the target. In large database environments (multiple 100s of gigabytes) where full refresh table copy transmission is economically or technically unfeasible on a nightly basis, change propagation is the only solution. In order to insure that consistent data is propagated in this scenario, a 2-phase commit process should be used for the changed data transactions.
The value added to the data by manipulation or enhancement is very important in DSS-R environments. Sources are typically legacy systems and the replication solution should provide the ability to restructure the data from legacy formats into the relational model. Tools should provide support for JOINing data from multiple sources, for calculating new values, for aggregating data and for transforming encoded data into descriptive forms. (See Figure 5). An important side point to keep in mind is that one of the principal benefits of DSS-R, aggregation of data or de normalization, is something that should not be done when the replicate is updatable. This will be discussed further below under TP-R Replication Schema.
Time based data is also important, particularly where trend analysis is desired. For this capability, the maintenance of data histories is important. Such histories can include complete records of all activities to a table, summaries based on point in time source data, and summaries based on changed data.
A common application model is one where there are 100's or 1,000's of database servers (e.g. in branch offices) fed from a smaller number of distribution nodes, which are in turn fed from a central host source. Efficient distribution requires support for cascading replicates where copies can be made from other copies. For example, the central host distributes to 20 distribution nodes each of which distributes to 20 branch offices. The replication system must distribute consistent data across each of the 400 branches (perhaps at the end of the business day), but at the same time, subset the data for branch related operations.
Where change capture and propagation schemes are used, there is a choice in the distribution model: whether to "push" the changes from the source to the target system(s) as they occur, or whether to "pull" the changes from the source system as the target(s) request. In general, the push model is best for continuous almost real time propagation, whereas the pull model is best for more loose currency requirements. This is because the pull model provides greater flexibility in reducing/combining the data at the source site. The pull model also allows more control and flexibility in the timing of network traffic.
For example, push systems typically distribute every transaction to the target. Target systems must therefore process every transaction. Where only summary data is required, data transformation is an added cost after replication. Pull systems, however, provide the opportunity for aggregation prior to distribution. This is effective both where only summary data is required and where database hot spots (areas within the database which receive the most update activity) can be netted out.
If you are distributing production operational systems, DSS-R technology isn't likely to work for you and a TP-R approach which can maintain near real time transaction integrity at data copy sites is essential. On the other hand, for decision support or other static data the need for real time information may not be important. Here, multiple schema's or data views, efficiency for size or cost reasons, and a consistent stable database for a specific period of time, will argue for copies of an end of period database.
Although many DBMS vendors are talking about replication offerings, it would be a mistake to assume that replication is a commodity. Different architectural approaches to the implementation of replication provide fundamentally different capabilities. Not only are there important replication server differences between DSS-R and TP-R approaches, but within each of these architectures there are important differences.
TP-R approaches have been implemented with two fundamentally different architectures by CA-Ingres and Sybase. CA-Ingres has built its replicator on a peer to peer architecture approach. Sybase uses a master/slave approach.
TP-R replication is primarily concerned with creating a single image of a database across distributed autonomous sites and preserving database integrity in near real time processing. The overall integrity of databases is preserved by forwarding data changes resulting from single user transactions.
All data replication, regardless of vendor, copies data from sources to targets. Master/slave approaches replicate data from master to slave, requiring updates to successfully complete at the master before the transaction is considered a success (as far as the application goes). On the other hand, updates in peer to peer approaches can be made to any data location and then copied into other locations. A transaction is successfully completed as soon as any one or combination of locations is able to update one complete copy of the affected data. Peer to peer allows all locations to own and manipulate any data, broadcasting changes as required.
In the master/slave architecture every table or table fragment is assigned to a primary site. If the primary table's database server fails or access to that server from the network (where a transaction updating that table has occurred) is denied, replication doesn't occur and the transaction is queued. This can present a problem for remotely generated transactions because those processes cannot update their local, or other sites, until they are first routed synchronously through their primary tables.
The master/slave approach to TP-R has the following characteristics:
- It's simpler for a vendor to implement (from the replication server point of view) because it eliminates the potential problem of update collisions (explained below).
- Because its implementation is simpler and more straightforward than peer to peer, in some circumstances applications will run faster because of lower DBMS overhead.
- It introduces a single point of failure that can lower the overall system availability as compared with the peer to peer approach.
- It's a less general solution than peer to peer.
Although the Sybase architecture is master/slave, the vendor states that its Replication Server can be set up to support a peer to peer approach. As is discussed below, collision detection and resolution software should be provided by any system that supports peer to peer transaction replication. Sybase normally requires that updates to slave databases be first routed through the master database. This eliminates the need for collision detection and resolution. However, if you want to build a peer to peer architecture with Sybase technology you'll have to write your own 1) collision identification software, 2) collision resolution logic and 3) logging transfer manager (including recovery). This would be work well beyond the capabilities of the typical DP shop.
The peer to peer architecture, of which CA-Ingres is the only vendor at this point in time, is the most general and powerful approach to TP-R replication. It is closest in capability to a true distributed DBMS in that there is no limitation on where data can be located or updated. And yet, because 1) we're talking about a replication server which uses many individual 2-phase commits to broadcast data changes and 2) those changes are asynchronously distributed from the originating application, peer to peer is more fault tolerant than a distributed DBMS.
A problem that is related to use of a peer to peer replication approach, however, is the possibility of "collisions". Collision occur when two different originating nodes update two different physical copies of the same logical data with two different transactions. When the replication server attempts to broadcast changes from each of those originating sites it will become aware of this conflict in updates and need to begin a process of reconciling the differences.
A collision is when the same record, which is physically replicated at two or multiple sites, is updated during the asynchronous latency period. In other words, after the time a first update has happened, a second update occurs which is processed at one site before the propagation of the first update has been completed. So although a peer to peer approach provides the most general solution for transaction distribution, it requires software for collision resolution.
When a collision occurs there is no way to construct an application independent approach that can recover all different types of databases. However, the replication server can and should have collision resolution logic. First and most important, collision resolution requires that the system provide notification that a collision has occurred.
From the moment any transaction is committed, the replication server has to keep track of all of the processes that further happen in the processing and distribution of that transaction. That's because in the event of a collision, this information has to be available to properly resolve the collision.
The replication server should support multiple options for the d.b.a. to choose from in resolving the conflict. Examples of resolution possibilities include:
- The initial update has priority. Rollback the conflicting (and later) transaction with necessary messages to designated parties.
- The last update has priority. Overwrite the conflict and send the necessary notices.
- Resolve the conflict by firing a user specified trigger.
- Halt the replication process and send a message to the d.b.a..
In order for a number of these processes to work it's helpful is there is a distributed time service available because current replication servers don't provide this. The replication server depends on the separate operating system clocks. If they aren't synchronized, errors will result. An important new facility for this service is OSF's Distributed Computing Environment (DCE) which provides the necessary synchronization.
Experience to date with users of peer to peer replication indicates that if the replication timing chosen is ASAP and if your databases have been properly designed for replication, the volume of collisions is likely to be very low. Those conflicts that do occur can be handled 1) by rules in a collision resolution software module with log entries for manual review, or 2) by manual review. Future capabilities for replication servers in this area may include expert systems to help resolve collisions.
Collisions don't happen with a master/slave architecture such as Sybase's. This is because the transaction is simply not accepted unless it can be committed at the master site, or what Sybase calls a "clearing house".
It might be useful to refer back to Figure 3 and re analyze what would have happened had peer to peer replication been used. In that case, the application, would have been accepted and considered successful at the completion of its first database update. That's a powerful performance advantage. Later, however, further processing on the network resulted in a collision. Some further processing and/or manual involvement, then, will be required to recover the multiple database copies in a consistent way.
One of the principal benefits of all replication approaches is added fault tolerance for a distributed computing environment. Fault tolerance provides the overall system with a capability of continuing to function when a piece of the environment is down.
When something breaks, then, the system working in combination with the d.b.a., should provide as much assistance as possible in the recovery process. (Mike Stonebraker has used the phrase "failover reconstruction" to describe when this recovery process occurs automatically under software control). Necessary steps in the failover reconstruction process should include:
- Understanding what is broken
- Understanding what or how the break occurred
- Determining how to fix the damage and reinstate the broken pieces
- Bringing the broken pieces back on-line
- Making sure that the recovery of the database(s) results in consistent data in those database(s)
The highest level of fault tolerance will be from a system supporting peer to peer replication. That's because the system considers an update to be successfully completed when it has completed a database update at any peer site. The site that is updated is like a floating master in this case. The replication server will queue the updates to all other data locations.
In a master/slave architecture if access to the master is denied, then the update is not allowed from the application. When the master location becomes available it becomes updated. After the master has been updated and when there is some failure elsewhere, the replication server queues the updates to the slaves until they are available. This system works as well as a peer to peer approach unless it's the master node or network that fails.
In either case, it's important that your system provide the necessary utilities to allow the rebuilding of remote databases from information on the local log and database information on other remote databases. One key utility should be able to "difference" replicates - in other words to look at a master and slave or two peers and determine if inconsistencies exist.
For a replication server product to be successful, it has to provide enough added function over what customers have developed for themselves and it should provide that function transparently to customers. There is a significant difference in the amount of replication function provided by various DBMS vendors and in the ease of implementing replication and its various features. Some products require significant programming with database triggers or database calls to implement replication. Most of the current replication functionality in Oracle 7 and much of the service available through Sybase System 10 Replication Server requires programming with RPC's or DBLib calls by the distributed data base administrator (d.d.b.a.). Setting up database replication with CA-Ingres is easier in that a configuration manager is provided that offers a three step forms based approach to defining the replicated environment.
In order to provide transparent replication services to applications, the d.d.b.a. needs to be very much aware of the use of a replication server and needs to have designed the database in a manner that is conducive to distributed operation. In practice this issue means that de-normalized and/or aggregated data should not be replicated in TP-R situations. Such derived/aggregated data should be computed at each site from the basic data contained in a transaction.
To see this point more clearly the banking example below may help. It illustrates a process that spans three periods of time (A, B, C) and three branches of a bank (1, 2, 3).
| Bank 1
| Bank 2
| Bank 3
|
| Time |
Balance |
Transaction |
|
Balance |
Transaction |
Balance |
Transaction |
|
| A |
100 |
|
|
100 |
|
100 |
|
| B |
100 |
|
X |
60 |
-40 |
60 |
-40 |
| C |
70 |
-30 |
X |
60 |
-40 |
60 |
-40 |
We're looking at one customer's balances after withdrawals are made during a period of time when the network to one replicated site is down.
- At time A, the network is entirely up and the customer's balance (100) and current transaction (none) are identical at all three bank sites.
- At time B, the network link to 1 is broken. The customer makes a withdrawal at bank 2. That transaction is replicated into Bank 3 and the balance from 2 is also replicated into 3. Bank 1 still has the old information, since access to it is unavailable.
- At time C, the customer makes a withdrawal at Bank 1.
At any time after this an attempt to reconcile the balances among the three banks is going to fail. That's because the account balance field in this example is aggregated (and denormalized). Replicating balance information is going to cause integrity problems with the data bases.
Repeating, then, in the TP-R environment an important rule for replicating data is to not replicate aggregated or denormalized data. If the system had simply replicated the transaction amounts, normalized data, each site would be able to recover correctly from a collision like the one illustrated by using a time order to sequence and process (and compute the balances). In general, a good rule for distributed processing is to use local database triggers to handle computed amounts such as account balances.
Your application shouldn't need to worry about the timing of the asynchronous distribution of data to target sites. Getting this functionality from your replication server also shouldn't require you to do programming.
The replication server, be it TP-R or DSS-R, should also provide several alternatives for timing. Examples are:
- Immediately, as soon as possible (ASAP). In this case the data is moved through the queues and replication server as fast as possible.
- Scheduled, as determined by the system administrator. In this case, data remains in the replication server until it is scheduled for distribution.
- Triggered, by user defined criteria such as an event happening, the number of records exceeding a limit or time of day. When that trigger is fired, the server moves the data to the distribution queue for remote processing.
- Under manual control
The nature of the system usage will dictate the type of timing used in replication. For operational systems that expect to be updated with near real time transactions, the best approach is likely to be ASAP. There is no additional processing overhead attached to ASAP replication in this case because the user is likely to be in a situation where the copy distribution is under 2-phase control for each updated site (to preserve transaction integrity). In such a case, then, there is no processing savings attached to batching the transactions (although transmission at night might offer savings).
For decision support or period accounting types of systems a stable database that is consistent throughout may be preferable to having the most current status. In this case, for reasons discussed above, scheduled replication may be preferable.
©Copyright 1996 by
Digital Consulting, Inc.
All Event names are trademarks of DCI or their clients.
Comments?
webmaster@dciexpo.com