MCTS: Microsoft Certified Technology Specialist - SQL Server 2005

Definition & Purpose
The Technology Specialist certifications enable professionals to target specific technologies and distinguish themselves by demonstrating in-depth knowledge and expertise in their specialized technologies.
Microsoft Certified Technology Specialists in Microsoft SQL Server 2005 (MCTS: SQL Server 2005) 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 explore the user interface. Technology Specialists typically pursue careers as database administrators, database developers, or business intelligence developers. They may also be developers and systems administrators who do not work with SQL Server daily but who want to show their breadth of technology experience.
Microsoft Skills Assessments for SQL Server 2005 help database administrators, database developers, and business intelligence developers focus their exam preparation training and find learning resources.
MCTS candidates must pass one exam that focuses on the implementation and maintenance of SQL Server 2005.

• 70–431: TS: Microsoft SQL Server 2005 - Implementation and Maintenance

Course Outline
Creating Databases and Database Files
This module explains how to create databases, filegroups, schemas, and database snapshots.

• Creating Databases.
• Creating Filegroups.
• Creating Schemas.
• Creating Database Snapshots.

Creating Data Types and Tables
This module explains how to create data types and tables. It also describes how to create partitioned tables.

• Creating Data Types.
• Creating Tables.
• Creating Partitioned Tables.

Using XML
This module explains how to use the FOR XML clause and the OPENXML function. It also describes how to use the xml data type and its methods.

• Retrieving XML by Using FOR XML.
• Shredding XML by Using OPENXML.
• Using the xml Data Type.

Creating and Tuning Indexes
This module explains how to plan, create, and optimize indexes. It also describes how to create XML indexes.

• Planning Indexes.
• Creating Indexes.
• Optimizing Indexes.
• Creating XML Indexes.

Implementing Data Integrity
This module explains how to implement constraints, triggers, and XML schemas.

• Data Integrity Overview.
• Implementing Constraints.
• Implementing Triggers.
• Implementing XML Schemas.

Implementing Views
This module explains how to create views.

• Introduction to Views.
• Creating and Managing Views.
• Optimizing Performance by Using Views.

Implementing Stored Procedures and Functions
This module explains how to create stored procedures and functions.

• Implementing Stored Procedures.
• Creating Parameterized Stored Procedures.
• Creating Functions.
• Handling Errors.
• Controlling Execution Context.

Implementing Managed Code in the Database
This module explains how to implement managed database objects.

• Introduction to the SQL Server Common Language Runtime.
• Importing and Configuring Assemblies.
• Creating Managed Database Objects.

Using Service Broker
This module explains how to build a messaging-based solution with Service Broker.

• Service Broker Overview.
• Creating Service Broker Objects.
• Sending and Receiving Messages.

Installing and Configuring SQL Server 2005
This module explains how to plan for and install SQL Server 2005, how to manage a SQL Server 2005 installation, and how to use the SQL Server 2005 administrative tools.

• Preparing to install SQL Server.
• Installing SQL Server.
• Managing a SQL Server 2005 Installation.

Managing Databases and Files
This module explains how to manage databases and files.

• Planning databases.
• Creating databases.
• Managing databases.

Disaster Recovery
This module explains how to plan and implement a backup and restore strategy.

• Planning a Backup Strategy.
• Backing Up User Databases.
• Restoring User Databases.
• Recovering Data from Database Snapshots.
• System Database and Disaster Recovery.

Managing Security
This module explains how to manage principals, securables, and permissions, and how to implement cryptography in a SQL Server database.

• Overview of SQL Server Security.
• Securing the Server Scope.
• Securing the Database Scope.
• Managing Keys and Certificates in SQL Server.

Monitoring SQL Server
This module explains how to monitor SQL Server performance and activity.

• Viewing Current Activity.
• Using System Monitor.
• Using SQL Server Profiler.
• Using DDL Triggers.
• Using Event Notifications.

Transferring Data
This module explains how to transfer and transform data.

• Overview of Data Transfer.
• Introduction to SQL Server Integration Services.
• Using SQL Server Integration Services.

Automating Administrative Tasks
This module explains how to use the SQL Server Agent to automate administrative tasks.

• Automating Administrative Tasks in SQL Server 2005.
• Configuring the SQL Server Agent.
• Creating Jobs and Operators.
• Creating Alerts.
• Managing Multiple Servers.
• Managing SQL Server Agent Security.

Maintaining High Availability
This module explains how to implement high availability technologies with SQL Server 2005.

• Introduction to High Availability.
• Implementing Server Clustering.
• Implementing Database Mirroring.
• Implementing Log Shipping.

Introduction to Replication
This module explains considerations for implementing replication.

• Overview of Replication.
• Replication Scenarios.

Prerequisites

• Basic knowledge of the Microsoft Windows operating system and its core functionality
• Working knowledge of Transact-SQL
• Working knowledge of relational databases
• Some experience with database design