Architecture of Large Databases

1
Architecture of Large Databases

• Sreejith Gopinath
• DBA, Delta Technologies
• Nov 20, 2003

2
Design approach
Conceptual Design
Logical Modeling
Physical Design
Database Creation
Production Support
3
Conceptual Design

• Very important phase
• Collect 80 of the requirements
• Develop a paper prototype
• Conduct a design review
• Users approve the design

4
Logical Modeling

• Creates Entities and Relationships
• Conforms to the Enterprise standards
• Identifies Enterprise data
• Maps entities to Enterprise entities
• Conduct reviews with business
• 90 of requirements completed
• Business signs off for implementation
• Model passed on to the DBA

5
DBA role

• Physical Modeling
• Database creation
• Security and user accounts
• Performance tuning
• Backup and recovery testing
• Monitoring and Auditing
• Maintenance
• Database software installation
• Database upgrades and patches
• Reporting on health
• Escalate problems to vendor

6
Physical Modeling

• Entities are mapped to physical tables
• De-normalization visited
• Physical only tables created
• Row counts for each table and growth estimates
• Additional indexes
• Surrogate keys vs. Business keys
• Model reviewed and signed off

7
Database creation - development

• DDLs generated from the model
• Database created with about 10 of the data
• Application tested for functionality
• Users review the application
• Minor changes are allowed at this stage
• Model 100 completed at this stage

8
Database System Test

• Performance requirements
• Uptime/recovery objectives
• Transactional levels for the large tables
• Disks layout
• Physical partitioning of tables
• Additional indexes created for performance.

9
Database System Test

• Performance tuning
• Database parameters visited
• Backup and recovery tested
• DR configuration
• Production Architecture
• Database reviewed and signed off

10
Challenges with Large Databases

• Maintaining scalable performance
• Backup and restore
• Maintenance window and procedures
• Extensive structure changes
• Data archival
• Auditing and security

11
Table Partitioning
Bookings Table (200G)
Jan02 (10G)
Jul03 (10G)
Nov03 (10G)
Nov04 (10G)
12
Partitions- Advantages

• Scalable Performance
• Parallel query and DML operations
• Easy data archival process
• Faster data restore
• Ease of maintenance Table and index builds

13
Database backup/restore

• Use partitions for separating static and dynamic
  data
• Do selective backups based on volatility
• Multiple mirroring strategy

14
Database Server
Mirror 2
Mirror 1
Primary Disk
15
Database - Production

• Database created identical to that of Test
• Service Levels Agreement signed
• Support document created
• Monitoring and maintenance procedures established
• Backups scheduled
• Data archiving and security
• Database reviewed and signed off

16
Recipe for success

• Business requirements collected at the early
  stages
• Realistic performance requirements
• Proper documentation and standards
• Proactive monitoring and maintenance