Introduction to DSS-R Approaches and Table Copying
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