|
Course Overview:
This three-day instructor-led course provides students with the
knowledge and skills to upgrade their skills to SQL Server 2005 so
that they can support, configure, and maintain enterprise SQL Server
2005 databases and servers.
Audience:
This course is intended for experienced database administrators
and database developers who are responsible for the supporting,
configuring, and maintaining enterprise SQL Server 2005
databases and servers.
At Course Completion
After completing this course, students will be able to:
Describe the architecture of SQL Server 2005.
Install SQL Server 2005.
Use SQL Server 2005 administrative tools and utilities.
Maintain SQL Server 2005 servers and databases.
Secure a SQL Server 2005 server and database.
Monitor SQL Server 2005.
Implement a Backup and Restore solution for SQL Server 2005.
Implement a data availability solution with database mirroring
or clustering.
Use enhanced SQL Server 2005 replication features.
Prerequisites
Before attending this course, students must have:
-
MCDBA certification or equivalent knowledge
-
Administration experience with Microsoft SQL Server 2000 or MS
SQL Server 7.0
-
Knowledge of SQL Server 2000/7.0
-
Database and server architecture
-
Installation and configuration
-
Database security
-
Database recovery (backup and restore)
-
SQL Server technologies used for database availability such as
log shipping and clustering
-
Job automation
-
Database and server monitoring
-
Troubleshooting
-
Replication
In addition, it is recommended, but not required, that students
have completed:
• Course 2072, Administering a Microsoft SQL Server 2000
Database.
Course Materials -
The student kit includes a comprehensive workbook and other
necessary materials for this class.
Course Outline
Module 1: Overview of SQL Server 2005 Architecture
This module introduces students to the various components and
services that comprise SQL Server. Database administrators are often required to manage multiple
aspects of the database server – not just the relational
database engine, so an awareness of the components and
functionality available in SQL Server 2005 is essential in order
to prepare database administrators for SQL Server 2005
deployments and management.
Lessons
Overview of SQL Server 2005
System Enhancements for Administrators
After completing this module, students will be able to:
Describe the components of SQL Server 2005.
Describe new and enhanced features for administrators in SQL
Server 2005.
Module 2: Installing SQL Server 2005
This module explains how to plan for a SQL Server 2005
installation, install SQL Server 2005 and maintain a SQL Server
2005 installation.
Lessons
Preparing to Install SQL Server 2005
Performing a SQL Server 2005 Installation
Maintaining a SQL Server 2005 Installation
Lab 2: Installing SQL Server 2005
Installing a SQL Server Instance.
After completing this module, students will be able to:
Plan a SQL Server 2005 installation.
Install SQL Server 2005.
Maintain a SQL Server 2005 installation.
Module 3: SQL Server 2005 Administrative Tools
This module explains what built-in administration tools and
utilities are available in Microsoft® SQL Server™ 2005 and how
they can be used to help database administrators be successful
in managing a SQL Server 2005 solution.
Lessons
Using SQL Server Management Studio
Using SQL Server Computer Manager
Using the sqlcmd Utility
Using the SQL Management Objects
Lab 3: Administering SQL Server 2005
Creating the Personnel database.
Populating the Personnel database.
Backing Up the Personnel Database.
After completing this module, students will be able to:
Choose the appropriate tool or utility to perform an
administrative task.
Use SQL Server Management Studio to manage databases.
Use SQL Server Computer Manager to manage a server.
Use the sqlcmd utility to execute Transact-SQL from the command
prompt.
Use SQL Management Objects to script common administrative
tasks.
Module 4: Monitoring SQL Server 2005
This module discusses the various tools and techniques you
can use to monitor Microsoft® SQL Server™ 2005.
Lessons
Using SQL Profiler
Using DDL Triggers
Using Event Notifications
Using Metadata Views
Lab 4: Monitoring SQL Server 2005
Creating DDL Triggers
Creating Event Notifications
After completing this module, students will be able to:
Use SQL Profiler to monitor SQL Server 2005.
Use DDL triggers to respond to DDL events.
Use event notifications to respond to database events.
Query SQL Server metadata.
Module 5: Maintaining Databases and Indexes
This module explains the tools and utilities in Microsoft®
SQL Server™ 2005 that can help you implement database
maintenance plans, manage database indexes, and tune databases.
Lessons
Managing Indexes
Using the Database Tuning Advisor
Lab 5: Maintaining a Database
Defining Indexes
Tuning a Database After completing this module, students will be able to:
-
Maintain indexes in a SQL Server 2005 database.
-
Use the Database Tuning Advisor to assist with database design.
-
Maintain SQL Server 2005 databases.
Module 6: Securing SQL Server 2005
This module explains the changes and enhancements to the
Microsoft® SQL Server™ security model in SQL Server 2005.
Lessons
SQL Server 2005 Security Overview
Managing SQL Server 2005 Security
Managing Permissions
Managing Certificates
Lab 6: Securing a SQL Server 2005 Database
Managing securables
Managing principals
Managing Permissions
Dropping a User that Created Objects.
After completing this module, students will be able to:
Describe the security architecture of SQL Server 2005.
Manage securables and principals.
Manage permissions.
Use certificates in SQL Server 2005.
Module 7: Disaster Recovery
This module you will learn about the enhanced backup and
restore functionality in Microsoft® SQL Server™ 2005.
Lessons
Disaster Recovery in SQL Server 2005
Using Database Snapshots
Backup and Restore Operations
Lab 7: Managing Disaster Recovery
Creating a Database Snapshot
Backing up a Database
After completing this module, students will be able to:
Describe common disaster recovery enhancements in SQL Server
2005.
Use database snapshots.
Perform backup and restore operations.
Module 8: Data Availability in SQL Server 2005
This module teaches you how to take advantage of the high
availability features found in SQL Server 2005
Lessons
Introduction to Data Availability
Introduction to Database Mirroring
Configuring Database Mirroring
Lab 8: Maintaining Availability
Preparing the Principal and Mirror Server
Establishing a Mirroring Session
Establishing a Witness Server
Executing Transactions
Simulating Principal Server Failure
Restarting the Failed Server
Terminating the Mirror Session
After completing this module, students will be able to:
Describe the data availability technologies included in SQL
Server 2005.
Describe the concepts of database mirroring.
Configure database mirroring.
Module 9: SQL Server 2005 Replication
This module explains the new and enhanced replication
features in SQL Server 2005
Lessons
Overview of Replication Enhancements
New Types of Replication
Configuring Replication
After completing this module, students will be able to:
Describe the enhancements to replication functionality in SQL
Server 2005.
Describe the new types of replication available in SQL Server
2005.
Configure replication using SQL Server 2005. |