Course Length
5.0 Days

Description
This instructor-led course provides you with the knowledge and skills to maintain a Microsoft SQL Server 2008 R2 database. You will learn to use SQL Server 2008 R2 product features and tools to install and configure a database, manage database files, and create and tune indexes.

This course incorporates materials from the Official Microsoft Learning Product 6231: Maintaining a Microsoft SQL Server 2008 R2 Database.

Audience
IT professionals who administer and maintain SQL Server databases
Individuals who develop applications that deliver content from SQL Server databases

Prerequisites

• 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

Learning Objectives

• Plan, install, and configure SQL Server 2008 R2
• Manage database files
• Back up and restore databases
• Manage security
• Implement SQL Server 2008 R2 audits
• Configure and assign permissions
• Monitor data by using dynamic management views
• Manage SQL Server 2008 R2 agent and jobs
• Execute multi-server queries and configure a central management server
• Deploy a data-tier-application
• Troubleshoot common issues

Module 1 - Installing and Configuring SQL Server

1.Explain how to prepare the hardware and other resources necessary to install SQL Server.
2.Install SQL Server.
3.Manage and configure SQL Server.

Module 2 - Managing Databases and Files

1.Plan a database implementation that meets an organization's requirements.
2.Create a SQL Server database.
3.Manage a SQL Server database.

Module 3 - Disaster Recovery

1.Plan a backup strategy for a database.
2.Back up user databases.
3.Restore user databases from backups.
4.Restore data in a user database while it is online.
5.Recover data for a user database from a database snapshot.
6.Restore and recover system databases.

Module 4 - Managing Security

1.Describe how SQL Server manages security.
2.Protect SQL Server at the server level.
3.Protect SQL Server databases.
4.Use keys and certificates to protect SQL Server objects.
5.Audit SQL Server security.

Module 5 - Transferring Data

1.Use UI and command-line tools to import and export data.
2.Describe the features of SQL Server Integration Services.

Module 6 - Automating Administrative Tasks

1.Define SQL Server administrative tasks and schedule those tasks to run automatically.
2.Configure SQL Server Agent to support automatic task scheduling.
3.Script tasks by using SQL Server jobs, and define operators for managing those jobs.
4.Define alerts to warn operators about events raised by SQL Server.
5.Define and manage administrative tasks that span multiple servers.
6.Configure SQL Server Agent security.

Module 7 - Implementing Replication

1.Describe replication and its components.
2.Configure and implement replication.
3.Use replication to meet the requirements of some common scenarios.

Module 8 - Maintaining High Availability

1.Describe the factors affecting database availability.
2.Describe how to implement log shipping to support fast recovery of a standby SQL Server database
3.Describe how to use SQL Server mirroring to implement a software solution for fast failover
4.Explain how to implement clustering to support fast failover of computers running SQL Server instances.
5.Describe how to implement distributed high availability solutions.

Module 9 - Monitoring SQL Server

1.Examine the current activity in a SQL Server instance.
2.Use SQL Server Profiler to trace server and database activity.
3.Use DDL triggers to monitor changes to the structure of database objects.
4.Use event notifications to capture and monitor significant events for a SQL Server instance.

Module 10 - Troubleshooting and Performance Tuning

1.Troubleshoot common SQL Server problems, such as connectivity, concurrency, and job and disk space problems.
2.Perform basic performance tuning tasks in SQL Server using the Database Engine Tuning Advisor, index tuning, and query tuning.
3.Use Resource Governor to manage SQL Server workloads and resources.
4.Use Data Collector to obtain performance data about your computer and the instances of SQL Server running on your computer.