Transcript ch24

Chapter 24
Replication and Mobile Databases
Transparencies
© Pearson Education Limited 1995, 2005
Chapter 24 - Objectives
 How
a replicated database differs from a
distributed database.
 The benefits of database replication. Distributed
deadlock detection.
 Examples of applications that use database
replication.
 Basic components of a replication system.
2
© Pearson Education Limited 1995, 2005
Chapter 24 - Objectives
 How
synchronous replication differs from
asynchronous replication.
 The main types of data ownership are
master/salve, workflow, and update-anywhere.
 The functionality of a database replication
server.
 Main implementation issues associated with
database replication.
3
© Pearson Education Limited 1995, 2005
Chapter 24 - Objectives
 How
mobile computing supports the mobile
worker.
 Functionality of a mobile DBMS.
 How Oracle DBMS supports database
replication.
4
© Pearson Education Limited 1995, 2005
Introduction to Database Replication

Functionality of DDBMS is attractive. However,
implementations of required protocols and
algorithms are complex and can cause problems
that may outweigh advantages.

Alternative and more simplify approach to data
distribution is provided by a replication server.

Every major database vendor has replication
solution.
5
© Pearson Education Limited 1995, 2005
Introduction to Database Replication

Database Replication is the process of copying
and maintaining database objects, such as
relations, in multiple databases that make up a
distributed database system.
6
© Pearson Education Limited 1995, 2005
Benefits of Database Replication
7
© Pearson Education Limited 1995, 2005
Applications of Replication

Replication supports a variety of applications
that have very different requirements.

Some applications are supported with only
limited synchronization between the copies of the
database and the central database system.
 Other
applications demand continuous
synchronization between all copies of the
database.
8
© Pearson Education Limited 1995, 2005
Basic Components of Database Replication

Replication object is a database object such as a
relation, index, view, procedure, or function
existing on multiple servers in a distributed
database system.

In a replication environment, any updates made
to a replication object at one site are applied to
the copies at all other sites.
9
© Pearson Education Limited 1995, 2005
Basic Components of Database Replication

Replication objects are managed using
replication groups.

A replication group is a collection of replication
objects that are logically related.

A replication group can exist at multiple
replication sites.
10
© Pearson Education Limited 1995, 2005
Basic Components of Database Replication

Replication environments support two basic
types of sites: master sites and slave sites.

A replication group can be associated with one or
more master sites and with one or more slave
sites.
11
© Pearson Education Limited 1995, 2005
Basic Components of Database Replication

One site can be both master site for one
replication group and slave site for different
replication group.

However, one site cannot be both the master site
and slave site for same replication group.
12
© Pearson Education Limited 1995, 2005
Basic Components of Database Replication

A master site controls a replication group and
the objects in that group.

This is achieved by maintaining a complete copy
of all objects in a replication group and by
propagating any changes to a replication group
to any slave sites.
13
© Pearson Education Limited 1995, 2005
Basic Components of Database Replication

A slave site can contain all or a subset of objects
from a replication group. However, slave sites
only contain a snapshot of a replication group.

Typically, a snapshot site is refreshed
periodically to synchronize it with its master site.
14
© Pearson Education Limited 1995, 2005
Basic Components of Database Replication

For a replication environment with many master
sites, all of those sites communicate directly with
one another to continually propagate data
changes in the replication group.
15
© Pearson Education Limited 1995, 2005
Synchronous Versus Asynchronous Replication

Synchronous – updates to replicated data are
part of enclosing transaction.
– If one or more sites that hold replicas are
unavailable transaction cannot complete.
– Large number of messages required to
coordinate synchronization.
 Asynchronous
- target database updated after
source database modified. Delay in regaining
consistency may range from few seconds to
several hours or even days.
© Pearson Education Limited 1995, 2005
16
Data Ownership
 Ownership
relates to which site has privilege to
update the data.

Main types of ownership are:
– Master/slave (or asymmetric replication),
– Workflow,
– Update-anywhere (or peer-to-peer or
symmetric replication).
17
© Pearson Education Limited 1995, 2005
Master/Slave Ownership
 Asynchronously
replicated data is owned by one
(master) site, and can be updated by only that
site.
 Using ‘publish-and-subscribe’ metaphor, master
site makes data available.
 Other sites ‘subscribe’ to data owned by master
site, receiving read-only copies.
 Potentially, each site can be master site for nonoverlapping data sets, but update conflicts
cannot occur.
18
© Pearson Education Limited 1995, 2005
Master/Slave Ownership – Data Dissemination
19
© Pearson Education Limited 1995, 2005
Master/Slave Ownership – Data Consolidation
20
© Pearson Education Limited 1995, 2005
Workflow Ownership
 Avoids
update conflicts, while providing more
dynamic ownership model.
 Allows right to update replicated data to move
from site to site.
 However, at any one moment, only ever one site
that may update that particular data set.
 Example is order processing system, which
follows series of steps, such as order entry, credit
approval, invoicing, shipping, and so on.
21
© Pearson Education Limited 1995, 2005
Workflow Ownership
22
© Pearson Education Limited 1995, 2005
Update-Anywhere Ownership
 Creates
peer-to-peer environment where
multiple sites have equal rights to update
replicated data.
 Allows local sites to function autonomously,
even when other sites are not available.
 Shared ownership can lead to conflict
scenarios and have to employ methodology for
conflict detection and resolution.
23
© Pearson Education Limited 1995, 2005
Update-Anywhere Ownership
24
© Pearson Education Limited 1995, 2005
Replication Servers Functionality
Basic function is copy data from one database to
another (using synch. or asynch. replication).
 Other functions include:
– Scalability
– Mapping and Transformation
– Object Replication
– Specification of Replication Schema
– Subscription mechanism
– Initialization mechanism
– Easy Administration

25
© Pearson Education Limited 1995, 2005
Implementation Issues

Issues associated with the provision of data
replication by the replication server include:
– transactional updates;
– snapshots and database triggers;
– conflict detection and resolution.
26
© Pearson Education Limited 1995, 2005
Non-Transactional versus Transactional Update
 Early
replication mechanisms were nontransactional.

Data was copied without maintaining atomicity
of transaction.

With transactional-based mechanism, structure
of original transaction on source database is
also maintained at target site.
27
© Pearson Education Limited 1995, 2005
Non-Transactional versus Transactional Update
28
© Pearson Education Limited 1995, 2005
Snapshots
 Allow
asynchronous distribution of changes to
individual tables, collections of tables, views, or
partitions of tables according to pre-defined
schedule.

Common approach for snapshots uses the
recovery log, minimizing the extra overhead to
the system.
29
© Pearson Education Limited 1995, 2005
Snapshots

In some DBMSs, process is part of server, while
in others it runs as separate external server.

In event of network or site failure, need queue to
hold updates until connection is restored.

To ensure integrity, order of updates must be
maintained during delivery.
30
© Pearson Education Limited 1995, 2005
Database Triggers
 Could
allow users to build their own replication
applications using database triggers.

Users’ responsibility to create code within trigger
that will execute whenever appropriate event
occurs.
31
© Pearson Education Limited 1995, 2005
Database Triggers
CREATE TRIGGER StaffAfterInsRow
BEFORE INSERT ON Staff
FOR EACH ROW
BEGIN
INSERT INTO
[email protected]
VALUES (:new.staffNo, :new:fName, :new:lName,
:new.position, :new:sex, :new.DOB, :new:salary,
:new:branchNo);
END;
32
© Pearson Education Limited 1995, 2005
Database Triggers - Drawbacks
 Management
and execution of triggers have a
performance overhead.
 Burden on application/network if master table
updated frequently.
 Triggers cannot be scheduled.
 Difficult to synchronize replication of multiple
related tables.
 Activation of triggers cannot be easily undone in
event of abort or rollback.
33
© Pearson Education Limited 1995, 2005
Conflict Detection and Resolution
 When
multiple sites are allowed to update
replicated data, need to detect conflicting
updates and restore data consistency.
 For a single table, source site could send both
old and new values for any rows updated since
last refresh.
 At target site, replication server can check each
row in target database that has also been
updated against these values.
34
© Pearson Education Limited 1995, 2005
Conflict Detection and Resolution
Also want to detect other types of conflict such
as violation of referential integrity.
 Some of most common mechanisms are:

–
–
–
–
–
–
Earliest and latest timestamps.
Site Priority.
Additive and average updates.
Minimum and maximum values.
User-defined.
Hold for manual resolution.
35
© Pearson Education Limited 1995, 2005
Mobile Databases
 Increasing
demands on mobile computing to
provide types of support required by growing
number of mobile workers.
 Work as if in the office but in reality working
from remote locations.
 ‘Office’ may accompany remote worker in
form of laptop, PDA (Personal Digital
Assistant), or other Internet access device.
36
© Pearson Education Limited 1995, 2005
Mobile Database
 Database
that is portable and physically
separate from a centralized database server
but is capable of communicating with server
from remote sites allowing the sharing of
corporate data.
37
© Pearson Education Limited 1995, 2005
Mobile DBMS
38
© Pearson Education Limited 1995, 2005
Mobile DBMS
 Functionality
required of mobile DBMSs
includes ability to:
– communicate with centralized database server
through modes such as wireless or Internet access;
– replicate data on centralized database server and
mobile device;
– synchronize data on centralized database server
and mobile device;
– capture data from various sources such as Internet;
– manage/analyze data on the mobile device;
– create customized mobile applications.
39
© Pearson Education Limited 1995, 2005