I. OBJECTIVES
1. Understand and become familiar with the benefits and limitations of distributed database architecture.
2. Learn to create, use, and maintain distributed databases using the MySQL FEDERATED storage engine.
3. Join tables between different host database servers.
4. Explore key concepts of database replication and materialized views as performance-enhancing techniques in a distributed database architecture.

II. PARTS LIST
1. EDUPE-VT Omnymbus Virtual Machine Environment (https://devry.edupe.net:9090/) and/or:
2. MySQL (dev.mysql.com/downloads)

III. PROCEDURE
To the end user working with databases distributed throughout a company’s network is not different than working with multiple tables within a single database. The fact that the different databases exist in other locations should be totally transparent to the user. For this lab, we are going to take on the roll of a database administrator in a company that has three regional offices in the country. You work in the central regional office, but there is also a West Coast Region located in Seattle and an East Coast Region located in Miami. Your role is to gather report information from the other two regions.

For this lab, you are going to work with three different databases. You will be working with a database named SEATTLE representing the West Coast Region and a database named MIAMI representing the East Coast Region. For the central regional office database, you may re-use a VM running MySQL from a previous lab if you have one, use a VM provided for this purpose by your instructor (if your instructor provides it), or create a new VM, install MySQL, and enable the FEDERATED storage engine. Your instructor may provide a shared instance of the SEATTLE and MIAMI MySQL servers for your class to share, may provide you with your own VM instances of these servers, or may require you to create and configure these VMs for yourself. If your instructor has provided shared SEATTLE and MIAMI servers, contact your instructor for login account and password information for these servers, if the instructor has not already provided it. It is also possible to complete this lab using multiple computers on a home network, multiple VMs running under a local VM environment (e.g., VMWARE Player, or Sun’s Virtual Box), or a combination of the two.

To record your work for this lab use the LAB3_Report.doc found at the bottom of this document.

Preliminary Steps

1. Some of the preliminary steps may be optional, depending on what components your instructor provides for you, and which the instructor requires you to create and configure yourself.
2. (Optional) Create the Central Regional Office DBMS. This is the database you will consider to be local. From this database, you will connect to the remote databases for MIAMI and SEATTLE. If you have a VM running MySQL available from a previous lab, you may use that system. If not, configure a new VM. Download and install MySQL.
3. (Optional) Create the MIAMI DBMS. This database will contain data specific to the East Coast operations. If your instructor has provided a MIAMI server to be shared by you and your classmates, you may skip this step. Otherwise, create/configure a new VM as appropriate to your VM lab environment (this may include setting a root password, and establishing a hostname. Using a host naming convention, such as MIAMI_LNAME, where LNAME is replaced with your last name is a wise practice). Install MySQL and perform basic configuration.
4. (Optional) If you have created your own MIAMI server, log onto that server, and open the MySQL admin console. Create a new schema (e.g., DBM449LAB3). Create a user account to be used to connect to this server remotely (from the Central Regional Office), and provision that user account with at least the privileges needed to read and query tables on the schema (Hint: for our purposes in this lab, it is okay to grant the user full privileges). Configure MySQL to permit remote connections on the default port. Configure the firewall (if running) to permit connections from the Central Regional Office server (Hint: for our purposes, it is okay to simply turn the firewall off. While we would never do this in the real world, it may be simpler and save time to simply disable the firewall).
5. (Optional) If you have created your own MIAMI server, log onto that server, and open the MySQL admin console. Analyze the data below, and create an appropriate data model, including Primary Key (Hint: when creating other tables for this lab, make sure analogous fields and fields likely to be used as Foreign Keys or for other JOINS have compatible (identical, ideally) data types). Name this table MIAMI_EMP:

MIAMI_EMPNO
ENAME JOB SAL
8369 SMITH CLERK 800
8499 ALLEN SALESMAN 1600
8521 WARD SALESMAN 1250
8566 JONES DEPARTMENT MANAGER 2975
8654 MARTIN SALESMAN 1250
8698 FREANK DEPARTMENT MANAGER 2850
8782 THOMPSON DEPARTMENT MANAGER 2450
8788 GLENN ANALYST 3000
8839 SMITH REGION 1 MANAGER 5000
8844 TURNER SALESMAN 1500
8876 ADAMS CLERK 1100
8900 JAMES CLERK 950
8902 FORD ANALYST 3000
8934 MILLER CLERK 1300
Table 1
6. (Optional) Create the SEATTLE DBMS. This database will contain data specific to the West Coast operations. If your instructor has provided a SEATTLE server to be shared by you and your classmates, you may skip this step. Otherwise, create/configure a new VM as appropriate to your VM lab environment (this may include setting a root password, and establishing a hostname. Using a host naming convention, such as MIAMI_LNAME, where LNAME is replaced with your last name is a wise practice). Install MySQL and perform basic configuration.
7. (Optional) If you have created your own SEATTLE server, log onto that server, and open the MySQL admin console. Create a new schema (e.g., DBM449LAB3). Create a user account to be used to connect to this server remotely (from the Central Regional Office), and provision that user account with at least the privileges needed to read and query tables on the schema (Hint: for our purposes in this lab, it is okay to grant the user full privileges). Configure MySQL to permit remote connections on the default port. Configure the firewall (if running) to permit connections from the Central Regional Office server (Hint: for our purposes, it is okay to simply turn the firewall off. While we would never do this in the real world, it may be simpler and save time to simply disable the firewall).
8. (Optional) If you have created your own SEATTLE server, log onto that server, and open the MySQL admin console. Analyze the data below, and create an appropriate data model, including Primary Key (Hint: when creating other tables for this lab, make sure analogous fields and fields likely to be used as Foreign Keys or for other JOINS have compatible (identical, ideally) data types). Name this table SEATTLE_EMP:

SEATTLE_EMPNO ENAME JOB SAL
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES DEPARTMENT MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE DEPARTMENT MANAGER 2850
7782 CLARK DEPARTMENT MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING REGION 1 MANAGER 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
Table 2
 

9. Log into the Central Regional Office DBMS, and open and log into MySQL admin. Create a new schema (e.g., DBM449LAB3). Analyze the data below, and create an appropriate data model, including Primary Key (Hint: when creating other tables for this lab, make sure analogous fields and fields likely to be used as Foreign Keys or for other JOINS have compatible (identical, ideally) data types). Name this table DEPT.

DEPTNO DNAME LOC
10 HR MIAMI
20 MARKETING MIAMI
30 MID MANAGEMENT MIAMI
40 ACCOUNTING MIAMI
50 MANAGEMENT MIAMI
60 HR SEATTLE
70 MARKETING SEATTLE
80 MID MANAGEMENT SEATTLE
90 ACCOUNTING SEATTLE
00 MANAGEMENT SEATTLE
Table 3
10. Research requirements for configuring the FEDERATED storage engine for use in MySQL.
11. On the Central Regional Office DBMS, implement/enable/configure the FEDERATED storage engine.

Testing, Troubleshooting, and Debugging

12. Check to make certain that all the VMs or computers (Central Regional Office, Miami, Seattle) are up and running (e.g., not in a suspended or shutdown state).
13. Log onto the Central Regional Office system.
14. Ping the MIAMI server, using either its IP address, or hostname. Although the first one or two ping packets may be lost (no response/host unreachable, remaining pings should be successful). Take a screen shot of the successful result (troubleshoot and remediate until you are able to ping successfully), and paste it into your lab report.
15. Ping the SEATTLE server, using either its IP address, or hostname. Although the first one or two ping packets may be lost (no response/host unreachable, remaining pings should be successful). Take a screen shot of the successful result (troubleshoot and remediate until you are able to ping successfully), and paste it into your lab report.
16. CHECKPOINT QUESTION: Why is testing to ensure that you are able to ping MIAMI and SEATTLE the first step to take in making sure that the remainder of the lab procedure will be possible? Paste your response into the lab report.
17. Note that the MySQL Admin Console may be used to log into EITHER the local MySQL database, OR to a database running on a remote server. The format for logging in is:
mysql -u USERNAME -pPASSWORD -h HOSTNAMEORIP
For example, from the Central Regional Office system, one might log into a server named MIAMI_SMITH with a username of USER1 and a password of ABRACADBRA using the following command.
mysql -u USER1 -pABRACADABRA -h MIAMI_SMITH
Similarly, the same login might be accomplished using the IP address of the MIAMI_SMITH host. If that IP address were: 192.168.1.253, then the command would resemble:
mysql -u USER1 -pABRACADABRA -h 192.168.1.253

18. From the Central Regional Office system, run the MySQL Admin Console and log into the MIAMI server. If the login fails, troubleshoot and correct the issue. You should only attempt this AFTER you have proven that it is possible to ping to this server. If ping works, but the remote login using MySQL Admin fails, some possibilities to consider in your troubleshooting include: Is the MySQL database engine running on the remote system? Are firewall rules on the remote system preventing the connection? Are firewall rules on the local system preventing the connection? Does the ID and password exist on the remote system’s MySQL database, AND has it been granted permission for remote connection? While we would never run without appropriate firewall protections in place in the real world, it may be expedient to disable the firewalls on both local and remote systems for purposes of this lab exercise. Upon establishing a successful remote MySQL Admin Console connection to the remote server, take a screen shot showing the command and its successful result, and paste it into your lab report.
19. Issue a Select * query on the MIAMI_EMP table, using the remote MySQL Admin Console connection established in the previous step. Take a screen shot showing the Select statement and its result, and paste it into your lab report.
20. From the Central Regional Office system, run the MySQL Admin Console and log into the SEATTLE server. If the login fails, troubleshoot and correct the issue. You should only attempt this AFTER you have proven that it is possible to ping to this server. If ping works, but the remote login using MySQL Admin fails, some possibilities to consider in your troubleshooting include: Is the MySQL database engine running on the remote system? Are firewall rules on the remote system preventing the connection? Are firewall rules on the local system preventing the connection? Does the ID and password exist on the remote system’s MySQL database, AND has it been granted permission for remote connection? While we would never run without appropriate firewall protections in place in the real world, it may be expedient to disable the firewalls on both local and remote systems for purposes of this lab exercise. Upon establishing a successful remote MySQL Admin Console connection to the remote server, take a screenshot showing the command and its successful result, and paste it into your lab report.
21. Issue a Select * query on the SEATTLE_EMP table, using the remote MySQL Admin Console connection established in the previous step. Take a screen shot showing the Select statement and its result, and paste it into your lab report.
22. CHECKPOINT QUESTION: Why is ping testing between the systems not enough? Why is testing connectivity to the remote MySQL DBMS necessary? What are some problems/misconfigurations that could allow ping to work, but prevent use of/connection to the remote MySQL database instance? Record your response in your lab report.
23. Referring to your research and reading on creating FEDERATED tables, create FEDERATED tables for MIAMI_EMP and SEATTLE_EMP on the Central Regional Office DBMS. Capture one screen shot for the SQL statement used to create each table, AND its successful result. Paste these two screen shots into your lab report. Note: At this point, you have already established that basic network connectivity and remote database connectivity are working. However, this step can still fail due to configuration errors. The most likely reason for a failure at this point is that the FEDERATED storage engine is not properly configured on the Central Regional Office computer, that the IDs being used to connect the FEDERATED tables to their counterparts on the MIAMI and SEATTLE servers do not exist or lack appropriate privileges, or that the SQL or commands are not properly composed/formatted. You must troubleshoot and resolve any errors that arise. Remember to make use of the Q & A discussions in order to problem solve with your classmates and your instructor.
24. CHECKPOINT QUESTION: You have now created FEDERATED tables, which act like links (they contain no actual data) to the tables residing on the remote servers. Describe what occurs when a user logged into the Central Regional Office instance of MySQL writes a query which references data using the local (FEDERATED) tables. Does the user need to be aware of, or do anything special in order to make use of this remotely-stored data? Record your answers in your lab report.

Using FEDERATED Tables

25. Log into the Central Regional Office computer, if you are not already logged on. If you have any MySQL Admin Consoles open, close them. Open a new MySQL Admin Console, and log into the local (Central) MySQL database instance.
26. Write a query to retrieve the employee number, name, job function, and salary from the FEDERATED (local) SEATTLE_EMP table. Create a screenshot showing both the SQL and its result, and paste a copy into your lab report.
27. Write a query to retrieve the employee number, name, job function, and salary from the FEDERATED (local) MIAMI_EMP table. Create a screenshot showing both the SQL and its result, and paste a copy into your lab report.
28. Now we need to increase our report. Write a single query that will retrieve employees from both the Seattle and Miami regions who work in sales. Show the employee number, employee name, job function, salary, and location name in the result set (HINT: The location name is in the DEPT table).
29. CHECKPOINT QUESTION: Describe the part each local and remote server plays in processing the query and especially how joins are resolved when data resides across remote systems. Record your response in the lab report.

Improving Performance

30. As you can see from your experience in the previous steps, the ability to distribute data and yet access that data as if it resided locally provides great flexibility and transparency. However, for certain purposes, such as consolidated processing and reporting, it can also introduce performance bottlenecks and complexities. As you have learned in previous studies, one means of hiding complexities is to provide a consolidated presentation of data using a view. However, simply creating a view residing on Central that aggregated data from SEATTLE_EMP and MIAMI_EMP would not markedly improve performance, as it would still essentially be limited by the bandwidth and network performance connecting the Central servers to the remote servers. This situation would only be exacerbated as the size of these tables grow, when queries generated table scans (requiring the entire table contents to be transmitted), and with queries performing additional joins between remote tables. Many databases offer the ability to create a materialized view. In a materialized view, data and indices required to construct the view are cached on the local database server (usually cached in-memory, for high performance), and automatically synchronized with any changes in their underlying tables. As you might expect, locking up the memory resources to keep the view wired in memory, and the CPU work required to keep the view updated can be considerable. Consequently, the decision to create a materialized view is one to be carefully considered. However, materialized views can deliver unprecedented performance benefits.
31. MySQL, as of the time of this writing, does not explicitly provide materialized views. However, essentially the same benefit can be achieved through the expediency of simply creating a new table possessing the same attributes as would the view. The onus of this approach on the DBA or database developer is this: the designer must provide for the table’s synchronization with any changes in the underlying data. This can be achieved and tuned using a system of stored procedures and triggers, or through MySQL’s excellent REPLICATION services. The correct solution will depend on the requirements for the currency of the data. For example, all changes in the underlying data can be propagated as they occur, keeping the view table immediately up-to-date. However, if this is not required, updates could be batched every 5 minutes, hourly, daily, or on any arbitrary schedule as determined by the designer. For example, such a view used only to provide a consolidated report each night need only be refreshed once each day (before the nightly report processing).
32. Design a table to act as a Materialized View, which consolidates the SEATTLE_EMP and MIAMI_EMP tables into a single, local table (CONSOL_EMP) with data resident on the Central server.
33. Run the query designed in the previous step, and paste a screenshot showing the query and its result into your lab report.
34. Perform a SELECT * query on CONSOL_EMP, and paste the result into your lab report.
35. CHECKPOINT QUESTIONS: Research MySQL Replication services, and describe how you might use these to keep CONSOL_EMP up-to-date automatically. Give an example of an UPDATE, INSERT, or DELETE Trigger that you could create upon the SEATTLE_EMP table to ensure that CONSOL_EMP would automatically be updated when such a transaction occurred on the SEATTLE_EMP table. What are some concerns that need to be considered in such a design (e.g., if the network connection between Seattle and the Central office is down, would such a transaction be rolled back (stopping business in Seattle!), or permitted (in which case, how would the CONSOL_EMP table be brought back in sync later?).

 
Laboratory Report
DeVry University
College of Engineering and Information Sciences

Course Number: DBM449
Laboratory Number: 3
Laboratory Title: Working With Distributed Databases

Note: There is no limit on how much information you will enter under the three topics below. It is important to be clear and complete with your comments. Like a scientist you are documenting your progress in this week’s lab experiment.

Objectives: (In your own words what was this lab designed to accomplish? What was its purpose?)

Results: (Discuss the steps you used to complete your lab. Were you successful? What did you learn? What were the results? Explain what you did to accomplish each step. You can include screen shots, code listings, and so on. to clearly explain what you did. Be sure to record all results specifically directed by the lab procedure. Number all results to reflect the procedure number to which they correspond.)

Conclusions: (After completing this lab, in your own words, what conclusions can you draw from this experience?)