Course duration: 5 days

Cost: £1495 + Vat

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.

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

Pre requisite

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

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.