Managing and Maintaining Microsoft SQL ServerTM 2005

Course 2785AL: Five weeks; Microsoft Official Distance Learning (MODL)

Times: 4:30pm to 7:00pm each session

Introduction

Course 2785AL: Five weeks; Microsoft Official Distance Learning (MODL)

Elements of this syllabus are subject to change.

This Distance Learning version of the course consists of 10 online live instructor-led sessions over 5 weeks (2hrs for each session). These sessions would be accompanied by additional self-paced e-learning content, scenario-based labs, and assessments to provide students with the knowledge and skills to manage and maintain Microsoft SQL Server 2005.

Audience

This course is intended for IT Professionals wanting to become skilled on SQL Server 2005 product features and technologies for maintaining a database.

At Course Completion

After completing this course, students will be able to:

This course syllabus should be used to determine whether the course is appropriate for the students, based on their current skills and technical training needs.

Course content, prices, and availability are subject to change without notice.

Prerequisites

Before attending this course, students must have:

Microsoft Certified Professional Exams

This course will help the student prepare for the following Microsoft Certified Professional exam:

Course Materials

The student kit includes a comprehensive workbook and other necessary materials for this class.

Course Outline

Module 1-1: Installing and Configuring SQL Server 2005

This module introduces views and summarizes the advantages that they provide. It describes how to create views by using Microsoft SQL Server Management Studio and Transact-SQL, the options available, and how to find information about views. The module then addresses restrictions on modifying data through views and how views can improve database performance.

Lessons

Lab 1-1 Installing SQL Server 2005

After completing this module, students will be able to:

Module 1-2: Creating Databases and Database Files

This module describes how data is stored in SQL Server 2005. It provides information on how to create filegroups, databases, and schemas. It also covers the aspects of creating database snapshots and managing them.

Lessons

Lab 1-2 Creating Databases and Database Files

After completing this module, students will be able to:

Module 2-1: Creating Data Types and Tables

It is important to know the characteristics of the various data types so that you can assign the appropriate data values for any column in an SQL Server database table. SQL Server provides a number of data types that are associated with the categories of data types used by common programming languages. This module describes the characteristics of the various SQL Server data types.

This module will also help you understand how SQL Server organizes data in rows. This knowledge is important, as it can enable you to accurately estimate the size of a table.

Lessons

Lab 2-1 Creating Data Types and Tables

After completing this module, students will be able to:

Module 2-2: Querying SQL Server 2005 Databases

This module provides the knowledge and skills to perform basic Transact SQL queries in Microsoft SQL Server 2005. It also provides an overview of T-SQL and shows how to retrieve, group, summarize, and modify data. It provides an overview of querying multiple tables by using different types of joins, and gives you the foundation to query XML data by using XQuery.

Lessons

Lab 2-2 Querying SQL Databases

After completing this module, students will be able to:

Module 3-1: Managing Security in SQL Server 2005

Security is a primary consideration when designing and managing a database environment. In this module, you will learn about the Microsoft SQL Server security model in SQL Server 2005, which is considerably enhanced and extended from previous versions of SQL Server.

Lessons

Lab 3-1 Managing SQL Security

After completing this module, students will be able to:

Module 3-2: Disaster Recovery

Frequently, the data in a database substantiates an organization’s operations. Without the database, the organization cannot function properly. It is important that every mission-critical database system has adequate disaster-recovery procedures in place.

Backup and restore operations are a vital part of data management and are essential for recovering from failures and disasters. Therefore, a major part of a database administrator’s role is to ensure that data is backed up and can be restored quickly in the event of a disaster.

Lessons

Lab 3-2 Disaster Recovery

After completing this module, students will be able to:

Module 4-1: Creating and Tuning Indexes

This module provides an overview of planning, creating, and optimizing indexes. It explains the differences between heaps, clustered indexes, and nonclustered indexes and where you would use each one. It describes how to create the different types of indexes and how to configure and maintain them to attain optimal performance benefits.

Lessons

Lab 4-1 Creating and Tuning Indexes

After completing this module, students will be able to:

Module 4-2: Implementing Data Integrity

This module provides an overview of the types of data integrity that will concern you as a database developer, and summarizes the features provided by SQL Server 2005 to meet those concerns. It also provides and opportunity to discuss the powerful data integrity features of SQL Server 2005 - constraints, triggers, and XML schemas.

Lessons

Lab 4-2 Implementing Data Integrity

After completing this module, students will be able to:

Module 5-1: Implementing Programming Objects

This module introduces views and summarizes the advantages that they provide. It describes how to create views by using Microsoft SQL Server Management Studio and Transact-SQL, and how to find information about views. The module then addresses restrictions on modifying data through views and how views can improve database performance.

You will also learn how to create stored procedures and user-defined functions. You will also learn how to implement structured error handling and understand execution context.

Lessons

Lab 5-1 Implementing Stored Procedures and Functions

After completing this module, students will be able to:

Module 5-2: Availability and Scalability Technologies Included With SQL Server 2005

In this module you will learn about the different types of replication, the server roles involved, and scenarios involving server-to-server and server-to-client replication. In addition to replication, you will also learn about database mirroring, which is an alternative high-availability solution to failover clustering in SQL Server 2005 Enterprise Edition.

This module also introduces Microsoft SQL Server 2005 Service Broker, a message-based platform for building service-oriented database solutions. The module also summarizes the architecture and functionality of Service Broker, explains how to create Service Broker objects, and describes how to send and receive Service Broker messages.

Lessons

Lab 5-2 Mirroring SQL Databases

After completing this module, students will be able to: