book

Database Administration: The Complete Guide to Practices and Procedures

Name: Database Administration: The Complete Guide to Practices and Procedures
Author: Craig S. Mullins
ISBN: 9780201741292

by Craig S. Mullins

June 2002

Intermediate to advanced

736 pages

17h 21m

English

Addison-Wesley Professional

Read now

Unlock full access

Copyright
Dedication
Preface
How to Use This Book
Acknowledgments
About the Author
1. What Is a DBA?
The DBA: Revered or Reviled?Why Learn Database Administration?A Unique Vantage PointDBA SalariesDatabase TechnologyThe Management Discipline of Database AdministrationA Day in the Life of a DBAEvaluating a DBA Job OfferDatabase, Data, and System AdministrationData AdministrationDatabase AdministrationSystem AdministrationDBA TasksDatabase DesignPerformance Monitoring and TuningAvailabilityDatabase Security and AuthorizationBackup and RecoveryData IntegrityDBMS Release MigrationJack-of-All-TradesTypes of DBAsSystem DBADatabase ArchitectDatabase AnalystData ModelerApplication DBATask-Oriented DBAPerformance AnalystData Warehouse AdministratorStaffing ConsiderationsHow Many DBAs?DBA Reporting StructuresMultiplatform DBA IssuesTest and ProductionNew Technology and the DBAProcedural DBAs: Managing Database LogicStored ProceduresTriggersUser-Defined FunctionsAdministering Stored Procedures, Triggers, and UDFsThe Internet: From DBA to e-DBAThe PDA DBADBA CertificationThe Rest of the BookReviewBonus Question
2. Creating the Database Environment
Defining the Organization's DBMS StrategyChoosing a DBMSDBMS ArchitecturesDBMS ClusteringDBMS ProliferationHardware IssuesInstalling the DBMSDBMS Installation BasicsHardware RequirementsStorage RequirementsMemory RequirementsConfiguring the DBMSConnecting the DBMS to Supporting Infrastructure SoftwareInstallation VerificationDBMS EnvironmentsUpgrading DBMS Versions and ReleasesFeatures and ComplexityComplexity of the DBMS EnvironmentReputation of the DBMS VendorSupport Policies of the DBMSOrganization StyleDBA Staff Skill SetPlatform SupportSupporting SoftwareFallback PlanningMigration VerificationThe DBMS Upgrade StrategyDatabase Standards and ProceduresDatabase Naming ConventionsStandard AbbreviationsOther Database Standards and ProceduresRoles and ResponsibilitiesData Administration StandardsDatabase Administration StandardsSystem Administration StandardsDatabase Application Development StandardsDatabase Security StandardsApplication Migration and Turnover ProceduresDesign Review GuidelinesOperational Support StandardsDBMS EducationSummaryReviewBonus Question
3. Data Modeling and Normalization
Data Modeling ConceptsEntity-Relationship DiagrammingThe Components of a Data ModelEntitiesEntity Naming GuidelinesEntity OccurrencesAttributesDomainsAttribute Naming GuidelinesAttribute ValuesNulls and Lack of ValueKeysCandidate KeysPrimary KeyForeign KeysRelationshipsCardinalityOptionalityDiscovering Entities, Attributes, and RelationshipsConceptual, Logical, and Physical Data ModelsWhat Is Normalization?The Normal FormsFirst Normal FormSecond Normal FormThird Normal FormA Normalized Data ModelFurther Normal FormsNormalization in PracticeAdditional Data Modeling IssuesSummaryReviewBonus QuestionSuggested Reading
Bibliography
4. Database Design
From Logical Model to Physical DatabaseTransform Entities to TablesTransform Attributes to ColumnsTransform Domains to Data TypesPrimary KeysColumn OrderingBuild Referential Constraints for All RelationshipsReferential IntegrityBuild Physical Data StructuresDatabase Performance DesignDesigning IndexesB-Tree IndexesBitmap IndexesReverse Key IndexesPartitioned IndexesOrdered IndexesIndex SummaryHashingClusteringInterleaving DataDenormalizationWhen to DenormalizePrejoined TablesReport TablesMirror TablesSplit TablesSplitting Long Text ColumnsCombined TablesRedundant DataRepeating GroupsDerivable DataHierarchiesSpecial Physical Implementation NeedsDenormalization SummaryViewsData Definition LanguageSummaryReviewBonus QuestionSuggested Reading
Bibliography

5. Application Design
Database Application Development and SQLSQLSet-at-a-Time Processing and Relational ClosureEmbedding SQL in a ProgramSQL Middleware and APIsMicrosoft Database ProgrammingObject Orientation and SQLTypes of SQLSQL Coding for PerformanceDefining TransactionsTransaction GuidelinesUnit of WorkTransaction Processing SystemsApplication ServersLockingTypes of LocksIntent LocksLock TimeoutsDeadlocksLock DurationIsolation LevelAcquire/Release SpecificationLock EscalationProgramming Techniques to Minimize Locking ProblemsLocking SummaryBatch ProcessingSummaryReviewBonus QuestionSuggested Reading
Bibliography
6. Design Reviews
What Is a Design Review?Rules of EngagementDesign Review ParticipantsThe LeaderThe ScribeThe MediatorThe ParticipantsKnowledge and Skills RequiredTypes of Design ReviewsConceptual Design ReviewLogical Design ReviewPhysical Design ReviewOrganizational Design ReviewSQL and Application Code Design ReviewPre-Implementation Design ReviewPost-Implementation Design ReviewDesign Review OutputSummaryReviewSuggested Reading
Bibliography
7. Database Change Management
Change Management RequirementsThe Change Management Perspective of the DBATypes of ChangesDBMS SoftwareHardware ConfigurationLogical and Physical DesignApplicationsPhysical Database StructuresImpact of Change on Database StructuresThe Limitations of ALTERDatabase Change ScenariosSome Database Change ExamplesComparing Database StructuresRequesting Database ChangesStandardized Change RequestsChecking the ChecklistsCommunicationSummaryReview
8. Data Availability
Defining AvailabilityIncreased Availability RequirementsThe Shrinking Maintenance WindowDecision SupportData WarehousingFull-Time AvailabilityGrowing IT ComplexityCost of DowntimeHow Much Availability Is Enough?Availability ProblemsLoss of the Data CenterNetwork ProblemsLoss of the Server HardwareDisk-Related OutagesOperating System FailureDBMS Software FailureApplication ProblemsSecurity and Authorization ProblemsCorruption of DataLoss of Database ObjectsLoss of DataData Replication and Propagation FailuresSevere Performance ProblemsRecovery IssuesDBA MistakesOutages: Planned and UnplannedEnsuring AvailabilityPerform Routine Maintenance While Systems Remain OperationalAutomate DBA FunctionsExploit High-Availability FeaturesExploit Clustering TechnologyA Few Database ExamplesSummaryReviewSuggested Reading
Bibliography
9. Performance Management
Defining PerformanceA Basic Database Performance Road MapMonitoring vs. ManagementReactive vs. ProactivePreproduction Performance EstimationHistorical TrendingService-Level ManagementTypes of Performance TuningSystem TuningDatabase TuningApplication TuningPerformance Tuning ToolsDBMS Performance BasicsSummaryReviewBonus QuestionsSuggested Reading
Bibliography
10. System Performance
The Larger EnvironmentInteraction with the Operating SystemAllied AgentsHardware ConfigurationDisk Storage and I/OComponents of the DBMSDBMS Installation and Configuration IssuesTypes of ConfigurationMemory UsageAdditional Areas of Memory ConsumptionHow Much Memory Is Enough?Data Cache DetailsMonitoring and Tuning the Data CacheMonitoring and Tuning the Procedure Cache“Open” Database ObjectsDatabase LogsDatabase Log Configuration ConsiderationsAre All Database Operations Logged?Locking and ContentionThe System CatalogOther Configuration OptionsGeneral AdviceSystem MonitoringSummaryReviewBonus QuestionSuggested Reading
Bibliography
11. Database Performance
Techniques for Optimizing DatabasesPartitioningRaw Partition vs. File SystemIndexingWhen to Avoid IndexingIndex OverloadingDenormalizationClusteringPage SplittingInterleaving DataFree SpaceCompressionFile Placement and AllocationDatabase Log PlacementDistributed Data PlacementDisk AllocationPage Size (Block Size)Database ReorganizationDetermining When to ReorganizeAutomationSummaryReviewSuggested Reading
Bibliography
12. Application Performance
Designing Applications for Relational AccessRelational OptimizationCPU and I/O CostsDatabase StatisticsQuery AnalysisJoinsJoin OrderAccess Path ChoicesTable ScansIndexed AccessUsing Indexes to Avoid Sorts.Why Wasn't the Index Chosen?Hashed AccessParallel AccessAdditional Optimization ConsiderationsView AccessQuery RewriteRule-Based OptimizationReviewing Access PathsForcing Access PathsSQL Coding and Tuning for EfficiencySQL Rules of ThumbRule 1: “It Depends!”Rule 2: Be Careful What You Ask ForRule 3: KISSRule 4: Retrieve Only What Is NeededRule 5: Avoid Cartesian ProductsRule 6: Judicious Use of ORRule 7: Judicious Use of LIKERule 8: Know What Works BestRule 9: Issue Frequent COMMITsRule 10: Beware of Code GeneratorsRule 11: Consider Stored ProceduresAdditional SQL Tuning TipsIdentifying Poorly Performing SQLSummaryReviewSuggested Reading
Bibliography
13. Data Integrity
Types of IntegrityDatabase Structure IntegrityTypes of Structural ProblemsManaging Structural ProblemsConsistency OptionsDatabase CheckingMemory UsageAdditional OptionsSemantic Data IntegrityEntity IntegrityUnique ConstraintsData TypesUser-Defined Data TypesDefault ValuesCheck ConstraintsCheck Constraint BenefitsCheck Constraint ExamplesNulls and Other Potential Problems.TriggersWhen Does a Trigger Fire?Nested TriggersUsing Triggers to Implement Referential IntegrityTransition Variables and TablesA Sample TriggerTrigger GranularityTrigger SynopsisReferential IntegrityINSERT RuleUPDATE RuleForeign key perspective.Primary key perspective.DELETE RulePendant DELETESetting Up RelationshipsDeclarative RI Implementation ConcernsDBMS Support for Referential IntegrityReferential Integrity Using TriggersUser-Managed vs. System-Managed RIRI vs. Check Constraints vs. Program LogicRI Rules of ThumbSummaryReviewSuggested Reading
Bibliography
14. Database Security
Database Security BasicsDatabase UsersGranting and Revoking AuthorityTypes of PrivilegesGranting Table PrivilegesGranting Database Object PrivilegesGranting System PrivilegesGranting Program and Procedure PrivilegesGranting to PUBLICRevoking PrivilegesCascading REVOKEsChronology and RevokesSecurity ReportingAuthorization Roles and GroupsRolesGroupsLimit the Number of SA UsersGroup-Level Security and Cascading REVOKEsOther Database Security MechanismsUsing Views for SecurityUsing Stored Procedures for SecurityLogic-Oriented SecurityAuditingExternal SecurityJob Scheduling and SecurityNon-DBMS DBA SecuritySummaryReviewSuggested Reading
Bibliography
15. Database Backup and Recovery
Preparing for ProblemsImage Copy BackupsFull vs. Incremental BackupsMerging Incremental CopiesDatabase Objects and BackupsCopying IndexesDBMS ControlConcurrent Access IssuesBackup ConsistencyWhen to Create a Point of ConsistencyLog Archiving and BackupDetermining Your Backup ScheduleDBMS Instance BackupDesigning the DBMS Environment for RecoveryAlternate Approaches to Database BackupUsing Database Exports to Create Logical BackupsUsing Storage Management Software to Make Backup CopiesDocument Your Backup StrategyDatabase Object Definition BackupsRecoveryDetermining Recovery OptionsGeneral Steps for Database Object RecoveryTypes of RecoveryChoosing the Optimum Recovery StrategyMatching Type of Failure to Type of RecoveryIndex RecoveryTesting Your Recovery PlanRecovering a Dropped Database ObjectRecovering Broken Blocks and PagesPopulating Test DatabasesAlternatives to Backup and RecoveryStandby DatabasesReplicationDisk MirroringSummaryReviewSuggested Reading
Bibliography
16. Disaster Planning
The Need for PlanningRisk and RecoveryGeneral Disaster Recovery GuidelinesThe Remote SiteThe Written PlanTesting Your Disaster PlansPersonnelBacking Up the Database for Disaster RecoveryTape BackupsStorage Management BackupsOther ApproachesSome GuidelinesOrder of RecoveryData LatencyRemember Other Vital DataBeware of CompressionPost-Recovery Image CopiesDisaster PreventionDisaster and Contingency Planning Web SitesSummaryReviewSuggested Reading
Bibliography
17. Data and Storage Management
Storage Management BasicsFiles and Data SetsFile Placement on DiskRaw Partitions vs. File SystemsTemporary Database FilesSpace ManagementData Page LayoutsAllocation PagesData Record LayoutsCalculating Table SizeIndex Page LayoutsCalculating Index SizeTransaction LogsStorage OptionsRAIDRAID LevelsJBODStorage Area NetworksNetwork-Attached StorageSAN vs. NASDirect Access File SystemPlanning for the FutureCapacity PlanningSummaryReviewSuggested Reading
Bibliography
18. Data Movement and Distribution
Loading and Unloading DataThe LOAD UtilityDescribing the Input FileEfficient LoadingRunning Other Utilities During a LOADThe UNLOAD UtilityConcurrencyUnloading from Image Copy BackupsGeneration of LOAD ParametersData Encoding SchemeFloating-Point DataLimiting UNLOAD DataUnloading from ViewsMaintaining Application Test BedsEXPORT and IMPORTBulk Data MovementETL SoftwareReplication and PropagationMessaging SoftwareOther MethodsDistributed DatabasesSetting Up a Distributed EnvironmentData Distribution StandardsAccessing Distributed DataTwo-Phase COMMITDistributed Performance ProblemsSummaryReviewBonus QuestionSuggested Reading
Bibliography
19. Data Warehouse Administration
What Is a Data Warehouse?Analytical vs. Transaction ProcessingAdministering the Data WarehouseToo Much Focus on Technology?Data Warehouse DesignData MovementConsistent Data AcquisitionData CleansingData Quality IssuesOperational ProblemsData Warehouse ScalabilitySize IssuesData Warehouse PerformanceAutomated Summary TablesData FreshnessData ContentData UsageFinancial ChargebackBackup and RecoveryDon't Operate in a Vacuum!SummaryReviewSuggested Reading
Bibliography
20. Database Connectivity
Client/Server ComputingA Historical LookBusiness IssuesWhat Is Client/Server Computing?Centralized vs. Distributed ProcessingTypes of Client/Server ApplicationsThin Client or Fat ClientDatabase GatewaysNetwork TrafficDatabases, the Internet, and the WebInternet-Connected DatabasesAvailabilityNew TechnologiesXMLJavaDatabase DesignSummaryReviewSuggested Reading
Bibliography
21. Metadata Management
What Is Metadata?From Data to Knowledge and BeyondMetadata StrategyData Warehousing and MetadataTypes of MetadataRepositories and Data DictionariesRepository BenefitsRepository ChallengesData DictionariesSummaryReviewSuggested Reading
Bibliography
22. DBA Tools
Types and Benefits of DBA ToolsData Modeling and DesignDatabase Change ManagementChange Management ToolsDatabase Comparison ToolsDatabase Object Migration ToolsReferential Integrity ToolsAuditing ToolsCatalog Query and Analysis ToolsSecurity ToolsTable EditorsPerformance ManagementSystem Performance ToolsDatabase Performance ToolsApplication Performance ToolsBackup and RecoveryDatabase UtilitiesUtility Management ToolsData Warehousing and Business IntelligenceETL ToolsQuery and Reporting ToolsProgramming and Development ToolsCheckpoint/Restart ToolsTesting ToolsDebugging ToolsMiscellaneous ToolsSpace Management ToolsCompression ToolsOnline Standards ManualsOther ToolsEvaluating DBA Tool VendorsHomegrown DBA ToolsSummaryReview
23. DBA Rules of Thumb
The RulesWrite Down EverythingKeep EverythingAutomate!Share Your KnowledgeAnalyze, Simplify, and FocusDon't Panic!Measure Twice, Cut OnceUnderstand the Business, Not Just the TechnologyDon't Become a HermitUse All of the Resources at Your DisposalKeep Up-to-DateSummaryFinal Exam
A. Database Fundamentals
What Is a Database?Why Use a DBMS?Advantages of Using a DBMSLevels of Data AbstractionData IndependenceData SecurityConcurrency ControlDatabase LoggingEnsuring Atomicity and DurabilityData IntegrityData AccessSummary
B. The DBMS Vendors
The Big ThreeContact InformationThe Second TierContact InformationOther Significant PlayersContact InformationOpen-Source DBMS OfferingsNonrelational DBMS VendorsObject-Oriented DBMS VendorsPC-Based DBMS Vendors
C. DBA Tool Vendors
The Major VendorsOther DBA Tool VendorsData Modeling Tool VendorsRepository VendorsData Movement and Business Intelligence Vendors
D. DBA Web Resources
Usenet NewsgroupsMailing ListsWeb Sites and PortalsVendor Web SitesMagazine Web SitesConsultant Web SitesDatabase PortalsOther Web Sites
Bibliography
Database Management and Database SystemsData Administration, Data Modeling, and Database DesignData WarehousingObject Orientation and Database ManagementRelated TopicsDB2IMSInformixOracleSQL ServerSybase

404

Not Found

Either this content does not exist or you do not have access to it. Try searching for something new.

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Database Administration: The Complete Guide to Practices and Procedures

by Craig S. Mullins

404

Not Found