Microsoft Certified Technology Specialist (MCTS): SQL Server 2005

Prerequisites

To successfully complete your exam, you should have knowledge and experience with Microsoft SQL Server 2005, and be able to implement and maintain databases via specific instructions and specifications.

You should also have expertise in the following areas:

• Tools usage
• UI navigation
• Wizards usage
• Writing code (Transact-SQL, CLR language, and other appropriate scripting languages)
• Code debugging or syntactic issue resolution
• Troubleshooting
• Completing tasks by using code or UI navigation.

Who is this course for?

This course is for those looking to pursue careers as database administrators, database developers, or business intelligence developers. It may also be for developers and systems administrators who want to show their breadth of technology experience, but do not work daily with SQL Server.

What will I learn?

Upon successful completion of the course, you will be able to:

• Install and configure SQL Server 2005
• Implement high availability and disaster recovery
• Support data consumers
• Maintain databases
• Monitor and troubleshoot SQL Server performance
• Create and implement database objects.

What certificate will I get?

After passing this course, you will receive the MCTS SQL Server 2005 certification.

You will also have completed one of three exams necessary to gain the MCITP Database Administrator and MCITP Database Developer certifications.

What course materials do I get?

To help you excel in your examinations through effective and efficient learning, ICS will provide you with all the learning materials necessary:

• Flash video tutorials led by a virtual instructor
• Interactive simulations and resources
• E-books
• Practice exams
• Microsoft-branded supporting textbook 
• Course and assessment guide to help you pass your exams.

What support do I get?

You will have access to our dedicated ICS Technology Student Community, which will allow you to share and collaborate with your peers, get interactive and expert feedback from tutors and browse our resource library.

You will also receive 6 months of email tutorial support.

Sample Course Material

You need to upgrade your Flash Player!

This web site makes use of the Macromedia Flash player version 8 or newer.

Related Courses

• MCITP Database Administrator
• MCITP Database Developer
  

Microsoft SQL Server 2005 (MCTS) Course Outline

Implementing a Microsoft SQL Server 2005 Database

• Module 1 - Creating Databases and Database Files
• Creating Databases
• Creating Filegroups
• Creating Schemas
• Creating Database Snapshots
• Module 2 - Creating Data Types and Tables
• Creating Data Types
• Creating Tables
• Creating Partitioned Tables
• Module 3 - Using XML
• Retrieving XML by Using FOR XML
• Shredding XML by Using OPENXML
• Using the XML Data Type
• Module 4 - Creating and Tuning Indexes
• Planning Indexes
• Creating Indexes
• Optimising Indexes
• Creating XML Indexes
• Module 5 - Implementing Data Integrity
• Data Integrity Overview
• Implementing Constraints
• Implementing Triggers
• Implementing XML Schemas
• Module 6 - Implementing Views
• Introduction to Views
• Creating and Managing Views
• Optimising Performance by Using Views
• Module 7 - Implementing Stored Procedures and Functions
• Implementing Stored Procedures
• Creating Parameterised Stored Procedures
• Creating Functions
• Handling Errors
• Controlling Execution Context
• Module 8 - Implementing Managed Code in the Database
• Introduction to the SQL Server Common Language Runtime
• Importing and Configuring Assemblies
• Creating Managed Database Objects
• Module 9 - Using Service Broker
• Service Broker Overview
• Creating Service Broker Objects
• Sending and Receiving Messages.

Maintaining a Microsoft SQL Server 2005 Database

• Module 1 - Installing and Configuring SQL Server 2005
• Preparing to install SQL Server
• Installing SQL Server
• Managing a SQL Server 2005 Installation
• Module 2 - Managing Databases and Files
• Planning databases
• Creating databases
• Managing databases
• Module 3 - Disaster Recovery
• Planning a Backup Strategy
• Backing Up User Databases
• Restoring User Databases
• Recovering Data from Database Snapshots
• System Database and Disaster Recovery
• Module 4 - Managing Security
• Overview of SQL Server Security
• Securing the Server Scope
• Securing the Database Scope
• Managing Keys and Certificates in SQL Server
• Module 5 - Monitoring SQL Server
• Viewing Current Activity
• Using System Monitor
• Using SQL Server Profiler
• Using DDL Triggers
• Using Event Notifications
• Module 6 - Transferring Data
• Overview of Data Transfer
• Introduction to SQL Server Integration Services
• Using SQL Server Integration Services
• Module 7 - Automating 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
• Module 8 - Maintaining High Availability
• Introduction to High Availability
• Implementing Server Clustering
• Implementing Database Mirroring
• Implementing Log Shipping
• Module 9 - Introduction to Replication
• Overview of Replication
• Replication Scenarios.

Assessment

Your knowledge of the course materials will be assessed by an examination. This can be sat at your local authorised Prometric test centre:

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

Please note that examination fees are additional to course fees.

Hours of Study

40 hours

Minimum System Requirements

• 600MHz Intel Pentium III processor or equivalent
• Windows 98, Windows 2000, Windows XP or Windows Vista
• 128MB RAM (256MB RAM recommended)
• 128MB available disc space
• CD-ROM drive
• Microsoft Internet Explorer 5.01SP2.