Microsoft Certified IT Professional/Exam 70-444

From Wikibooks, open books for an open world
Jump to navigation Jump to search

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

  • 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

  • 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

  • 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 SQL Server injection 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.

References