2780B Maintaining a Microsoft SQL Server 2005 Database
Printer friendly

Course Overview
  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.

Objectives
  After completing this course, students will be able to:
  • Install and configure SQL Server 2005.
  • Manage database files.
  • Backup and restore databases.
  • Manage security.
  • Monitor SQL Server.
  • Transfer data into and out of SQL Server.
  • Automate administrative tasks.
  • Replicate data between SQL Server instances.
  • Maintain high availability.

Audience
  This course is intended for IT Professionals wanting to become skilled on SQL Server 2005 product features and technologies for maintaining a database.


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 Number
  MI2780B
Course Length
  5 Days
Course Price
  $2,750.00
Category
  1
Topics
  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 and Configuring 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: Managing Databases and Files
  • 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.
  • Performing Online Restore Operations
  • Recovering Data from Database Snapshots.
  • System Database and Disaster Recovery.
Lab 3: Disaster Recovery
  • Implementing a Backup Strategy.
  • Restoring Databases.
  • Performing Piecemeal Backup and Restore Operations
  • Rebuild The Master Database.
After completing this module, students will be able to:
  • Plan a backup strategy.
  • Back up user databases.
  • Restore user databases.
  • Perform online restore operations.
  • 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 4: Managing Security
  • 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.
  • Features of SQL Server Integration Services
Lab 6: Transferring Data
  • Create an SSIS Package.
  • Deploying an SSIS Package
  • Using SSIS to Merge Data
  • Auditing and logging SSIS operations
After completing this module, students will be able to:
  • Describe the tools and technologies for transferring data.
  • Describe the features of SSIS.
  • Transfer and transform data with SSIS.
  • Explain how to use SSIS to integrate and merge data from multiple sources.
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 Administrative Tasks
  • 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: Implementing Replication
This module explains the purpose of replication, introduces the concepts underpinning replication, and describes how to implement replication in several common scenarios.
Lessons
  • Overview of Replication.
  • Implementing Replication
  • Configuring Replication in Some Common Scenarios. Lab 8: Implementing Replication
    • Creating a Publication.
    • Creating a Subscription.
    • Implementing HTTP Merge Replication
    After completing this module, students will be able to:
    • Describe replication and its components.
    • Configure and implement replication
    • Use replication to meet the requirements of some common scenarios.
    Module 9: 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.
    • Implementing Peer to Peer Replication
    Lab 9: Maintaining High Availability
    • Configuring Database Mirroring to Support Failover.
    • Implementing Distributed High Availability
    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.
    • Use peer-to-peer replication to implement high availability in a distributed environment
    		•