Home > Courses Certifications > Programming > Course 2780 synopsis

2780- Maintaining a Microsoft SQL Server 2005 Database

Overview: This three-day instructor-led course provides students with product knowledge and skills needed to maintain a Microsoft SQL ServerT 2005 database. The course focuses on teaching individuals how to use SQL Server 2005 product features and tools related to maintaining a database.

Duration: 5 Days

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

In addition, it is recommended, but not required, that students have completed:

• Course 2778: Writing Queries Using Microsoft SQL Server 2005 Transact-SQL
• Course 2779: Implementing a Microsoft SQL Server 2005 Database

At Course Completion

• Install and configure SQL Server 2005
• Manage database files
• Manage security
• Perform administrative tasks
• Back up databases
• Restore databases
• Monitor SQL Server
• Troubleshoot SQL Server
• Transfer data
• Maintain high availability.

Module 1: Installing and Configuring SQL Server 2005

Lesson 1: Preparing to Install

• SQL Server 2005 Editions
• Hardware Requirements
• Software Requirements
• SQL Server Instances
• Licensing Options
• Security Considerations
• Collation Considerations
• Considerations for Upgrading

Lesson 2: Installing

• Overview of the Installation Process
• System Consistency Checker?
• Options
• Performing an Unattended Installation

Lesson 3: Managing an Installation

• Surface Area Configuration Tool
• SQL Server Configuration Manager
• SQL Server Management Studio
• sqlcmd
• Server-Level Options?

Module 2: Managing Databases and Files

Lesson 1: Planning

• Data Storage
• Transaction Logging
• File Placement
• What Are Filegroups?
• Filegroup Considerations
• Capacity Planning Considerations

Lesson 2: Creating

• Considerations
• Syntax for Creating a Database
• Schemas
• Syntax for Creating a Schema
• Name Resolution

Lesson 3: Managing

• Options
• Finding Information about Databases

Module 3: Disaster Recovery

Lesson 1: Backup Strategy

• Backup Types
• Recovery Models?
• Full Database Backup Strategy
• DB & Transaction Log Backup Strategy
• Differential Backup Strategy
• File or Filegroup Backup Strategy
• Backup Operators
• Backup Media

Lesson 2: Back Up User Databases

• Full Database Backup
• Transaction Log Backup
• Differential Backup
• File and Filegroup Backups
• Ensuring Backup Integrity

Lesson 3: Restore User Databases

• The Restore Process
• Restoring a Database
• Restoring a Transaction Log Backup
• Restoring Pages, Files, and Filegroups
• Piecemeal Restore

Lesson 4: Recover Data -DB Snapshots

• Database Snapshots
• How Database Snapshots Work
• Recovering Data from a DB Snapshot

Lesson 5: Disaster Recovery

• Backing Up System Databases
• Restoring System Databases
• Restoring the Master Database

Module 4: Managing Security

Lesson 1: Overview

• Principals
• Securables
• Permissions

Lesson 2: Server Scope

• Authentication Modes
• Password Policies
• Managing SQL Server Logins
• Fixed Server Roles
• Delegation
• Credentials
• Server-Scope Permissions

Lesson 3: Database Scope

• Managing Users
• Special Users
• Database Roles
• Application Roles
• Database-Scope Permissions
• Schema-Scope Permissions

Lesson 4: Managing Keys & Certificates Keys

• Certificates?
• Cryptography Architecture
• Keys and Certificates

Module 5: Monitoring SQL Server

Lesson 1: Current Activity

• Activity Monitor
• Dynamic Management Objects

Lesson 2: System Monitor

• System Monitor
• Performance Objects
• Monitoring SQL Server

Lesson 3: SQL Server Profiler

• SQL Server Profiler
• Profiler Trace Options
• Categories, Events, and Columns

Lesson 4: DDL Triggers

• Introduction
• Creating DDL Triggers
• Managing DDL Triggers

Lesson 5: Event Notifications

• Introduction
• Creating Event Notifications
• Processing Event Notifications
• Managing Event Notifications

Module 6: Transferring Data

Lesson 1: Introduction to Data Transfer

• Data Transfer
• Data Transformation
• Tools for Transferring Data

Lesson 2: SQL Server Integration Srvcs

• Introduction to SSIS
• SSIS Task Processing
• SSIS Tools

Lesson 3: How to use SSIS

• Data Sources and Data Source Views
• Connection Manager
• Package Control Flow
• Data Flow Tasks
• Deploying Packages

Module 7: Automating Administrative Tasks

Lesson 1: Overview

• Benefits
• The Maintenance Plan Wizard
• The SQL Server Agent

Lesson 2: Configuring SQL Server Agent

• Considerations
• SQL Server Agent Mail Options

Lesson 3: Jobs and Operators Jobs

• Operators
• Creating Job Steps
• The Job Activity Monitor

Lesson 4: Alerts

• Introduction
• Creating an Alert

Lesson 5: Multiple Servers

• Master and Target Servers
• Considerations
• Event Forwarding

Lesson 6: Managing Security

• SQL Server Agent Roles
• SQL Server Agent Proxies

Module 8: Maintaining High Availability

Lesson 1: Introduction

• Availability Factors
• Considerations

Lesson 2: Server Clustering

• Introduction
• Considerations
• Installing SQL Server in a Cluster

Lesson 3: Database Mirroring

• Introduction
• Options
• Database Mirroring Endpoints
• Preparing Servers for Database Mirroring
• Establishing a Mirror Session
• Handling Failover

Lesson 4: Log Shipping

• Introduction
• Implementing Log Shipping
• Switching Server Roles

Module 9: Introduction to Replication

Lesson 1: Introduction

• Introduction
• Replication Roles
• Replication Types
• Articles, Publications, and Subscriptions

Lesson 2: Scenarios

• Server to Server Scenarios
• Server to Client Scenarios