Microsoft Learning:

Exam 70-444:

PRO: Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005

Published:February 10, 2006
Language(s):English, French, German, Japanese, Chinese (Simplified)
Audience(s):IT Professionals
Technology:Microsoft SQL Server 2005
Type:Proctored Exam
Preparing for an ExamThis exam will be retired on June 30th, 2011.
• See a complete list of exams scheduled for retirement.
• Read frequently asked questions about exam retirements.
Audience Profile
Candidates for this exam are professional database administrators who optimize and maintain database solutions. They have three or more years dedicated to database work, which may include two or more of the following phases in the product lifecycle: design, development, deployment, optimization, maintenance, or support. The typical work environment is an enterprise or a medium-sized organization. Candidates should be experienced in using Microsoft SQL Server 2005.
Candidates for this exam should be an expert in the following areas:
  • Defining high-availability solutions
  • Automating administrative tasks
  • Defining security solutions
  • Monitoring and troubleshooting the database server
  • Designing and executing deployments
  • Defining the infrastructure (such as storage, hardware, and number of servers or instances)
Credit Toward CertificationExam 70-444: PRO: Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005: counts as credit toward the following certification(s):
Note This preparation guide is subject to change at any time without prior notice and at the sole discretion of Microsoft. Microsoft exams might include adaptive testing technology and simulation items. Microsoft does not identify the format in which exams are presented. Please use this preparation guide to prepare for the exam, regardless of its format.
Skills Being MeasuredThis exam measures your ability to accomplish the technical tasks listed below.The percentages indicate the relative weight of each major topic area on the exam.The higher the percentage, the more questions you are likely to see on that content area on the exam.

The information after “This objective may include but is not limited to” is intended to further define or scope the objective by describing the types of skills and topics that may be tested for the objective. However, it is not an exhaustive list of skills and topics that could be included on the exam for a given skill area. You may be tested on other skills and topics related to the objective that are not explicitly listed here.
Optimizing the Performance of Database Servers and Databases (25%)
  • Troubleshoot physical server performance.
  • Troubleshoot instance performance.
  • Troubleshoot database performance.
  • Troubleshoot and maintain query performance.
    • Identify poorly performing queries.
    • Analyze a query plan to detect inefficiencies in query logic.
    • Maintain and optimize indexes.
    • Enforce appropriate stored procedure logging and output.
  • Troubleshoot concurrency issues.
Optimizing and Implementing a Data Recovery Plan for a Database (19%)
  • Diagnose causes of failures. Failure types include database failures, physical server failures, and SQL Server service failures.
  • Plan for fault-tolerance.
  • Recover from a failure of SQL Server 2005.
  • Recover from a database disaster.
    • Plan a strategy.
    • Restore a database.
    • Configure logins.
    • Recover lost data.
    • Maintain server and database scripts for recoverability.
  • Salvage good data from a damaged database by using restoration techniques.
Designing a Strategy to Monitor and Maintain a Database Solution (18%)
  • Define and implement monitoring standards for a physical server.
    • Establish the thresholds for performance.
    • Establish the baselines for performance.
    • Define which types of information to monitor on the physical server.
    • Define traces.
    • Set alerts.
    • Set notifications.
  • Choose the appropriate information to monitor.
  • Create and implement a maintenance strategy for database servers.
    • Create a job dependency diagram.
    • Manage the maintenance of database servers.
  • Design a database maintenance plan.
  • Design a strategy to manage Reporting Services.
Designing a Database Data Management Strategy (25%)
  • Design and manage SQL Server Integration Services (SSIS) packages.
    • Construct complex SSIS packages.
    • Design security for accessing packages.
    • Restart failed packages.
    • Troubleshoot or debug packages.
    • Deploy and move packages.
    • Schedule package execution.
    • Move packages to different servers.
  • Enforce data quality according to business requirements.
    • Establish the business requirements for quality.
    • Create queries to inspect the data.
    • Use checksum.
    • Clean the data.
  • Design data integrity.
    • Reconcile data conflicts.
    • Make implicit constraints explicit.
    • Assign data types to control characteristics of data stored in a column.
  • Design a strategy to manage data across linked servers.
    • Set up and manage linked servers.
  • Design a strategy to manage replication.
    • Design alerts.
    • Design a maintenance plan to monitor health, latency, and failures.
    • Verify replication.
    • Design a plan to resolve replication conflicts.
    • Design a plan to modify agent profiles.
    • Tune replication configuration.
  • Optimize a database control strategy to meet business requirements.
    • Verify that database change control procedures are being followed.
    • Identify all database objects related to a particular deployment.
Designing a Strategy to Manage and Maintain Database Security (13%)
  • Perform a security audit of the existing security infrastructure based on the security plan.
    • Analyze the physical server security.
    • Compare the existing security infrastructure to business and regulatory requirements.
    • Identify variations from the security design.
  • Maintain a server-level security strategy.
    • Design a strategy to audit Windows account permissions.
    • Design a strategy to audit SQL Server service access.
    • Maintain a strategy to assign the appropriate minimum level of privileges.
    • Maintain an encryption strategy that meets business requirements.
    • Design a strategy to apply service packs and security updates.
    • Configure the surface area.
  • Maintain a user-level security strategy.
    • Verify the existence and enforcement of account policies.
    • Verify SQL Server login authentication.
    • Verify permissions on SQL Server roles and accounts.
  • Prepare for respond to threats and attacks.
    • Prepare for and respond to denial-of-service attacks that are specific to SQL Server.
    • Prepare for and respond to virus and worm attacks that are specific to SQL Server.
    • Prepare for and respond to internal attacks that are specific to SQL Server.
    • Prepare for and respond to SQL Server injection attacks.
Preparation Tools and ResourcesTo help you prepare for this exam, Microsoft Learning recommends that you have hands-on experience with the product and that you use the following training resources. These training resources do not necessarily cover all of the topics listed in the "Skills Measured" tab.
Learning Plans and Classroom Training
Microsoft E-Learning
Microsoft Press Books There are no Microsoft Press books currently available.
Practice Tests
Microsoft Online Resources
  • Learning Plan: Get started with a step-by-step study guide that is based on recommended resources for this exam.
  • Microsoft Learning Community: Join newsgroups and visit community forums to connect with peers for suggestions on training resources and advice on your certification path and studies.
  • Microsoft SQL Server 2005 – Learning Portal: Find special offers and information on training and certification.
  • Product information: Visit the SQL Server 2005 Web site for detailed product information.
  • Microsoft Learning Community: Join newsgroups and visit community forums to connect with peers for suggestions on training resources and advice on your certification path and studies.
  • TechNet: Designed for IT professionals, this site features how-to instructions, best practices, downloads, technical resources, newsgroups, and chats.
  • MSDN: Designed for developers, the Microsoft Developer Network (MSDN) features code samples, technical articles, downloads, newsgroups, and chats.
Have Questions? For advice about training and certification, connect with peers: For questions about a specific certification, chat with a Microsoft Certified Professional (MCP): To find out about recommended blogs, Web sites, and upcoming Live Meetings on popular topics, visit our community site:
Exam index

How To Get It

Schedule your exam through the following exam provider: