This chapter guides you through planning a successful replication scheme. It describes:
This chapter is especially intended for the distributed database administrator.
|
|
|
The key to success with Ingres Replicator is careful planning. There are a number of possible replication schemes that you can use to propagate transaction data throughout your distributed processing environment. The way you choose to design replication on your network depends on the needs of your business and the purpose of your application.
|
|
|
When you design a distributed replicated database, you must consider the type of data that is contained in the database, how that data is being used to process information, and which data is owned by a database.
This section defines these considerations and identifies the issues that can affect the accuracy, reliability, and efficiency of your replication system:
|
|
|
Derived information is information that is derived or calculated from other information in the database. In relational terms, this redundant information makes the table it is in de-normalized. For example, a bank balance is information that can be derived. Instead of adding up all the deposits and withdrawals from the time the account was opened, a bank can maintain a balance in the account record and manipulate the balance every time a transaction occurs on the account. Although derived data can be an efficient way to manage computing resources in a non-replicated environment, you must be aware of the risks of using derived information in a replicated environment.
|
|
|
The following scenario shows the problems of using derived data to obtain bank balances in a replicated environment. Assume there is a banking application with three replicated database copies. There is one database copy per branch, designated respectively as Databases A, B, and C.
On Monday afternoon, a customer opens an account at Branch A and deposits $100.
As of Tuesday morning, the customer's account balance is reflected correctly in Databases A, B, and C, which show a balance of $100.
A |
B |
C |
|---|---|---|
$100 |
$100 |
$100 |
On Tuesday at 9:00 am, the customer visits Branch C. At this time data communications is disrupted between Branch C and the other branches. The customer withdraws $50 from the account.
The local database at Branch C commits the transaction at 9:05 am, showing a balance of $50. The account balance is queued for distribution to Branches A and B.
A |
B |
C |
|---|---|---|
$100 |
$100 |
$50 |
The customer drives across town to Branch A. There, the customer withdraws another $50 from the account.
Due to the communications problem, the account balance at Branch C has not yet arrived at Branch A. The local database at Branch A commits the transaction at 9:20 am. The Branch A database shows a balance of $50 in the customer's account. The account balance at Branch A is queued for distribution to Branches B and C. Branch B receives the update from Branch A and changes the customer's balance to $50.
A |
B |
C |
|---|---|---|
$50 |
$50 |
$50 |
The result of these steps is that all three branches end up showing that the customer has a balance of $50 in the account. However, the true balance must be zero.
Assume now that the communication problem at Branch C is repaired on Tuesday at 9:30 am. The Replicator Server at Branch C detects a collision when it attempts to update the customer's balance at Branch A, and again at Branch B. Similarly, the Replicator Server at Branch A detects a collision at Branch C.
Each Replicator Server's response to collision is dependent on the way the CDDS containing the balance information is configured. The collision can be resolved in one of the following ways:
The problem in these resolution scenarios is that information is lost (or at best temporarily misplaced if we assume that the archive tables or log files contain the missing information). Even though the customer performed two transactions, the collision handling permitted the recording of only one transaction. The aggregate result of the two transactions is therefore overlooked. A possible solution to this problem is shown in Example 2.
|
|
|
The potential risk of replicating derived data, as shown in the previous example, can be avoided if each branch maintains its own balance on its own local database, as shown in the following example.
The customer withdraws $50 from the account at Branch C. Data communications are disrupted to all other branches.
A |
B |
C |
|---|---|---|
$100 |
$100 |
$50 |
Branch C queues the $50 transaction for replication to Branches A and B.
The customer withdraws $50 from Branch A. Branch A queues the $50 transaction for replication to Branches B and C. The transaction from A is replicated to B.
A |
B |
C |
|---|---|---|
$50 |
$50 |
$50 |
The customer's balance is now $50 in both Branches A and B. When the network comes up for Branch C, Branch A and B are informed of the $50 withdrawal that the customer made at Branch C. Branches A and B can update their balances. Branch A informs Branch C about the customer's other $50 withdrawal transaction. All branches show the correct balance of $0.
A |
B |
C |
|---|---|---|
$0 |
$0 |
$0 |
In this example, the transactions are replicated in the form of deposits and withdrawals. Because the balance is not being replicated, the replication does not collide on the balance table.
|
|
|
In designing a replication system, consider allowing each location ownership over specific data. Data ownership can reduce Ingres Replicator maintenance by eliminating collisions and keeping databases synchronized.
For example, assume that there is a sales organization in one location and a warehouse in another location. The warehouse owns the inventory tables and all information about shipping, while the sales organization owns the sales order records, except for shipping data. In this case, assume also that the sales organization needs to know the state of the inventory and the warehouse needs to know about sales orders. These two sets of information react to each other, but do not manipulate each other's data.
Following is the replicated process flow for this scenario:
Because salespersons can only manipulate their sales orders, and the servers at the warehouse can only update inventory information, no replication collisions are possible. If a database is unavailable, all other databases can continue business. When the failed database is again in service, it is automatically brought back in synch with other databases and is collision free.
|
|
|
Data aggregation prevents you from replicating unnecessary information and allows you to maximize your computing resources. Assume that corporate headquarters wants an overall total of each store's revenue for the day. You can run a program at the end of the day that performs an insert into a daily totals table. This replicated table can be distributed to corporate headquarters. The alternative, which is to replicate each transaction and send its value to headquarters, is resource intensive.