The first platform-independent guide to the discipline of database administration, this book presents best practices and procedures that apply to any database platform: Oracle, Microsoft, IBM, Sybase, even MySQL and Postgres. It will be an ideal resource for organizations that have deployed multiple databases -- and for professionals who want to move comfortably amongst database platforms. Craig Mullins begins with an up-to-date introduction to the field of database administration, outlining a broad range of DBA roles and responsibilities, and showing how evolving technology has changed the profession. He walks through creating the database environment; data modeling and normalization; database/application design; and change management. Next, he introduces best practices for maximizing availability, security, data integrity, and all aspects of system, database, and application performance. From data warehouse administration to Web-based data access, Database Administration: Practices and Procedures is a single source for authoritative guidance on every facet of database administration. For every database administrator, and for managers responsible for supervising database administrators.
Click below for Sample Chapter(s) related to this title:
Sample Chapter 1
(NOTE: Most chapters conclude with Summary, Review, Bonus Question and Suggested Reading.)
How to Use This Book.
About the Author.
1. What Is a DBA?
The DBA: Revered or Reviled?
Why Learn Database Administration?
A Unique Vantage Point.
The Management Discipline of Database Administration.
A Day in the Life of a DBA.
Evaluating a DBA Job Offer.
Database, Data, and System Administration.
Performance Monitoring and Tuning.
Database Security and Authorization.
Backup and Recovery.
DBMS Release Migration.
Types of DBAs.
Data Warehouse Administrator.
How Many DBAs?
DBA Reporting Structures.
Multiplatform DBA Issues.
Test and Production.
New Technology and the DBA.
Procedural DBAs: Managing Database Logic.
The Internet: From DBA to e-DBA.
The PDA DBA.
The Rest of the Book.
Defining the Organization's DBMS Strategy.
Choosing a DBMS.
Installing the DBMS.
DBMS Installation Basics.
Configuring the DBMS.
Connecting the DBMS to Supporting Infrastructure Software.
Upgrading DBMS Versions and Releases.
Features and Complexity.
Complexity of the DBMS Environment.
Reputation of the DBMS Vendor.
Support Policies of the DBMS.
DBA Staff Skill Set.
The DBMS Upgrade Strategy.
Database Standards and Procedures.
Database Naming Conventions.
Other Database Standards and Procedures.
Data Modeling Concepts.
The Components of a Data Model.
Discovering Entities, Attributes, and Relationships.
Conceptual, Logical, and Physical Data Models.
What Is Normalization?
The Normal Forms.
First Normal Form.
Second Normal Form.
Third Normal Form.
A Normalized Data Model.
Further Normal Forms.
Normalization in Practice.
Additional Data Modeling Issues.
From Logical Model to Physical Database.
Transform Entities to Tables.
Transform Attributes to Columns.
Build Referential Constraints for All Relationships.
Build Physical Data Structures.
Database Performance Design.
When to Denormalize.
Special Physical Implementation Needs.
Data Definition Language.
Database Application Development and SQL.
Set-at-a-Time Processing and Relational Closure.
Embedding SQL in a Program.
SQL Middleware and APIs.
Object Orientation and SQL.
Types of SQL.
SQL Coding for Performance.
Unit of Work.
Transaction Processing Systems.
Types of Locks.
Programming Techniques to Minimize Locking Problems.
What Is a Design Review?
Rules of Engagement.
Design Review Participants.
Knowledge and Skills Required.
Types of Design Reviews.
Conceptual Design Review.
Logical Design Review.
Physical Design Review.
Organizational Design Review.
SQL and Application Code Design Review.
Pre-Implementation Design Review.
Post-Implementation Design Review.
Design Review Output.
Change Management Requirements.
The Change Management Perspective of the DBA.
Types of Changes.
Logical and Physical Design.
Physical Database Structures.
Impact of Change on Database Structures.
The Limitations of ALTER.
Database Change Scenarios.
Comparing Database Structures.
Requesting Database Changes.
Standardized Change Requests.
Increased Availability Requirements.
Cost of Downtime.
How Much Availability Is Enough?
Loss of the Data Center.
Loss of the Server Hardware.
Operating System Failure.
DBMS Software Failure.
Security and Authorization Problems. @@BHEADS = Corruption of Data.
Loss of Database Objects.
Loss of Data.
Data Replication and Propagation Failures.
Severe Performance Problems.
Outages: Planned and Unplanned.
Perform Routine Maintenance While Systems Remain Operational.
Automate DBA Functions.
Exploit High-Availability Features.
Exploit Clustering Technology.
A Basic Database Performance Road Map.
Monitoring vs. Management.
Reactive vs. Proactive.
Preproduction Performance Estimation.
Types of Performance Tuning.
Performance Tuning Tools.
DBMS Performance Basics.
The Larger Environment.
Interaction with the Operating System.
Components of the DBMS.
DBMS Installation and Configuration Issues.
Types of Configuration.
Data Cache Details.
“Open” Database Objects.
Locking and Contention.
The System Catalog.
Other Configuration Options.
Techniques for Optimizing Databases.
Raw Partition vs. File System.
File Placement and Allocation.
Page Size (Block Size).
Determining When to Reorganize.
Designing Applications for Relational Access.
CPU and I/O Costs.
Access Path Choices.
Additional Optimization Considerations.
Reviewing Access Paths.
Forcing Access Paths.
SQL Coding and Tuning for Efficiency.
SQL Rules of Thumb.
Additional SQL Tuning Tips.
Identifying Poorly Performing SQL.
Types of Integrity.
Database Structure Integrity.
Types of Structural Problems.
Managing Structural Problems.
Semantic Data Integrity.
Database Security Basics.
Granting and Revoking Authority.
Types of Privileges.
Granting to PUBLIC.
Authorization Roles and Groups.
Other Database Security Mechanisms.
Using Views for Security.
Using Stored Procedures for Security.
Job Scheduling and Security.
Non-DBMS DBA Security.
Preparing for Problems.
Image Copy Backups.
Full vs. Incremental Backups.
Database Objects and Backups.
Concurrent Access Issues.
Log Archiving 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.
Determining Recovery Options.
General Steps for Database Object Recovery.
Types of Recovery.
Testing Your Recovery Plan.
Recovering a Dropped Database Object.
Recovering Broken Blocks and Pages.
Populating Test Databases.
Alternatives to Backup and Recovery.
The Need for Planning.
Risk and Recovery.
General Disaster Recovery Guidelines.
The Remote Site.
The Written Plan.
Backing Up the Database for Disaster Recovery.
Storage Management Backups.
Disaster and Contingency Planning Web Sites.
Storage Management Basics.
Files and Data Sets.
File Placement on Disk.
Raw Partitions vs. File Systems.
Temporary Database Files.
Data Page Layouts.
Index Page Layouts.
Storage Area Networks.
Direct Access File System.
Planning for the Future.
Loading and Unloading Data.
The LOAD Utility.
The UNLOAD Utility.
Maintaining Application Test Beds.
EXPORT and IMPORT.
Bulk Data Movement.
Replication and Propagation.
Setting Up a Distributed Environment.
Data Distribution Standards.
Accessing Distributed Data.
Distributed Performance Problems.
What Is a Data Warehouse?
Analytical vs.Transaction Processing.
Administering the Data Warehouse.
Too Much Focus on Technology?
Data Warehouse Design.
Data Warehouse Scalability.
Data Warehouse Performance.
Backup and Recovery.
Don't Operate in a Vacuum!
A Historical Look.
What Is Client/Server Computing?
Types of Client/Server Applications.
Databases, the Internet, and the Web.
What Is Metadata?
From Data to Knowledge and Beyond.
Data Warehousing and Metadata.
Types of Metadata.
Repositories and Data Dictionaries.
Types and Benefits of DBA Tools.
Data Modeling and Design.
Database Change Management.
Backup and Recovery.
Data Warehousing and Business Intelligence.
Programming and Development Tools.
Evaluating DBA Tool Vendors.
Homegrown DBA Tools.
Write Down Everything.
Share Your Knowledge.
Analyze, Simplify, and Focus.
Measure Twice, Cut Once.
Understand the Business, Not Just the Technology.
Don't Become a Hermit.
Use All of the Resources at Your Disposal.
What Is a Database?
Why Use a DBMS?
Advantages of Using a DBMS.
The Big Three.
The Second Tier.
Other Significant Players.
Open-Source DBMS Offerings.
Nonrelational DBMS Vendors.
Object-Oriented DBMS Vendors.
PC-Based DBMS Vendors.
The Major Vendors.
Other DBA Tool Vendors.
Data Modeling Tool Vendors.
Data Movement and Business Intelligence Vendors.
Web Sites and Portals.
Vendor Web Sites.
Magazine Web Sites.
Consultant Web Sites.
Other Web Sites.
Database Management and Database Systems.
Data Administration, Data Modeling, and Database Design.
Object Orientation and Database Management.
A Database Management System (DBMS) is used to create databases. Most of today's applications deploy databases to store information like names, addresses, account balances, etc. This information can be accessed and manipulated by application programs to perform business processes (like payroll processing, sales processing, and customer billing). Every DBMS requires database administration to ensure efficient and effective usage of databases by applications. This means that every user of Oracle, Microsoft SQL Server, DB2, Informix, Sybase, mySQL, Teradata, PostgreSQL, Ingres and any other popular DBMS will benefit from the information in this book.
Many organizations have multiple of these products and will benefit from a consolidated view of DBA that does not focus on the internals and nuances of each particular DBMS product. Such a view is presented in this text.This book provides the industry's first non-product based description of database administration techniques and practices. The book defines the job of database administrator and outlines what is required of a DBA in clear, easy-to-understand language. The book can be used:
Every organization that deploys databases using a DBMS needs to understand the concepts outlined in this book. Many small to medium organizations attempt to implement DBMS products without DBA. This book explains the practice of DBA and can be used to educate these organizations as to the necessity of DBA in order for DBMS implementation to succeed. Other organizations implement only subsets of the DBA practices that are covered in this book. With a thorough reading of Database Administration: Practices and Procedures, it will become quite clear that a comprehensive approach to DBA is required. This book examines and explains each of the components that comprise the discipline of database administration.
As technology advances new IT techniques emerge that impact the discipline of DBA. Two such areas are Internet-enabled database access and storing procedural logic in the DBMS in the form of triggers, user-defined functions, and stored procedures. Because the impact of these newer technologies and techniques on the role of the DBA is examined in this book, even seasoned database professionals will find the book useful. Indeed, the book will be helpful for any and all of the following folks:
Because this book covers heterogeneous database administration without focusing on just one DBMS, it can be used by organizations to set up a DBA function when more than one DBMS product is being used. This is particularly important because Gartner Group, the industry analyst firm, estimates that most medium to large organizations have from 3 to 10 different DBMS products in use that require administration. The single-DBMS shop is a rarity these days.
Additionally, DBA positions are currently very hot, with DBAs demanding and obtaining very high salaries. As such, many technicians aspire to become DBAs and this book will help them to do just that. If you are an IT professional with interest in becoming a DBA, this book will help you to achieve that objective.Other books about database administration are available, but they approach the subject from the perspective of a single DBMS. Many of these books are quite good. I wrote one myself for DB2. This book is not intended to replace such books, but to augment these books with an independent treatment of database administration tasks.How to Use This book
This book can be used as both a tutorial and a reference. The book is organized to proceed chronologically through DBA tasks that are likely to be encountered. So, if you read the book sequentially from Chapter 1 through Chapter 23 you will get a comprehensive chronological overview of the DBA job. Or you can read any chapter independently if you wish because each chapter deals with a single subject matter. References to other chapters are clearly made where appropriate if other material in the book would aid the reader's understanding.
Click below to download the Index file related to this title: