Maintaining a Microsoft SQL Server 2005 Database
Course Overview
This three-day instructor-led course provides students with product knowledge and skills needed to maintain a Microsoft SQL ServerT 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.
Skills Gained
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.
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 2005 Transact-SQL.
- Course 2779: Implementing a Microsoft SQL Server 2005 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.
- Managing Filegroups.
- Viewing Metadata.
After completing this module, students will be able to:
- Plan databases.
- Create databases.
- Manage databases.
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.
After completing this module, students will be able to:
- Plan a backup strategy.
- Back up user databases.
- Restore user databases.
- Recover data from database snapshots.
- Back up and restore system databases.
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.
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 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.
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.
- Using SQL Server Integration Services.
Lab 6: Transferring Data with SQL Server Integration Services (SSIS)
- 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 SSIS.
- 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.
- 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.
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
- Setting the Recovery Model.
- Backing Up and Restoring the Database.
- Starting Database Mirroring.
- Performing 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.
- Replication Scenarios.
Lab 9: Implementing Replication
- Creating a Publication.
- Creating a Subscription.
After completing this module, students will be able to:
- Describe replication and its components.
- Describe common replication scenarios.
