SitesPower Training Center 306 Montana Bldg, Zabeel Rd, Karama
Tel: 335-5549
Email: learn@sitespower.com
Course
2780: Maintaining a Microsoft SQL Server 2005 Database
5 day Instructor-Led Course (40 hours)
COURSE USUALLY PROVIDED AS 3 DAY WORKSHOP / BOOTCAMP
Course Overview
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 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:
• 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.
SitesPower
Training Center
Dubai Head Office :
+9714-335-5549 | Sharjah Institute : +9716-575-1315
