Firepower's list of 70-447 prep materials
A free guide to exam prep resources
70-447: UPGRADE: MCDBA Skills to MCITP Database Administrator by Using Microsoft SQL Server 2005

Microsoft's MCSE FAQ covers several of the general questions as well. I hope you find this guide helpful in your test preparation, as well as my other lists.



Good luck & good studying to all.



  • Windows 2003 MCSE information
  • 70-447 exam objectives.
  • MOC course 2210 is supposed to cover the material. It's supposed to be available to CTEC's June 19, 2003, but check your local CTEC to see when they'll start offering it.

    The software to play with

    The Windows Server 2003 Trial Edition is available from Microsoft for free. It has a 180-day license - long enough to practice & take at least a couple tests. Until July 31st shipping of the CD is free as well, for those who want the CD or don't want to burn all that bandwidth downloading it.

    General info on Windows Server 2003


    You will see below several links to SQL Server Central. The site requires registration. Usually I don't like sites that do, but SQL Server Central has so many good articles that I both registered and encourage every SQL Server admin, developer or wannabe to register.

    Online resources by exam objective

    Skills measured by Exam 70-447

  • Designing a Database Server Infrastructure
    • Analyze CPU requirements.
    • Analyze the current configuration.
    • Analyze memory requirements.
    • Forecast and incorporate anticipated growth requirements into the capacity requirements.
  • Specify software versions and hardware configurations.
    • Choose a version and edition of the operating system.
    • Choose a version of SQL Server 2005.
    • Choose a CPU type.
    • Choose memory options.
    • Choose a type of storage.
  • Design physical storage.
    • Design transaction log storage.
    • Design backup file storage.
    • Decide where to install the operating system.
    • Decide where to place SQL Server service executables.
    • Specify the number and placement of files to create for each database.
  • Design instances.
    • Decide how many databases to create.
    • Decide on the placement of system databases for each instance.
    • Decide on the physical storage for the tempdb database for each instance.
    • Decide on the number of instances.
    • Decide on the naming of instances.
    • Decide how many physical servers are needed for instances.
    • Establish service requirements.
    • Specify instance configurations.
  • Design a database consolidation strategy.
    • Gather information to analyze the dispersed environment.
    • Identify potential consolidation problems.
    • Create a specification to consolidate SQL Server databases.
    • Design a database migration plan for the consolidated environment.
    • Test existing applications against the consolidated environment.
  • Designing Security for a Database Server Solution
    • Analyze business requirements.
    • Gather business and regulatory requirements.
    • Decide how requirements will impact choices at various security levels.
    • Evaluate costs and benefits of security choices.
    • Decide on appropriate security recommendations.
    • Inform business decision-makers about security recommendations and their impact.
    • Incorporate feedback from business decision-makers into a design.
  • Integrate database security with enterprise-level authentication systems.
    • Decide which authentication system to use.
    • Design Active Directory organizational units (OUs) to implement server-level security policies.
    • Ascertain the impact of authentication on a high-availability solution.
    • Establish the consumption of enterprise authentication.
    • Ascertain the impact of enterprise authentication on service up-time requirements.
  • Develop Microsoft Windows server-level security policies.
    • Develop a password policy.
    • Develop an encryption policy.
    • Specify server accounts and server account rights.
    • Specify the interaction of the database server with antivirus software.
    • Specify the set of running services and disable unused services.
    • Specify the interaction of the database server with server-level firewalls.
    • Specify a physically secure environment for the database server.
  • Modify the security design based on the impact of network security policies.
    • Analyze the risk of attacks to the server environment and specify mitigations.
  • Design SQL Server service-level security.
    • Specify logins.
    • Select SQL Server server roles for logins.
    • Specify a SQL Server service authentication mode.
    • Design a secure HTTP endpoint strategy.
    • Design a secure job role strategy for the SQL Server Agent Service.
    • Specify a policy for .NET assemblies.
  • Design database-level security.
    • Specify database users.
    • Design schema containers for database objects.
    • Specify database roles.
    • Define encryption policies.
    • Design DDL triggers.
      • John Papa explains the point of DDL Triggers as well as several other enhancments. "DDL triggers are useful when tracking or securing the creation and modification of database objects or changes to the database server."
      • Jeffrey Juday's Using DDL Triggers to Manage SQL Server 2005 is a good intro to the subject. From that:
        • DDL Triggers are at either DB level or Server level (not object level like DML)
        • DDL triggers do not support INSTEAD OF
        • lack the Inserted and Deleted tables
        • Understand scope (DB vs Server)
  • Design object-level security.
    • Design a permissions strategy.
    • Analyze existing permissions.
    • Design an execution context.
    • Design column-level encryption.
    • Design security for CLR objects in the database.
    • Designing a Physical Database
    • Modify an existing database design based on performance and business requirements.
    • Ensure that a database is normalized.
    • Allow selected denormalization for performance purposes.
    • Ensure that the database is documented and diagrammed.
  • Design tables.
    • Decide if partitioning is appropriate.
    • Specify primary and foreign keys.
    • Specify column data types and constraints.
    • Decide whether to persist computed columns.
    • Specify physical location of tables, including file groups and a partitioning scheme.
  • Design file groups.
    • Design file groups for performance.
    • Design file groups for recoverability.
    • Design file groups for partitioning.
  • Design index usage.
    • Design indexes for faster data access.
    • Design indexes to improve data modification.
    • Specify physical placement of indexes.
  • Create database change control procedures.
    • Establish where to store database source code.
    • Isolate development and test environments from the production environment.
    • Define procedures for moving from development to test.
    • Define procedures for promoting from test to production.
    • Define procedures for rolling back a deployment.
    • Document the database change control procedures.
    • Designing a Database Solution for High Availability
    • Select high-availability technologies based on business requirements.
    • Analyze availability requirements.
    • Analyze potential availability barriers.
    • Analyze environmental issues.
    • Analyze potential problems related to processes and staff.
    • Identify potential single points of failure.
    • Decide how quickly the database solution must fail over.
    • Choose automatic or manual failback.
    • Analyze costs versus benefits of various solutions.
    • Combine high-availability technologies to improve availability.
  • Develop a strategy for migration to a highly available environment.
    • Analyze the current environment.
    • Ascertain migration options.
    • Choose a migration option.

  • Design a highly available database storage solution.
    • Design the RAID solutions for your environment.
    • Design a SAN solution.
  • Design a database-clustering solution.
    • Design a Microsoft Cluster Service (MSCS) implementation.
    • Design the cluster configuration of the SQL Server service.

  • Design database mirroring.
    • Design server roles for database mirroring.
    • Design the initialization of database mirroring.
    • Design a test strategy for planned and unplanned role changes.

  • Design a high-availability solution that is based on replication.
    • Specify an appropriate replication solution.
    • Choose servers for peer-to-peer replication.
    • Establish a strategy for resolving data conflicts.
    • Design an application failover strategy.
    • Design a strategy to reconnect client applications.

  • Design log shipping.
    • Specify the primary server and secondary server.
    • Switch server roles.
    • Design an application failover strategy.
    • Design a strategy to reconnect client applications.

    • Designing a Data Recovery Solution for a Database
    • Specify data recovery technologies based on business requirements.
    • Analyze how much data the organization can afford to lose.
    • Analyze alternative techniques to save redundant copies of critical business data.
    • Analyze how long the database system or database can be unavailable.
  • Design backup strategies.
    • Specify the number and location of devices to be used for backup.
    • Specify what data to back up.
    • Specify the frequency of backup.
    • Choose a backup technique.
    • Specify the type of backup.
    • Choose a recovery model.

  • Create a disaster recovery plan.
    • Document the sequence of possible events.
    • Create a disaster decision tree that includes restore strategies.
    • Establish recovery success criteria.
    • Validate restore strategies.

    • Designing a Strategy for Data Archiving
      • Select archiving techniques based on business requirements.
      • Gather requirements that affect archiving.
      • Ascertain data movement requirements for archiving.
      • Design the format of archival data.
      • Plan for data archival and access.
      • Specify the destination for archival data.
      • Specify the frequency of archiving.
      • Decide if replication is appropriate.
      • Establish how to access archived data.

      • Design the topology of replication for archiving data.
      • Specify the publications and articles to be published.
      • Specify the distributor of the publication.
      • Specify the subscriber of the publication.
      • Design the type of replication for archiving data.
      • Section 2
    • 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.






        If you know of more resources which should be listed here, please email a link to me. I very much appreciate other resources to study and will be sure to acknowledge you on this page.



    • Williamston Consulting is now ...

      Black Locust Software

      Technology Solutions for Business ... Large or Small
      .



      Home

      Auto Dealers' Form Software

      Software Development

      Website Development

      Website Advice

      IT Guys' Stuff:

      Certification
      Study Guides

      Programming
      Tips