Exam 70-431: TS: Microsoft SQL Server 2005: Implementation and Maintenance Curriculum Outline
SQL Server 2005 High Availability and Disaster Recovery
Overview/DescriptionTo implement database mirroring, log shipping, database snapshots, and data replication in SQL Server 2005
Target AudienceProfessionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
PrerequisitesThis is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration3 Hours
SQL Server 2005 High Availability and Disaster Recovery- Recognize the features and functionality of database mirroring
- Configure database mirroring in a given scenario
- Configure database mirroring in SQL Server 2005
- Configure and monitor log shipping in a given scenario
- Configure log shipping options in SQL Server 2005
- Configure database snapshots in a given scenario
- Configure data replication in a given scenario
- Manage replication in SQL Server 2005, given a scenario
- Set up data replication in SQL Server 2005
Back to ListSQL Server 2005 Backup and Restores
Overview/DescriptionTo back up and restore a database in SQL Server 2005
Target AudienceProfessionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
PrerequisitesThis is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration1.75 Hours
SQL Server 2005 Backup and Restores - Determine the appropriate recovery model and backup strategy for a given scenario
- Perform database backups in a given scenario
- Back up a SQL Server database
- Restore a SQL Server backup in a given scenario
- Move a SQL Server database in a given scenario
- Perform a database restore in SQL Server 2005
Back to ListInstalling SQL Server 2005
Overview/DescriptionTo install SQL Server 2005 and upgrade to SQL Server 2005 from previous versions
Target AudienceProfessionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
PrerequisitesThis is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration3 Hours
Installing SQL Server 2005- Recognize how SQL Server 2005 can help improve enterprise data management, developer productivity, and business intelligence
- Differentiate between the components and services provided by SQL Server 2005
- Distinguish between SQL Server 2005 management tools
- Identify the requirements and considerations for installing SQL Server 2005
- Configure a SQL Server 2005 installation in a given scenario
- Install SQL Server 2005
- Select the appropriate SQL Server 2005 upgrade path for a given scenario
- Perform the steps to upgrade to SQL Server 2005
- Upgrade to SQL Server 2005
Back to ListCreating and Implementing Database Objects in SQL Server 2005
Overview/DescriptionTo create and implement key database objects in SQL Server 2005
Target AudienceProfessionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
PrerequisitesThis is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration3.25 Hours
Creating and Implementing Database Objects in SQL Server 2005 - Create a database table in SQL Server 2005, given a scenario
- Create a database view in a given scenario
- Create a database table and a database view
- Identify recommended guidelines for designing indexes
- Create a database index in a given scenario
- Create an online index in SQL Server 2005
- Create functions and triggers in a given scenario
- Create stored procedures in a given scenario
- Manage stored procedures in a given scenario
- Create and implement a stored procedure
Back to ListSQL Server 2005 Database Performance and Optimization
Overview/DescriptionTo monitor and optimize database performance using SQL Server utilities
Target AudienceProfessionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
PrerequisitesThis is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration2.50 Hours
SQL Server 2005 Database Performance and Optimization- Configure SQL Server Profiler Trace files in a given scenario
- Configure System Monitor to monitor SQL Server performance in a given scenario
- Use SQL Server Profiler and System Monitor to monitor an instance of SQL Server 2005
- Recognize the features and functionality of Database Engine Tuning Advisor
- Use Database Engine Tuning Advisor to tune a SQL Server database in a given scenario
- Tune a SQL Server database using Database Engine Tuning Advisor
- Retrieve SQL Server state information using dynamic management views (DMVs) in a given scenario
- Query dynamic management views and functions
Back to ListConfiguring SQL Server 2005
Overview/DescriptionTo configure a SQL Server instance and create a database, configure logins, users, and schemas for a database, and set up a linked server configuration
Target AudienceProfessionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
PrerequisitesThis is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration3 Hours
Configuring SQL Server 2005- Use SQL Server Surface Area Configuration to configure a SQL Server 2005 installation
- Create a SQL Server database in a given scenario
- Create a database in SQL Server 2005
- Recognize the security functionality of the SQL Server 2005 Database Engine
- Create logins and grant permissions in a given scenario
- Create logins, users, and schemas in SQL Server 2005
- Recognize the security mechanisms provided by encryption, the TRUSTWORTHY database property, and execution context in SQL Server 2005
- Recognize how SQL Server 2005 can access data from OLE DB data sources on remote servers using a linked server configuration
- Use SQL Server Management Studio to configure a linked server in a given scenario
- Configure a linked server using SQL Server Management Studio
Back to ListRetrieving Data and Running Queries in SQL Server 2005
Overview/DescriptionTo perform relational data and XML data management tasks, and work with assemblies and transactions in SQL Server 2005
Target AudienceProfessionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
PrerequisitesThis is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration3 Hours
Retrieving Data and Running Queries in SQL Server 2005- Retrieve data from a database in a given scenario
- Modify data from a relational database in a given scenario
- Use queries to retrieve and modify data in a SQL Server database
- Retrieve, insert, and convert XML data in SQL Server 2005
- Create and secure an HTTP endpoint in a given scenario
- Work with XML data in SQL Server 2005
- Create and modify an assembly in a given scenario
- Control SQL Server transactions in a given scenario
- Manage transactions in SQL Server 2005
Back to ListSQL Server 2005 Messaging and Querying Tools
Overview/DescriptionTo implement Service Broker components and use full-text search in SQL Server 2005
Target AudienceProfessionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
PrerequisitesThis is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration2 Hours
SQL Server 2005 Messaging and Querying Tools - Create Service Broker components in a given scenario
- Create a Service Broker service
- Recognize the routing, networking, and security features provided by Security Broker
- Identify the features of Full-Text Searches and recognize considerations for implementing them
- Create and troubleshoot full-text queries in a given scenario
- Use full-text search to query SQL Server 2005
Back to ListManaging and Maintaining SQL Server 2005 Databases
Overview/DescriptionTo manage and maintain SQL Server 2005 databases
Target AudienceProfessionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
PrerequisitesThis is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration3.25 Hours
Managing and Maintaining SQL Server 2005 Databases- Export bulk data in SQL Server 2005 in a given scenario
- Import bulk data into SQL Server 2005 in a given scenario
- Import and export bulk data in SQL Server 2005
- Create and manage jobs using SQL Server Agent
- Configure alerts using SQL Server Agent in a given scenario
- Configure operators using SQL Server Agent in a given scenario
- Perform management and monitoring tasks using the SQL Server Agent service
- Perform database management tasks in SQL Server 2005, given a scenario
- Perform the steps required to shrink a database in a given scenario
- Check and maintain database integrity in SQL Server 2005
Back to ListMonitoring and Diagnosing Database Problems in SQL Server 2005
Overview/DescriptionTo monitor and resolve database blocks, and connect to the server using a dedicated administrator connection (DAC)
Target AudienceProfessionals typically pursuing a career as a Database Administrator, Database Developer or Business Intelligence Developer; individuals who do not work with SQL Server as a part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others
PrerequisitesThis is a high-level technical path and candidates for the exam, and hence learners should be expert in the following areas: Tools usage UI navigation Wizards usage Writing code in the appropriate language (Transact-SQL, CLR Language, other scripting languages, etc.) Code debugging or syntactic issue resolution Troubleshooting Accomplishing specific focused tasks through the use of code or UI navigation
Expected Duration1.25 Hours
Monitoring and Diagnosing Database Problems in SQL Server 2005- Recognize how locking is used to secure data integrity in a SQL Server database
- Detect and end deadlocks and blocking connections in a given scenario
- Detect and terminate a transaction lock
- Connect to the server using the dedicated administrator connection (DAC) in a given scenario
- Connect to a non-responsive database using the dedicated administrator connection (DAC)
Back to List
The unique design of CBT Direct’s Microsoft MCTS certification course incorporates a proven four-step learning process:
presentation, demonstration, guidance and independent practice. This four-step proven learning model for CBT Direct’s
Microsoft MCTS training course ensures the greatest level of retention to prepare you for your Microsoft MCTS certification exam.
CBT Direct also offers online mentoring for over 100 current major certification exams, including Microsoft MCTS, for IT
professionals and end-users alike. CBT Direct’s mentors have a minimum of 20 certifications each and are available 24/7*.
* Available for most courses.
This training would be beneficial for individuals looking for IT job positions such as; SQL Database Administrator or a
Database Manager.