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