Microsoft test 70-431 focuses on your ability to maintain and implement SQL Server 2005 computers. To successfully pass the test 70-431, you need to practice the following:

  1. Install and uninstall SQL Server 2005.
  2. Upgrade earlier versions of SQL Server.
  3. Configure SQL Server 2005 security.
  4. Create a trigger, identify recursive triggers, nested triggers, and transaction triggers.
  5. Create a function and identify deterministic and nondeterministic functions.
  6. Create and recompile stored procedures, and assign permissions to a role for a stored procedure.
  7. Create indexes.
  8. Configure linked servers by using SQL Server Management Studio.
  9. Implement and configure log shippings.
  10. Implement and prepare databases for database mirroring.
  11. Execute ad hoc queries for retrieving data from a database.
  12. Execute Data Manipulation Language statements such as Insert, Update, and Delete.
  13. Manage and create database snapshots.
  14. Retrieve and modify XML data and create XML indexes.
  15. Implement Service Broker and create message types, queues, contracts, and services.
  16. Monitor and resolve blocks and deadlocks and identify the cause of a block.

  17. Set up and configure SQL Server 2005 for replication.
  18. Configure alerts and operators by using SQL Server Agent.
  19. Manage index fragmentation and file shrinking, and perform database integrity by using the DBCC CHECKDB statement.
  20. Gather performance and optimization data by using Database Engine Tuning Advisor.
  21. Perform full backups, differential backups, and transaction log backups.
  22. Create full-text search indexes.
  23. Create views and indexed views, and assign permissions to a role for views.
  24. Restore a database.
  25. Monitor SQL Server Agent job history and identify the cause of a failure.
  26. Create user-defined types.
  27. Create constraints.
  28. Create tables, define columns, specify filegroups, assign permissions to a role for tables, and specify partition schemes.
  29. Gather performance and optimization data using SQL Server Profiler by starting a new trace and saving trace logs.
  30. Import and export data using the Bulk Insert task, the bulk copy program, and SQL Server Integration Services (SSIS).