IT TRAINING

Microsoft 6231 – Maintaining a Microsoft SQL Server 2008 R2 Database

In this five day course will provide you with the skills and knowledge required to maintain a Microsoft SQL Server 2008 R2 database.

Who needs to attend

Who needs to attend?
This course is aimed at people who administer and maintain SQL Server databases or people who develop applications that deliver content for SQL Server database.

what you will learn

What you will learn

Upon completion students should know how 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

Prerequisites

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

Course Outline

Module 1: Introduction to SQL Server 2008 R2 and its Toolset
This module introduces you to the entire SQL Server platform and its major tools. This module also covers editions, versions, basics of network listeners, and concepts of services and service accounts.
Lessons

Introduction to SQL Server Platform
Working with SQL Server Tools
Configuring SQL Server Services

Lab : Introduction to SQL Server 2008 R2 and its Toolset

Describe the SQL Server Platform
Work with SQL Server Tools
Configure SQL Server Services

Module 2: Preparing Systems for SQL Server 2008 R2
This module covers planning for an installation related to SQL Server I/O requirements, 32 bit vs 64 bit, memory configuration options and I/O subsystem pre-installation checks using SQLIOSim and SQLIO.
Lessons

Overview of SQL Server 2008 R2 Architecture
Planning Server Resource Requirements
Pre-installation Testing for SQL Server 2008 R2

Lab : Preparing Systems for SQL Server 2008 R2

Describe the SQL Server architecture
Plan for server resource requirements
Conduct pre-installation stress testing for SQL Server

Module 3: Installing and Configuring SQL Server 2008 R2
This module details installing and configuring SQL Server 2008 R2.
Lessons

Preparing to Install SQL Server 2008 R2
Installing SQL Server 2008 R2
Upgrading and Automating Installation

Lab : Installing and Configuring SQL Server 2008 R2

Prepare to install SQL Server
Install SQL Server
Upgrade and automate the installation of SQL Server

Module 4: Working with Databases
This module describes the system databases, the physical structure of databases and the most common configuration options related to them.
Lessons

Overview of SQL Server Databases
Working with Files and Filegroups
Moving Database Files

Lab : Working with Databases

Describe the role and structure of SQL Server databases
Work with files and filegroups
Move database files within servers and between servers

Module 5: Understanding SQL Server 2008 R2 Recovery Models
This module describes the concept of the transaction log and SQL Server recovery models. It introduces the different backup strategies available with SQL Server 2008 R2.
Lessons

Backup Strategies
Understanding SQL Server Transaction Logging
Planning a SQL Server Backup Strategy

Lab : Understanding SQL Server 2008 R2 Recovery Models

Describe the critical concepts surrounding backup strategies
Explain the transaction logging capabilities within the SQL Server database engine
Plan a SQL Server backup strategy

Module 6: Backup of SQL Server 2008 R2 Databases
This module describes SQL Server 2008 R2 Backup and the backup types.
Lessons

Backing up Databases and Transaction Logs
Managing Database Backups
Working with Backup Options

Lab : Backup of SQL Server 2008 R2 Databases

Back up databases and transaction logs
Manage database backups
Work with more advanced backup options

Module 7: Restoring SQL Server 2008 R2 DatabasesThis module describes the restoration of databases.Lessons

Understanding the Restore Process
Restoring Databases
Working with Point-in-time Recovery
Restoring System Databases and Individual Files

Lab : Restoring SQL Server 2008 R2 Databases

Understand the restore process
Restore databases
Work with Point-in-time Recovery
Restore system databases and individual files

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

Transferring Data To/From SQL Server 2008 R2
Importing & Exporting Table Data
Inserting Data in Bulk

Lab : Importing and Exporting Data

Transfer data to and from SQL Server
Import and export table data
Insert data in bulk and optimize the bulk insert process

Module 9: Authenticating and Authorizing Users
This module covers SQL Server 2008 R2 security models, logins and users.
Lessons

Authenticating Connections to SQL Server
Authorizing Logins to Access Databases
Authorization Across Servers

Lab : Authenticating and Authorizing Users

Describe how SQL Server authenticates connections
Describe how logins are authorized to access databases
Explain the requirements for authorization across servers

Module 10: Assigning Server and Database Roles
This module covers fixed server roles, fixed database roles and user-defined database roles.
Lessons

Working with Server Roles
Working with Fixed Database Roles
Creating User-defined Database Roles

Lab : Assigning Server and Database Roles

Work with server roles
Work with fixed database roles
Create user-defined database roles

Module 11: Authorizing Users to Access Resources
This module covers permissions and the assignment of permissions.
Lessons

Authorizing User Access to Objects
Authorizing Users to Execute Code
Configuring Permissions at the Schema Level

Lab : Authorizing Users to Access Resources

Authorize user access to objects
Authorize users to execute code
Configure permissions at the schema level

Module 12: Auditing SQL Server Environments
This module covers SQL Server Audit.
Lessons

Options for Auditing Data Access in SQL Server
Implementing SQL Server Audit
Managing SQL Server Audit

Lab : Auditing SQL Server Environments

Describe the options for auditing data access in SQL Server
Implement SQL Server Audit
Manage SQL Server Audit

Module 13: Automating SQL Server 2008 R2 Management
This module covers SQL Server Agent, jobs and job history.
Lessons

Automating SQL Server Management
Working with SQL Server Agent
Managing SQL Server Agent Jobs

Lab : Automating SQL Server 2008 R2 Management

Automate SQL Server Management
Work with SQL Server Agent
Manage SQL Server Agent jobs

Module 14: Configuring Security for SQL Server Agent
This module covers SQL Server agent security, proxy accounts and credentials.
Lessons

Understanding SQL Server Agent Security
Configuring Credentials
Configuring Proxy Accounts

Lab : Configuring Security for SQL Server Agent

Explain SQL Server Agent security
Configure credentials
Configure Proxy accounts

Module 15: Monitoring SQL Server 2008 R2 with Alerts and NotificationsThis module covers the configuration of database mail, alerts and notifications.Lessons

Configuration of Database Mail
Monitoring SQL Server Errors
Configuring Operators, Alerts and Notifications

Lab : Monitoring SQL Agent Jobs with Alerts and Notifications

Configure database mail
Monitor SQL Server errors
Configure operators, alerts and notifications

Module 16: Performing Ongoing Database Maintenance
This module covers database maintenance plans.
Lessons

Ensuring Database Integrity
Maintaining Indexes
Automating Routine Database Maintenance

Lab : Ongoing Database Maintenance

Ensure database integrity
Maintain indexes
Automate routine database maintenance

Module 17: Tracing Access to SQL Server 2008 R2This module covers SQL Profiler and SQL Trace stored procedures.Lessons

Capturing Activity using SQL Server Profiler
Improving Performance with the Database Engine Tuning Advisor
Working with Tracing Options

Lab : Tracing Access to SQL Server 2008 R2

Capture activity using SQL Server Profiler
Improve performance with the Database Engine Tuning Advisor
Work with tracing options

Module 18: Monitoring SQL Server 2008 R2
This module introduces DMVs and the configuration of data collection.
Lessons

Monitoring Activity
Capturing and Managing Performance Data
Analyzing Collected Performance Data

Lab : Monitoring SQL Server 2008 R2

Monitor current activity
Capture and manage performance data
Analyze collected performance data

Module 19: Managing Multiple Servers
This module covers Central Management Servers and Multi-Server queries, Virtualization of SQL Server and Data-Tier Applications.
Lessons

Working with Multiple Servers
Virtualizing SQL Server
Deploying and Upgrading Data-Tier Applications

Lab : Managing Multiple Servers

Work with multiple servers
Describe options for virtualizing SQL Server
Deploy and upgrade Data-Tier Applications

Module 20: Troubleshooting Common SQL Server 2008 R2 Administrative Issues
This module covers common issues that require troubleshooting and gives guidance on where to start looking for solutions.
Lessons

SQL Server Troubleshooting Methodology
Resolving Service-related Issues
Resolving Concurrency Issues
Resolving Login and Connectivity Issues

Lab : Troubleshooting Common Issues

Explain SQL Server troubleshooting methodology
Resolve service-related issues
Resolve concurrency issues
Resolve login and connectivity issues

Follow on
There are no follow-ons for this course.

Certification programs
There are no certifications associated with this course.