Maintaining a Microsoft SQL Server R2 Database

Course details

This instructor-led course provides students with the knowledge and skills to maintain a Microsoft SQL Server 2008 R2 database. The course focuses on teaching individuals how to use SQL Server 2008 R2 product features and tools related to maintaining a database.

Objectives

At Course Completion

After completing this course, students will be able to:

• Explain SQL Server 2008 R2 architecture, resources requirements and perform pre-checks of I/O subsystems
• Plan, install and configure SQL Server 2008 R2
• Backup and restore databases
• Import and export wizards and explain how they relate to SSIS
• Use BCP and BULK INSERT to import data
• Manage security
• Assign, configure fixed database roles and create and assign user defined database roles
• Configure and assign permissions
• Implement SQL Server 2008 R2 Audits
• Manage SQL Server 2008 R2 Agent and Jobs
• Configure database mails, alerts and notifications
• Maintain databases
• Configure SQL Profiler Traces and Use the Database Tuning Advisor
• Monitor data by using Dynamic Management Views
• Execute multi-server queries and configure a central management server
• Deploy a data-tier-application
• Troubleshoot common issues

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.
• Completed Course 2778: Writing Queries Using Microsoft SQL Server 2008 Transact-SQL

Module

• Module 1: Introduction to SQL Server 2008 R2 and its Toolset
• Module 2: Preparing Systems for SQL Server 2008 R2
• Module 3: Installing and Configuring SQL Server 2008 R2
• Module 4: Working with Databases
• Module 5: Understanding SQL Server 2008 R2 Recovery Models
• Module 6: Backup of SQL Server 2008 R2 Databases
• Module 7: Restoring SQL Server 2008 R2 DatabasesThis module describes the restoration of databases.
• Module 8: Importing and Exporting DataThis module covers the use of the import/export wizards and explains how they relate to SSIS. Also introduces BCP.
• Module 9: Authenticating and Authorizing Users
• Module 10: Assigning Server and Database Roles
• Module 11: Authorizing Users to Access Resources
• Module 12: Auditing SQL Server Environments
• Module 13: Automating SQL Server 2008 R2 Management
• Module 14: Configuring Security for SQL Server Agent
• Module 15: Monitoring SQL Server 2008 R2 with Alerts and NotificationsThis module covers the configuration of database mail, alerts and notifications.
• Module 16: Performing Ongoing Database Maintenance
• Module 17: Tracing Access to SQL Server 2008 R2This module covers SQL Profiler and SQL Trace stored procedures.
• Module 18: Monitoring SQL Server 2008 R2
• Module 19: Managing Multiple Servers
• Module 20: Troubleshooting Common SQL Server 2008 R2 Administrative Issues