Database Administration
The Complete Guide to DBA Practices and Procedures
2nd Edition
Published on 25. October 2012
Book
Paperback/Softback
928 pages
978-0-321-82294-9 (ISBN)
Description
Database Administration, Second Edition, is the definitive, technology-independent guide to the modern discipline of database administration. Packed with best practices and proven solutions for any database platform or environment, this text fully reflects the field's latest realities and challenges. Drawing on more than thirty years of database experience, Mullins focuses on problems that today's DBAs actually face, and skills and knowledge they simply must have.
Mullins presents realistic, thorough, and up-to-date coverage of every DBA task, including creating database environments, data modeling, normalization, design, performance, data integrity, compliance, governance, security, backup/recovery, disaster planning, data and storage management, data movement/distribution, data warehousing, connectivity, metadata, tools, and more.
This edition adds new coverage of "Big Data," database appliances, cloud computing, and NoSQL. Mullins includes an entirely new chapter on the DBA's role in regulatory compliance, with substantial new material on data breaches, auditing, encryption, retention, and metadata management. You'll also find an all-new glossary, plus up-to-the-minute DBA rules of thumb.
Mullins presents realistic, thorough, and up-to-date coverage of every DBA task, including creating database environments, data modeling, normalization, design, performance, data integrity, compliance, governance, security, backup/recovery, disaster planning, data and storage management, data movement/distribution, data warehousing, connectivity, metadata, tools, and more.
This edition adds new coverage of "Big Data," database appliances, cloud computing, and NoSQL. Mullins includes an entirely new chapter on the DBA's role in regulatory compliance, with substantial new material on data breaches, auditing, encryption, retention, and metadata management. You'll also find an all-new glossary, plus up-to-the-minute DBA rules of thumb.
Reviews / Votes
"I've forgotten how many times I've recommended this book to people. It's well written, to the point, and covers the topics that you need to know to become an effective DBA."-Scott Ambler, Thought Leader, Agile Data Method
"This is a well-written, well-organized guide to the practice of database administration. Unlike other books on general database theory or relational database theory, this book focuses more directly on the theory and reality of database administration as practiced by database professionals today, and does so without catering too much to any specific product implementation. As such, Database Administration is very well suited to anyone interested in surveying the job of a DBA or those in similar but more specific roles such as data modeler or database performance analyst."
-Sal Ricciardi, Program Manager, Microsoft
"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, Manager, Remote DBA Experts and Oracle ACE
"A complete and comprehensive listing of tasks and responsibilities for DBAs, ranging from creating the database environment to data warehouse administration, and everything in between."
-Mike Tarrani, Computer Consultant
"I think every business manager and every IT manager should have a copy of this book."
-Dan Hotka, Independent Consultant and Oracle ACE
"This book by Craig Mullins is wonderfully insightful and truly important. Mullins describes the role and duties of data administrators and database administrators in modern organizations with remarkable insight and clarity."
-Michael Tozer, Author and former U.S. Navy officer
More details
Other editions
Additional editions
E-Book
10/2012
2nd Edition
Addison Wesley
€60.49
Available for download
E-Book
10/2012
2nd Edition
Addison Wesley
€41.99
Available for download
Previous edition
Book
07/2002
Addison Wesley
€55.70
Article exhausted; check for reprint
Person
Craig S. Mullins is president and principal consultant of Mullins Consulting, a leading data and database management consultancy. Mullins has more than thirty years of experience in all facets of database administration and development, in industries ranging from manufacturing and finance to education and research. He covered database administration as research director for Gartner Group and authored DB2 Developer's Guide, Sixth Edition, the number-one guide to DB2 for z/OS. Mullins publishes The Database Site (thedatabasesite.com) and presents at industry events worldwide. IBM named him Information Management Champion for his work in the DB2 community.
Content
How to Use This Book xxxiii Acknowledgments xxxv
About the Author xxxvii
Chapter 1: What Is a DBA? 1
Why Learn Database Administration? 3
A Unique Vantage Point 4
The Management Discipline of Database Administration 9
Evaluating a DBA Job Offer 14
Database, Data, and System Administration 15
DBA Tasks 20
DBMS Release Migration 29
The Types of DBAs 31
Staffing Considerations 37
Multiplatform DBA Issues 42
Production versus Test 44
The Impact of Newer Technology on DBA 46
DBA Certification 56
The Rest of the Book 58
Review 58
Chapter 2: Creating the Database Environment 61
Defining the Organization's DBMS Strategy 61
Installing the DBMS 75
Upgrading DBMS Versions and Releases 82
Database Standards and Procedures 92
DBMS Education 103
Summary 104
Review 104
Suggested Reading 105
Chapter 3: Data Modeling and Normalization 107
Data Modeling Concepts 108
The Components of a Data Model 113
Discovering Entities, Attributes, and Relationships 124
Conceptual, Logical, and Physical Data Models 125
What Is Normalization? 128
The Normal Forms 128
Normalization in Practice 135
Additional Data Modeling Issues 135
Summary 136
Review 137
Suggested Reading 138
Chapter 4: Database Design 141
From Logical Model to Physical Database 141
Database Performance Design 150
Denormalization 160
Views 175
Data Definition Language 177
Temporal Data Support 177
Summary 180
Review 181
Suggested Reading 182
Chapter 5: Application Design 185
Database Application Development and SQL 186
Defining Transactions 205
Locking 210
Batch Processing 221
Summary 222
Review 222
Suggested Reading 223
Chapter 6: Design Reviews 227
What Is a Design Review? 227
Types of Design Reviews 232
Design Review Output 239
Additional Considerations 240
Summary 241
Review 241
Suggested Reading 242
Chapter 7: Database Change Management 243
Change Management Requirements 244
Types of Changes 247
Impact of Change on Database Structures 250
Summary 262
Review 263
Suggested Reading 263
Chapter 8: Data Availability 265
Defining Availability 267
Cost of Downtime 271
Availability Problems 274
Ensuring Availability 287
Summary 296
Review 297
Suggested Reading 298
Chapter 9: Performance Management 299
Defining Performance 299
Service-Level Management 308
Types of Performance Tuning 311
Performance Tuning Tools 313
DBMS Performance Basics 315
Summary 316
Review 316
Suggested Reading 317
Chapter 10: System Performance 319
The Larger Environment 320
DBMS Installation and Configuration Issues 327
System Monitoring 345
Summary 346
Review 346
Suggested Reading 347
Chapter 11: Database Performance 349
Techniques for Optimizing Databases 349
Database Reorganization 365
Summary 371
Review 371
Suggested Reading 372
Chapter 12: Application Performance 373
Designing Applications for Relational Access 373
Relational Optimization 374
Additional Optimization Considerations 391
Reviewing Access Paths 394
SQL Coding and Tuning for Efficiency 399
Summary 407
Review 407
Suggested Reading 408
Chapter 13: Data Integrity 409
Types of Integrity 409
Database Structure Integrity 410
Semantic Data Integrity 414
Temporal Database Systems 444
Summary 446
Review 447
Suggested Reading 448
Chapter 14: Database Security 449
Data Breaches 449
Database Security Basics 451
Granting and Revoking Authority 456
Authorization Roles and Groups 466
Other Database Security Mechanisms 468
Encryption 470
SQL Injection 473
Auditing 477
External Security 478
DBMS Fixpacks and Maintenance 480
Summary 481
Review 481
Suggested Reading 482
Chapter 15: Regulatory Compliance and Database Administration 483
A Collaborative Approach to Compliance 486
Metadata Management, Data Quality, and Data Governance 488
Database Auditing and Data Access Tracking 490
Data Masking and Obfuscation 496
Database Archiving for Long-Term Data Retention 498
Closer Tracking of Traditional DBA Tasks 507
Summary 511
Review 511
Suggested Reading 512
Chapter 16: Database Backup and Recovery 515
The Importance of Backup and Recovery 515
Preparing for Problems 516
Backup 517
Recovery 537
Alternatives to Backup and Recovery 554
Summary 557
Review 557
Suggested Reading 558
Chapter 17: Disaster Planning 559
The Need for Planning 559
General Disaster Recovery Guidelines 563
Backing Up the Database for Disaster Recovery 569
Disaster Prevention 575
Summary 576
Review 576
Suggested Reading 577
Chapter 18: Data and Storage Management 579
Storage Management Basics 579
Files and Data Sets 583
Space Management 587
Fragmentation and Storage 595
Storage Options 596
Planning for the Future 608
Summary 609
Review 609
Suggested Reading 610
Chapter 19: Data Movement and Distribution 613
Loading and Unloading Data 614
EXPORT and IMPORT 622
Bulk Data Movement 623
Distributed Databases 626
Summary 633
Review 634
Suggested Reading 635
Chapter 20: Data Warehouse Administration 637
What Is a Data Warehouse? 637
Administering the Data Warehouse 640
Summary 658
Review 658
Suggested Reading 659
Chapter 21: Database Connectivity 661
Multitier, Distributed Computing 661
Network Traffic 670
Databases, the Internet, and the Web 675
Summary 681
Review 682
Suggested Reading 682
Chapter 22: Metadata Management 685
What Is Metadata? 685
Types of Metadata 689
Repositories and Data Dictionaries 691
Summary 696
Review 696
Suggested Reading 697
Chapter 23: DBA Tools 699
Types and Benefits of DBA Tools 699
Examine Native DBA Tools 728
Evaluating DBA Tool Vendors 729
Summary 733
Review 733
Chapter 24: DBA Rules of Thumb 735
Write Down Everything 735
Keep Everything 736
Automate! 737
Share Your Knowledge 739
Analyze, Simplify, and Focus 741
Don't Panic! 742
Measure Twice, Cut Once 743
Understand the Business, Not Just the Technology 743
Don't Become a Hermit 745
Use All of the Resources at Your Disposal 745
Keep Up-to-Date 746
Invest in Yourself 747
Summary 748
Final Exam 748
Appendix A: Database Fundamentals 753
What Is a Database? 753
Why Use a DBMS? 754
Summary 759
Appendix B: The DBMS Vendors 761
The Big Three 762
The Second Tier 763
Other Significant Players 763
Open-Source DBMS Offerings 764
Nonrelational DBMS Vendors 765
NoSQL DBMS Vendors 765
Object-Oriented DBMS Vendors 766
PC-Based DBMS Vendors 766
Appendix C: DBA Tool Vendors 769
The Major Vendors 769
Other DBA Tool Vendors 770
Data Modeling Tool Vendors 771
Repository Vendors 772
Data Movement and Business Intelligence Vendors 773
Appendix D: DBA Web Resources 775
Usenet Newsgroups 775
Mailing Lists 776
Web Sites, Blogs, and Portals 778
Appendix E: Sample DBA Job Posting 785
Job Posting 785
Bibliography 793
Database Management and Database Systems 793
Data Administration, Data Modeling, and Database Design 799
Database Security, Protection, and Compliance 802
Data Warehousing 804
SQL 805
Object Orientation and Database Management 807
Operating Systems 807
Related Topics 808
DB2 812
IMS 813
MySQL 813
Oracle 814
SQL Server 815
Sybase 816
Other Database Systems 817
Glossary 819
Index 853
About the Author xxxvii
Chapter 1: What Is a DBA? 1
Why Learn Database Administration? 3
A Unique Vantage Point 4
The Management Discipline of Database Administration 9
Evaluating a DBA Job Offer 14
Database, Data, and System Administration 15
DBA Tasks 20
DBMS Release Migration 29
The Types of DBAs 31
Staffing Considerations 37
Multiplatform DBA Issues 42
Production versus Test 44
The Impact of Newer Technology on DBA 46
DBA Certification 56
The Rest of the Book 58
Review 58
Chapter 2: Creating the Database Environment 61
Defining the Organization's DBMS Strategy 61
Installing the DBMS 75
Upgrading DBMS Versions and Releases 82
Database Standards and Procedures 92
DBMS Education 103
Summary 104
Review 104
Suggested Reading 105
Chapter 3: Data Modeling and Normalization 107
Data Modeling Concepts 108
The Components of a Data Model 113
Discovering Entities, Attributes, and Relationships 124
Conceptual, Logical, and Physical Data Models 125
What Is Normalization? 128
The Normal Forms 128
Normalization in Practice 135
Additional Data Modeling Issues 135
Summary 136
Review 137
Suggested Reading 138
Chapter 4: Database Design 141
From Logical Model to Physical Database 141
Database Performance Design 150
Denormalization 160
Views 175
Data Definition Language 177
Temporal Data Support 177
Summary 180
Review 181
Suggested Reading 182
Chapter 5: Application Design 185
Database Application Development and SQL 186
Defining Transactions 205
Locking 210
Batch Processing 221
Summary 222
Review 222
Suggested Reading 223
Chapter 6: Design Reviews 227
What Is a Design Review? 227
Types of Design Reviews 232
Design Review Output 239
Additional Considerations 240
Summary 241
Review 241
Suggested Reading 242
Chapter 7: Database Change Management 243
Change Management Requirements 244
Types of Changes 247
Impact of Change on Database Structures 250
Summary 262
Review 263
Suggested Reading 263
Chapter 8: Data Availability 265
Defining Availability 267
Cost of Downtime 271
Availability Problems 274
Ensuring Availability 287
Summary 296
Review 297
Suggested Reading 298
Chapter 9: Performance Management 299
Defining Performance 299
Service-Level Management 308
Types of Performance Tuning 311
Performance Tuning Tools 313
DBMS Performance Basics 315
Summary 316
Review 316
Suggested Reading 317
Chapter 10: System Performance 319
The Larger Environment 320
DBMS Installation and Configuration Issues 327
System Monitoring 345
Summary 346
Review 346
Suggested Reading 347
Chapter 11: Database Performance 349
Techniques for Optimizing Databases 349
Database Reorganization 365
Summary 371
Review 371
Suggested Reading 372
Chapter 12: Application Performance 373
Designing Applications for Relational Access 373
Relational Optimization 374
Additional Optimization Considerations 391
Reviewing Access Paths 394
SQL Coding and Tuning for Efficiency 399
Summary 407
Review 407
Suggested Reading 408
Chapter 13: Data Integrity 409
Types of Integrity 409
Database Structure Integrity 410
Semantic Data Integrity 414
Temporal Database Systems 444
Summary 446
Review 447
Suggested Reading 448
Chapter 14: Database Security 449
Data Breaches 449
Database Security Basics 451
Granting and Revoking Authority 456
Authorization Roles and Groups 466
Other Database Security Mechanisms 468
Encryption 470
SQL Injection 473
Auditing 477
External Security 478
DBMS Fixpacks and Maintenance 480
Summary 481
Review 481
Suggested Reading 482
Chapter 15: Regulatory Compliance and Database Administration 483
A Collaborative Approach to Compliance 486
Metadata Management, Data Quality, and Data Governance 488
Database Auditing and Data Access Tracking 490
Data Masking and Obfuscation 496
Database Archiving for Long-Term Data Retention 498
Closer Tracking of Traditional DBA Tasks 507
Summary 511
Review 511
Suggested Reading 512
Chapter 16: Database Backup and Recovery 515
The Importance of Backup and Recovery 515
Preparing for Problems 516
Backup 517
Recovery 537
Alternatives to Backup and Recovery 554
Summary 557
Review 557
Suggested Reading 558
Chapter 17: Disaster Planning 559
The Need for Planning 559
General Disaster Recovery Guidelines 563
Backing Up the Database for Disaster Recovery 569
Disaster Prevention 575
Summary 576
Review 576
Suggested Reading 577
Chapter 18: Data and Storage Management 579
Storage Management Basics 579
Files and Data Sets 583
Space Management 587
Fragmentation and Storage 595
Storage Options 596
Planning for the Future 608
Summary 609
Review 609
Suggested Reading 610
Chapter 19: Data Movement and Distribution 613
Loading and Unloading Data 614
EXPORT and IMPORT 622
Bulk Data Movement 623
Distributed Databases 626
Summary 633
Review 634
Suggested Reading 635
Chapter 20: Data Warehouse Administration 637
What Is a Data Warehouse? 637
Administering the Data Warehouse 640
Summary 658
Review 658
Suggested Reading 659
Chapter 21: Database Connectivity 661
Multitier, Distributed Computing 661
Network Traffic 670
Databases, the Internet, and the Web 675
Summary 681
Review 682
Suggested Reading 682
Chapter 22: Metadata Management 685
What Is Metadata? 685
Types of Metadata 689
Repositories and Data Dictionaries 691
Summary 696
Review 696
Suggested Reading 697
Chapter 23: DBA Tools 699
Types and Benefits of DBA Tools 699
Examine Native DBA Tools 728
Evaluating DBA Tool Vendors 729
Summary 733
Review 733
Chapter 24: DBA Rules of Thumb 735
Write Down Everything 735
Keep Everything 736
Automate! 737
Share Your Knowledge 739
Analyze, Simplify, and Focus 741
Don't Panic! 742
Measure Twice, Cut Once 743
Understand the Business, Not Just the Technology 743
Don't Become a Hermit 745
Use All of the Resources at Your Disposal 745
Keep Up-to-Date 746
Invest in Yourself 747
Summary 748
Final Exam 748
Appendix A: Database Fundamentals 753
What Is a Database? 753
Why Use a DBMS? 754
Summary 759
Appendix B: The DBMS Vendors 761
The Big Three 762
The Second Tier 763
Other Significant Players 763
Open-Source DBMS Offerings 764
Nonrelational DBMS Vendors 765
NoSQL DBMS Vendors 765
Object-Oriented DBMS Vendors 766
PC-Based DBMS Vendors 766
Appendix C: DBA Tool Vendors 769
The Major Vendors 769
Other DBA Tool Vendors 770
Data Modeling Tool Vendors 771
Repository Vendors 772
Data Movement and Business Intelligence Vendors 773
Appendix D: DBA Web Resources 775
Usenet Newsgroups 775
Mailing Lists 776
Web Sites, Blogs, and Portals 778
Appendix E: Sample DBA Job Posting 785
Job Posting 785
Bibliography 793
Database Management and Database Systems 793
Data Administration, Data Modeling, and Database Design 799
Database Security, Protection, and Compliance 802
Data Warehousing 804
SQL 805
Object Orientation and Database Management 807
Operating Systems 807
Related Topics 808
DB2 812
IMS 813
MySQL 813
Oracle 814
SQL Server 815
Sybase 816
Other Database Systems 817
Glossary 819
Index 853