Distributed Databases - Gonzaga University

Distributed Databases - Gonzaga University

CHAPTER 12 (ONLINE): DISTRIBUTED DATABASES Modern Database Management 11th Edition Jeffrey A. Hoffer, V. Ramesh, Heikki Topi 2013 Pearson Education, Inc. Publishing as Prentice Hall 1 OBJECTIVES

Define terms Explain business conditions driving distributed databases Describe salient characteristics of distributed database environments Explain advantages and risks of distributed databases Explain strategies and options for distributed database design Discuss synchronous and asynchronous data replication and partitioning Discuss optimized query processing in distributed databases Explain salient features of several distributed database management systems Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 2

DEFINITIONS Distributed Database: A single logical database spread physically across computers in multiple locations that are connected by a data communications link Decentralized Database: A collection of independent databases on non-networked They are NOT the same thing! computers

Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 3 REASONS FOR DISTRIBUTED DATABASE Business unit autonomy and distribution Data sharing Data communication reliability and costs Multiple application vendors Database recovery Transaction and analytic Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 4 Figure 12-1 Distributed database environments

(based on Bell and Grimson, 1992) Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 5 DISTRIBUTED DATABASE OPTIONS Homogeneoussame DBMS at each node Autonomousindependent DBMSs Non-autonomouscentral, coordinating DBMS Easy to manage, difficult to enforce

Heterogeneousdifferent DBMSs at different nodes Systemswith full or partial DBMS functionality Gatewayssimple paths are created to other databases without the benefits of one logical database Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall Difficult to manage, preferred by 6 DISTRIBUTED DATABASE OPTIONS (CONT.) Systems (Heterogeneous)supports

some or all functionality of one logical database Full DBMS Functionalityall distributed DB functions Partial-Multi databasesome distributed DB functions Federatedsupports local databases for unique data requests Loose Integrationlocal databases have own schemas TightIntegrationlocal 2013 Pearson Education, Inc. Publishing as Prentice Hall Chapter 12-Web databases

use common 7 HOMOGENEOUS, NON-AUTONOMOUS DATABASE Data is distributed across all the nodes Same DBMS at each node All data is managed by the distributed DBMS (no exclusively local data) All access is through one, global schema 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall Chapter The global schema is the union of 8

Figure 12-2 Homogeneous Distributed Database Environment Identical DBMSs Source: Based on Bell and Grimson, 1992. Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 9 TYPICAL HETEROGENEOUS ENVIRONMENT Data distributed across all the

nodes Different DBMSs may be used at each node Local access is done using the local DBMS and schema Remote access is done using the global schema Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 10 Figure 12-3 Heterogeneous Distributed Database Environment Non-identical DBMSs Source: Based on Bell and Grimson, 1992. Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall

11 MAJOR OBJECTIVES Location Transparency User does not have to know the location of the data Data requests automatically forwarded to appropriate sites Local Autonomy

Local site can operate with its database when network connections fail Each site controls its own data, security, logging, recovery Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 12 SIGNIFICANT TRADE-OFFS Synchronous Distributed Database

All copies of the same data are always identical Data updates are immediately applied to all copies throughout network Good for data integrity High overhead slow response times Asynchronous Distributed Database Some data inconsistency is tolerated Data update propagation is delayed Lower data integrity Less overhead faster response time NOTE: all this assumes replicated data (to be discussed later) Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall

13 ADVANTAGES OF DISTRIBUTED DATABASE OVER CENTRALIZED DATABASES Increased reliability/availability Local control over data Modular growth Lower communication costs Faster response for certain queries Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 14

DISADVANTAGES OF DISTRIBUTED DATABASE COMPARED TO CENTRALIZED DATABASES Software cost and complexity Processing overhead Data integrity exposure Slower response for certain queries Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 15 OPTIONS FOR

DISTRIBUTING A DATABASE Data replication Horizontal partitioning Different rows of a table distributed to different sites Vertical partitioning

Copies of data distributed to different sites Different columns of a table distributed to different sites Combinations of the above Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 16 DATA REPLICATION ADVANTAGES Reliability Fast response May avoid complicated distributed transaction integrity routines (if replicated data is refreshed at scheduled intervals) Decouples nodes (transactions proceed even if some nodes are

down) Reduced network traffic at prime time updates can be delayed) 2013 Pearson Education, Inc. Publishing as Prentice Hall Chapter 12-Web(if 17 DATA REPLICATION Additional requirements for DISADVANTAGES storage space Additional time for update

operations Complexity and cost of updating Integrity exposure of getting incorrect data replicated data Therefore, better whenifused for non-volatile (read-only) data is not updated simultaneously Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 18

TYPES OF DATA REPLICATION Push Replication updating site sends changes to other sites Pull Replication receiving sites control when update messages will be processed Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 19

TYPES OF PUSH REPLICATION Snapshot Replication Changes periodically sent to master site Master collects updates in log Full or differential (incremental) snapshots Dynamic vs. shared update ownership Near Real-Time Replication

Broadcast update orders w/out requiring confirmation Done through use of triggers Update messages stored in message queue until processed by receiving site Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 20 ISSUES FOR DATA REPLICATION Data timelinesshigh tolerance for outof-date data may be required DBMS capabilitiesif DBMS cannot support multi-node queries, replication may be necessary Performance implicationsrefreshing may cause performance problems for busy nodes Network heterogeneitycomplicates

replication Network communication capabilities Pearson Education, Inc. Publishing as Prentice Hall Chapter 12-Web 2013 complete refreshes place heavy demand 21 HORIZONTAL PARTITIONING

Different rows of a table at different sites Advantages Data stored close to where it is used efficiency Local access optimization better performance Only relevant data is available security Unions across partitions ease of query Disadvantages

Accessing data across partitions Education, speed Inc. Publishing as Prentice Hall Chapter inconsistent 12-Web 2013 Pearson access 22 VERTICAL PARTITIONING Different columns of a table at different sites Advantages and disadvantages are the same as for horizontal partitioning except that combining data across partitions is more

difficult because it requires joins (instead of unions) Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 23 Figure 12-6 Distributed processing system for a manufacturing company Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 24 FIVE DISTRIBUTED DATABASE STRATEGIES Centralized database, distributed access Replication with periodic snapshot update Replication with near real-time synchronization of updates

Partitioned, one logical database Partitioned, independent, Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall nonintegrated segments 25 FACTORS IN CHOICE OF DISTRIBUTED STRATEGY Organizational forces funding, autonomy, security Frequency and locality or clustering of reference to data Growth and expansion needs Technological capabilities Costs of managing complex technologies Need for reliable service Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall

26 Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 27 FUNCTIONS OF A DISTRIBUTED DBMS Locate data with a distributed data dictionary Determine location from which to retrieve data and process query components Translate between nodes with different local DBMSs Provide data management functions: security, concurrency, deadlock control, query optimization, failure recovery Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall

Provide data consistency (via 28 FUNCTIONS OF A DISTRIBUTED DBMS (CONT.) Provide a single logical database, physically distributed Global primary key control Be scalable Replicate data and stored procedure Transparently use residual computing power to improve database processing performance Permit different nodes to run different DBMSs Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall Allow different versions of application

29 Figure 12-10 Distributed DBMS architecture Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 30 LOCAL TRANSACTION STEPS Application makes request to distributed DBMS. 2. Distributed DBMS checks distributed data repository for location of data. Finds that it is local. 3. Distributed DBMS sends request to local DBMS. 4. Local DBMS processes request.

5. Local DBMS sends results to Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall application. 1. 31 Figure 12-10 Distributed DBMS Architecture (showing local transaction steps) 2 1 3 5 4 Local transactionall data stored locally Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall

32 GLOBAL TRANSACTION STEPS 1. 2. 3. 4. 5. 6. 7. 8. Application makes request to distributed DBMS. Distributed DBMS checks distributed data repository for location of data. Finds that it is remote. Distributed DBMS routes request to remote site. Distributed DBMS at remote site translates request for its local DBMS if necessary, and sends request to local DBMS.

Local DBMS at remote site processes request. Local DBMS at remote site sends results to distributed DBMS at remote site. Remote distributed DBMS sends results back to originating site. Distributed DBMS at originating site sends results to application. Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 33 Figure 12-10 Distributed DBMS architecture (showing global transaction steps) 2 3 1 6 7

8 4 5 Global transaction some data is at remote site(s) Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 34 DISTRIBUTED DBMS TRANSPARENCY OBJECTIVES Location Transparency

Replication Transparency User/application does not need to know where data resides User/application does not need to know about duplication Failure Transparency Either all or none of the actions of a transaction are committed Each site has a transaction manager Logs transactions and before and after images Education, Inc. Publishing as Prentice Hall Chapter

12-Web 2013 Pearson Concurrency control scheme to ensure data 35 TWO-PHASE COMMIT Prepare Phase A message is broadcast to every participating site, asking whether that site is willing to commit its portion of the transaction at that site. Each site returns an OK or not OK message. An OK says that the remote site promises to allow the initiating request to govern the transaction 2013 Pearson Education,

Inc. Publishing as Prentice Hall Chapter at12-Web the remote database. 36 TWO-PHASE COMMIT (CONT.) Commit Phase The originating site collects the messages from all sites. If all are OK, it broadcasts a message to all sites to commit the portion of the transaction handled at each site. If one or more responses are not OK, it broadcasts a message to all sites to abort the transaction.

If the transaction fails during the commit phase it is in limbo. A limbo transaction can be identified by a Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 37 timeout or polling. CONCURRENCY CONTROL Concurrency Transparency Design goal for distributed database Appearance of serial transactions, even though simultaneous transactions are occurring Time stamping

Concurrency control mechanism Assign globally unique timestamp to each transaction Alternative to locks in distributed Chapterdatabases 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 38 QUERY OPTIMIZATION For a multi-site join the distributed DBMS must decide where to access the data and how to proceed with the join. Three step process: 1. 2. 3.

Query decomposition Data localization Global optimization Semijoin operation: only the joining attribute of the query is sent from one site to the other, rather than all selected Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 39 attributes EVOLUTION OF DISTRIBUTED DBMS Unit of WorkAll of a transactions steps

Remote Unit of Work SQL statements originated at one location can be executed as a single unit of work on a single remote DBMS Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 40 EVOLUTION OF DISTRIBUTED DBMS (CONT.) Distributed Unit of Work Different statements in a unit of work

may refer to different remote sites All databases in a single SQL statement must be at a single site Distributed Request A single SQL statement may refer to tables in more than one remote site May not support replication transparency or failure transparency Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 41 Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 42

Chapter 12-Web 2013 Pearson Education, Inc. Publishing as Prentice Hall 43

Recently Viewed Presentations

  • Where, Why and What? - Denton ISD

    Where, Why and What? - Denton ISD

    Dorsal. Refers to the back - also refers to the cranial surface of the manus (front of paw) and pes (rear of paw) Cranial. Front of the body. Posterior. Rear of the body. Rostral. Nose end of the head. Cephalic....
  • Fragrant Herbs - Welcome to The Mountain Laurel Garden Club!

    Fragrant Herbs - Welcome to The Mountain Laurel Garden Club!

    Fragrant Herbs"Sweet perfumes work immediately upon the spirits, for their refreshing sweet and healthfullayres are special preservatives to health, and therefore much to be prised."" - Ralph Austen (1653) MLGC. February 18, 2014. MaggiCoene. I am an avid member of...
  • Welcome Aiming High and Succeeding in Year 7:

    Welcome Aiming High and Succeeding in Year 7:

    R Johnstone/Mrs J Martin 7. F - Mr M Hake . 7C - Miss I Smith/Miss S . Endersbee. 7G - Miss R Fallows. 7. D - Mr . R Etienne . 7. H - Miss H Everett/Mrs J ....
  • Sales & Receivables Year End Processes

    Sales & Receivables Year End Processes

    Sales & Receivables Year End Processes Presented by: Stacie Sodolak Close-out FY07 Topics of Discussion Accounts Receivables Extension of Credit System Regulation 21.01.04 Year End AR Procedures Year End Accounts Receivables-Form 5 Accounts Receivables Write Offs-Form 3 Pledge Receivables Deadlines...
  • Pablo Escobar The King of Cocaine Everyone had

    Pablo Escobar The King of Cocaine Everyone had

    Born in 1949 in Colombia. Mother was a school teacher and father was a farmer. Grew up poor. Grew up in La Violencia. Was ambitious when he was young, had dreams of becoming rich. Began criminal activity as a teenager...
  • Ecological Succession Ecological succession is the set of

    Ecological Succession Ecological succession is the set of

    Ecological Succession Ecological succession is the set of changes in community composition that occur over time in a new or disturbed community.
  • Part 1: Introduction - Cengage

    Part 1: Introduction - Cengage

    Aesthetics - What is objective or subjective? Ethics - How is human action judged morally right or wrong? If philosophy is the rational attempt to formulate, understand, and answer fundamental questions, then: What is rational? What is fundamental? What is...
  • Apresentação do PowerPoint - WordPress.com

    Apresentação do PowerPoint - WordPress.com

    Por isso, é usual falar, por exemplo, duma espiritualidade do catequista, duma espiritualidade do clero diocesano, duma espiritualidade do trabalho. Na Evangelii Gaudium, quis concluir com uma espiritualidade da missão, Laudato si' com uma espiritualidade ecológica, e na Amoris laetitia...