Implementing and Maintaining Microsoft SQL Server 2008 (70-432)

Author: Microsoft
ISBN 13: 5038470018978
Published by: MICROSOFT LEARNING
Date Published: 06/2009
User level: Intermediate/Advance

This 16-hour collection provides you with the skills and knowledge required for implementing and maintaining Microsoft SQL Server 2008. You will learn how to install and configure SQL Server 2008. You will also learn how to manage databases, create indexes, automate administrative tasks, and implement high availability in SQL Server 2008.

This collection includes the following:
Course 10151: Installing and Configuring Microsoft SQL Server 2008
Course 10152: Creating and Managing Databases in Microsoft SQL Server 2008
Course 10153: Planning, Creating, and Optimizing Indexes in Microsoft SQL Server 2008
Course 10154: Backing Up and Restoring Databases in Microsoft SQL Server 2008
Course 10155: Managing Security in Microsoft SQL Server 2008
Course 10156: Automating Administrative Tasks in Microsoft SQL Server 2008
Course 10157: Monitoring and Controlling Resources in Microsoft SQL Server 2008
Course 10158: Implementing High Availability and Replication in Microsoft SQL Server 2008

Student prerequisites:
To get the most benefit, the learner must be familiar with using Microsoft Windows. They must have knowledge of basic relational database concepts and querying data by using Transact-SQL. They must be familiar with the role of a database administrator.

System Requirements: Pentium II 400 MHz
256MB RAM
Microsoft Internet Explorer 6.0 or higher
256k connection (broadband recommended)
Windows XP, 2000 or higher
Flash Player 7.0 or higher
Speakers and soundcard


Subscription: 1 year.

Your order will be processed during normal business hours so please allow an hour or two during this time to receive the information where you will logon. You will be given an access code.

Table Of Contents

Course 10151: Installing and Configuring Microsoft SQL Server 2008

Preparing to Install Microsoft SQL Server 2008

Introduction
SQL Server 2008 Editions
SQL Server 2008 Hardware and Software Requirements
Operating System Support for SQL Server 2008
Using Instances in SQL Server 2008
SQL Server 2008 Licensing Options
Security Considerations
Collation Settings for SQL Server 2008
Self Test

Installing SQL Server 2008

Introduction
SQL Server 2008 Installation Process
What Is Unattended Installation?
Upgrading to SQL Server 2008
Installing an Instance of SQL Server 2008
Verifying the Installation of SQL Server 2008
Self Test

Managing an SQL Server 2008 Installation

Introduction
Methods of Managing Surface Area Configuration
Using SQL Server Configuration Manager
Configuring the FILESTREAM Data Access
Using SQL Server Installation Center
Self Test

Working with SQL Server 2008 Applications

Introduction
Using SQL Server Management Studio
Setting Server-Level Instance Properties and Options
SQL Server Group Management
Using SQLCMD
Using Windows PowerShell to Manage SQL Server 2008
Self Test

Best Practices

Lab: Installing and Configuring Microsoft SQL Server 2008
Lab Overview
Scenario
Exercise Information
Launch the Lab
Lab Review

Module Summary

Glossary

Course 10152: Creating and Managing Databases in Microsoft SQL Server 2008

Overview of SQL Server 2008 Databases

Introduction

Types of SQL Server 2008 Databases
What Are Data and Transaction Log Files?
Optimising the Database by Using RAID
Data Storage in SQL Server 2008
Considerations for Selecting Data Files
Self Test

Filegroups in SQL Server 2008

Introduction
What Are Filegroups?
Using Filegroups for Performance
Using Filegroups for Maintenance
Using Filegroups for Table Partitioning
Using Filegroups with the FILESTREAM
Self Test

Creating a SQL Server 2008 Database

Introduction
Options for Creating a Database
Creating a Database by Using SQL Server Management Studio
Creating a Database by Using Transact-SQL
Creating User-Defined Filegroups
Self Test

Managing a SQL Server 2008 Database

Introduction
Modifying a Database
Managing Database Files and Filegroups
Changing Database-Level Options
How to Modify a Database in SQL Server 2008
Deleting a Database
Sources of Database Metadata
Querying Database Metadata
Self Test

Using Schemas in SQL Server 2008

Introduction
The Role of Schemas in SQL Server 2008
Creating Schemas in SQL Server 2008
How Object Name Resolution Works
Creating Objects in SQL Server 2008 by Using Schemas
Self Test

Best Practices

Lab: Creating and Managing Databases in Microsoft SQL Server 2008
Lab Overview
Scenario
Exercise Information
Launch the Lab
Lab Review

Module Summary

Glossary

Course 10153: Planning, Creating, and Optimizing Indexes in Microsoft SQL Server 2008

Overview of Indexes

Introduction
How SQL Server 2008 Retrieves Data
What Is a Heap?
What Is a Clustered Index?
What Is a Nonclustered Index?
Traversing Data with Indexes
Introduction to Spatial Indexes
Self Test

Creating Indexes

Introduction
How to Create Indexes
Creating Indexes on Multiple Columns
Creating Unique Indexes
Creating Partitioned Indexes
Creating Indexes to Cover Queries
How to Create Indexes on Computed Columns
Self Test

Optimising Indexes

Introduction

How SQL Server 2008 Handles Rows in Pages
Understanding Page Splits
Using the FILLFACTOR Option to Control Page Splits
Using PAD_INDEX Option to Control Page Splits
Self Test

Maintaining Indexes

Introduction
Understanding Index Statistics
Data Fragmentation
Managing Indexes
Obtaining Information About Indexes
The Database Engine Tuning Advisor Tool
Self Test

Spatial Indexes

Introduction
How SQL Server 2008 Uses a Spatial Index
What Are Tessallation Schemes?
Restrictions on Spatial Indexes
How to Create, Alter and Drop Spatial Indexes
How to Retrieve Spatial Index Information
Self Test

Best Practices

Lab: Creating and Optimising Indexes in Microsoft SQL Server 2008 Lab Overview Scenario Exercise Information Launch the Lab Lab Review

Module Summary

Glossary

Course 10154: Backing Up and Restoring Databases in Microsoft SQL Server 2008

Backup Strategies in Microsoft SQL Server 2008

Introduction
Database Recovery Models
Full Database Backup Strategy
Database and Transaction Log Backup Strategy
Differential Backup Strategy
File and Filegroup Backup Strategy
Partial Backup and Copy-Only Backup
Considerations for Backup Operators and Backup Media
Self Test

Backing Up User Databases

Introduction
Creating Backup Devices
How to Create a Full Database Backup
How to Ensure Backup Integrity
How to Create a Transaction Log Backup
How to Create a Differential Backup
What Is Backup Compression?
How to Create a File and Filegroup Backup
Self Test

Restoring User Databases

Introduction
How the Restore Process Works
How to Restore a Database
How to Restore a Transaction Log
How to Restore a File or Filegroup
How to Perform a Damaged Page Restore
How to Perform a Piecemeal Restore
Self Test

Implementing Database Snapshots

Introduction
How Database Snapshots Work
How to Create a Database Snapshot
How to Recover Data from a Database Snapshot
Self Test

System Databases and Disaster Recovery

Introduction
Considerations for Backing Up System Databases
Considerations for Restoring System Databases
How to Restore the Master Database
Self Test

Best Practices

Lab: Backup and Restore Databases in Microsoft SQL Server 2008

Lab Overview
Scenario
Exercise Information
Launch the Lab
Lab Review

Module Summary

Glossary

Course 10155: Managing Security in Microsoft SQL Server 2008

Overview of the SQL Server 2008 Security Model

Introduction
SQL Server 2008 Principals
SQL Server 2008 Securables
SQL Server 2008 Permissions
Self Test

Securing SQL Server 2008

Introduction
SQL Server 2008 Authentication Modes
Creating SQL Server 2008 Logins
SQL Server 2008 Fixed Server Roles
Creating SQL Server 2008 Credentials
Setting Server-Scope Permissions
Self Test

Securing SQL Server Databases

Introduction
Special Accounts in a Database
Creating Database Users
Describing Database Roles
Creating User-Defined Database Roles
Creating Application Roles
Setting Database Scope Permissions
Setting Schema Scope Permissions
Self Test

Using Data Encryption to Protect Data

Introduction
Creating and Using Certificates and Keys
SQL Server 2008 Cryptographic Architecture
Enabling a Database on TDE
Using EKM
Self Test

Auditing in SQL Server 2008

Introduction
Auditing in SQL Server 2008
Setting Server Scope Audit Properties
SQL Server Audit
Using SQL Server Audit
Auditing with SQL Server Profiler
Auditing by Using DDL Triggers
Self Test

Best Practices

Lab: Managing Security in Microsoft SQL Server 2008

Lab Overview
Scenario
Exercise Information
Launch the Lab
Lab Review
Module Summary
Module Summary

Glossary

Course 10156: Automating Administrative Tasks in Microsoft SQL Server 2008

Automating Administrative Tasks

Introduction
The Benefits of Automation
Using the Maintenance Plan Wizard
Using Maintenance Plans
Describing the SQL Server Agent
Self Test

Configuring SQL Server Agent

Introduction
Considerations for Configuring SQL Server Agent
SQL Server Agent Roles
Configuring Database Mail
Configuring the SQL Server Agent
Self Test

Creating Jobs and Operators

Introduction
Describing Jobs
Describing Operators
Creating Operators
Creating and Managing Jobs and Job Steps
SQL Server Agent Proxy Accounts
Using Job Activity Monitor and View the Job History
Self Test

Creating Alerts

Introduction
What Are Alerts?
Creating SQL Server Alerts and Alert Responses
Self Test

Managing Multiple Servers>

Introduction
Using Multiserver Administration
How to Create Master and Target Servers
Using Event Forwarding
Configuring Event Forwarding
Self Test

Best Practices

Lab: Automating Administrative Tasks in Microsoft SQL Server 2008

Lab Overview
Scenario
Exercise Information
Launch the Lab
Lab Review
Module Summary

Glossary

Course 10157: Monitoring and Controlling Resources in Microsoft SQL Server 2008

Introduction to Monitoring

Introduction
Importance of Monitoring
Types of Performance Monitoring Tools
Importance of Baselining
How to Use Activity Monitor
How to Use Transact-SQL to Monitor SQL Server 2008
Self Test

Using Windows Reliability and Performance Monitor

Introduction
Characteristics of Windows Reliability and Performance Monitor
Common Objects to Monitor
How to Use Windows Reliability and Performance Monitor
How to Create a Data Collector Set
Self Test

Using SQL Server Profiler

Introduction
What Is SQL Server Profiler?
Event Categories in SQL Server Profiler
How to Use SQL Server Profiler
Creating a SQL Server Profiler Template
How to Integrate SQL Server Profiler with Windows Data Collector
Self Test

Using SQL Server Data Collector

Introduction
What Is SQL Server 2008 Data Collector?
How to Create a Management Data Warehouse
How to Use System Data Collectors
How to Create Custom Data Collectors
Self Test

Using Resource Governor

Introduction
Control Resources Using Resource Governor
Components of Resource Governor
How to Create a Resource Governor Instance
Control Applications with Resource Governor
Self Test

Using Policy-Based Management

Introduction
Purpose of Policy-Based Management
Components of Policy-Based Management
How to Create a Policy
Self Test

Best Practices

Lab: Monitoring and Controlling Resources in Microsoft SQL Server 2008 Lab Overview
Scenario
Exercise Information
Launch the Lab
Lab Review

Module Summary

Glossary

Course 10158: Implementing High Availability and Replication in Microsoft SQL Server 2008

Introduction to High Availability

Introduction
Factors That Affect Availability
Factors for Optimizing Database Availability
Options for High-Availability Solutions
Self Test

Implementing Server Clustering

Introduction
What Is Server Clustering?
Considerations for Server Clustering
Installing SQL Server 2008 in a Cluster
Self Test

Implementing Database Mirroring

Introduction
Introducing Database Mirroring
Exploring Different Configurations of Database Mirroring
Preparing a Server for Database Mirroring
Creating Database Mirroring Endpoints
Create a Database Mirror
Testing Database Mirroring Failover
Self Test

Introducing Log Shipping

Introduction
Log Shipping
Configuring Log Shipping
Managing Log Shipping Failover
Implementing Log Shipping
Self Test

Introducing Replication

Introduction
Understanding Replication
Benefits of Replication
Server Roles in Replication
Describing Articles, Publications, and Subscriptions
Describing Replication Agents
Self Test

Types of Replication

Introduction
Snapshot Replication
Transactional Replication
Merge Replication
HTTP Merge Replication
Peer-to-Peer Replication
Configure Snapshot Replication
Self Test

Best Practices

Lab: Implementing High Availability and Replication in Microsoft SQL Server 2008

Lab Overview
Scenario
Exercise Information
Launch the Lab
Lab Review

Module Summary

Glossary