MCTS 70-431:
Implementing and Maintaining SQL Server 2005

Topic-Level Outline

Days: 5

Prerequisites: MCSE 70-290: Managing and Maintaining a Windows Server 2003 Environment (Updated)

Unit 1 : SQL Server 2005 installation

Topic A: 0 SQL Server features

A-1: Identifying SQL Server features

Topic B: 0 SQL Server installation

B-1: Installing SQL Server 2005

Topic C: 0 Alternative installations

C-1: Identifying alternative installation options

Unit 2 : SQL Server management tools

Topic A: 0 SQL Server 2005 services

A-1: Identifying SQL Server services

Topic B: 0 Management tools

B-1: Using SQL Server Configuration Manager

B-2: Using the Surface Area Configuration tool

B-3: Using SQL Server Management Studio

B-4: Exporting Surface Area Configuration settings

Topic C: 0 SQL Server settings

C-1: Managing server instance options

Topic D: 0 Database Mail

D-1: Enabling and configuring Database Mail

Unit 3 : Database management

Topic A: 0 SQL Server data storage

A-1: Viewing system database information

Topic B: 0 Database management

B-1: Creating and modifying a database

B-2: Managing database options

Topic C: 0 Capacity planning and management

C-1: Shrinking a database

Unit 4 : Data design

Topic A: 0 The data design process

A-1: Identifying design basics

Topic B: 0 Table and data design

B-1: Applying table and data design concepts

Topic C: 0 Data normalization

C-1: Using normalization

Topic D: 0 Data types

D-1: Using data types

D-2: Using alias data types

Unit 5 : Tables, constraints, and indexes

Topic A: 0 Objects

A-1: Using identifiers

Topic B: 0 Tables and constraints

B-1: Creating tables

B-2: Altering tables

Topic C: 0 Indexes

C-1: Managing indexes

Unit 6 : Data retrieval

Topic A: 0 Queries

A-1: Executing queries

A-2: Viewing execution plans

A-3: Creating and using a query template

Topic B: 0 Transact-SQL functions

B-1: Using Transact-SQL functions

Topic C: 0 Data organization

C-1: Grouping data

Topic D: 0 Multiple table queries

D-1: Using UNION, EXCEPT, and INTERSECT

D-2: Joining multiple tables

D-3: Using subqueries

Topic E: 0 Data modification

E-1: Updating data

Topic F: 0 Query Designer

F-1: Using Query Designer

Unit 7 : Programmable objects

Topic A: 0 Programming fundamentals

A-1: Writing Transact-SQL batches

Topic B: 0 User views

B-1: Creating views

Topic C: 0 Stored procedures

C-1: Creating stored procedures

Topic D: 0 User-defined functions

D-1: Creating and using functions

Unit 8 : Data management

Topic A: 0 Multiple language support

A-1: Managing collations

Topic B: 0 Full-text search

B-1: Creating a full-text index

Topic C: 0 Partitions

C-1: Partitioning a table

Topic D: 0 Error handling

D-1: Using structured error handling

Topic E: 0 Transactions and locking

E-1: Using transactions and locks

Topic F: 0 Triggers

F-1: Creating a trigger

Unit 9 : XML support

Topic A: 0 XML fundamentals

A-1: Using XML documents

Topic B: 0 The XML data type

B-1: Using XML data types

Topic C: 0 XML methods and indexes

C-1: Creating XML indexes

Topic D: 0 XML and relational data integration

D-1: Using FOR XML

Unit 10 : Data transfers

Topic A: 0 Bulk transfers

A-1: Importing and export data

A-2: Using BULK INSERT

Topic B: 0 SQL Server 2005 Integration Services

B-1: Importing flat files

Topic C: 0 XML import and export

C-1: Importing and exporting XML data

Unit 11 : SQL Server security

Topic A: 0 Principles

A-1: Managing logins

A-2: Managing users

Topic B: 0 Server securables

B-1: Managing server permissions

B-2: Managing database permissions

Topic C: 0 Database and schema securables

C-1: Managing database roles

C-2: Changing the execution context

Topic D: 0 SQL auditing

D-1: Configuring auditing

Topic E: 0 Encryption

E-1: Managing encryption keys

Topic F: 0 CLR security

F-1: Managing CLR assemblies

Unit 12 : Management and maintenance

Topic A: 0 SQL Server Agent

A-1: Configuring SQL Server Agent

A-2: Managing jobs

A-3: Working with a maintenance plan

Topic B: 0 Distributed queries

B-1: Working with remote data

Topic C: 0 Replication

C-1: Configuring the Distributor

C-2: Configuring transactional replication

C-3: Monitoring replication

Unit 13 : SQL Server optimization

Topic A: 0 Monitoring and optimization

A-1: Using monitoring tools

Topic B: 0 Database optimization

B-1: Monitoring system activity

Topic C: 0 Database tune-ups

C-1: Tuning a database

Topic D: 0 Query optimization concepts

D-1: Optimizing queries

Unit 14 : Reliability and availability

Topic A: 0 Database backup

A-1: Running backups

Topic B: 0 Database restore

B-1: Restoring data

Topic C: 0 Log shipping

C-1: Using log shipping

Topic D: 0 Database snapshots

D-1: Managing database snapshots

Topic E: 0 Mirrored databases

E-1: Implementing database mirroring

Unit 15 : Applications

Topic A: 0 Service Broker

A-1: Creating endpoints

A-2: Creating Service Broker objects

Topic B: 0 HTTP endpoints

B-1: Creating an HTTP endpoint

Unit 16 : Troubleshooting and repair

Topic A: 0 Blocks and deadlocks

A-1: Viewing and killing blocking processes

Topic B: 0 SQL Server errors

B-1: Viewing logs

B-2: Connecting using DAC

Topic C: 0 Recovery

C-1: Managing database and file states

Appendix A : Outlander Spices case study

Topic A: 0 Outlanders Spices

Appendix B : Command reference

Topic A: 0 SQL Server command reference

Appendix C : MCTS 70-431 exam objectives map

Topic A: 0 Comprehensive exam objectives