Course Outline
Module 1: Creating Databases and Database Files
This module explains how to
create databases, filegroups, schemas, and database snapshots.
Lessons
•
Creating
Databases
•
Creating
Filegroups
•
Creating
Schemas
•
Creating
Database Snapshots
Lab : Creating Databases and
Database Files
•
Creating
a Database
•
Creating
Schemas
•
Creating
a Database Snapshot
After completing this module,
students will be able to:
•
Create
databases.
•
Create
filegroups.
•
Create
schemas.
•
Create
database snapshots.
Module 2: Creating Data Types and Tables
This module explains how to
create data types and tables. It also describes how to create partitioned
tables.
Lessons
•
Creating
Data Types
•
Creating
Tables
•
Creating
Partitioned Tables
Lab : Creating Data Types and Tables
•
Creating
Data Types
•
Creating
Tables
•
Creating
Partitioned Tables
After completing this module,
students will be able to:
•
Create
new data types.
•
Create
new tables.
•
Create
partitioned tables.
Module 3: 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.
Lessons
•
Retrieving
XML by Using FOR XML
•
Shredding
XML by Using OPENXML
•
Introducing
XQuery
•
Using
the xml Data Type
Lab : Using XML
•
Mapping
Relational Data and XML
•
Storing
XML Natively in the Database
•
Using
XQuery with xml Methods
•
After
completing this module, students will be able to:
•
Retrieve
XML by using the FOR XML clause.
•
Shred
XML by using the OPENXML function.
•
Use
XQuery expressions.
•
Use the
xml data type.
Module 4: Creating and Tuning Indexes
This module explains how to plan,
create, and optimize indexes. It also describes how to create XML indexes.
Lessons
•
Planning
Indexes
•
Creating
Indexes
•
Optimizing
Indexes
•
Creating
XML Indexes
Lab : Creating and Tuning Indexes
•
Creating
Indexes
•
Tuning
Indexes
•
Creating
XML Indexes
•
After
completing this module, students will be able to:
•
Plan
indexes.
•
Create
indexes.
•
Optimize
indexes.
•
Create
XML indexes.
Module 5: Implementing Data Integrity by Using Constraints
This module explains how to
implement constraints and provides an overview of data integrity.
Lessons
•
Data
Integrity Overview
•
Implementing
Constraints
Lab : Implementing Data Integrity by Using Constraints
•
Creating
Constraints
•
Disabling
Constraints
After completing this module,
students will be able to:
•
Describe
the options for enforcing data integrity in SQL Server 2005.
•
Implement
data integrity in SQL Server 2005 databases by using constraints.
Module 6: Implementing Data Integrity by Using Triggers and XML
Schemas
This module explains how to
implement triggers and XML schemas.
Lessons
•
Implementing
Triggers
•
Implementing
XML Schemas
Lab : Implementing Data Integrity by Using Triggers and XML Schemas
•
Creating
Triggers
•
Implementing
XML Schemas
After completing this module,
students will be able to:
•
Implement
data integrity in SQL Server 2005 databases by using triggers.
•
Implement
data integrity in SQL Server 2005 databases by using XML schemas.
Module 7: Implementing Views
This module explains how to
create views.
Lessons
•
Introduction
to Views
•
Creating
and Managing Views
•
Optimizing
Performance by Using Views
Lab : Implementing Views
•
Creating
Views
•
Creating
Indexed Views
•
Creating
Partitioned Views
After completing this module,
students will be able to:
•
Describe
the purpose of views.
•
Create
and manage views.
•
Explain
how to optimize query performance by using views.
Module 8: Implementing Stored Procedures
This module explains how to
create stored procedures and functions. It also describes execution plans, plan
caching, and query compilation.
Lessons
•
Implementing
Stored Procedures
•
Creating
Parameterized Stored Procedures
•
Working
With Execution Plans
•
Handling
Errors
Lab : Implementing Stored Procedures
•
Creating
Stored Procedures
•
Working
With Execution Plans
After completing this module,
students will be able to:
•
Implement
stored procedures.
•
Create
parameterized stored procedures.
•
Work
with execution plans.
•
Handle
errors in stored procedures.
Module 9: Implementing Functions
This module explains how to
create functions. It also describes how to control the execution context.
Lessons
•
Creating
and Using Functions
•
Working
with Functions
•
Controlling
Execution Context
Lab : Implementing Functions
•
Creating
Functions
•
Controlling
Execution Context
After completing this module,
students will be able to:
•
Create
and use functions.
•
Work
with functions.
•
Control
execution context.
Module 10: Implementing Managed Code in the Database
This module explains how to
implement managed database objects.
Lessons
•
Introduction
to the SQL Server Common Language Runtime
•
Importing
and Configuring Assemblies
•
Creating
Managed Database Objects
Lab : Implementing Managed Code in the Database
•
Importing
an Assembly
•
Creating
Managed Database Objects
After completing this module,
students will be able to:
•
Identify
appropriate scenarios for managed code in the database.
•
Import
and configure assemblies.
•
Create
managed database objects.
Module 11: Managing Transactions and Locks
This module explains how to use
transactions and the SQL Server locking mechanisms to meet the performance and
data integrity requirements of your applications.
Lessons
•
Overview
of Transactions and Locks
•
Managing
Transactions
•
Understanding
SQL Server Locking Architecture
•
Managing
Locks
Lab : Managing Transactions and Locks
•
Using
Transactions
•
Managing
Locks
After completing this module,
students will be able to:
•
Describe
how SQL Server 2005 transactions use locks.
•
Execute
and cancel a transaction.
•
Describe
concurrency issues and SQL Server 2005 locking mechanisms.
•
Manage
locks.
Module 12: Using Service Broker
This module explains how to build
a messaging-based solution with Service Broker.
Lessons
•
Service
Broker Overview
•
Creating
Service Broker Objects
•
Sending
and Receiving Messages
Lab : Using Service Broker (Optional)
•
Creating
Service Broker Objects
•
Creating
Service Broker Objects
•
Implementing
the Target Service
After completing this module,
students will be able to:
•
Describe
Service Broker functionality and architecture.
•
Create
Service Broker objects.
•
Send and
receive Service Broker messages.
Module 13: Using Notification Services (Optional)
This module explains how to
develop applications that generate and send timely messages to subscribers.
Lessons
•
Introduction
to Notification Services
•
Developing
Notification Services Solutions
After completing this module,
students will be able to:
•
Describe
how Notification Services operates.
•
Develop
a Notification Services application.
Module 14: 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.
Lessons
•
Preparing
to Install SQL Server
•
Installing
SQL Server 2005
•
Managing
a SQL Server 2005 Installation
Lab : Installing and Configuring SQL Server 2005
•
Performing
an Installation
•
Managing
SQL Server
After completing this module,
students will be able to:
•
Explain
how to prepare the hardware and other resources necessary to install SQL Server
2005.
•
Install
SQL Server 2005.
•
Manage
and configure a SQL Server 2005 installation.
Module 15: Managing Databases and Files
This module explains how to
manage databases and files.
Lessons
•
Planning
Databases
•
Creating
Databases
•
Managing
Databases
Lab : Managing Databases and Files
•
Creating
a Database
•
Monitoring
and Managing Filegroup Usage
•
Viewing
Database Metadata
After completing this module,
students will be able to:
•
Plan how
to implement a database that meets an organization's requirements.
•
Create a
SQL Server database.
•
Manage a
SQL Server database.
Module 16: Disaster Recovery
This module explains how to plan
and implement a backup and restore strategy.
Lessons
•
Planning
a Backup Strategy
•
Backing
up User Databases
•
Restoring
User Databases
•
Performing
Online Restore Operations
•
Recovering
Data from Database Snapshots
•
System
Database and Disaster Recovery
Lab : Disaster Recovery
•
Implementing
a Backup Strategy
•
Restoring
and Recovering a Database
•
Performing
Piecemeal Backup and Restore Operations
•
Restoring
the master Database
After completing this module,
students will be able to:
•
Plan a
backup strategy for a database.
•
Back up
user databases.
•
Restore
user databases from backups.
•
Restore
data in a user database while it is online.
•
Recover
data for a user database from a database snapshot.
•
Restore
and recover systems databases.
Module 17: Managing Security
This module explains how to
manage principals, securables, and permissions, and how to implement
cryptography in a SQL Server database.
Lessons
•
Overview
of SQL Server Security
•
Protecting
the Server Scope
•
Protecting
the Database Scope
•
Managing
Keys and Certificates in SQL Server
Lab : Managing Security
•
Creating
Logins and Assigning Server-Scope Permissions
•
Creating
and Managing Users
•
Using a
Certificate to Protect Data
After completing this module,
students will be able to:
•
Describe
how SQL Server manages security.
•
Protect
SQL Server at the server level.
•
Protect
SQL Server databases.
•
Use keys
and certificates to protect SQL Server objects.
Module18: Monitoring SQL Server
This module explains how to
monitor SQL Server performance and activity.
Lessons
•
Viewing
Current Activity
•
Using
System Monitor
•
Using
SQL Server Profiler
•
Using
DDL Triggers
•
Using
Event Notifications
Lab : Monitoring SQL Server
•
Monitoring
SQL Server Performance
•
Tracing
SQL Server Activity
•
Implementing
DDL Triggers
After completing this module,
students will be able to:
•
Examine
the current activity in a SQL Server instance.
•
Use
System Monitor to obtain performance data about your computer and the instances
of SQL Server running on your computer.
•
Use SQL
Server Profiler to trace server and database activity.
•
Implement
DDL triggers that enable you to audit changes to the structure of database
objects.
•
Use
event notifications to capture and monitor significant events for a SQL Server
instance.
Module 19: Transferring Data
This module explains how to
transfer and transform data.
Lessons
•
Overview
of Data Transfer
•
Introduction
to SQL Server Integration Services
•
Using
SQL Server Integration Services
•
Features
of SQL Server Integration Services
Lab : Transferring Data
•
Creating
an SSIS Package
•
Deploying
an SSIS Package
•
Using
SSIS to Extract Data, Perform Lookups, Sort, and Split Data
After completing this module,
students will be able to:
•
Describe
the problems surrounding data transfer and the tools that SQL Server 2005
provides to perform data transfer.
•
Describe
the purpose of SQL Server Integration Services.
•
Use SQL
Server Integration Services to transfer data into a SQL Server database.
•
Describe
the features of SQL Server Integration Services.
Module 20: Automating Administrative Tasks
This module explains how to use
the SQL Server Agent to automate administrative tasks.
Lessons
•
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
Lab : Automating Administrative Tasks
•
Configuring
SQL Server Agent
•
Creating
Operators and Jobs
•
Creating
Alerts
After completing this module,
students will be able to:
•
Define
SQL Server 2005 administrative tasks and schedule these tasks to run
automatically.
•
Configure
SQL Server Agent to support automatic task scheduling.
•
Script
tasks by using SQL Server jobs, and define operators for managing these jobs.
•
Define
alerts to warn operators about events raised by SQL Server.
•
Define
and manage administrative tasks that span multiple servers.
•
Configure
SQL Server Agent security.
Module 21: Implementing Replication
This module explains the purpose
of replication, introduces the concepts underpinning replication, and describes
how to implement replication in several common scenarios.
Lessons
•
Overview
of Replication
•
Implementing
Replication
•
Configuring
Replication in Some Common Scenarios
Lab : Implementing Replication
•
Creating
a Publication
•
Creating
a Subscription
•
Implementing
HTTP Merge Replication
After completing this module,
students will be able to:
•
Describe
replication and its components.
•
Configure
and implement replication.
•
Use
replication to meet the requirements of some common scenarios.
Module 22: Maintaining High Availability
This module explains how to
implement high availability technologies with SQL Server 2005.
Lessons
•
Introduction
to High Availability
•
Implementing
Server Clustering
•
Implementing
Database Mirroring
•
Implementing
Log Shipping
•
Implementing
Peer-to-Peer Replication
Lab : Maintaining High Availability
•
Configuring
Database Mirroring to Support Failover
•
Implementing
Distributed High Availability
After completing this module,
students will be able to:
•
Describe
the factors affecting database availability.
•
Explain
how to implement clustering to support fast failover of computers running
Microsoft SQL Server instances.
•
Describe
how to use SQL Server mirroring to implement a software solution for fast
failover.
•
Describe
how to implement log shipping to support fast recovery of a standby SQL Server
database.
•
Explain
how to use peer-to-peer replication to implement high availability in a
distributed environment.
