Data Replication in General Perspective

Warning! MyCSSMenu may have been blocked by IE-SP2's active content option. This browser feature blocks JavaScript from running locally on your computer.

This warning will not display once the menu is on-line. To enable the menu locally, click the yellow bar above, and select "Allow Blocked Content".

To permanently enable active content locally...

1: Select 'Tools' --> 'Internet Options' from the IE menu.
2: Click the 'Advanced' tab.
3: Check the 2nd option under 'Security' in the tree (Allow active content to run in files on my computer.)

Top 3 Products & Services

1.
2.
3.

Essential Products & Services

Dated: Aug. 13, 2004

Related Categories

By Najmi

Abstract

This non-technical document is intended for thedatabase administrators or database designers who areplanning for data-replication or who want tounderstand it usage and its implementation on theongoing database with respect to the applicationrequirements.

This document includes the unified treatment ofconflicts and errors, column-level tracking ofchanges, improved priority-conflict-resolutionalgorithm, added replica types and extendedreplication functionality exposed through out theavailable replication types.

Before diving into detail of data-replication, thepaper focus some major issues that covers DataReplication, its usage, its planning, designing andimplementation. It also listed the major availabledatabase servers for performing database replication.

Keywords: Data Replication, Publisher, Subscriber, Conflict Resolution

Database Replication?

Database replication is a technique that is use tosupport multiple online or offline users of anapplication. Replication is the process of creatingmultiple copies of an application and its data to beused at different locations that are not alwaysconnected to each other.

Database replication is different from filereplication, which essentially copies files.Replication checks the tables for updated data andmoves the data from the source to the target systemswhile guaranteeing data coherency and consistency.

For example, a voucher created by a shopkeeper can betransferred from one shop in Karachi to an Head Officein Dubai; and a sales force automation systemreplicates all purchase entries to the local salesrepresentative.

Why Replication?

The above goal from data replication can be fulfill byproviding 24 hours online connected or distributedcomputing environment with high availability ofoptimal network providing the demand of data integritytoo. The current challenge in the field of databasemanagement for administrators is to determine the bestway for distributing large amount of data withoutconnected to the server for the whole day.

Although once should not expect from a shopkeeper or a saleperson having an average monthly revenue to beconnected with the Head Office for the whole day. Insuch a case we use Data Replication that is a best wayto distribute a large amount of data across thenetwork in a timely fashion. Data Replication isalmost supported by all major database vendors such asMicrosoft Corp, Oracle Corp, Sybase Inc, InformixSoftware Inc, IBM Corp, and Computer AssociatesInternational Inc. These vendors have advancedreplication technology, and once can build solidreplication architectures by using these vendors.

Planning for Replication

Replication is a serious business, even If the userinterface simplifies the implementation andadministration, replication requires careful planningand analysis. It is important to have a thoroughunderstanding of the mechanism of your database thatuses replication. Managing a distributed database isvastly more difficult than managing a centralizeddatabase as it requires a lot of time for justifyingand planning replication.

Before planning for replication we have to considersome major factors such asHow quickly you need data synchronized across allsites. Budget for hardware, software, and communicationservices. Overall system-reliability requirements.

The best candidates for replication are applicationsthat can tolerate some latency in data updates inexchange for a robust configuration that can allowupdates from any replica and that supports users whoare only occasionally connected. This flexibilitymeans the system can work more effectively,potentially improving business performance.

Using flexible, low-cost, off-peak asynchronouscommunication links and asynchronous data duplicationprovides "real-time-enough" updates without theexpense and vulnerability of full-time connectionsbetween all nodes. When the application's users areconnected, it might be through a direct connection ona local area network (LAN) or wide area network (WAN),or through the Internet or an intranet. Data can beexchanged on a LAN, a WAN, or the Internet.

Designing database for Replication

Now, It is a time that we took some stuff in handsincluding an ERD of a Database, a pencil and a rubber,a coffee is also necessary as we are designing adatabase into a replicable format. As stated aboveReplication requires careful planning and analysisso first we start from selecting the activities ofdifferent database servers. It is better to select atleast 2 database servers that are: -

1. Main-Database also known as Head-Office and willact as a Data-Distributor

2. Subscriber-Database also known as Factory/Shop andwill act as a Data-Receiver.

Head-Office Published and Distribute the necessarydata to its clients. The main purpose of the HeadOffice is to replicate the central data to itssubscribers and to store all setup tables here, sothat all setup entries are done at Head-Office andthen replicated to its subscribers.

Rest of the Data-Servers are subscribers to the HeadOffice. Data will be replicated to the HeadOffice (actas a Publisher) first and then the HeadOffice (act asa Distributor) will replicate it to the subscriber.Any data from one subscriber will not be migrated toany other subscriber until and until it is notmigrated to the Head Office first. This can be furtherdepicted from the figure1 below: -

In such a case the Head Office can PUSH data toanother Data-Server and can PULL (get) data back fromits Subscribers.

Conflict Resolution

In order to avoid conflict between identical values ofdifferent Data-Servers, we have to define a separateidentity (identity-sequence) for each Data-Server, insuch a case the id does not match to the ids of thedependents tables which cause a major problem inparent-child relationship among tables. This can bedepicted from the chart below

Data Sequence for the Data-Servers

HEAD-OFFICE has a seed of 1 and an increment of 2(odd, positive no)

FACTORY has a seed of 2 and an increment of 2 (even,positive no)

CUSTOMER has a seed of -1 and an increment of -2 (odd,negative no)

SUPPLIER has a seed of -2 and an increment of -2(even, negative no)

Managing Master Tables/ Setup Tables

Every application requires some data for few tables bydefault. These tables are categorizes into two furthertypes: -

1. Base-Tables

2. Look-Up Tables

Base-Tables are those tables whose data can beadded/updated/deleted thru application. Base-Tablesare also migrated but initial lookup values will besame of each base-table for each Data-Server, suchthat at the time of each replication the initialvalues will not replicated due to the Primary-Keysconflict.

Lookup Tables are those tables whose data cannot beadded/updated/deleted thru application. These tablesare not migrated in order to increase migrationlatency.

Note: - Keep in mind that the Database structure ofeach Data-Server will be the same, except for therules of generating Identical numbers.

Conclusion

Replication plays an important role of moving dataquickly throughout the enterprise irrespective of thelocation. Whether you are building a data warehouse,corporate intranet, sales force automation tool, orany other distributed application; replication is aserious business, it needs a careful analysis andplanning.

Before diving into replication once should categorizedthe database with respect to their projectrequirements. The above architecture is build onindustry standards, and has been successfullyimplemented on one of the biggest ERP solutionBusiness Patterns developed by ePatterns Pvt.Ltd after a research of 7 months.

Now that you've gotten free know-how on this topic, try to grow your skills even faster with online video training. Then finally, put these skills to the test and make a name for yourself by offering these skills to others by becoming a freelancer. There are literally 2000+ new projects that are posted every single freakin' day, no lie!