MCTS: SQL Server 2005 Boot Camp

Those who attain the TS: SQL Server 2005 credentiall can implement and maintain databases using specific instructions and specifications. They have thorough knowledge of the product, understand how to use the tools and Transact-SQL language, and know how to navigate the user interface.

Technology Specialists are typically pursuing careers as a database administrator, database developer, or business intelligence developer. The 70-431 certification exam measures your ability to implement and maintain a SQL Server 2005 database

Prerequisites

Basic knowledge of Microsoft Windows operating system and its core functionality, working knowledge of Transact_SQL, working knowledge of relational databases, some experience with database design

Our MCTS: SQL Server 2005 Certification Program includes:

• Intensive Hands-on Training by Microsoft Certified Trainers utilizing our (Lecture | Lab | Review)™ Delivery.
• Blended-Learning Instruction comprised of comprehensive study materials and pre-class mentoring, customized Microsoft Official Curriculum, comprehensive lab manuals, and Self-Test Testing Software
• Deluxe Package of Five (5) Days of Hotel Accommodations, Breakfast and Lunch, Unlimited Beverages, Snacks, and Freshly-brewed Coffee
• 24-Hour Lab and Instructor Access.
• Examination Vouchers & On-site Certification Testing.
• Examination Passing Policy.

MCTS SQL Server 2005 Schedule for 2010 

2/15/2010 2/19/2010 San Antonio, TX  and Minneapolis, MN 

5/10/2010 5/14/2010 Denver, CO  and  Philadelphia, PA

Curriculum

Day 1 Writing Queries Using Microsoft SQL Server 2005 Transact-SQL   
Day 2 Implementing a Microsoft SQL Server 2005 Database   
Day 3 Maintaining a Microsoft SQL Server 2005 Database   
Day 4 Introduction to SQL Server Services   
Day 5 EXAM: Microsoft SQL Server 2005 - Implementation and Maintenance   70-431

Installing and Configuring SQL Server 2005 Install SQL Server 2005.

• · Verify prerequisites.
• · Upgrade from a previous version of SQL Server.
• · Create an instance.

• Configure SQL Server 2005 instances and databases.

• · Configure log files and data files.
• · Configure the SQL Server DatabaseMail subsystem for an instance.
• · Choose a recovery model for the database.

• Configure SQL Server security.

• · Configure server security principals.
• · Configure database securables.
• · Configure encryption.

• Configure linked servers by using SQL Server Management Studio (SSMS).

• · Identify the external data source.
• · Identify the characteristics of the data source.
• · Identify the security model of the data source.

• Implementing High Availability and Disaster Recovery Implement database mirroring.

• · Prepare databases for database mirroring.
• · Create endpoints.
• · Specify database partners.
• · Specify a witness server.
• · Configure an operating mode.

• Implement log shipping.

• · Initialize a secondary database.
• · Configure log shipping options.
• · Configure a log shipping mode.
• · Configure monitoring.

• Manage database snapshots.

• · Create a snapshot.
• · Revert a database from a snapshot.

• Supporting Data Consumers Retrieve data to support ad hoc and recurring queries.

• · Construct SQL queries to return data.
• · Format the results of SQL queries.
• · Identify collation details.

• Manipulate relational data.

• · Insert, update, and delete data.
• · Handle exceptions and errors.
• · Manage transactions.

• Manage XML data.

• · Identify the specific structure needed by a consumer.
• · Retrieve XML data.
• · Modify XML data.
• · Convert between XML data and relational data.
• · Create an XML index.
• · Load an XML schema.

• Implement an HTTP endpoint.

• · Create an HTTP endpoint.
• · Secure an HTTP endpoint.

• Implement Service Broker components.

• · Create services.
• · Create queues.
• · Create contracts.
• · Create conversations.
• · Create message types.
• · Send messages to a service.
• · Route a message to a service.
• · Receive messages from a service.

• Import and export data from a file.

• · Set a database to the bulk-logged recovery model to avoid inflating the transaction log.
• · Run the bcp utility.
• · Perform a Bulk Insert task.
• · Import bulk XML data by using the OPENROWSET function.
• · Copy data from one table to another by using the SQL Server 2005 Integration Services (SSIS) Import and Export Wizard.

• Manage replication.

• · Distinguish between replication types.
• · Configure a publisher, a distributor, and a subscriber.
• · Configure replication security.
• · Configure conflict resolution settings for merge replication.
• · Monitor replication.
• · Improve replication performance.
• · Plan for, stop, and restart recovery procedures.

• Maintaining Databases Implement and maintain SQL Server Agent jobs.

• · Set a job owner.
• · Create a job schedule.
• · Create job steps.
• · Configure job steps.
• · Disable a job.
• · Create a maintenance job.
• · Set up alerts.
• · Configure operators.
• · Modify a job.
• · Delete a job.
• · Manage a job.

• Manage databases by using Transact-SQL.

• · Manage index fragmentation.
• · Manage statistics.
• · Shrink files.
• · Perform database integrity checks by using DBCC CHECKDB.

• Back up a database.

• · Perform a full backup.
• · Perform a differential backup.
• · Perform a transaction log backup.
• · Initialize a media set by using the FORMAT option.
• · Append or overwrite an existing media set.
• · Create a backup device.
• · Back up filegroups.

• Restore a database.

• · Identify which files are needed from the backup strategy.
• · Restore a database from a single file and from multiple files.
• · Choose an appropriate restore method.

• Move a database between servers.

• · Choose an appropriate method for moving a database.

• Monitoring and Troubleshooting SQL Server Performance Gather performance and optimization data by using the SQL Server Profiler.

• · Start a new trace.
• · Save the trace logs.
• · Configure SQL Server Profiler trace properties.
• · Configure a System Monitor counter log.
• · Correlate a SQL Server Profiler trace with System Monitor log data.

• Gather performance and optimization data by using the Database Engine Tuning Advisor.

• · Build a workload file by using the SQL Server Profiler.
• · Tune a workload file by using the Database Engine Tuning Advisor.
• · Save recommended indexes.

• Monitor and resolve blocks and deadlocks.

• · Identify the cause of a block by using the sys.dm_exec_requests system view.
• · Terminate an errant process.
• · Configure SQL Server Profiler trace properties.
• · Identify transaction blocks.

• Diagnose and resolve database server errors.

• · Connect to a nonresponsive server by using the dedicated administrator connection (DAC).
• · Review SQL Server startup logs.
• · Review error messages in event logs.

• Monitor SQL Server Agent job history.

• · Identify the cause of a failure.
• · Identify outcome details.
• · Find out when a job last ran.

• Gather performance and optimization data by using DMVs. Creating and Implementing Database Objects Implement a table.

• · Specify column details.
• · Specify the filegroup.
• · Assign permissions to a role for tables.
• · Specify a partition scheme when creating a table.
• · Specify a transaction.

• Implement a view.

• · Create an indexed view.
• · Create an updateable view.
• · Assign permissions to a role or schema for a view.

• Implement triggers.

• · Create a trigger.
• · Create DDL triggers for responding to database structure changes.
• · Identify recursive triggers.
• · Identify nested triggers.
• · Identify transaction triggers.

• Implement functions.

• · Create a function.
• · Identify deterministic versus nondeterministic functions.

• Implement stored procedures.

• · Create a stored procedure.
• · Recompile a stored procedure.
• · Assign permissions to a role for a stored procedure.

• Implement constraints.

• · Specify the scope of a constraint.
• · Create a new constraint.

• Implement indexes.

• · Specify the filegroup.
• · Specify the index type.
• · Specify relational index options.
• · Specify columns.
• · Specify a partition scheme when creating an index.
• · Disable an index.
• · Create an online index by using an ONLINE argument.

• Create user-defined types.

• · Create a Transact-SQL user-defined type.
• · Specify details of the data type.
• · Create a CLR user-defined type.

• Implement a full-text search.

• · Create a catalog.
• · Create an index.
• · Specify a full-text population method.

• Implement partitions.

TEC Boot Camp
20423 State Road 7, Unit F6-311 Boca Raton, FL 33498
tel -1.800.617.5586
fax - 1.877.557.3064
Copyright 2005-2010

Examination Passing Policy. Should a student complete a TEC Boot Camp Program without having successfully passed all vendor examinations, the student may re-attend that program for a period of one year. A student will only be responsible for hotel accommodation and vendor exam fees.