Course 2785AL: Managing and Maintaining Microsoft SQL Server™ 2005
Instructor Led via Microsoft Official Distance Learning
(MODL)
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.
This course is intended for IT Professionals wanting to
become skilled on SQL Server 2005 product features and technologies for
maintaining a database.
After completing this course, students will be able to:
·
Install and configure SQL Server 2005.
·
Create databases and database files.
·
Create data types and tables.
·
Generate basic SQL Server 2005 queries and XML
reports.
·
Manage SQL security.
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.
·
Backup and restore user databases.
·
Recover system database in the event of a
disaster.
·
Implement data integrity for a database by using
the constraints, triggers, and XML schema.
·
Implement basic stored procedures and functions.
·
Mirror SQL databases.
Before attending this course, students must have:
·
Basic knowledge of the Microsoft Windows
operating system and its core functionality.
·
Working knowledge of Transact-SQL.
·
Working knowledge of relational databases.
·
Some experience with database design.
·
Working knowledge of XML
This course will help the student prepare for the
following Microsoft Certified Professional exam:
·
Exam 70-431: TS: Microsoft SQL Server 2005 -
Implementation and Maintenance:
The student kit includes a comprehensive workbook and other
necessary materials for this class.
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.
·
SQL Server 2005 Architecture
·
Preparing to Install SQL Server 2005
·
Installing SQL Server 2005
·
Installing and Configuring Administrative Tools
·
SQL Server 2005 Conformation Tools
After completing this module, students will be able to:
·
Describe the architecture and components of SQL
Server 2005.
·
Describe how to prepare for a SQL Server
installation.
·
Install SQL Server 2005.
·
Manage a SQL Server installation.
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.
·
Planning Data Storage for SQL Server 2005
·
Creating Databases and File Storage
·
Understanding Name Resolution and Creating
Schemas
·
Creating Database Snapshots
·
Managing Databases
After completing this module, students will be able to:
·
Plan the creation of databases.
·
Create databases.
·
Create schemas
·
Manage databases.
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.
·
What Are Data Types?
·
Creating Data Types
·
Creating Tables
·
Creating Partitioned Tables
After completing this module, students will be able to:
·
Describe data types.
·
Create data types and tables.
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.
·
What is T-SQL?
·
Retrieving Data
·
Grouping and Summarizing Data
·
Modifying Data
·
Working with XML Data
After completing this module, students will be able to:
·
Explain printing in the Windows Server 2003
family.
·
Retrieve data from tables by using the SELECT
statement.
·
Use the GROUP BY clause to filter data.
·
Write INSERT, DELETE, and UPDATE statements to
modify data in tables.
·
Organize summary data for a column by using
aggregate functions with the GROUP BY and HAVING clauses.
· Generate XML-based reports.
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.
·
SQL Server 2005 Security Overview
·
Managing SQL Server 2005 Security
·
Monitoring SQL Server 2005
·
Automating SQL Server 2005 Administration
After completing this module, students will be able to:
·
Describe the security architecture in SQL Server
2005.
·
Monitor SQL Server 2005 performance and
activity.
·
Automate tasks by creating jobs, operators, and
alerts.
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.
·
Planning a Backup Strategy
·
Backing Up User Databases
·
Restoring User Databases
·
Recovering Data from Database Snapshots
·
System Databases and Disaster Recovery
After completing this module, students will be able to:
·
Plan a backup strategy.
·
Back up and restore user databases.
·
Recover data from database snapshots.
·
Recover system databases.
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.
·
Planning Indexes
·
Creating Indexes
·
Optimizing Indexes
·
Creating XML Indexes
After completing this module, students will be able to:
·
Plan, create, and optimize SQL indexes.
·
Create XML indexes.
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 an
opportunity to discuss the powerful data integrity features of SQL Server 2005
- constraints, triggers, and XML schemas.
·
Data Integrity Overview
·
Implementing Constraints
·
Implementing Triggers
·
Implementing XML Schemas
After completing this module, students will be able to:
·
Implement data integrity for a database by using
the constraints, triggers, and XML schema features of 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, 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.
·
Implementing Views
·
Implementing Stored Procedures
·
Implementing User Defined Functions
·
Introducing Database Programming Objects
After completing this module, students will be able to:
·
Describe the types and uses of views.
·
Create and manage views.
·
Implement stored procedure
·
Create parameterized stored procedures.
·
Implement functions
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.
·
Data Availability for Data Consumers
·
Tools for Transferring and Transforming Data
·
Replicating Data
·
Mirroring Data
·
Exposing Data by Using SQL Server 2005 Service
Broker
After completing this module, students will be able to:
·
Identify ways to improve data availability.
·
Transfer and transform data by using SSIS.
·
Describe common replication scenarios.
·
Implement database mirroring.
·
Send and receive Service Broker messages.