Microsoft SQL Server 2005 MCTS 70-431 - Online Course

This series explains how to install, configure, support, and use Microsoft SQL Server 2005.

This series for anyone who's preparing to take the SQL Server 2005 MCTS 70-431 exam, or for anyone who wants to learn more about SQL Server 2005. This online course contains 10 lessons and 1 practice exams and should take approximately 26 hours to complete.

Lesson 1: Installing and Configuring SQL Server 2005 (2.0) hours

Installing and Configuring SQL Server 2005 teaches you how to evaluate your hardware and software requirements prior to installation, install SQL Server 2005, configure linked servers for use with SQL Server 2005, and establish login security. This course explains how to prepare for the installation and configuration of SQL Server 2005. Topics include Preparing for Installation, Installing SQL Server 2005, Postinstallation Procedures, and SQL Server Login Security.

Lesson 2: Creating Databases (2.0) hours

Creating Databases teaches you how to create and alter databases, employ views for data query operations, use indexed views, and use partitioned views. This course explains how to use SQL Server 2005 to create databases and establish views you can employ to work with data. Topics include Defining Databases, Views, Using Indexed Views, and Using Partitioned Views.

Lesson 3: Working with Tables and Programmability Objects (3.0) hours

Working with Tables and Programmability Objects teaches you how to implementing a table, implementing triggers, implementing functions, implementing stored procedures, implementing constraints, and implementing indexes. This course provides you with information concerning job skills tested by Microsoft Exam MCTS70-431. Topics include Table Basics, Column Attributes, Indexes, Primary and Foreign Keys, Cascading Actions and Triggers, Partitioned Tables, Implementing Partitioned Tables, Programmability Objects, Full-Text Searches, and Security.

Lesson 4: Implementing Database Objects (4.0) hours

Implementing Database Objects teaches you how to perform queries that extract data, produce reports, insert, update, and remove data, use full-text queries, and use system tables and views. This course explains how to work with the data in database tables. Topics include Listing Table Contents, Enhancing Reports, Querying Data, Conditional Data Filtering, Data Querying via Full-Text Indexes, Using System Tables and Stored Procedures, Using Views and Data Types, Inserting Records, Changing Stored Data, and Removing Unwanted Data.

Lesson 5: Supporting the XML Framework (2.0) hours

Supporting the XML Framework teaches you how to recognize the importance of XML within a SQL Server database, identify the new features and functionality for XML in SQL Server 2005, describe how to output data in XML format, use the xml data type, explain methods associated with the xml data type, and create XML indexes. This course explains how to manage the XML data and features that are integrated into SQL Server 2005. Topics include Managing XML data, Supported XML features, XML basics, The XML data type, XML method interactions, and Other SQL Server XML support.

Lesson 6: Enhancing Data Consumption and Throughput (2.0) hours

Enhancing Data Consumption and Throughput teaches you how to explain importing and exporting data with SQL Server 2005, import and export data using the Bulk Copy Program (BCP), use OPENROWSET to access any OLE DB data source, use SSIS to handle the import and export of data, implement Service Broker, and assemble components into a service. This course explains how to move large amounts of data either within a single server or between servers. Bulk Copy Program (BCP) and SQL Server Integration Services (SSIS) are also discussed. Topics include Importing and Exporting Data, Using the Bulk Copy Program, Using OPENROWSET and SSIS, Implementing Service Broker, and Assembling Components into a Service.

Lesson 7: Maintaining and Backing Up Databases (3.0) hours

Maintaining and Backing Up Databases teaches you how to identify the new features in SQL Server database maintenance, perform a database backup, identify the various backup types, use the T-SQL BACKUP statement, restore data from a backup, create a database snapshot, and automate maintenance with job scheduling. This course explains how to use SQL Server 2005 to maintain and backup databases. Topics include SQL Server Database Maintenance, Performing Database Backups, Backup Types, Using the T-SQL BACKUP Statement, Restoring Data from a Backup, Working with Database Snapshots, and Automating Maintenance with Job Scheduling.

Lesson 8: Monitoring Performance (2.0) hours

Monitoring Performance teaches you how to describe monitoring tools used by SQL Server 2005, identify the features of Log File Viewer, describe the types of server-maintained information found in SQL Server 2005, list types of System Monitor counters, and define a SQL Profiler trace. This course explains how to monitor the performance of a SQL Server 2005 system. Topics include SQL Server Management Studio, Using Activity Monitor, Working with Log File Viewer, Server-Maintained Information, Using Windows System Monitor, Working with SQL Server Profiler, and Defining a Profiler Trace.

Lesson 9: Troubleshooting and Optimizing (2.0) hours

Troubleshooting and Optimizing teaches you how to list the settings that affect statistics, describe how SQL Server deals with multiple users, discuss indexing strategies, describe how partitioning can improve performance, and list tools that assist with maintenance tasks. This course explains how to troubleshoot issues that may arise when using SQL Server 2005. It also provides information on optimizing SQL Server for the best performance. Topics include Tuning the Operating System and Hardware, Locks, Blocks, and Deadbolts, Indexing Strategies, Data Partitioning Across Servers, Working with the DTA, and Using the Database Console Command.

Lesson 10: Implementing High Availability (3.0) hours

Implementing High Availability teaches you how to describe the costs and risks involved in certain high-availability solutions, implement log shipping and explain how it operates, enable database mirroring using trace flags and use failover clustering, discuss the strategies, types, and elements of replication, and explain Microsoft Analysis Services in SQL Server 2005. This course explains how to implement high-availability solutions such as log shipping, database mirroring, failover clusters, and replication. Topics include High-availability solutions, Implementing log shipping, Database mirroring and failover clustering, Replication strategies, Types of replication, Elements of replication, and Microsoft analysis services.

1 Practice Exam

This course provides practice questions for the SQL Server 2005 MCTS 70-431 exam.