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 Administrator: Microsoft® SQL Server™ 2005 Database Server Infrastructure Design Study Guide

Book Description

  • This comprehensive book guides readers through Microsoft's brand-new SQL 2005 administrator exam: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005 (Exam 70443)
  • Addresses the database tasks that the MCITP exam focuses on such as design, development, deployment, optimization, maintenance, and support
  • Reviews key topics, including defining high availability solutions, automating administrative tasks, defining security solutions, monitoring and troubleshooting the database server, and much more
  • The CD-ROM features leading-edge exam prep software with test engine plus hundreds of practice questions and electronic flashcards

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Table of Contents

  1. Cover Image
  2. Title page
  3. Copyright
  4. To Our Valued Readers:
  5. Dedication
  6. Acknowledgements
  7. About the Authors
  8. Contents at a Glance
  9. Contents
  10. Table of Exercises
  11. Introduction
    1. Introducing the Microsoft Certified IT Professional
    2. How to Become Certified as a MCITP: Database Administrator
    3. Registering for the Exam
    4. Taking the Designing a Database Server Infrastructure by Using SQL Server 2005 Exam
    5. Types of Exam Questions
    6. Tips for Taking the Exam
    7. What's in the Book?
    8. What's on the Book's CD?
    9. How Do You Use This Book?
    10. Hardware and Software Requirements
  12. Assessment Test
  13. Chapter 1: Designing the Hardware and Software Infrastructure
    1. Analyzing the Current Configuration
    2. Designing for Capacity Requirements
    3. Specifying Software Versions and Hardware Configurations
    4. Summary
    5. Exam Essentials
    6. Review Questions
    7. Answers to Review Questions
  14. Chapter 2: Designing Physical Storage
    1. Understanding SQL Server Storage Concepts
    2. Estimating Database Size
    3. Designing Transaction Log Storage
    4. Designing Backup-File Storage
    5. Deciding Where to Install the Operating System
    6. Deciding Where to Place SQL Server Service Executables
    7. Specifying the Number and Placement of Files for Each Database
    8. Designing Instances
    9. Deciding on the Number of Instances
    10. Deciding How to Name Instances
    11. Deciding How Many Physical Servers You Need for Instances and How Many Databases to Create
    12. Deciding Where to Place System Databases for Each Instance
    13. Deciding on the Physical Storage for the tempdb Database for Each Instance
    14. Establishing Service Requirements
    15. Specifying Instance Configurations
    16. Summary
    17. Exam Essentials
    18. Case Study: Mullen Enterprises
    19. Review Questions
    20. Answers to Review Questions
  15. Chapter 3: Designing a Consolidation Strategy
    1. Phase 1: Envisioning
    2. Phase 2: Planning
    3. Phase 3: Developing
    4. Phase 4: Deploying
    5. Summary
    6. Exam Essentials
    7. Case Study: dkRanch Cabinets
    8. Review Questions
    9. Answers to Review Questions
  16. Chapter 4: Designing Database-Level Security
    1. Gathering Your Security Requirements
    2. Integrating with the Enterprise
    3. Assessing the Impact of Network Policies
    4. Achieving High Availability in a Secure Way
    5. Mitigating Server Attacks
    6. Auditing Access
    7. Making Security Recommendations
    8. Performing Ongoing Reviews
    9. Summary
    10. Exam Essentials
    11. Case Study: Delaney's Simulations
    12. Review Questions
    13. Answers to Review Questions
  17. Chapter 5: Designing Windows Server-Level Security Processes
    1. Understanding Password Rules
    2. Setting Up the Encryption Policy
    3. Introducing SQL Server Service Accounts
    4. Setting Up Antivirus Software
    5. Working with Services
    6. Configuring Server Firewalls
    7. Physically Securing Your Servers
    8. Summary
    9. Exam Essentials
    10. Case Study: The Ever-Growing Wealth Company
    11. Review Questions
    12. Answers to Review Questions
  18. Chapter 6: Designing SQL Server Service-Level Security
    1. Creating Logins
    2. Granting Server Roles
    3. Mapping Database Users to Roles
    4. Securing Schemas
    5. Granting Database Roles
    6. Introducing DDL Triggers
    7. Defining a Database-Level Encryption Policy
    8. Securing Endpoints
    9. Granting SQL Server Agent Job Roles
    10. Designing .NET Assembly Security
    11. Summary
    12. Exam Essentials
    13. Case Study: Herd of Two
    14. Review Questions
    15. Answers to Review Questions
  19. Chapter 7: Designing SQL Server Object-Level Security
    1. Developing a Permissions Strategy
    2. Analyzing Existing Permissions
    3. Specifying the Execution Context
    4. Specifying Column-Level Encryption
    5. Using CLR Security
    6. Summary
    7. Exam Essentials
    8. Case Study: Jack's Steamed Shrimp
    9. Review Questions
    10. Answers to Review Questions
  20. Chapter 8: Designing a Physical Database
    1. Modifying a Database Design Based on Performance and Business Requirements
    2. Designing Filegroups
    3. Designing Index Usage
    4. Designing Views
    5. Summary
    6. Exam Essentials
    7. Case Study: Trevallyn Travel
    8. Review Questions
    9. Answers to Review Questions
  21. Chapter 9: Creating Database Conventions and Standards
    1. Understanding the Benefits of Database Naming Conventions
    2. Establishing and Disseminating Naming Conventions
    3. Defining Database Standards
    4. Summary
    5. Exam Essentials
    6. Review Questions
    7. Answers to Review Questions
  22. Chapter 10: Designing a SQL Server 2005 Solution for High Availability
    1. Examining High-Availability Technologies
    2. Understanding Failover Clustering
    3. Understanding Database Mirroring
    4. Understanding Log Shipping
    5. Understanding Replication
    6. Designing Highly Available Storage
    7. Designing a High-Availability Solution
    8. Developing a Migration Strategy
    9. Summary
    10. Exam Essentials
    11. Case Study: Ed's Heavy Equipment
    12. Review Questions
    13. Answers to Review Questions
  23. Chapter 11: Designing a Data Recovery Solution for a Database
    1. Backing Up Data
    2. Restoring Databases
    3. Devising a Backup Strategy
    4. Designing a Backup and Restore Strategy: The Process
    5. Developing a Database Disaster Recovery Plan
    6. Summary
    7. Exam Essentials
    8. Case Study: Waves Styles on George
    9. Review Questions
    10. Answers to Review Questions
  24. Chapter 12: Designing a Data-Archiving Solution
    1. Why Archive Data?
    2. Determining Business and Regulatory Requirements
    3. Determining What Data Will Be Archived
    4. Selecting a Storage Format
    5. Developing a Data-Movement Strategy
    6. Designing a Replication Topology
    7. Summary
    8. Exam Essentials
    9. Case Study: Developing an Archive Plan
    10. Review Questions
    11. Answers to Review Questions
  25. Glossary
  26. Index
  27. Backcover Page