6231B: Maintaining a Microsoft SQL Server 2008 R2 Database
Course Overview
This instructor-led course provides students with the knowledge and skills to maintain a Microsoft SQL
Server 2008 R2 database. The course teaches individuals how to use SQL Server 2008 R2 product
features and tools related to maintaining a database.
Course Outline
Module 01 - Introduction to SQL Server 2008 R2 and its Toolset 1hr 31m Lesson 1: Introduction to the SQL Server Platform
SQL Server Architecture SQL Server Components SQL Server Instances SQL Server Editions SQL Server Versions
Lesson 2: Working with SQL Server Tools Connecting from Clients and Applications Software Layers for Connections SQL Server Management Studio Demo - SQL Server Management Studio Business Intelligence Development Studio Demo - Business Intelligence Development Studio Books Online
Demo - Books Online
Lesson 3: Configuring SQL Server Services SQL Server Configuration Manager
SQL Server Services Network Ports and Listeners Creating Server Aliases Other SQL Server Tools Demo - SQL Server Profiler Module 01 Review
Module 02 - Preparing Systems for SQL Server 2008 R2 33m Lesson 1: Overview of SQL Server Architecture
SQL Server Architecture CPU Usage by SQL Server Parallelism
32 bit vs. 64 bit Servers
Overview of SQL Server Memory Physical vs. Logical I/O
Planning Memory Requirements Planning Network Requirements Planning Storage and I/O Requirements
Lesson 3: Pre-installation Testing for SQL Server Overview of Pre-installation Testing
Perform Pre-checks of I/O subsystems Introducing SQLIOSIM
Introducing SQLIO Module 02 Review
Module 03 - Installing and Configuring SQL Server 2008 R2 42m Lesson 1: Preparing to Install SQL Server
Hardware Requirements – General Hardware Requirements – Memory
Software Requirements – Operating Systems Software Requirements – General
Determining File Placement Service Account Requirements Working with Collations Demo – Collations
Lesson 2: Installing SQL Server Overview of the Installation Process System Configuration Checker Post-installation Checks
Demo - System Configuration Checker
Lesson 3: Upgrading and Automating Installation Upgrading SQL Server
Unattended Installation
Demo - Unattended Installation File Module 03 Review
Module 04 - Working with Databases 1hr 20m Lesson 1: Overview of SQL Server Databases
How Data is Stored in SQL Server
Determining File Placement and Number of Files Ensuring Sufficient File Capacity
System Databases Supplied with SQL Server Overview of tempdb
Demo - Working with tempdb
Lesson 2: Working with Files and Filegroups Creating User Databases
Configuring Database Options Instant File Initialization Demo - Creating Databases Altering Databases
Expanding and Shrinking Database Files Demo - Altering Databases
Working with Filegroups Demo: Filegroups
Overview of Detach and Attach Moving User Database Files Demo - Detach and Attach Moving System Database Files Copying Databases
Demo - Moving and Reconfiguring tempdb Module 04 Review
Module 05 - Understanding SQL Server 2008 R2 Recovery Models 50m Lesson 1: Backup Strategies
Discussion: Previous Experience with Backup Strategies Determining an Appropriate Backup Strategy
Choosing Appropriate Backup Media
Determining a Retention and Testing Policy for Backups Lesson 2: Understanding SQL Server Transaction Logging Overview of SQL Server Transaction Logs
Transaction Log File Structure Working with Recovery Models Capacity Planning for Transaction Logs Demo - Logs and Full Recovery
Lesson 3: Planning a SQL Server Backup Strategy Overview of Microsoft SQL Server Backup Types Full Database Backup Strategies
Transaction Log Backup Strategies Differential Backup Strategies
Discussion: Meeting Business Recovery Requirements Module 05 Review
Module 06 - Backup of SQL Server 2008 R2 Databases 37m Lesson 1: Backing up Databases and Transaction Logs
Performing a Full Database Backup Working with Backup Sets Using Backup Compression Performing Differential Backups Performing Transaction Log Backups Demo - Backing up Databases
Lesson 2: Managing Database Backups Options for Ensuring Backup Integrity Viewing Backup Information Demo - Viewing Backup History
Lesson 3: Working with Backup Options Backup Considerations
Copy-only Backups Tail-log Backups Demo - Tail-log Backup Module 06 Review
Module 07 - Restoring SQL Server 2008 R2 Databases 46m Lesson 1: Understanding the Restore Process
Preparations for Restoring Backups
Discussion: Determining Required Backups to Restore Lesson 2: Restoring Databases
Phases of the Restore Process WITH RECOVERY Option Restoring a Database Restoring a Transaction Log WITH STANDBY Option Demo - Restoring Databases
Lesson 3: Working with Point-in-time Recovery Overview of Point-in-time Recovery
STOPAT Option
Discussion: Synchronizing Recovery of Multiple Databases STOPATMARK Option
Demo - Using STOPATMARK
Lesson 4 : Restoring System Databases and Individual Files Recovering System Databases
Restoring the master Database
Restoring a File or Filegroup from a Backup Demo - Restoring a File
Module 07 Review
Module 08 - Importing and Exporting Data 1hr 15m Lesson 1: Transferring Data To/From SQL Server
Overview of Data Transfer Available Tools for Data Transfer
Improving the Performance of Data Transfers Disabling & Rebuilding Indexes
Disabling & Enabling Constraints Demo - Disabling & Enabling Constraints Lesson 2: Importing & Exporting Table Data Overview of SQL Server Integration Services Demo - Working with SSIS
SQL Server Import/Export Wizard Demo - Using the Import/Export Wizard Lesson 3: Inserting Data in Bulk BCP Utility
BULK INSERT Statement
Demo - Working with BULK INSERT OPENROWSET Function
Demo - Working with OPENROWSET Module 08 Review
Module 09 - Authenticating and Authorizing Users 44m Lesson 1: Authenticating Connections to SQL Server
Overview of SQL Server Security SQL Server Authentication Options Managing Windows Logins
Lesson 2: Authorizing Logins to Access Databases Authorization Overview
Granting Access to Databases Managing dbo and guest Access
Demo - Authorizing Logins and User Tokens Lesson 3: Authorizing Across Servers Typical "Double-Hop" Problem Impersonation vs. Delegation
Working with Mismatched Security IDs Demo - Working with Mis-matched SID’s Module 09 Review
Module 10 - Assigning Server and Database Roles 33m Lesson 1: Working with Server Roles
Server-scoped Permissions Typical Server-scoped Permissions Overview of Fixed Server Roles public Server Role
Demo - Assigning Fixed Server Roles
Lesson 2: Working with Fixed Database Roles Database-scoped Permissions
Overview of Fixed Database Roles Assigning Users to Roles
Database Owner
Demo - Managing Roles and Users
Lesson 3: Creating User-defined Database Roles Working with User-defined Database Roles Applying Roles in Common Scenarios Demo - User-defined Database Roles Defining Application Roles
Demo - Application Roles Module 10 Review
Module 11 - Authorizing Users to Access Resources 35m Lesson 1: Authorizing User Access to Objects
What Are Principals? What Are Securables? GRANT, REVOKE, DENY Securing Tables and Views Column-level Security WITH GRANT Option
Demo - Authorizing User Access to Objects Lesson 2: Authorizing Users to Execute Code Securing Stored Procedures
Securing User-defined Functions Securing Managed Code Managing Ownership Chains
Demo - Authorizing Users to Execute Code
Object Name Resolution
Granting Permissions at the Schema Level Module 11 Review
Module 12 - Auditing SQL Server Environments 57m Lesson 1: Options for Auditing Data Access in SQL Server
Discussion: Auditing Data Access Using C2 Audit Mode
Common Criteria Audit Option Using Triggers for Auditing Using SQL Trace for Auditing
Demo - Using DML Triggers for Auditing Lesson 2: Implementing SQL Server Audit Introduction to Extended Events
Introduction to SQL Server Audit Configuring SQL Server Audit Audit Actions and Action Groups Defining Audit Targets
Creating Audits
Creating Server Audit Specifications Creating Database Audit Specifications Audit-related DMVs and System Views Demo - Using SQL Server Audit Lesson 3: Managing SQL Server Audit Retrieving Audits
Working with the Audit Record Structure Potential SQL Server Audit Issues
Demo - Viewing the Output of a File-based Audit Module 12 Review
Module 13 - Automating SQL Server 2008 R2 Management 33m Lesson 1: Automating SQL Server Management
Benefits of Automating SQL Server Management
Available Options for Automating SQL Server Management Overview of SQL Server Agent
Demo - SQL Server Agent
Lesson 2: Working with SQL Server Agent Defining Jobs, Job Step Types and Job Categories Creating Job Steps
Scheduling Jobs for Execution Scripting Jobs
Demo - Scripting Jobs
Lesson 3: Managing SQL Server Agent Jobs Viewing Job History
Querying SQL Server Agent-related System Tables and Views Troubleshooting Failed Jobs
Demo - Viewing Job History and Resolving Failed Jobs Module 13 Review
Lesson 1: Understanding SQL Server Agent Security Overview of SQL Server Agent Security
SQL Server Agent Roles
Discussion: SQL Server Agent Job Dependencies Assigning Security Contexts to Agent Job Steps SQL Server Agent Security Troubleshooting Demo - Assigning a Security Context to Job Steps Lesson 2: Configuring Credentials
Overview of Credentials Configuring Credentials Managing Credentials
Lesson 3: Configuring Proxy Accounts Overview of Proxy Accounts
Working with Built-in Proxy Accounts Managing Proxy Accounts
Module 14 Review
Module 15 - Monitoring SQL Server 2008 R2 45m with Alerts and Notifications
Lesson 1: Configuration of Database Mail Overview of Database Mail
Database Mail Profiles Database Mail Security
Database Mail Logs and Retention Demo - Configuring Database Mail Lesson 2: Monitoring SQL Server Errors What's in an Error?
Error Severity
Configuring the SQL Server Error Log
Lesson 3: Configuring Operators, Alerts and Notifications SQL Server Agent Operator Overview
Overview of SQL Server Alerts Create an Alert
Configuring Alert Actions
Troubleshooting Alerts and Notifications Demo - Alerts and Notifications Module 15 Review
Module 16 - Performing Ongoing Database Maintenance 47m Lesson 1: Ensuring Database Integrity
Discussion: Ensuring Database Integrity Overview of DBCC CHECKDB DBCC CHECKDB Options DBCC CHECKDB Repair Options Demo - DBCC CHECKDB Lesson 2 : Maintaining Indexes How Indexes Affect Performance Types of SQL Server Indexes Index Fragmentation
Ongoing Maintenance of Indexes Online Index Operations Updating Statistics
Lesson 3: Automating Routine Database Maintenance Overview of SQL Server Database Maintenance Plans Monitoring Database Maintenance Plans
Module 16 Review
Module 17 - Tracing Access to SQL Server 2008 R2 49m Lesson 1: Capturing Activity using SQL Server Profiler
Overview of SQL Server Profiler Available Tracing Output Options Commonly used Trace Events Commonly used Trace Columns Filtering Traces
Working with Trace Templates
Demo - Capturing Activity using SQL Server Profiler
Lesson 2: Improving Performance with the Database Engine Tuning Advisor Overview of Performance Tuning
Available Options for Performance Tuning Database Engine Tuning Advisor
Database Engine Tuning Advisor Options Lesson 3: Working with Tracing Options Overview of SQL Trace
SQL Trace vs. SQL Server Profiler Retrieving Trace Output
Replaying Traces Default Trace
Combining Traces with Performance Monitor Logs Module 17 Review
Module 18 - Monitoring SQL Server 2008 R2 49m Lesson 1: Monitoring Activity
Overview of Dynamic Management Views and Functions Viewing Activity using Dynamic Management Views Demo - Viewing Activity using Dynamic Management Views Working with Activity Monitor in SQL Server Management Studio Working with Reliability and Performance Monitor
Working with SQL Server Counters
Lesson 2: Capturing and Managing Performance Data Overview of Data Collector
Designing a Data Collector Topology Configuring Data Collector
Data Collector Security Monitoring Data Collector
Lesson 3: Analyzing Collected Performance Data Overview of Data Collector Reports
Module 18 Review
Module 19 - Managing Multiple Servers 38m Lesson 1: Working with Multiple Servers
Overview of Central Management Servers Executing Multi-server Queries
Lesson 2: Virtualizing SQL Server
Discussion: Advantages and Disadvantages of Virtualizing SQL Server Overview of SQL Server Virtualization
Common Virtualization Scenarios
Considerations for Virtualizing SQL Server
Overview of System Center Virtual Machine Manager Lesson 3: Deploying and Upgrading Data-Tier Applications Data-tier Application Overview
Deploying Data-tier Applications Upgrading Data-tier Applications Extracting Data-tier Applications Demo - Creating ADACPAC
Demo - Server Registration and Central Management Server Module 19 Review
Module 20 - Managing Multiple Servers 28m Lesson 1: SQL Server Troubleshooting Methodology
Discussion: Characteristics of Good Trouble-shooters Applying a Troubleshooting Methodology
Lesson 2: Resolving Service-related Issues Troubleshooting Service-related Issues SQL Server Error Log
Windows Event Logs
Demo - Troubleshooting Service-related Issues Lesson 3: Resolving Concurrency Issues Core Concurrency Concepts
Troubleshooting Blocking Troubleshooting Deadlocks
Lesson 4: Resolving Login and Connectivity Issues Troubleshooting Connectivity Issues
Troubleshooting Login Failures Module 20 Review
Course Closure
