Microsoft Certified IT Professional/Exam 70-444

Optimizing the Performance of Database Servers and Databases  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  Diagnose causes of failures. Failure types include database failures, physical server failures, and SQL Server service failures. Plan for fault-tolerance. <li>Recover from a failure of SQL Server 2005. <li>Recover from a database disaster. <ul><li>Plan a strategy. <li>Restore a database. <li>Configure logins. <li>Recover lost data. <li>Maintain server and database scripts for recoverability. <li>Salvage good data from a damaged database by using restoration techniques. </ul> </ul>

Designing a Strategy to Monitor and Maintain a Database Solution <ul> <li>Define and implement monitoring standards for a physical server. <ul> <li>Establish the thresholds for performance. <li>Establish the baselines for performance. <li>Define which types of information to monitor on the physical server. <li>Define traces. <li>Set alerts. <li>Set notifications. </ul> <li>Choose the appropriate information to monitor. <li>Create and implement a maintenance strategy for database servers. <ul> <li>Create a job dependency diagram. <li>Manage the maintenance of database servers. </ul> <li>Design a database maintenance plan. <li>Design a strategy to manage Reporting Services. </ul>

Designing a Database Data Management Strategy <ul> <li>Design and manage SQL Server Integration Services (SSIS) packages. <ul> <li>Construct complex SSIS packages. <li>Design security for accessing packages. <li>Restart failed packages. <li>Troubleshoot or debug packages. <li>Deploy and move packages. <li>Schedule package execution. <li>Move packages to different servers. </ul> <li>Enforce data quality according to business requirements. <ul> <li>Establish the business requirements for quality. <li>Create queries to inspect the data. <li>Use checksum. <li>Clean the data. </ul> <li>Design data integrity. <ul> <li>Reconcile data conflicts. <li>Make implicit constraints explicit. <li>Assign data types to control characteristics of data stored in a column. </ul> <li>Design a strategy to manage data across linked servers. <ul> <li>Set up and manage linked servers. </ul> <li>Design a strategy to manage replication. <ul> <li>Design alerts. <li>Design a maintenance plan to monitor health, latency, and failures. <li>Verify replication. <li>Design a plan to resolve replication conflicts. <li>Design a plan to modify agent profiles. <li>Tune replication configuration. </ul> <li>Optimize a database control strategy to meet business requirements. <ul> <li>Verify that database change control procedures are being followed. <li>Identify all database objects related to a particular deployment. </ul> </ul>

Designing a Strategy to Manage and Maintain Database Security <ul> <li>Perform a security audit of the existing security infrastructure based on the security plan. <ul> <li>Analyze the physical server security. <li>Compare the existing security infrastructure to business and regulatory requirements. <li>Identify variations from the security design. </ul> <li>Maintain a server-level security strategy. <ul> <li>Design a strategy to audit Windows account permissions. <li>Design a strategy to audit SQL Server service access. <li>Maintain a strategy to assign the appropriate minimum level of privileges. <li>Maintain an encryption strategy that meets business requirements. <li>Design a strategy to apply service packs and security updates. <li>Configure the surface area. </ul> <li>Maintain a user-level security strategy. <ul> <li>Verify the existence and enforcement of account policies. <li>Verify SQL Server login authentication. <li>Verify permissions on SQL Server roles and accounts. </ul> <li>Prepare for respond to threats and attacks. <ul> <li>Prepare for and respond to SQL Server injection attacks. <li>Prepare for and respond to denial-of-service attacks that are specific to SQL Server. <li>Prepare for and respond to virus and worm attacks that are specific to SQL Server. <li>Prepare for and respond to internal attacks that are specific to SQL Server. </ul> </ul>

References <ul> <li>Preparation Guide for Exam 70-444 </ul>