Database Administration: The Complete Guide to Practices and Procedures / Edition 1

Database Administration: The Complete Guide to Practices and Procedures / Edition 1

by Craig S. Mullins
     
 

View All Available Formats & Editions

ISBN-10: 0201741296

ISBN-13: 2900201741291

Pub. Date: 06/28/2002

Publisher: Addison-Wesley

"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

Overview

"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.



Product Details

ISBN-13:
2900201741291
Publisher:
Addison-Wesley
Publication date:
06/28/2002
Edition description:
New Edition
Pages:
703

Table of Contents

Dedication.
Acknowledgments.
Trademark Acknowledgments.
Preface.
How to Use This Book.
About the Author.
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, and System Administration.
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.
The Internet: FromDBA to eDBA.
The PDA DBA.
Impact on DBAs.
New Technology Impacts DBA.

DBA Certification.
The Rest of the Book.
Review Questions.
Bonus Question.


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.
Other Database Standards and Procedures.

DBMS Education.
Summary.
Review Questions.
Bonus Question.


3. Data Modeling and Normalization.
Data Modeling Concepts.
Entity-Relationship Diagramming.

The Components of a Data Model.
Entities.
Attributes.
Keys.
Relationships.

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.

Additional Suggested Reading.

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.
Desiging 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.

Additional Suggested Reading.

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.
Miscrosoft 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.
Lock Duration.

Isolation Level.
Acquire/Release Specification.
Lock Escalation.
Programming Techniques to Minimize Locking Problems.
Locking Summary.

Batch Processing.
Summary.
Review Questions.
Bonus Question.

Additional Suggested Reading.

6. Design Reviews.
What is a Design Review?
Rules of Engagement.
Design Review 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.
Additional Suggested Reading.

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.
Comparing Database Structures.

Change Management Guidelines.
Requesting Database Changes.
Standardized Change Requests.
Communication.

Summary.
Review Questions.

8. Data Availability.
Defining Availability.
Increased Availability Requirements.

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.

Summary.
Review Questions.
Addtional Suggested Reading.

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.

Addtional Suggested Reading.

10. System Performance.
The Larger Environment.
Interaction with the Operating System.
Allied Agents.
Hardware configuration.
Components of the DBMS.

DBMS Installation and Configuration Issues.
Types of Configuration.
Memory Usage.
Data Cache Details.
“Open” Database Objects.
Database Logs.
Locking and Contention.
The System Catalog.
Other Configuration Options.
General Advice.

System Monitoring.
Summary.
Review Questions.
Bonus Question.

Addtional Suggested Reading.

11. Database Performance.
Techniques For Optimizing Databases.
Partitioning.
Raw Partition Versus File System.
Indexing.
Denormalization.
Clustering.
Interleaving Data.
Free Space.
Compression.
File Placement and Allocation.
Page Size (Block Size).

Database Reorganization.
Determining When to Reorganize.
Automation.

Summary.
Review Questions.
Addtional Suggested Reading.

12. Application Performance.
Designing Applications for Relational Access.
Relational Optimization.
CPU Cost.
I/O Cost.
Database Statistics.
Query Analysis.
Joins.
Access Path Choices.
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.
Identifying Poorly Performing SQL.

Summary.
Review Questions.
Addtional Suggested Reading.

13. Data Integrity.
Types of Integrity.
Database Structure Integrity.
Types of Stuctural Problems.
Managing Structural Problems.

Semantic Data Integrity.
Entity Integrity.
Unique Constraints.
Data Types.
Default Values.
Check Constraints.
Triggers.
Referential Integrity.
User- vs. System-Managed RI.
General RI Rules of Thumb.

Summary.
Review Questions.
Addtional Suggested Reading.

14. Database Security.
Database Security Basics.
Database Users.

Granting and Revoking Authority.
Types of Privileges.
Granting to Public.
Revoking Privileges.
Security Reporting.

Authorization Roles and Groups.
Roles.
Groups.

Other Database Security Mechanisms.
Using Views for Security.
Using Stored Procedures for Security.
Special Functionality.

Auditing.
External Security.
Job Scheduling and Security.
Non-DBMS DBA Security.

Summary.
Review Questions.
Addtional Suggested Reading.

15. Database Backup and Recovery.
Preparing for Problems.
Image Copy Backups.
Full Versus Incremental Image Copy Backups.
Merging Incremental Copies.
Database Objects and Backups.
DBMS Control.
Concurrent Access During Backup Processing.
Backup Consistency.
Log Archival and Backup.
Determining Your Backup Schedule.
DBMS Instance Backup.
Designing the DBMS Environment for Recovery.
Alternate Approaches to Database Backup.
Document Your Backup Strategy.
Database Object Definition Backups.

Recovery.
Determining Recovery Options.
General Steps to Recover a Database Object.
Types 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 Arecovery.
Standby Databases.
Replication.
Disk Mirroring.

Summary.
Review Questions.
Additional Suggested Reading.

16. Disaster Planning.
The Need for Planning.
Risk and Recovery.

General Disaster Recovery Guidelines.
The Remote Site.
The Written Plan.
Personnel.

Backing Up Data for Database Disaster Recovery.
Image Copy Backups.
Storage Management Backups.
Other Approaches.

Database Disaster Recovery.
Order of Recovery.
Data Latency.
Remember Other Vital Data.
Beware of Compression.
Post-Recovery Image Copies.
Disaster Prevention.

Disaster and Contingency Planning Web Sites.
Summary.
Review Questions.
Additional Suggested Reading.

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.
Index Page Layouts.
Transaction Logs.

Storage Options.
RAID.
JBOD.
SANs.
Netword Attached Storage (NAS).
DAFS.

Planning for The Future.
Capacity Planning.

Summary.
Review Questions.
Additional Suggested Reading.

18. Data Movements and Distribution.
Loading and Unloading Data.
The LOAD Utility.
The UNLOAD Utility.
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.
Data Distribution Standards.
Accessing Distributed Data.
Two-Phase Commit.
Distributed Performance Problems.

Summary.
Review Questions.
Bonus Question.

Additional Suggested Reading.

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.
Data Cleansing.
Data Warehouse Scalability.
Data Warehouse Performance.
Data Freshness.
Data Content.
Data Usage.
Financial Chargeback.
Backup and Recovery.
Do Not Operate in a Vacuum.

Summary.
Review Questions.
Additional Suggested Reading.

20. Database Connectivity.
Client/Server Computing.
An Historical Look.
Business Issues.
What is Client/Server Computing?
Types of Client/Server Applications.
Database Gateways.
Reduce Network Traffic.

Databases, the Internet, and the World Wide Web.
Managing Internet-Connected Databases.
Database Design.

Summary.
Review Questions.
Additional Suggested Reading.

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.

22. DBA Tools.
Types of DBA Tools and Benefits.
Data Modeling and Design.
Database Change Management.
Table Editors.
Performance Management.
Backup & Recovery.
Database Utilities.
Data Warehousing and Business Intelligence.
Programming and Development Tools.
Miscellaneous Tools.

Evaluating DBA Tool Vendors.
Home-Grown DBA Tools.

Summary.
Review Questions.

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.

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.
Index.

Customer Reviews

Average Review:

Write a Review

and post it to your social network

     

Most Helpful Customer Reviews

See all customer reviews >