Local vs. Client/Server Databases

1. Local vs. Client/Server Databases Ted Blue

2. Web Apps are Client/Server

3. Web Apps are Client/Server

4. Web Apps are Client/Server

5. Web Apps are Client/Server

6. Web Apps are Client/Server

7. Local Databases • dBASE, FoxPro, Paradox, Microsoft Access

8. Local Databases • dBASE, FoxPro, Paradox, Microsoft Access • No Database Management System

9. Local Databases • dBASE, FoxPro, Paradox, Microsoft Access • No Database Management System • Minimal Setup and Configuration

10. Local Databases • dBASE, FoxPro, Paradox, Microsoft Access • No Database Management System • Minimal Setup and Configuration • Low Cost

11. Performance of Local Databases • Unable to perform under load

12. Performance of Local Databases • Unable to perform under load • Unable to handle large datasets

13. Performance of Local Databases

14. Performance of Local Databases • Load limits: • About 10-20 users maximum

15. Performance of Local Databases • Load limits: • About 10-20 users maximum • Dataset size limits: • About 100-500k records maximum and/or • About 1-5MB total size

16. Performance of Local Databases • Load limits: • About 10-20 users maximum • Dataset size limits: • About 100-500k records maximum and/or • About 1-5MB total size • Cannot be improved with hardware

17. Data Integrity and Local Databases • Easily corrupted

18. Data Integrity and Local Databases • Easily corrupted • Corruption amplified under load • Memo fields sensitive to corruption

19. Data Integrity and Local Databases • Easily corrupted • Pessimistic record locking

20. Data Integrity and Local Databases • Easily corrupted • Pessimistic record locking • Relies on active connection to data • Not possible in client/server web applications

21. Data Integrity and Local Databases • Easily corrupted • Pessimistic record locking • Unmanaged indexes

22. Data Integrity and Local Databases • Easily corrupted • Pessimistic record locking • Unmanaged indexes • Indexes corrupt during modifications • …Corrupt indexes cause invalid data retrieval • …Invalid data retrieval damages data integrity

23. Data Integrity and Local Databases • Easily corrupted • Pessimistic record locking • Unmanaged indexes • Concurrency

24. Data Integrity and Local Databases • Easily corrupted • Pessimistic record locking • Unmanaged indexes • Concurrency • Multiple users can update same data • Concurrency problems do not generate errors

25. Data Integrity and Local Databases • Easily corrupted • Pessimistic record locking • Unmanaged indexes • Concurrency • Transaction control

26. Data Integrity and Local Databases • Easily corrupted • Pessimistic record locking • Unmanaged indexes • Concurrency • Transaction control • No simultaneous commit • No rollback capability

27. Client/Server Databases • Oracle, Sybase, SQL Server, Informix, etc.

28. Client/Server Databases • Oracle, Sybase, SQL Server, Informix, etc. • Database Management Systems (DBMS)

29. Client/Server Databases • Oracle, Sybase, SQL Server, Informix, etc. • Database Management Systems (DBMS) • More elaborate Setup and Configuration

30. Client/Server Databases • Oracle, Sybase, SQL Server, Informix, etc. • Database Management Systems (DBMS) • More elaborate Setup and Configuration • Higher Cost

31. Performance in Client/Server Databases • Excellent performance under load

32. Performance in Client/Server Databases • Excellent performance under load • Designed to handle large datasets

33. Performance in Client/Server Databases

34. Performance in Client/Server vs. Local Databases

35. Performance in Client/Server Databases • Load limits: • Hundreds or Thousands of Users

36. Performance in Client/Server Databases • Load limits: • Hundreds or Thousands of Users • Database size limits: • Millions of records • Size limited only by available storage

37. Performance in Client/Server Databases • Load limits: • Hundreds or Thousands of Users • Database size limits: • Millions of records • Size limited only by available storage • Scaleable with hardware

38. Data Integrity in Client/Server Databases • Corruption Management

39. Data Integrity in Client/Server Databases • Corruption Management • Detects and corrects corruption dynamically • Prevents corruption automatically

40. Data Integrity in Client/Server Databases • Corruption Management • Optimistic Record Locking • Locks occur during updates • Manages record lock conflicts • Perfectly suited to web applications

41. Data Integrity in Client/Server Databases • Corruption Management • Optimistic Record Locking • Managed Indexes • Indexes created as needed • Automatic corruption detection and correction • Accurate data retrieval

42. Data Integrity in Client/Server Databases • Corruption Management • Optimistic Record Locking • Managed Indexes • Concurrency Control

43. Data Integrity in Client/Server Databases • Corruption Management • Optimistic Record Locking • Managed Indexes • Concurrency Control • Queues updates from multiple users • Concurrency problems generate errors • Invalid data not allowed into database

44. Data Integrity in Client/Server Databases • Corruption Management • Optimistic Record Locking • Managed Indexes • Concurrency Control • Transaction Control

45. Data Integrity in Client/Server Databases • Corruption Management • Optimistic Record Locking • Managed Indexes • Concurrency Control • Transaction Control • Transaction Log tracks all changes by all users • Commits multiple changes simultaneously • Rollback for incomplete or inaccurate updates

46. Additional Client/Server Database Features • Disaster Recovery

47. Additional Client/Server Database Features • Disaster Recovery • Security

48. Additional Client/Server Database Features • Disaster Recovery • Security • Views

49. Additional Client/Server Database Features • Disaster Recovery • Security • Views • Stored Procedures

50. Additional Client/Server Database Features • Disaster Recovery • Security • Views • Stored Procedures • Triggers