O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

MCITP SQL Server 2005 Database Administration All-in-One exam Guide

MCITP SQL Server 2005 Database Administration All-in-One exam Guide

by Darril Gibson
Publisher: McGraw-Hill Osborne Media
Release Date: September 2007
ISBN: 9780071496094
View table of contents
Start reading

Book Description

All-in-One is All You Need

Get complete coverage of all three Microsoft Certified IT Professional database administration exams for SQL Server 2005 in this comprehensive volume. Written by a SQL Server expert and MCITP, this definitive exam guide features learning objectives at the beginning of each chapter, exam tips, practice questions, and in-depth explanations. Detailed and authoritative, the book serves as both a complete certification study guide and an essential on-the-job reference.

Get full details on all exam topics including how to:

• Install and configure SQL Server 2005

• Use Transact-SQL

• Manage server infrastructure design

• Optimize databases

• Secure databases and servers

• Ensure high availability

• Implement backup and recovery strategies

• Maximize the built-in administration tools

• Use Business Intelligence tools, including SSIS and SSRS

• Manage concurrency

The CD-ROM features:

• Six full practice exams--two for each exam: 70-431, 70-443, and 70-444

• Scripts from the step-by-step exercises in the book

• Video training clips from the author

• Complete electronic book

Table of Contents

  1. Cover Page
  2. All-in-One MCITP SQL Server 2005 Database Administration
  3. Copyright Page
  4. Dedication
  5. ABOUT THE AUTHOR
  6. CONTENTS
  7. Acknowledgments
  8. Introduction
  9. Chapter 1 Installing and Configuring SQL Server 2005
    1. SQL Server Editions
      1. Operating Systems and Supported Benefits
      2. Instances
    2. Installing SQL Server 2005
      1. Prerequisites
      2. Downloading
      3. Upgrading
    3. Configuring SQL Server 2005
      1. Service Accounts
      2. Services
      3. SQL Server Surface Area Configuration Tool
      4. SQL Server Configuration Manager
      5. Collations
    4. Connecting to SQL Server 2005
      1. SQL Server Management Studio (SSMS)
      2. SQLCmd
      3. Dedicated Administrator Connection (DAC)
    5. Examining the Installation
      1. System Databases
    6. Troubleshooting the Installation or Operation
      1. Operating System Logs
      2. SQL Logs
    7. Books Online
      1. Additional Study
      2. Summary of What You Need to Know
      3. Questions
      4. Answers
  10. Chapter 2 SQL Server 2005 Database Basics
    1. Tables
      1. Data Types
      2. Creating Tables
    2. Views
      1. Creating Views
      2. Updating Data in a View
      3. VIEW Options
    3. Data Quality
    4. Creating a Database
    5. Schemas and Naming Conventions
      1. Four-Part Naming
      2. Naming Conventions
    6. Database Control
      1. Change Management
      2. Documentation
      3. Additional Study
      4. Summary of What You Need to Know
      5. Questions
      6. Answers
  11. Chapter 3 Database Design
    1. Data Integrity
      1. Primary Key
      2. Foreign Key
      3. Exploring Relationships in AdventureWorks
    2. Normalization and Normal Forms
      1. 1st Normal Form (1NF)
      2. 2nd Normal Form (2NF)
      3. 3rd Normal Form (3NF)
      4. Denormalization
    3. Constraints
      1. Overview
      2. Default Definitions
      3. CHECK
      4. UNIQUE
    4. Partitioning Tables
      1. Horizontal Partitioning
      2. Partition Schemes
    5. Database Design Exercise
      1. The Scenario
      2. The Solution
      3. Additional Study
      4. Summary of What You Need to Know
      5. Questions
      6. Answers
  12. Chapter 4 Transact-SQL Primer
    1. General Rules to Know
      1. ANSI Standard
      2. Delimiting Identifiers
      3. Case Sensitive and Case Insensitive
    2. SELECT
      1. Columns
      2. Where
      3. Order By
      4. Collate
      5. Group By and Having
      6. Joins
    3. Other DML Statements
      1. UPDATE
      2. INSERT
      3. DELETE
    4. Transactions
      1. Overview
      2. Commit
      3. Rollback
      4. Error Catching
    5. XML
      1. XML Overview
      2. FOR XML
      3. Typed vs. Untyped XML
      4. XML Schema Collection
      5. Storing XML Data
      6. Methods
    6. Hints
      1. Query Hints
      2. Query Plan Guides
      3. Additional Study
      4. Summary of What You Need to Know
      5. Questions
      6. Answers
  13. Chapter 5 Advanced Database Objects
    1. Functions
      1. Overview of Functions
      2. Built-In Functions
      3. User-Defined Functions
    2. Stored Procedures
      1. System Stored Procedures
      2. Extended Stored Procedures
      3. Creating Stored Procedures
      4. SQL Injection Attacks
      5. Recompiling
    3. Triggers
      1. Overview of Triggers
      2. DML Triggers
      3. INSTEAD OF
      4. DDL Triggers
      5. Disabling Triggers
      6. Recursive and Nested Triggers
    4. Common Language Runtime (CLR) Integration
      1. Execution Context
      2. Additional Study
      3. Summary of What You Need to Know
      4. Questions
      5. Answers
  14. Chapter 6 Server Infrastructure Design
    1. Server Resources
      1. Memory
      2. CPU
      3. Disk
      4. NIC
    2. System Monitor
      1. Measuring Counters in System Monitor
    3. Server Design Considerations
      1. Remote Administration
      2. WSUS
      3. Steps to Implement WSUS
    4. Linked Servers
      1. Creating a Linked Server
      2. Configuring Logins for a Linked Server
      3. OPENQUERY
      4. OPENDATASOURCE and OPENROWSET
      5. Additional Study
      6. Summary of What You Need to Know
      7. Questions
      8. Answers
  15. Chapter 7 Optimizing Databases
    1. Indexes
      1. Clustered
      2. Nonclustered
      3. Full-Text Indexes
      4. XML Indexes
    2. Index Design
      1. When and Why to Create an Index
      2. Analyzing Queries
      3. Leaf and Nonleaf Level
      4. Fill Factor
      5. Calculating Size
      6. tempdb
      7. Creating an Indexed View
    3. Statistics
      1. Statistics in SQL Server
      2. Viewing and Updating Statistics
    4. Full-Text Index
      1. Full-Text Catalogs and Full-Text Indexes
    5. Database Engine Tuning Advisor (DTA)
      1. Setup and Configuration
    6. Index Maintenance
      1. Dynamic Management Views and Functions
      2. Fragmentation
      3. System Monitor Counters
      4. DBCC
    7. Filegroups
      1. Filegroup Possibilities
      2. Additional Study
      3. Summary of What You Need to Know
      4. Questions
      5. Answers
  16. Chapter 8 Database Security
    1. Security Principals
      1. Database Users
      2. Database Roles
      3. Application Roles
      4. Schemas
    2. Database Securables
      1. Permissions
    3. Encryption
      1. Keys and Algorithms
      2. Encrypting Data
      3. Additional Study
      4. Summary of What You Need to Know
      5. Questions
      6. Answers
  17. Chapter 9 Server Security
    1. Server Security Basics
      1. Authentication Modes
      2. sa Account
      3. Creating Logins
      4. Server Roles
    2. Working in a Domain
      1. Groups
      2. SQL Server in a Domain
      3. Active Directory and Group Policy
    3. Network Security
      1. Communicating Securely
    4. Security Strategy
      1. Services
      2. Auditing
      3. Cost-Benefit Analysis
      4. Physical Security
      5. Antivirus Software
      6. Additional Study
      7. Summary of What You Need to Know
      8. Questions
      9. Answers
  18. Chapter 10 High-Availability Strategies
    1. Log Shipping
      1. Benefits and Requirements
      2. Procedures for Changing Roles
    2. Database Snapshots
      1. Purpose and Benefits
      2. Creating and Using a Database Snapshot
      3. Managing Snapshots
      4. Recovering Data from a Snapshot
    3. Database Mirroring
      1. Requirements
      2. Benefits
      3. Witness Server
      4. Implement Database Mirroring
    4. Replication
      1. Publisher Metaphor
      2. Replication Methods
      3. Replication Monitor
    5. Clustering
      1. How Many Nodes?
      2. Cost vs. Benefits
    6. High-Availability Comparisons
      1. Additional Study
      2. Summary of What You Need to Know
      3. Questions
      4. Answers
  19. Chapter 11 Data Recovery Strategies
    1. Detach and Attach
    2. Transaction Log
      1. Checkpoints
      2. Restoring from the Transaction Log
    3. Recovery Models
      1. Full Recovery Model
      2. Bulk-Logged Recovery Model
      3. Simple Recovery Model
      4. Recovery Model Summary
      5. Setting the Recovery Model
    4. Database Backups
      1. Backup Types
    5. Backup Devices
      1. Backup and Restore Arguments
      2. Before the Backup
    6. Restores
      1. Restore's First Step
      2. Restoring with NORECOVERY and RECOVERY
      3. Restore Strategies
      4. Restoring to a Point in Time (STOPAT)
      5. Verifying Backups
      6. Restoring Files and Filegroups
      7. Minimizing Restores
    7. Protecting System Databases and Scripts
      1. Rebuilding the Master
      2. Protecting Scripts
      3. Additional Study
      4. Summary of What You Need to Know
      5. Questions
      6. Answers
  20. Chapter 12 Data Access
    1. Service Broker
      1. Service Broker Object Types
      2. Service Broker Applications
      3. Event Notifications
      4. Implementing Event Notifications
    2. Web Services and HTTP Endpoints
      1. HTTP Endpoints
    3. Bulk Imports and Exports
      1. The bcp Utility
      2. BULK INSERT Statement
      3. OPENROWSET and OPENDATASOURCE
      4. Import/Export Wizard
      5. Additional Study
      6. Summary of What You Need to Know
      7. Questions
      8. Answers
  21. Chapter 13 Database Administrator Tools
    1. SQL Server Agent
      1. SQL Server Agent Service
      2. SQL Server Agent Properties
      3. Creating Operators, Alerts, and Jobs
      4. SQL Server Agent Mail
    2. Maintenance Plans
    3. SQL Server Profiler
      1. Templates
      2. SQL Trace
    4. Dynamic Management Views and Functions
      1. Dashboard Reports
      2. Additional Study
      3. Summary of What You Need to Know
      4. Questions
      5. Answers
  22. Chapter 14 Business Intelligence
    1. Business Intelligence (BI) Basics
      1. OLAP
      2. ETL
    2. SQL Server Integration Services (SSIS)
      1. Requirements
      2. Transformations
      3. Business Intelligence Development Studio (BIDS)
      4. Packages
      5. Deploying Packages
      6. Monitoring SSIS
    3. SQL Server Reporting Services (SSRS)
      1. Reporting Services Database
      2. Requirements
      3. Creating Reports
      4. Additional Study
      5. Summary of What You Need to Know
      6. Questions
      7. Answers
  23. Chapter 15 Managing Concurrency
    1. Locks and Deadlocks
      1. What Are Locks?
      2. What Are Deadlocks?
      3. Preventing Locks and Deadlocks
      4. Troubleshooting Locks and Deadlocks
      5. Profiler and System Monitor
    2. Transaction Isolation Levels
      1. Concurrency Effects
      2. Picking a Transaction Isolation Level
      3. Additional Study
      4. Summary of What You Need to Know
      5. Questions
      6. Answers
  24. Appendix A About the Download
    1. System Requirements
    2. LearnKey Online Training
    3. Installing and Running MasterExam and MasterSim
      1. About MasterExam
      2. About MasterSim
    4. CertCam
    5. Scripts
    6. Electronic Book
    7. Help
    8. Removing Installation(s)
    9. Technical Support
    10. LearnKey Technical Support
  25. Appendix B Exam 70-431: What You Need to Know to Pass
    1. Introduction
      1. Pick a Date
    2. What You Can Expect
    3. What's Expected of You
      1. Chapters Covering 70-431 Topics
  26. Appendix C Inside the Design Test
    1. The Design Test
      1. Design Test Success Tips
      2. Scenario Topics
      3. Sample Scenario Questions
      4. Sample Scenario: MCITPSuccess Corporation
      5. Sample Scenario Answers
  27. Appendix D Exam 70-443: What You Need to Know to Pass
    1. Introduction
    2. What You Can Expect
    3. What's Expected of You
      1. Chapters Covering 70-443 Topics
  28. Appendix E Exam 70-444: What You Need to Know to Pass
    1. Introduction
    2. What You Can Expect
    3. What's Expected of You
      1. Chapters Covering 70-444 Topics
  29. Appendix F SQL Database Design Object Summary
  30. Glossary
  31. Index