You are previewing Database Administration: The Complete Guide to Practices and Procedures.
O'Reilly logo
Database Administration: The Complete Guide to Practices and Procedures

Book Description

"One of Craig's hallmarks is his ability to write in a clear, easy-to-read fashion. The main purpose of any technical book is to transfer information from writer to reader, and Craig has done an excellent job. He wants the reader to learn—and it shows."—Chris Foot, Senior Oracle Certified Instructor, Contemporary Technologies

"I think every business manager and every IT manager should have a copy of this book."—Dan Hotka, Senior Technical Advisor, Quest Software Inc.

With Database Administration, database professionals now have a comprehensive sourcebook of strategies for delivering quality administration across today's challenging multi-DBMS environments. Filled with practical guidelines and strategies, this book provides the most comprehensive survey available of the administrator's world for the existing or aspiring database professional.

Part tutorial and part reference, Database Administration explains and examines each of the components that comprise the discipline of database administration. The book opens by providing an overview of the responsibilities of database administrators (DBAs) along with the various "flavors" of DBAs and their tasks. From there, the book proceeds chronologically through every task a DBA is likely to encounter. Although designed as a comprehensive survey of the entire DBA environment, the book's individual chapters are also well suited for quick look-up of specific information.

Highlights include:

  • The world of the DBA: types, tasks, daily issues, and much more

  • The DBA environment—installation and upgrading issues, standards, and procedures

  • Data modeling and normalization

  • Database design and application design

  • Performance management, including system, database, and application performance

  • Ensuring data integrity and database security

  • Database back-up and recovery strategies and disaster planning

  • Storage management and data warehouse administration

  • Connectivity, metadata management, Internet-enabled database access, and additional DBA tools

  • Platform-independent coverage applicable for all the major DBMS products including DB2, Oracle, and SQL Server

  • Whether you're a database analyst, architect, or application engineer, this book will provide the strategies and solutions you need to navigate the rocky terrain of today's complex data environments.



    0201741296B05142002

    Table of Contents

    1. Copyright
      1. Dedication
    2. Preface
      1. How to Use This Book
    3. Acknowledgments
    4. About the Author
    5. 1. What Is a DBA?
      1. The DBA: Revered or Reviled?
      2. Why Learn Database Administration?
        1. A Unique Vantage Point
        2. DBA Salaries
        3. Database Technology
      3. The Management Discipline of Database Administration
        1. A Day in the Life of a DBA
        2. Evaluating a DBA Job Offer
      4. Database, Data, and System Administration
        1. Data Administration
        2. Database Administration
        3. System Administration
      5. DBA Tasks
        1. Database Design
        2. Performance Monitoring and Tuning
        3. Availability
        4. Database Security and Authorization
        5. Backup and Recovery
        6. Data Integrity
        7. DBMS Release Migration
        8. Jack-of-All-Trades
      6. Types of DBAs
        1. System DBA
        2. Database Architect
        3. Database Analyst
        4. Data Modeler
        5. Application DBA
        6. Task-Oriented DBA
          1. Performance Analyst
        7. Data Warehouse Administrator
      7. Staffing Considerations
        1. How Many DBAs?
        2. DBA Reporting Structures
      8. Multiplatform DBA Issues
      9. Test and Production
      10. New Technology and the DBA
        1. Procedural DBAs: Managing Database Logic
          1. Stored Procedures
          2. Triggers
          3. User-Defined Functions
          4. Administering Stored Procedures, Triggers, and UDFs
        2. The Internet: From DBA to e-DBA
        3. The PDA DBA
      11. DBA Certification
      12. The Rest of the Book
      13. Review
        1. Bonus Question
    6. 2. Creating the Database Environment
      1. Defining the Organization's DBMS Strategy
        1. Choosing a DBMS
        2. DBMS Architectures
        3. DBMS Clustering
        4. DBMS Proliferation
        5. Hardware Issues
      2. Installing the DBMS
        1. DBMS Installation Basics
        2. Hardware Requirements
        3. Storage Requirements
        4. Memory Requirements
        5. Configuring the DBMS
        6. Connecting the DBMS to Supporting Infrastructure Software
        7. Installation Verification
        8. DBMS Environments
      3. Upgrading DBMS Versions and Releases
        1. Features and Complexity
        2. Complexity of the DBMS Environment
        3. Reputation of the DBMS Vendor
        4. Support Policies of the DBMS
        5. Organization Style
        6. DBA Staff Skill Set
        7. Platform Support
        8. Supporting Software
        9. Fallback Planning
        10. Migration Verification
        11. The DBMS Upgrade Strategy
      4. Database Standards and Procedures
        1. Database Naming Conventions
          1. Standard Abbreviations
        2. Other Database Standards and Procedures
          1. Roles and Responsibilities
          2. Data Administration Standards
          3. Database Administration Standards
          4. System Administration Standards
          5. Database Application Development Standards
          6. Database Security Standards
          7. Application Migration and Turnover Procedures
          8. Design Review Guidelines
          9. Operational Support Standards
      5. DBMS Education
      6. Summary
      7. Review
        1. Bonus Question
    7. 3. Data Modeling and Normalization
      1. Data Modeling Concepts
        1. Entity-Relationship Diagramming
      2. The Components of a Data Model
        1. Entities
          1. Entity Naming Guidelines
          2. Entity Occurrences
        2. Attributes
          1. Domains
          2. Attribute Naming Guidelines
          3. Attribute Values
          4. Nulls and Lack of Value
        3. Keys
          1. Candidate Keys
          2. Primary Key
          3. Foreign Keys
        4. Relationships
          1. Cardinality
          2. Optionality
      3. Discovering Entities, Attributes, and Relationships
      4. Conceptual, Logical, and Physical Data Models
      5. What Is Normalization?
      6. The Normal Forms
        1. First Normal Form
        2. Second Normal Form
        3. Third Normal Form
        4. A Normalized Data Model
        5. Further Normal Forms
      7. Normalization in Practice
      8. Additional Data Modeling Issues
      9. Summary
      10. Review
        1. Bonus Question
      11. Suggested Reading
        1. Bibliography
    8. 4. Database Design
      1. From Logical Model to Physical Database
        1. Transform Entities to Tables
        2. Transform Attributes to Columns
          1. Transform Domains to Data Types
          2. Primary Keys
          3. Column Ordering
        3. Build Referential Constraints for All Relationships
          1. Referential Integrity
        4. Build Physical Data Structures
      2. Database Performance Design
        1. Designing Indexes
          1. B-Tree Indexes
          2. Bitmap Indexes
          3. Reverse Key Indexes
          4. Partitioned Indexes
          5. Ordered Indexes
          6. Index Summary
        2. Hashing
        3. Clustering
        4. Interleaving Data
      3. Denormalization
        1. When to Denormalize
        2. Prejoined Tables
        3. Report Tables
        4. Mirror Tables
        5. Split Tables
          1. Splitting Long Text Columns
        6. Combined Tables
        7. Redundant Data
        8. Repeating Groups
        9. Derivable Data
        10. Hierarchies
        11. Special Physical Implementation Needs
        12. Denormalization Summary
      4. Views
      5. Data Definition Language
      6. Summary
      7. Review
        1. Bonus Question
      8. Suggested Reading
        1. Bibliography
    9. 5. Application Design
      1. Database Application Development and SQL
        1. SQL
        2. Set-at-a-Time Processing and Relational Closure
        3. Embedding SQL in a Program
        4. SQL Middleware and APIs
          1. Microsoft Database Programming
        5. Object Orientation and SQL
        6. Types of SQL
        7. SQL Coding for Performance
      2. Defining Transactions
        1. Transaction Guidelines
        2. Unit of Work
        3. Transaction Processing Systems
        4. Application Servers
      3. Locking
        1. Types of Locks
          1. Intent Locks
        2. Lock Timeouts
        3. Deadlocks
        4. Lock Duration
          1. Isolation Level
          2. Acquire/Release Specification
        5. Lock Escalation
        6. Programming Techniques to Minimize Locking Problems
        7. Locking Summary
      4. Batch Processing
      5. Summary
      6. Review
        1. Bonus Question
      7. Suggested Reading
        1. Bibliography
    10. 6. Design Reviews
      1. What Is a Design Review?
        1. Rules of Engagement
        2. Design Review Participants
          1. The Leader
          2. The Scribe
          3. The Mediator
          4. The Participants
        3. Knowledge and Skills Required
      2. Types of Design Reviews
        1. Conceptual Design Review
        2. Logical Design Review
        3. Physical Design Review
        4. Organizational Design Review
        5. SQL and Application Code Design Review
        6. Pre-Implementation Design Review
        7. Post-Implementation Design Review
      3. Design Review Output
      4. Summary
      5. Review
      6. Suggested Reading
        1. Bibliography
    11. 7. Database Change Management
      1. Change Management Requirements
        1. The Change Management Perspective of the DBA
      2. Types of Changes
        1. DBMS Software
        2. Hardware Configuration
        3. Logical and Physical Design
        4. Applications
        5. Physical Database Structures
      3. Impact of Change on Database Structures
        1. The Limitations of ALTER
        2. Database Change Scenarios
          1. Some Database Change Examples
        3. Comparing Database Structures
        4. Requesting Database Changes
        5. Standardized Change Requests
          1. Checking the Checklists
        6. Communication
      4. Summary
      5. Review
    12. 8. Data Availability
      1. Defining Availability
        1. Increased Availability Requirements
          1. The Shrinking Maintenance Window
          2. Decision Support
          3. Data Warehousing
          4. Full-Time Availability
          5. Growing IT Complexity
      2. Cost of Downtime
        1. How Much Availability Is Enough?
      3. Availability Problems
        1. Loss of the Data Center
        2. Network Problems
        3. Loss of the Server Hardware
        4. Disk-Related Outages
        5. Operating System Failure
        6. DBMS Software Failure
        7. Application Problems
        8. Security and Authorization Problems
        9. Corruption of Data
        10. Loss of Database Objects
        11. Loss of Data
        12. Data Replication and Propagation Failures
        13. Severe Performance Problems
        14. Recovery Issues
        15. DBA Mistakes
        16. Outages: Planned and Unplanned
      4. Ensuring Availability
        1. Perform Routine Maintenance While Systems Remain Operational
        2. Automate DBA Functions
        3. Exploit High-Availability Features
        4. Exploit Clustering Technology
          1. A Few Database Examples
      5. Summary
      6. Review
      7. Suggested Reading
        1. Bibliography
    13. 9. Performance Management
      1. Defining Performance
        1. A Basic Database Performance Road Map
      2. Monitoring vs. Management
        1. Reactive vs. Proactive
        2. Preproduction Performance Estimation
        3. Historical Trending
      3. Service-Level Management
      4. Types of Performance Tuning
        1. System Tuning
        2. Database Tuning
        3. Application Tuning
      5. Performance Tuning Tools
      6. DBMS Performance Basics
      7. Summary
      8. Review
        1. Bonus Questions
      9. Suggested Reading
        1. Bibliography
    14. 10. System Performance
      1. The Larger Environment
        1. Interaction with the Operating System
        2. Allied Agents
        3. Hardware Configuration
          1. Disk Storage and I/O
        4. Components of the DBMS
      2. DBMS Installation and Configuration Issues
        1. Types of Configuration
        2. Memory Usage
          1. Additional Areas of Memory Consumption
          2. How Much Memory Is Enough?
        3. Data Cache Details
          1. Monitoring and Tuning the Data Cache
          2. Monitoring and Tuning the Procedure Cache
        4. “Open” Database Objects
        5. Database Logs
          1. Database Log Configuration Considerations
          2. Are All Database Operations Logged?
        6. Locking and Contention
        7. The System Catalog
        8. Other Configuration Options
        9. General Advice
      3. System Monitoring
      4. Summary
      5. Review
        1. Bonus Question
      6. Suggested Reading
        1. Bibliography
    15. 11. Database Performance
      1. Techniques for Optimizing Databases
        1. Partitioning
        2. Raw Partition vs. File System
        3. Indexing
          1. When to Avoid Indexing
          2. Index Overloading
        4. Denormalization
        5. Clustering
          1. Page Splitting
        6. Interleaving Data
        7. Free Space
        8. Compression
        9. File Placement and Allocation
          1. Database Log Placement
          2. Distributed Data Placement
          3. Disk Allocation
        10. Page Size (Block Size)
      2. Database Reorganization
        1. Determining When to Reorganize
        2. Automation
      3. Summary
      4. Review
      5. Suggested Reading
        1. Bibliography
    16. 12. Application Performance
      1. Designing Applications for Relational Access
      2. Relational Optimization
        1. CPU and I/O Costs
        2. Database Statistics
        3. Query Analysis
        4. Joins
          1. Join Order
        5. Access Path Choices
          1. Table Scans
          2. Indexed Access
            1. Using Indexes to Avoid Sorts.
            2. Why Wasn't the Index Chosen?
          3. Hashed Access
          4. Parallel Access
      3. Additional Optimization Considerations
        1. View Access
        2. Query Rewrite
        3. Rule-Based Optimization
      4. Reviewing Access Paths
        1. Forcing Access Paths
      5. SQL Coding and Tuning for Efficiency
        1. SQL Rules of Thumb
          1. Rule 1: “It Depends!”
          2. Rule 2: Be Careful What You Ask For
          3. Rule 3: KISS
          4. Rule 4: Retrieve Only What Is Needed
          5. Rule 5: Avoid Cartesian Products
          6. Rule 6: Judicious Use of OR
          7. Rule 7: Judicious Use of LIKE
          8. Rule 8: Know What Works Best
          9. Rule 9: Issue Frequent COMMITs
          10. Rule 10: Beware of Code Generators
          11. Rule 11: Consider Stored Procedures
        2. Additional SQL Tuning Tips
        3. Identifying Poorly Performing SQL
      6. Summary
      7. Review
      8. Suggested Reading
        1. Bibliography
    17. 13. Data Integrity
      1. Types of Integrity
      2. Database Structure Integrity
        1. Types of Structural Problems
        2. Managing Structural Problems
          1. Consistency Options
          2. Database Checking
          3. Memory Usage
          4. Additional Options
      3. Semantic Data Integrity
        1. Entity Integrity
        2. Unique Constraints
        3. Data Types
          1. User-Defined Data Types
        4. Default Values
        5. Check Constraints
          1. Check Constraint Benefits
          2. Check Constraint Examples
            1. Nulls and Other Potential Problems.
          3. Triggers
            1. When Does a Trigger Fire?
            2. Nested Triggers
            3. Using Triggers to Implement Referential Integrity
            4. Transition Variables and Tables
            5. A Sample Trigger
            6. Trigger Granularity
            7. Trigger Synopsis
          4. Referential Integrity
            1. INSERT Rule
            2. UPDATE Rule
            3. Foreign key perspective.
            4. Primary key perspective.
          5. DELETE Rule
          6. Pendant DELETE
          7. Setting Up Relationships
          8. Declarative RI Implementation Concerns
          9. DBMS Support for Referential Integrity
          10. Referential Integrity Using Triggers
          11. User-Managed vs. System-Managed RI
          12. RI vs. Check Constraints vs. Program Logic
          13. RI Rules of Thumb
      4. Summary
      5. Review
      6. Suggested Reading
        1. Bibliography
    18. 14. Database Security
      1. Database Security Basics
        1. Database Users
      2. Granting and Revoking Authority
        1. Types of Privileges
          1. Granting Table Privileges
          2. Granting Database Object Privileges
          3. Granting System Privileges
          4. Granting Program and Procedure Privileges
        2. Granting to PUBLIC
        3. Revoking Privileges
          1. Cascading REVOKEs
          2. Chronology and Revokes
        4. Security Reporting
      3. Authorization Roles and Groups
        1. Roles
        2. Groups
          1. Limit the Number of SA Users
          2. Group-Level Security and Cascading REVOKEs
      4. Other Database Security Mechanisms
        1. Using Views for Security
        2. Using Stored Procedures for Security
          1. Logic-Oriented Security
      5. Auditing
      6. External Security
        1. Job Scheduling and Security
        2. Non-DBMS DBA Security
      7. Summary
      8. Review
      9. Suggested Reading
        1. Bibliography
    19. 15. Database Backup and Recovery
      1. Preparing for Problems
      2. Image Copy Backups
        1. Full vs. Incremental Backups
          1. Merging Incremental Copies
        2. Database Objects and Backups
          1. Copying Indexes
        3. DBMS Control
        4. Concurrent Access Issues
        5. Backup Consistency
          1. When to Create a Point of Consistency
        6. Log Archiving and Backup
        7. Determining Your Backup Schedule
        8. DBMS Instance Backup
        9. Designing the DBMS Environment for Recovery
        10. Alternate Approaches to Database Backup
          1. Using Database Exports to Create Logical Backups
          2. Using Storage Management Software to Make Backup Copies
        11. Document Your Backup Strategy
        12. Database Object Definition Backups
      3. Recovery
        1. Determining Recovery Options
        2. General Steps for Database Object Recovery
        3. Types of Recovery
          1. Choosing the Optimum Recovery Strategy
          2. Matching Type of Failure to Type of Recovery
        4. Index Recovery
        5. Testing Your Recovery Plan
        6. Recovering a Dropped Database Object
        7. Recovering Broken Blocks and Pages
        8. Populating Test Databases
      4. Alternatives to Backup and Recovery
        1. Standby Databases
        2. Replication
        3. Disk Mirroring
      5. Summary
      6. Review
      7. Suggested Reading
        1. Bibliography
    20. 16. Disaster Planning
      1. The Need for Planning
        1. Risk and Recovery
      2. General Disaster Recovery Guidelines
        1. The Remote Site
        2. The Written Plan
          1. Testing Your Disaster Plans
        3. Personnel
      3. Backing Up the Database for Disaster Recovery
        1. Tape Backups
        2. Storage Management Backups
        3. Other Approaches
        4. Some Guidelines
          1. Order of Recovery
          2. Data Latency
          3. Remember Other Vital Data
          4. Beware of Compression
          5. Post-Recovery Image Copies
      4. Disaster Prevention
        1. Disaster and Contingency Planning Web Sites
      5. Summary
      6. Review
      7. Suggested Reading
        1. Bibliography
    21. 17. Data and Storage Management
      1. Storage Management Basics
      2. Files and Data Sets
        1. File Placement on Disk
        2. Raw Partitions vs. File Systems
        3. Temporary Database Files
      3. Space Management
        1. Data Page Layouts
          1. Allocation Pages
          2. Data Record Layouts
          3. Calculating Table Size
        2. Index Page Layouts
          1. Calculating Index Size
        3. Transaction Logs
      4. Storage Options
        1. RAID
          1. RAID Levels
        2. JBOD
        3. Storage Area Networks
        4. Network-Attached Storage
          1. SAN vs. NAS
        5. Direct Access File System
      5. Planning for the Future
        1. Capacity Planning
      6. Summary
      7. Review
      8. Suggested Reading
        1. Bibliography
    22. 18. Data Movement and Distribution
      1. Loading and Unloading Data
        1. The LOAD Utility
          1. Describing the Input File
          2. Efficient Loading
          3. Running Other Utilities During a LOAD
        2. The UNLOAD Utility
          1. Concurrency
          2. Unloading from Image Copy Backups
          3. Generation of LOAD Parameters
          4. Data Encoding Scheme
          5. Floating-Point Data
          6. Limiting UNLOAD Data
          7. Unloading from Views
        3. Maintaining Application Test Beds
      2. EXPORT and IMPORT
      3. Bulk Data Movement
        1. ETL Software
        2. Replication and Propagation
        3. Messaging Software
        4. Other Methods
      4. Distributed Databases
        1. Setting Up a Distributed Environment
        2. Data Distribution Standards
        3. Accessing Distributed Data
        4. Two-Phase COMMIT
        5. Distributed Performance Problems
      5. Summary
      6. Review
        1. Bonus Question
      7. Suggested Reading
        1. Bibliography
    23. 19. Data Warehouse Administration
      1. What Is a Data Warehouse?
        1. Analytical vs. Transaction Processing
      2. Administering the Data Warehouse
        1. Too Much Focus on Technology?
        2. Data Warehouse Design
        3. Data Movement
          1. Consistent Data Acquisition
        4. Data Cleansing
          1. Data Quality Issues
          2. Operational Problems
        5. Data Warehouse Scalability
          1. Size Issues
        6. Data Warehouse Performance
          1. Automated Summary Tables
        7. Data Freshness
        8. Data Content
        9. Data Usage
        10. Financial Chargeback
        11. Backup and Recovery
        12. Don't Operate in a Vacuum!
      3. Summary
      4. Review
      5. Suggested Reading
        1. Bibliography
    24. 20. Database Connectivity
      1. Client/Server Computing
        1. A Historical Look
        2. Business Issues
        3. What Is Client/Server Computing?
          1. Centralized vs. Distributed Processing
        4. Types of Client/Server Applications
          1. Thin Client or Fat Client
        5. Database Gateways
        6. Network Traffic
      2. Databases, the Internet, and the Web
        1. Internet-Connected Databases
          1. Availability
        2. New Technologies
          1. XML
          2. Java
        3. Database Design
      3. Summary
      4. Review
      5. Suggested Reading
        1. Bibliography
    25. 21. Metadata Management
      1. What Is Metadata?
        1. From Data to Knowledge and Beyond
        2. Metadata Strategy
        3. Data Warehousing and Metadata
      2. Types of Metadata
      3. Repositories and Data Dictionaries
        1. Repository Benefits
        2. Repository Challenges
        3. Data Dictionaries
      4. Summary
      5. Review
      6. Suggested Reading
        1. Bibliography
    26. 22. DBA Tools
      1. Types and Benefits of DBA Tools
        1. Data Modeling and Design
        2. Database Change Management
          1. Change Management Tools
          2. Database Comparison Tools
          3. Database Object Migration Tools
          4. Referential Integrity Tools
          5. Auditing Tools
          6. Catalog Query and Analysis Tools
          7. Security Tools
        3. Table Editors
        4. Performance Management
          1. System Performance Tools
          2. Database Performance Tools
          3. Application Performance Tools
        5. Backup and Recovery
        6. Database Utilities
          1. Utility Management Tools
        7. Data Warehousing and Business Intelligence
          1. ETL Tools
          2. Query and Reporting Tools
        8. Programming and Development Tools
          1. Checkpoint/Restart Tools
          2. Testing Tools
          3. Debugging Tools
        9. Miscellaneous Tools
          1. Space Management Tools
          2. Compression Tools
          3. Online Standards Manuals
          4. Other Tools
      2. Evaluating DBA Tool Vendors
        1. Homegrown DBA Tools
      3. Summary
      4. Review
    27. 23. DBA Rules of Thumb
      1. The Rules
        1. Write Down Everything
        2. Keep Everything
        3. Automate!
        4. Share Your Knowledge
        5. Analyze, Simplify, and Focus
        6. Don't Panic!
        7. Measure Twice, Cut Once
        8. Understand the Business, Not Just the Technology
        9. Don't Become a Hermit
        10. Use All of the Resources at Your Disposal
        11. Keep Up-to-Date
      2. Summary
      3. Final Exam
    28. A. Database Fundamentals
      1. What Is a Database?
      2. Why Use a DBMS?
        1. Advantages of Using a DBMS
          1. Levels of Data Abstraction
          2. Data Independence
          3. Data Security
          4. Concurrency Control
          5. Database Logging
          6. Ensuring Atomicity and Durability
          7. Data Integrity
          8. Data Access
      3. Summary
    29. B. The DBMS Vendors
      1. The Big Three
        1. Contact Information
      2. The Second Tier
        1. Contact Information
      3. Other Significant Players
        1. Contact Information
      4. Open-Source DBMS Offerings
      5. Nonrelational DBMS Vendors
      6. Object-Oriented DBMS Vendors
      7. PC-Based DBMS Vendors
    30. C. DBA Tool Vendors
      1. The Major Vendors
      2. Other DBA Tool Vendors
      3. Data Modeling Tool Vendors
      4. Repository Vendors
      5. Data Movement and Business Intelligence Vendors
    31. D. DBA Web Resources
      1. Usenet Newsgroups
      2. Mailing Lists
      3. Web Sites and Portals
        1. Vendor Web Sites
        2. Magazine Web Sites
        3. Consultant Web Sites
        4. Database Portals
        5. Other Web Sites
    32. Bibliography
      1. Database Management and Database Systems
      2. Data Administration, Data Modeling, and Database Design
      3. Data Warehousing
      4. Object Orientation and Database Management
      5. Related Topics
      6. DB2
      7. IMS
      8. Informix
      9. Oracle
      10. SQL Server
      11. Sybase