Three days; Instructor-Led
Introduction
This three-day instructor-led course provides students with product knowledge and skills needed to maintain a Microsoft SQL Server 2005 database. The course focuses on teaching individuals how to use SQL Server 2005 product features and tools related to maintaining a database.
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:
|
• |
Install and configure SQL Server 2005 |
|
• |
Manage database files. |
|
• |
Manage security. |
|
• |
Perform administrative tasks. |
|
• |
Backup databases. |
|
• |
Restore databases. |
|
• |
Monitor SQL Server. |
|
• |
Troubleshoot SQL Server. |
|
• |
Transfer data. |
|
• |
Maintain high availability. |
Prerequisites
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. |
In addition, it is recommended, but not required, that students have completed:
|
• |
Course 2778, Writing Queries Using Microsoft SQL Server Transact-SQL. |
|
• |
Course 2779, Implementing a Microsoft SQL Server Database. |
Course Outline
Module 1: 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. |
|
• |
Managing a SQL Server 2005 Installation. |
Lab 1: Installing SQL Server 2005
|
• |
Performing an Installation. |
|
• |
Managing SQL Server. |
After completing this module, students will be able to:
|
• |
Prepare to install SQL Server. |
|
• |
Install SQL Server. |
|
• |
Manage a SQL Server installation. |
Module 2: Managing Databases and Files
This module explains how to manage databases and files.
Lessons
|
• |
Planning databases. |
|
• |
Creating databases. |
|
• |
Managing databases. |
Lab 2: Creating and Managing Databases
|
• |
Creating a Database. |
|
• |
Creating Schemas. |
|
• |
Configuring a Database. |
After completing this module, students will be able to:
|
• |
Plan databases. |
|
• |
Create databases. |
|
• |
Manage databases. |
Module 3: 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. |
|
• |
Securing the Server Scope. |
|
• |
Securing the Database Scope. |
|
• |
Implementing Cryptography in SQL Server. |
Lab 3: Securing SQL Server
|
• |
Creating Logins. |
|
• |
Creating and Managing Users. |
|
• |
Implementing Cryptography. |
After completing this module, students will be able to:
|
• |
Describe the SQL Server security architecture. |
|
• |
Implement security at the server scope. |
|
• |
Implement security at the database and schema scopes. |
|
• |
Use cryptographic functionality in SQL Server. |
Module 4: 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. |
|
• |
Recovering Data from Database Snapshots. |
|
• |
System Database and Disaster Recovery. |
Lab 4: Implementing a Disaster Recovery Strategy
|
• |
Implementing a Backup Strategy. |
|
• |
Restoring Databases. |
|
• |
Rebuild the Master Database. |
After completing this module, students will be able to:
|
• |
Backup user databases. |
|
• |
Restore user databases. |
|
• |
Recover data from database snapshots. |
|
• |
Backup and restore system databases. |
Module 5: 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 5: Monitoring SQL Server
|
• |
Monitoring SQL Server Performance. |
|
• |
Monitoring SQL Server With SQL Server Profiler. |
|
• |
Implementing DDL Trigger. |
|
• |
Implementing Event Notifications. |
After completing this module, students will be able to:
|
• |
View current activity in SQL Server. |
|
• |
Monitor SQL Server performance with System Monitor. |
|
• |
Monitor SQL Server events with SQL Server Profiler. |
|
• |
Implement DDL Triggers. |
|
• |
Implement Event Notifications. |
Module 6: Transferring Data
This module explains how to transfer and transform data.
Lessons
|
• |
Overview of Data Transfer. |
|
• |
Introduction to SQL Server Integration Services (SSIS). |
|
• |
Using SQL Server Integration Services. |
Lab 6: Transferring Data with SQL Server Integration Services
|
• |
Create an SSIS Package. |
|
• |
Deploying An SSIS Package. |
After completing this module, students will be able to:
|
• |
Describe the tools and technologies for transferring data. |
|
• |
Describe the main features of SQL Server Integration Services. |
|
• |
Transfer and transform data with SSIS. |
Module 7: 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. |
|
• |
Create Alerts. |
|
• |
Managing Multiple Servers. |
|
• |
Managing SQL Server Agent Security. |
Lab 7: Automating Database Administration
|
• |
Configuring the SQL Server Agent. |
|
• |
Creating Operators and Jobs. |
|
• |
Creating Alerts. |
|
• |
Managing Multiple Servers. |
After completing this module, students will be able to:
|
• |
Describe automation options in SQL Server. |
|
• |
Configure the SQL Server Agent. |
|
• |
Create jobs and operators. |
|
• |
Create alerts. |
|
• |
Manage multiple servers. |
|
• |
Manage SQL Server Agent security. |
Module 8: 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. |
Lab 8: Configuring Database Mirroring
|
• |
Set the Recovery Model |
|
• |
Backup and Restore |
|
• |
Start Database Mirroring |
|
• |
Perform an automatic and manual failover |
After completing this module, students will be able to:
|
• |
Describe high availability options for SQL Server. |
|
• |
Implement Server Clustering. |
|
• |
Implement Database Mirroring. |
|
• |
Implement Log Shipping. |
Module 9: Introduction to Replication
This module explains considerations for implementing replication.
Lessons
|
• |
Overview of Replication. |
Lab 9: Implementing Replication
|
• |
Creating a Publication. |
|
• |
Creating A Subscription. |
After completing this module, students will be able to:
|
• |
Describe considerations for implementing replication. |