2780 - Maintaining SQL Server 2005 Database Training Course
Course Summary
This five-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.Duration
5 days.
Objectives
After completing this course, students will be able to:
- Install and configure SQL Server 2005.
- Manage database files.
- Manage security.
- Perform administrative tasks.
- Back up databases.
- Restore databases.
- Monitor SQL Server.
- Troubleshoot SQL Server.
- Transfer data.
- Maintain high availability.
Audience
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.
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.
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.
• Managing Filegroups.
• Viewing Metadata.
Module 3: 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.
Module 4: 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.
• Managing Keys and Certificates in SQL Server.
Lab 3: Securing SQL Server
• Creating Logins.
• Creating and Managing Users.
• Using a Certificate to Encrypt Data.
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.
• Tracing SQL Server Activity.
• Implementing DDL Trigger.
Module 6: 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.
Lab 6: Transferring Data with SQL Server Integration Services (SSIS)
• Create an SSIS Package.
• Deploying an SSIS Package.
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.
• Creating 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.
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
• Setting the Recovery Model.
• Backing Up and Restoring the Database.
• Starting Database Mirroring.
• Performing an Automatic and Manual Failover.
Module 9: Introduction to Replication
This module explains considerations for implementing replication.
Lessons
• Overview of Replication.
• Replication Scenarios.
Lab 9: Implementing Replication
• Creating a Publication.
• Creating a Subscription.
