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. 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.