1 / 24

Distributed Database Systems

University of Manitoba Asper School of Business 3500 DBMS Bob Travica. Distributed Database Systems. Updated 2013 . Multiple independent systems Each has DBMS engine, queries, locking, transactinos, etc. Usually on different machines & locations Can be different hardware, OS, software.

zahur
Download Presentation

Distributed Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. University of ManitobaAsper School of Business3500 DBMSBob Travica Distributed Database Systems Updated 2013

  2. Multiple independent systems Each has DBMS engine, queries, locking, transactinos, etc. Usually on different machines & locations Can be different hardware, OS, software. Distributed Databases (DDB) Concept SELECT Sales FROM Britain.Sales UNION SELECT Sales FROM France.Sales UNION SELECT Sales FROM Italy.Sales Germany Britain France Italy

  3. Distributed Database System (DDBS) Concept Database Apollo Database Zeus England France Database Athena United States • There must be different databases as opposed to • a central database (teleprocessing) • Network is a part of DDB system

  4. Teleprocessing vs. DDBS Teleprocessing DB System DB Loc 2 Loc 3 Loc 1, central Distributed DB System DB Loc1 DB Loc2

  5. Golden Rule - Transparency: the user should not know or care that the database is distributed and how. User is independent of any constraint. Specifically: No reliance on a central site. Continuous operation. Location independence. Fragmentation (Partitioning) independence Replication independence. Support to distributed query processing. Support to distributed transaction management. Hardware independence. Operating system independence. Network independence. DBMS independence. DDBS Rules (C.J. Date)

  6. Each database can continue to run even if a portion fails. Performance gains in contrast to teleprocessing. Data and hardware can be moved without affecting operations or users. Operations expansion possible System expansion and upgrades possible DDBS Features and Benefits

  7. Support to operations’ dispersion Work and data are segmented on departments. Work and data are geographically segmented. Improved local performance Most updates and queries are performed locally. Local control and responsibility over data. Can still combine data across the system. Scalability and expansion support Local transactions Combine data Future expansion More on DDBS Benefits

  8. Partitioned- Replicated Hybrid Teleprocessing Replicated Partitioned high low Technical & economic requirements DDBS Designs Two typical designs A A A A B B B B Replication Partition C C C C B Loc 2 C Loc 3 A Loc 1 Loc 1 Loc 3 Loc 2

  9. Creating Distributed Database System • Choose hardware and DBMS vendor, and network. • Set up network and DBMS connections. • Choose locations for data segments. • Create backup plan and strategy. • Design local views (customized queries). • Design stress tests (max. loads, failure cases).

  10. Networks (LANs, WANs) infleunce the speed of data transfer. Goal is to minimize data transfers Each system must be capable of evaluating queries. Results depend heavily on how the system joins tables. WAN LAN Disk drive Query Processing in Partitioned DDBS

  11. Example NY: tbl.Customers: 1 M rows (Marketing) LA: tbl.Product: 10 M rows (Production) Chicago: tbl.Sale: 20 M rows (main Retail op’s) Query in Chicago: List customer and product data for blue products sold on 1-Mar-2001. Must join the 3 tables. Database is partitioned according to spatial dispersion of core operations (above). Only sales data are at the locale where query is run (Chicago). Bad query design : Transfer entire tables Product and Customer to Chicago Query Optimization in Partitioned DBS

  12. Better query design qry3: SELECT * FROM Customer WHERE C# in qry1’s output; NY Customer(C#, …) 1,000,000 rows returns requested customer records qry1: SELECT C# FROM Sale WHERE Date=3/1/2006; qry2: SELECT P# FROM SaleItem INNER JOIN Sale ON SaleItem.S#=Sale.S# WHERE Date=3/1/2006; requests customer records for certain customer IDs Chicago Sale(S#, C#, Date) 20,000,000 rows SaleItem(S#, P#,…) 50,000,000 rows requests product records for certain Product IDs returns requested product records qry4: SELECT * FROM Product WHERE P# in qry2’s output AND Color like “blue”; LA Product(P#, Color…) 10,000 rows

  13. Rule of thumb: Keep the data close to the location at which they are used. Goals: Minimize transmissions Improve performance Replication Manager Replicated DDBS Design Britain Britain: Customers & Sales Spain: Customers & Sales Periodic (batch) updates Britain: Customers & Sales Spain • Decision support systems: Use replicated data warehouse. Spain: Customers & Sales

  14. Design: Partitioned database The concurrency issues that apply to centralized database become even more difficult in a partitioned database Challenges: Slow network traffic Site unavailable Concurrency (more people trying to change the same data at the same time) => locks management DDBS and Concurrent Access

  15. Ensuring data consistency across the system. In updating data, DBMS initiating update becomes Coordinator. Two phases: 1. Get Ready: Coordinator sends new data and a request for update to all DDB parts. DB partitions store new data in logs, and report update status. 2. Execute: 2.1 If all DDB parts ready for update, Coordinator requests COMMIT. 2.2 If any DDB part not ready, Coordinator requests ROLLBACK, and the update procedure repeats. Two-Phase Commit Coordinator DB Partition 1 (Master price list) Initiate Transaction (Reduce price of cookies for 5%) 1. Prepare to update. All agree? 2. Commit OK? OK! Go! DB Partition 2 (Store A Product tbl) Partition 3 (Store B Product tbl)

  16. Server performs database tasks at request of clients. Clients handle front-end tasks and small data tables that are not shared. Client-Server DDB Database Server Query Output Query Request Database Server Update Request Client Front-end (Forms, Queries, Reports) Update Response

  17. Three-Tier Client-Server Database Servers Databases Transactions Legacy systems Database links Business rules Code Middleware Front-end Clients

  18. Client-Server Architecture for Internet Database Servers DBS Middleware Other Systems Application Server Web Server Client Browsers

  19. Technologies for DDB Used in E-Commerce - Open Database Connectivity (ODBC) - Microsoft’s Active Data Objects (ADO) - Client--Web Server Connectors (CGI) - Data transfer (XML, SOAP)

  20. Since 1992, SQL Access Group; Microsoft’s decisive support Provides SQL access to different DBMSes ODBC handles: Login to database. Send query. Interpret result. Exchange data. Open Database Connectivity: ODBC SERVER Oracle Database ODBC driver SELECT … Output SERVER CLIENT SQL Database ODBC driver ODBC driver ODBC driver Application

  21. Active Data Objects (ADO) • ADO - Microsoft object-based technology for accessing data • Data access at records level (“cursor programming”) via classes: • recordset - similar to table • rowset - looser structure Relational Database Server Non-relational Database Server SELECT … Output • Builds on concepts of OO and • cursor (the space for storing records • in DB systems) Client ADO objects • Used in Active Server Pages (ASP)* Front end (report)

  22. Client—Web Server Connectors • - HTML input needs to be translated for servers • down the stream • - Output needs to be put back into HTML format • Common Gateway Interface (e.g., CGI • programming in PERL)

  23. Communication technology -Extensible Markup Language (XML) & SOAP • Note: Document Type Definition (Declaration) • SQL can extract DB data into XML documents. So, any • database schema can be described => document • transfer, validation and format support (e.g., supplier • documents). • - XML requires additional software for extracting data • (parser) and formatting display (style sheets).

  24. Extensible Markup Language (XML) & SOAP • SOAP (Simple Object Access Protocol) - protocol for • exchanging structured (marked up) data in distributed environment • SOAP uses XML technologies to define an extensible • messaging framework, defining message that can • be exchanged over a variety of underlying protocols • (started with HTTP, hence “Simple”; complex indeed). • (See: http://www.w3.org/TR/2003/REC-soap12-part1-20030624/#encapsulation ) • SOAP and XML provide foundations for Web services – distributed application software, stored across nets, running on different operating systems and devices, written in different programming languages, and made by different vendors (e.g., .NET) • ( More... )

More Related