Database. Administration. The Complete. and Procedures. Guide to DBA Practices. AAddison-Wesley. Second Edition. Mullins

Administration

The

_Complete

Guide

to

DBA Practices

and Procedures

Second

Craig

AAddison-Wesley

UpperSaddle River,NJ • Boston • Indianapolis •San Francisco

New York•Toronto • Montreal • London• Munich• Paris •Madrid

Contents

Preface xxxi

HowtoUse This Book xxxiii

Acknowledgments xxxv About the Author xxxvii

Chapter 1 What Is a DBA? 1

WhyLearn Database Administration? 3

A_{Unique Vantage}Point 4 DBA Salaries 4

Database _Technology 6

The_{Management Discipline} ofDatabase Administration 9

A _Day in the _{Life of}aDBA 12

Evaluating a DBAJob Offer 14

Database, Data, and_SystemAdministration 15

DataAdministration 15

Database Administration 19

SystemAdministration 20

DBA Tasks 20

Database _Design 21

Performance Monitoring and_Tuning 22

Ensuring Availability 24

Database _Securityand Authorization 24

Governance and_{Regulatory Compliance} 26

Backupand_Recovery 26

EnsuringData_Integrity 27 DBMS Release _Migration 29

Jack-of-All-Trades 29

The _Types of DBAs 31

System DBA 31

Database Architect ₃₂

Database_Analyst 33

Data Modeler 33

Application DBA ₃₄

Task-OrientedDBA 36

Performance Analyst 36

Data Warehouse Administrator 36

Staffing Considerations 37

How_Many DBAs? 37

DBA _ReportingStructures 40

Multiplatform DBA Issues 42

ProductionversusTest 44

The _Impact ofNewer_Technologyon DBA 46

Contents xi

TheInternet: From DBA to eDBA 50

The Personal DBA and the Cloud ₅₃

NoSQL, Big Data, and the DBA 55

New _{Technology Impacts} on DBA 56

DBA Certification 56

The Rest of the Book 58

Review 58

Bonus _Question 59

Chapter 2 _Creating the Database Environment 61

Definingthe _{Organization's} DBMS _Strategy 61

Choosing a DBMS 63 DBMS Architectures 68

DBMS _Clustering 71

DBMS_{Proliferation} 73

Hardware Issues 73

CloudDatabase_Systems 74

Installingthe DBMS 75

DBMS Installation Basics 75

Hardware_Requirements 76

Storage Requirements 76

MemoryRequirements 78

ConfiguringtheDBMS 80

Connectingthe DBMSto _{Supporting Infrastructure Software} 81

Installation _Verification 81

DBMSEnvironments 82

UpgradingDBMS Versions and Releases 82 Featuresand _Complexity 87

Complexity ofthe DBMS Environment 87

Reputation ofthe DBMS Vendor 89

Organization Style 89

DBA _StaffSkill Set 90

Platform Support 90

Supporting Software 91 Fallback_Planning 92

Migration Verification 92

The DBMS _{Upgrade Strategy} 92 Database Standards and Procedures 92

Database_Naming Conventions 93

Other Database Standards andProcedures 96

DBMS Education 103

Summary 104 Review 104

Bonus_Question 105

Suggested Reading 105

Chapter 3 Data _Modeling and Normalization 107

Data_{Modeling Concepts} 108

Entity-Relationship Diagramming 110

The _Componentsofa Data Model 113 Entities 113

Attributes 115

Keys 120

Relationships 122

Discovering Entities, Attributes, and _{Relationships 124}

Conceptual, Logical, and _Physical Data Models 125 WhatIsNormalization? 128

The Normal Forms 128 FirstNormalForm 129 Second Normal Form 129

Contents xiii

A Normalized Data Model ₁₃₃

Further NormalForms 134

Normalization in Practice 135

Additional Data _Modeling Issues 135 Summary 136

Review 137

Bonus _Question 138

Suggested Reading 138

Chapter 4 Database _Design 141

From _LogicalModel to _Physical Database 141

TransformEntities to Tables 142

TransformAttributesto Columns 142

Build_Referential Constraints_forAll_{Relationships} 146 Build_PhysicalData Structures 147

DatabasePerformance_Design 150

DesigningIndexes 150

Hashing 158

Clustering 159

InterleavingData 160

Denormalization 160

When toDenormalize 161

Prejoined Tables 164

ReportTables 164

MirrorTables 165

Split Tables 165

Combined Tables 168

Redundant Data 168

Repeating Groups 169

Derivable Data 170 Hierarchies 171

Special Physical ImplementationNeeds 173

Denormalization _Summary 173 Views 175

Data Definition _Language 177

TemporalData _Support 177 A _{Temporal Example} 178

Business Time and_System Time 179

Summary 180 Review 181

Bonus _Question 181

Suggested Reading 182

Chapter 5 _{Application Design} 185

Database_{Application Development}and_SQL 186

SQL 186

Set-at-a-Time_Processing and Relational Closure ₁₈₉

Embedding SQL ina Program 191

SQLMiddleware andAPIs 192

Application Infrastructure 193

Object Orientation and_SQL 199

Types of SQL 200

SQL Codingfor Performance 202

QueryingXML Data 203

DefiningTransactions 205 Transaction Guidelines 207

Unit_ofWork 207

Transaction_{Processing Systems} 207

Application Servers 209

Locking 210

Types ofLocks 212 Lock Time-outs ₂₁₃

Contents XV

Deadlocks 214 LockDuration 215

Lock Escalation 219

Programming Techniques to Minimize _LockingProblems 220

Locking Summary 220

Batch_Processing 221

Summary 222

Review 222

Bonus_{Question 223}

Suggested Reading 223

Chapter 6 _Design Reviews 227 WhatIs a_DesignReview? 227

Rules_ofEngagement 228

Design Review_Participants 229

Knowledge and Skills_{Required 232}

Types of_DesignReviews 232

ConceptualDesignReview 233

Logical Design Review ₂₃₅

Physical DesignReview 236

Organizational Design Review ₂₃₇

SQL and_Application Code_DesignReview 238

Pre-Implementation DesignReview 239

Post-Implementation DesignReview 239 DesignReview_Output 239

Additional Considerations 240

Dealingwith Remote_Staff 240

Mentorship and_{Knowledge Transfer} 240 Summary 241

Review 241

Chapter 7 Database _{Change Management} 243

ChangeManagementRequirements 244

The_{Change Management Perspective of}the DBA 246

Types of_Changes 247

DBMS _Software 248

Hardware _{Configuration} 248

Logicaland_{PhysicalDesign} 248

Applications 249

PhysicalDatabase Structures 250

Impactof_Change onDatabase Structures 250

TheLimitations _ofALTER 252

Database _Change Scenarios 254

ComparingDatabase Structures 257

RequestingDatabase _Changes 258 Standardized _{Change Requests} 259 Communication 260

CoordinatingDatabase and_{Application Changes} 260

Compliance 261

DBA _Scripts and _{ChangeManagement} 262

Summary 262

Review 263

Suggested Reading 263

Chapter 8 Data_Availability 265

Defining Availability 267

Increased_{Availability Requirements} 268 Cost of Downtime 271

How Much_AvailabilityIs_{Enough? 273}

Availability Problems 274

Loss _ofthe Data Center 274

Contents xvii

Loss_oftheServer Hardware 276

Disk-Related _Outages 278

Operating SystemFailure 279 DBMS_SoftwareFailure 279

Application Problems 279

Security and Authorization Problems 280

Corruption ofData 280 Loss _ofDatabase _Objects 281

Loss _ofData 282

Data_Replication and_Propagation Failures ₂₈₃ Severe_PerformanceProblems 283

RecoveryIssues 284 DBA Mistakes 284

Outages:Planned and _Unplanned 286

Ensuring Availability 287

Perform Routine Maintenance While_SystemsRemain

Operational 288

AutomateDBA Functions 290

ExploitHigh-AvailabilityFeatures 291

Exploit Clustering Technology 292 Database Architecture and_NoSQL 296

Summary 296

Review 297

Suggested Reading 298

Chapter 9 Performance _Management 299

Defining Performance 299

A BasicDatabase_PerformanceRoad_Map 302

Monitoringversus _Management 304

Preproduction PerformanceEstimation 307 Historical _Trending 308

Service-Level_Management 308

Typesof Performance _Tuning 311

System Tuning 311

Database _Tuning 312

Application Tuning 312

Performance _TuningTools 313

DBMS Performance Basics 315

Summary 316

Review 316

Bonus_{Question 317}

Suggested Reading 317

Chapter 10 _System Performance 319 The_LargerEnvironment 320

Interaction with the _{Operating System 320} Allied_Agents 321

Hardware _{Configuration 322}

Components ofthe DBMS 324

DBMS Installation and _{Configuration} Issues 327

Types of Configuration 327

Memory Usage 328

Data Cache Details 332

"Open"Database _{Objects 336} Database _{Logs 336}

Locking and Contention 341

The_{System Catalog 342}

Other _{Configuration Options 343} GeneralAdvice 344

Contents xix

Summary 346

Review 346

Bonus_Question 347 Suggested Reading 347

Chapter 11 Database Performance 349

Techniquesfor_OptimizingDatabases 349 Partitioning 350

Raw Partition versusFile System 351

Indexing 352

Denormalization 355

Clustering 356

InterleavingData ₃₆₀ Free_Space 360

Compression 361

File Placement and Allocation 362

PageSize _{(Block Size) 364} Database_{Reorganization 365}

DeterminingWhen to_{Reorganize 369} Automation ₃₇₁

Summary 371

Review 371

Suggested Reading 372

Chapter 12 _Application Performance 373

Designing Applications for RelationalAccess 373 Relational _Optimization 374

CPU andI/O Costs 376

Database Statistics ₃₇₆

Query Analysis 378 Joins 379

Additional _OptimizationConsiderations 391

View Access 391

QueryRewrite 392

Rule-Based _Optimization 393

ReviewingAccess Paths 394

ForcingAccess Paths 398

SQLCodingand_Tuningfor_Efficiency 399

A Dozen _SQLRules _ofThumb 400

Additional_{SQL Tuning Tips} 406

IdentifyingPoorly Performing SQL 406

Summary 407

Review 407

Suggested Reading 408

Chapter 13 Data _Integrity 409

Types of_Integrity 409

Database Structure _Integrity 410

Types ofStructuralProblems 410

Managing StructuralProblems 411 SemanticData _Integrity 414

Entity Integrity 416

Unique Constraints 417

Data _Types 417

DefaultValues 419

Check Constraints 419

Triggers 426

Referential Integrity 433 Temporal Database _Systems 444

Summary 446 Review 447

Chapter 14 Database _Security 449

DataBreaches 449

Database_SecurityBasics 451

Database Users 455

Grantingand _{Revoking Authority} 456

Types ofPrivileges 457

GrantingtoPUBLIC 460

Revoking Privileges 461

Label-BasedAccess Control ₄₆₃

Security Reporting 465

Authorization Roles and_Groups 466

Roles 466

Groups 467

Other Database _SecurityMechanisms 468

Using Views_{for Security} 468

Using Stored Procedures_{for Security} 470

Encryption 470

DataatRest_Encryption 472 Data in Transit_Encryption 472

Encryption Techniques 472

SQL Injection 473

SQL InjectionPrevention 475

Auditing 477

External_Security 478

Job Schedulingand_Security 479 Non-DBMS DBA _Security 480

DBMS_Fixpacks andMaintenance 480

Summary 481

Review 481

Chapter 15 _{Regulatory Compliance} and Database

Administration 483

A Collaborative_Approach to Compliance 486

WhyShould DBAs Care about_Compliance? 487

Metadata _Management, Data_Quality, and Data Governance 488

Metadata 488 Data _Quality 489

Data Governance 489

Database _Auditing and Data Access _Tracking 490

Database_{Auditing Techniques} 493

PrivilegedUser_Auditing 495

Data _Masking and Obfuscation 496

Data _{Masking Techniques} 497

Database _Archivingfor_Long-TermData Retention 498

The_{Life Cycle of}Data 499

Database_Archiving 500

Components ofa DatabaseArchiving Solution 505 The _{Impactofe-Discovery} on DBA 506

Closer_Tracking of Traditional DBA Tasks 507 Database _{ChangeManagement} 508

Database _Backup and_Recovery 508

Summary 511

Review 511

SuggestedReading 512

Chapter 16 Database _Backup and _Recovery 515

The _Importance of_Backup and _Recovery 515

Preparing for Problems 516

Backup 517

FullversusIncrementalBackups ₅₂₁ Database _Objects and_{Backups 523}

DBMS Control 524

ConcurrentAccess Issues 525

Backup Consistency 527

Log Archivingand_{Backup 529}

Determining Your_Backup Schedule 531

DBMSInstance_Backup 533

Designing the DBMS Environment_{for Recovery 533} Alternate _Approaches to Database_Backup 534

Document Your_{Backup Strategy 536}

Database _{Object Definition Backups 536}

Recovery 537

DeterminingRecovery Options 538

General_{Steps for} Database _{Object Recovery 540}

Types ofRecovery 541 Index_Recovery 550

Testing Your_RecoveryPlan 551

Recovering aDroppedDatabase Object 552

RecoveringBroken Blocks and_Pages 553

Populating Test Databases 553

Alternatives to _Backup and_Recovery 554

StandbyDatabases 554

Replication 555

Disk_Mirroring 556

Summary 557 Review 557

Suggested Reading 558

Chapter 17 Disaster _Planning 559 The Need for_Planning 559

General Disaster_Recovery Guidelines 563

TheRemote Site 564

The Written Plan 564

Personnel 569

Backing Upthe Database for Disaster_Recovery 569

Tape Backups 570

Storage Management Backups 572

Other_Approaches 573

Some Guidelines 573 Disaster Prevention 575

Disaster and _{Contingency Planning} Web Sites 576

Summary 576

Review 576

Suggested Reading 577

Chapter 18 Data and _{Storage Management} 579

Storage Management Basics 579 Files andData Sets 583

File PlacementonDisk 584

Raw Partitions versusFileSystems 586

TemporaryDatabase Files 587

Space Management 587 Data_{Page Layouts} 588

Index_{Page Layouts} 592 Transaction_Logs 594 Fragmentationand_Storage 595

Storage Options 596

RAID 597

JBOD 604

Contents XXV

Network-Attached_Storage 605

Tiered_Storage 606

Planningforthe Future 608

Capacity Planning 608

Summary 609

Review 609

Suggested Reading 610

Chapter 19 Data Movement and Distribution 613

Loading and_Unloading Data 614

The LOAD _Utility 614 The UNLOAD _Utility 618

MaintainingApplication TestBeds 621 EXPORT andIMPORT 622

BulkData Movement 623 ETL _Software 623

Replication and_Propagation 623

Messaging Software 624

Other Methods 625

Distributed Databases 626

Setting Upa Distributed Environment 627 Data DistributionStandards 629

AccessingDistributedData ₆₃₀

Two-Phase COMMIT 631

Distributed_PerformanceProblems ₆₃₂

Summary 633

Review ₆₃₄

Bonus _Question 634

Chapter 20 Data Warehouse Administration 637

What Is a DataWarehouse? 637

Analytical versus Transaction Processing 638

Administering the DataWarehouse 640 Too Much Focus on Technology? 641

Data Warehouse_Design 641

DataMovement 644

Data _Cleansing 645

Data Warehouse_Scalability 649

Data Warehouse_Performance 650

Data Freshness 654

Data Content 654

Data _Usage 655

Financial _Chargeback 655

Backupand_Recovery 656

Don't _Operate in aVacuum! 657

Summary 658

Review 658

Suggested Reading 659

Chapter 21 Database _Connectivity 661

Multitier, Distributed_Computing 661

A Historical Look 661

Business Issues 663

What Is Client/Server_Computing? 663

Types ofClient/Server_Applications 667

Network Traffic 670

Database _Gateways 671

Database Drivers 672

Contents xxvii

Databases, the_Internet, and the Web 675

Internet-ConnectedDatabases 676

Web_Developmentand Web Services 680

Summary 681

Review 682

Suggested Reading 682

Chapter 22 Metadata _Management 685

What Is Metadata? 685

From Data to_Knowledge and_Beyond 686

Metadata_Strategy 687

Data _Warehousing and Metadata 688

Types of Metadata 689

RepositoriesandData Dictionaries 691 Repository Benefits 693

Repository Challenges 693

Data Dictionaries 695 Summary 696

Review 696

SuggestedReading 697

Chapter 23 DBA Tools 699

Typesand Benefits of DBA Tools 699

Data_Modelingand_Design 700 Database _{Change Management} 701

Table Editors 707

Performance Management 708

Backup and_Recovery 714

Database Utilities 715

Data Protection, Governance, Risk, and_Compliance Tools 716

Programming and_DevelopmentTools 724

Miscellaneous Tools 726

ExamineNativeDBATools 728

Evaluating DBATool Vendors 729

Homegrown DBA Tools 732

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, andFocus 741

Don't Panic! 742

MeasureTwice, Cut Once 743

Understand the _Business, Not_Just the _Technology 743

Don't Become aHermit 745

Use All of the Resources atYour_Disposal 745

Keep Up-to-Date 746

Invest inYourself 747

Summary 748

Final Exam 748

Appendix A Database Fundamentals 753 What IsaDatabase? 753

WhyUseaDBMS? 754

Advantages of UsingaDBMS 755

Contents xxix

Appendix B The DBMS Vendors 761 The_BigThree 762

The Second Tier 763

Other _{Significant Players 763}

Open-SourceDBMS _Offerings 764

Nonrelational DBMS Vendors 765 NoSQL DBMSVendors 765

Object-OrientedDBMS Vendors 766

PC-Based DBMS Vendors 766

Appendix C DBATool Vendors 769 The _MajorVendors 769

OtherDBA Tool Vendors 770

Data_ModelingTool Vendors 771

RepositoryVendors 772

Data Movement and Business_IntelligenceVendors 773

Appendix D DBA Web Resources 775

Usenet _Newsgroups 775

Mailing Lists 776

Web_{Sites, Blogs,} and Portals 778 Vendor Web Sites 778

Magazine Web Sites 778 Consultant Web Sites 779

Blogs 780

Database Portals 781 Other Web Sites 782

Appendix E _Sample DBA Job _Posting 785

Job Posting 785

Bibliography 793

Database _Management and Database _Systems 793

Data_{Administration,} Data_Modeling, andDatabase_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