Maintaining a Microsoft SQL Server 2005 Database

Code: 2780
Course duration: 5 days
Price:

Maintaining a Microsoft SQL Server 2005 Database

Course 2780b: Five Days; Instructor-Led

About this Course

Elements of this syllabus are subject to change.

This five-day instructor-led course provides students with the knowledge and skills 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 Profile

This course is intended for IT Professionals who want 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.

At Course Completion

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.

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 2005
  • Managing a SQL Server 2005 Installation

Lab : Installing and Configuring SQL Server 2005

  • Performing an Installation
  • Managing SQL Server

After completing this module, students will be able to:

  • Explain how to prepare the hardware and other resources necessary to install SQL Server 2005.
  • Install SQL Server 2005.
  • Manage and configure a SQL Server 2005 installation.

Module 2: Managing Databases and Files
This module explains how to manage databases and files.
Lessons

  • Planning Databases
  • Creating Databases
  • Managing Databases

Lab : Managing Databases and Files

  • Creating a Database
  • Monitoring and Managing Filegroup Usage
  • Viewing Database Metadata

After completing this module, students will be able to:

  • Plan how to implement a database that meets an organization's requirements.
  • Create a SQL Server database.
  • Manage a SQL Server database.

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 : Disaster Recovery

  • Implementing a Backup Strategy
  • Restoring and Recovering a Database
  • Performing Piecemeal Backup and Restore Operations
  • Restoring the master Database

After completing this module, students will be able to:

  • Plan a backup strategy for a database.
  • Back up user databases.
  • Restore user databases from backups.
  • Restore data in a user database while it is online.
  • Recover data for a user database from a database snapshot.
  • Restore and recover systems 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
  • Protecting the Server Scope
  • Protecting the Database Scope
  • Managing Keys and Certificates in SQL Server

Lab : Managing Security

  • Creating Logins and Assigning Server-Scope Permissions
  • Creating and Managing Users
  • Using a Certificate to Protect Data

After completing this module, students will be able to:

  • Describe how SQL Server manages security.
  • Protect SQL Server at the server level.
  • Protect SQL Server databases.
  • Use keys and certificates to protect SQL Server objects.

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 : Monitoring SQL Server

  • Monitoring SQL Server Performance
  • Tracing SQL Server Activity
  • Implementing DDL Triggers

After completing this module, students will be able to:

  • Examine the current activity in a SQL Server instance.
  • Use System Monitor to obtain performance data about your computer and the instances of SQL Server running on your computer.
  • Use SQL Server Profiler to trace server and database activity.
  • Implement DDL triggers that enable you to audit changes to the structure of database objects.
  • Use event notifications to capture and monitor significant events for a SQL Server instance.

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 : Transferring Data

  • Creating an SSIS Package
  • Deploying an SSIS Package
  • Using SSIS to Extract Data, Perform Lookups, Sort, and Split Data

After completing this module, students will be able to:

  • Describe the problems surrounding data transfer and the tools that SQL Server 2005 provides to perform data transfer.
  • Describe the purpose of SQL Server Integration Services.
  • Use SQL Server Integration Services to transfer data into a SQL Server database.
  • Describe the features of SQL Server Integration Services.

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 : Automating Administrative Tasks

  • Configuring SQL Server Agent
  • Creating Operators and Jobs
  • Creating Alerts

After completing this module, students will be able to:

  • Define SQL Server 2005 administrative tasks and schedule these tasks to run automatically.
  • Configure SQL Server Agent to support automatic task scheduling.
  • Script tasks by using SQL Server jobs, and define operators for managing these jobs.
  • Define alerts to warn operators about events raised by SQL Server.
  • Define and manage administrative tasks that span multiple servers.
  • Configure 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 : 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 : Maintaining High Availability

  • Configuring Database Mirroring to Support Failover
  • Implementing Distributed High Availability

After completing this module, students will be able to:

  • Describe the factors affecting database availability.
  • Explain how to implement clustering to support fast failover of computers running Microsoft SQL Server instances.
  • Describe how to use SQL Server mirroring to implement a software solution for fast failover.
  • Describe how to implement log shipping to support fast recovery of a standby SQL Server database.
  • Explain how to use peer-to-peer replication to implement high availability in a distributed environment.

Course Reviews

No reviews found for this course.

Be the first to write a review