Course Outline
Module 1: Installing and Configuring SQL Server
The students will be introduced
to planning for a SQL Server installation. The students will then be introduced
to installing, configuring, and managing SQL Server.
Lessons
•
Preparing
to Install SQL Server
•
Installing
SQL Server
•
Configuring
a SQL Server Installation
Lab : Installing and Configuring SQL Server
•
Installing
SQL Server
•
Configuring
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.
•
Install
SQL Server.
•
Manage
and configure SQL Server.
Module 2: Managing Databases and Files
The students will be introduced
to database planning and creation, and using database options to control
database behavior.
Lessons
•
Planning
Databases
•
Creating
Databases
•
Using
Policy-Based Management
Lab : Managing Databases and Files
•
Creating
a Database
•
Monitoring
and Managing Filegroup Usage
•
Creating
a Policy
After completing this module,
students will be able to:
•
Plan a
database implementation that meets an organization's requirements.
•
Create a
SQL Server database.
•
Manage a
SQL Server database.
Module 3: Disaster Recovery
The students will be introduced
to disaster recovery techniques for SQL Server. They will learn how to perform
different types of backup and restore operations, including online restores and
backup and restores of system databases.
Lessons
•
Planning
a Database Backup Strategy
•
Backing
up a User Database
•
Restoring
User Databases
•
Performing
Online Restore Operations
•
Recovering
Data from Database Snapshots
•
System Databases
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 system databases.
Module 4: Managing Security
The students will be introduced
to protecting SQL Server. Students will learn about the SQL Server security
model and how to use SQL Server security features to control access to
databases and their contents.
Lessons
•
Overview
of SQL Server Security
•
Protecting
the Server Scope
•
Protecting
the Database Scope
•
Managing
Keys and Certificates
•
Auditing
Security
Lab : Managing Security
•
Creating
Logins and Assigning Server-Scope Permissions
•
Creating
Database Users and Assigning Database-Scope and Schema-Scope Permissions
•
Encrypting
Data Using Certificates and Keys
•
Implementing
SQL Server Audit
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.
•
Audit
SQL Server security.
Module 5: Transferring Data
The students will be introduced
to transfer data to and from SQL Server using UI and command-line tools, and
learn about transferring and transforming data with SQL Server Integration
Services.
Lessons
•
Overview
of Data Transfer
•
Introduction
to SQL Server Integration Services
Lab : Transferring Data
•
Using
the Import/Export Wizard
•
Performing
a Bulk Load
•
Creating
an SSIS Solution
After completing this module,
students will be able to:
•
Use UI
and command-line tools to import and export data.
•
Describe
the features of SQL Server Integration Services.
Module 6: Automating Administrative Tasks
The students will learn how to
automate routine administrative tasks using jobs, operators, and alerts.
Lessons
•
Automating
Administrative Tasks in SQL Server
•
Using
SQL Server Agent
•
Creating
Maintenance Plans
•
Implementing
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 administrative tasks and schedule those 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 those 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 7: Implementing Replication
The students will be introduced
to techniques for configuring SQL Server replication.
Lessons
•
Overview
of Replication
•
Managing
Publications and Subscriptions
•
Configuring
Replication in Some Common Scenarios
Lab : Implementing Replication
•
Implementing
Snapshot Replication
•
Implementing
Peer-to-Peer Transactional Replication
•
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 8: Maintaining High Availability
The students will be introduced to
concepts and methods for maintaining high availability with SQL Server.
Lessons
•
Introduction
to High Availability
•
Implementing
Log Shipping
•
Implementing
Database Mirroring
•
Implementing
Server Clustering
•
Using
Distributed High Availability Solutions
Lab : Maintaining High Availability
•
Configuring
Log Shipping
•
Configuring
Database Mirroring
After completing this module,
students will be able to:
•
Describe
the factors affecting database availability.
•
Describe
how to implement log shipping to support fast recovery of a standby SQL Server
database.
•
Describe
how to use SQL Server mirroring to implement a software solution for fast
failover.
•
Explain
how to implement clustering to support fast failover of computers running SQL
Server instances.
•
Describe
how to implement distributed high availability solutions.
Module 9: Monitoring SQL Server
The students will be introduced
to monitoring SQL Server performance and activity.
Lessons
•
Viewing
Current Activity
•
Using
SQL Server Profiler
•
Monitoring
with DDL Triggers
•
Using
Event Notifications
Lab : Monitoring SQL Server
•
Monitoring
SQL Server Activity
•
Tracing
SQL Server Activity
•
Using
DDL Triggers
•
Using
Event Notifications
After completing this module,
students will be able to:
•
Examine the
current activity in a SQL Server instance.
•
Use SQL
Server Profiler to trace server and database activity.
•
Use DDL
triggers to monitor changes to the structure of database objects.
•
Use
event notifications to capture and monitor significant events for a SQL Server
instance.
Module 10: Troubleshooting and Performance Tuning
The students will learn how to
troubleshoot a variety of common SQL Server problems. Students will also learn
how to tune SQL Server for improved performance using a variety of tools.
Lessons
•
Troubleshooting
SQL Server
•
Performance
Tuning in SQL Server
•
Using
Resource Governor
•
Using
Data Collector
Lab : Troubleshooting and Performance Tuning
•
Troubleshooting
Connectivity Problems
•
Troubleshooting
Concurrency Problems
•
Using
the Database Engine Tuning Advisor
•
Implementing
Resource Governor
•
Implementing
Data Collector
After completing this module,
students will be able to:
•
Troubleshoot
common SQL Server problems, such as connectivity, concurrency, and job and disk
space problems.
•
Perform
basic performance tuning tasks in SQL Server using the Database Engine Tuning
Advisor, index tuning, and query tuning.
•
Use
Resource Governor to manage SQL Server workloads and resources.
•
Use Data
Collector to obtain performance data about your computer and the instances of
SQL Server running on your computer.
Module 11: Creating Databases and Database Files
The students will learn one of
the most fundamental tasks that a database developer must perform, the creation
of a database and its major components, such as creating databases, setting
database options, creating 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 course,
students will be able to:
•
Create
databases
•
Create
filegroups
•
Create
schemas
•
Create
database snapshots
Module 12: Creating Data Types and Tables
The students will learn about the
system-supplied data types in SQL Server 2008. They will learn how to define
custom Transact-SQL data types and how to create tables and how to use
partitioned tables to organize data into multiple partitions.
Lessons
•
Creating
Data Types
•
Creating
Tables
•
Creating
Partitioned Tables
Lab : Creating Data Types and Tables
•
Creating
Data Types
•
Using
New Date and Time Data Types
•
Creating
Tables
•
Creating
Partitioned Tables
After completing this course, students
will be able to:
•
Create
new data types.
•
Create
new tables.
•
Create
partitioned tables.
Module 13: Creating and Tuning Indexes
The students will learn how to
plan, create, and optimize indexes to attain optimal performance benefits.
Lessons
•
Planning
Indexes
•
Creating
Indexes
•
Optimizing
Indexes
Lab : Creating and Tuning Indexes
•
Creating
Indexes
•
Optimizing
Indexes
After completing this course,
students will be able to:
•
Plan
indexes.
•
Create
indexes.
•
Optimize
indexes.
Module 14: Implementing Data Integrity by Using Constraints and
Triggers
The students will learn about
implementing data integrity in SQL Server 2008 by using constraints. They will
also implement data integrity by using triggers.
Lessons
•
Data
Integrity Overview
•
Implementing
Constraints
•
Implementing
Triggers
Lab : Implementing Data Integrity by Using Constraints and Triggers
•
Creating
Constraints
•
Disabling
Constraints
•
Creating
Triggers
After completing this course,
students will be able to:
•
Describe
the options for enforcing data integrity in SQL Server 2008.
•
Implement
data integrity in SQL Server 2008 databases by using constraints.
•
Implement
data integrity in SQL Server 2008 databases by using triggers.
Module 15: Using XML
The students will learn how to
work with XML, including use of the FOR XML clause, the OPENXML function,
XQuery expressions, and the xml native data type. They will learn the
considerations to be taken into account when creating XML indexes and the
syntax used to create the XML indexes. They will also learn what XML schemas
and XML schema collections are as well as how to use them to implement typed
XML data.
Lessons
•
Using
the XML Data Type
•
Retrieving
XML by Using FOR XML
•
Shredding
XML by Using OPENXML
•
Introducing
XQuery
•
Creating
XML Indexes
•
Implementing
XML Schemas
Lab : Using XML
•
Mapping
Relational Data and XML
•
Storing
XML Natively in the Database
•
Using
XQuery with XML Methods
•
Creating
XML Indexes
After completing this course,
students will be able to:
•
Use the
xml data type.
•
Retrieve
XML by using the FOR XML clause.
•
Shred
XML by using the OPENXML function.
•
Use
XQuery expressions.
•
Create
XML indexes.
•
Implement
data integrity in SQL Server 2008 databases by using XML schemas.
Module 16: Implementing Views
The students will be introduced
to the different types of views available in Microsoft SQL Server 2008 which
provide a convenient way to access data through a predefined query.
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 course,
students will be able to:
•
Describe
the purpose of views.
•
Create
and manage views.
•
Explain
how to optimize query performance by using views.
Module 17: Implementing Stored Procedures
The students will learn the
design and implementation of stored procedures to enforce business rules or
data consistency, or to modify and maintain existing stored procedures written
by other developers.
Lessons
•
Using
Stored Procedures
•
Creating
Parameterized Stored Procedures
•
Working
with Execution Plans
•
Handling
Exceptions
Lab : Implementing Stored Procedures
•
Creating
Stored Procedures
•
Working
with Execution Plans
After completing this course,
students will be able to:
•
Implement
stored procedures.
•
Create
parameterized stored procedures.
•
Work
with execution plans.
•
Handle
errors in stored procedures.
Module 18: Implementing Functions
The students will learn the design
and implementation of user-defined functions that enforce business rules or
data consistency, or to modify and maintain existing functions written by other
developers.
Lessons
•
Introducing
Functions
•
Working
with Functions
•
Controlling
Execution Context
Lab : Implementing Functions
•
Creating
Functions
•
Controlling
Execution Context
After completing this course,
students will be able to:
•
Create
and use functions.
•
Work
with functions.
•
Control
execution context.
Module 19: Implementing Managed Code in the Database
The students will learn to use
managed code to implement database objects, such as stored procedures,
user-defined data types, user-defined functions, and triggers.
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 course,
students will be able to:
•
Identify
appropriate scenarios for managed code in the database.
•
Import
and configure assemblies.
•
Create
managed database objects.
Module 20: Managing Transactions and Locks
The students will learn to use
transactions and SQL Server locking mechanisms to meet the performance and data
integrity requirements of their 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 course,
students will be able to:
•
Describe
how SQL Server 2008 transactions use locks.
•
Execute
and cancel a transaction.
•
Describe
concurrency issues and SQL Server 2008 locking mechanisms.
•
Manage
locks.
Module 21: Using Service Broker
The students will learn to use
Service Broker to create secure, reliable, and scalable applications.
Lessons
•
Service
Broker Overview
•
Creating
Service Broker Objects
•
Sending
and Receiving Messages
Lab : Using Service Broker
•
Creating
Service Broker Objects
•
Implementing
the Initiating Service
•
Implementing
the Target Service
After completing this course,
students will be able to:
•
Describe
Service Broker functionality and architecture.
•
Create
Service Broker objects.
•
Send and
receive Service Broker messages.
