Course 2780: Five day; Instructor-Led
Sign Up Now
Introduction
This five-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.
At Course Completion
After completing this course, students will be able to:
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. |
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 2005 |
| • |
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:
| • |
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 2: 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 3: 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 4: 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 5: 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 6: 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 7: 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 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:
| • |
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. |
|
