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