50238 - Introduction to SQL Server 2008 Administration

After completing this course, students will be able to:

• Install SQL Server
• Create databases
• Create logins and database users
• Create and maintain indexes for optimal database performance
• Backup and restore user-defined and system databases
• Automate routine tasks
• Monitor SQL Server performance
• Audit a variety of user actions
• Upgrade to SQL Server 2008

Module 1: Installing SQL Server 2008
This module explains how to install SQL Server 2008.

Lessons
• Planning to Install SQL Server 2008
• Installing SQL Server 2008
• Post-Installation Tasks

Lab : Installing SQL Server 2008
•Install a Named Instance of SQL Server

After completing this module, students will be able to:
• Install a named instance of SQL Server 2008
• Troubleshoot installation issues

Module 2: Managing SQL Server 2008
This module explains how to use the standard tools for managing SQL Server installs.

Lessons
• Managing SQL Server
• Metadata Retrieval

Lab : Managing SQL Server 2008
• Manage SQL Server Services
• Use Dedicated Administrator Connection
• Retrieve Metadata

After completing this module, students will be able to:
• Use SQL Server Configuration Manager
• Use SQL Server Management Studio
• Use sqlcmd utility
• Use Dedicated Administrator Connection
• Use Powershell
• Use system stored procedures
• Use catalog views
• Use dynamic management views
• Use built-in functions

Module 3: Database Management
This module explains how to create and manage databases.

Lessons
• Disk Configuration
• Databases and Filegroups
• Managing Databases
• Data Compression

Lab : Database Management
• Create a Database
• Add a Filegroup to an Existing Database
• Delete a Database

After completing this module, students will be able to:
• Describe various disk types such as SATA, SSD, SCSI
• Describe storage solutions such as SAN
• Describe common RAID levels
• Create a database with files spread across different filegroups across separate disks
• Describe the purpose of each system database
• Modify and delete databases
• Compress data at the row and page level

Module 4: Security
This module explains how to administer security in SQL Server 2008.

Lessons
• Overview of Security
• Accessing SQL Server
• Advanced Security

Lab : Security
• Modify SQL Server Authentication Mode
• Configure Permissions

After completing this module, students will be able to:
• Describe Principals and Securables
• Create schemas
• Change the authentication mode of SQL Server
• Create logins
• Create database users
• Create roles
• Assign principals to roles
• Understand impersonation and delegation
• Create linked servers

Module 5: Disaster Recovery
This module explains how to backup and restore data in SQL Server.

Lessons
• Disaster Recovery Overview
• Disaster Recovery Using Full Database Backups
• Disaster Recovery Using Differential Database Backups
• Disaster Recovery Using Transaction Log Backups
• Disaster Recovery Using File and Filegroup Backups
• Advanced Disaster Recovery

Lab : Full Database Backups
Lab : Differential Database Backups
Lab : Transaction Log Backups
Lab : File and Filegroup Backups
Lab : Advanced Disaster Recovery

After completing this module, students will be able to:
• Perform a full database backup and restore
• Perform a differential database backup and restore
• Perform a transaction log backup and restore
• Perform a file and filegroup backup and restore
• Restore system databases
• Rebuild system databases
• Restore a page
• Create database snapshots

Module 6: Automation and Maintenance
This module explains how to automate routine administrative tasks and perform maintenance tasks to keep the database performing optimally.

Lessons
• SQL Server Agent Automation
• Advanced SQL Server Automation
• Database Maintenance

Lab : Automation and Maintenance
• Create a Job
• Create an Alert
• Export Data using the Import and Export Wizard
• Create a Maintenance Plan

After completing this module, students will be able to:
• Create jobs
• Create alerts
• Configure operators
• Configure Database Mail
• Configure SQL Server Agent security using predefined roles
• Create a master and target server for multiserver administration
• Check for corruption in a database
• Import and export data to and from SQL Server and other sources
• Create maintenance plans

Module 7: Indexing
This module explains how to data is quickly retrieved using SQL Server indexes.

Lessons
• Optimizing Data Retrieval
• Types of Indexes
• Creating, Altering and Deleting Indexes
• Index Maintenance

Lab : Indexing
• Create Indexes
• Remove Fragmentation
• Delete Indexes

After completing this module, students will be able to:
• Describe statistics and how the query optimizer uses them
• Create clustered and nonclustered indexes
• Create unique indexes
• Modify and delete indexes
• Create included indexes
• Identify and remove fragmentation in an index

Module 8: Monitoring and Performance Tuning
This module explains how to monitor activity within SQL Server and limit resources that are consumed by applications and users.

Lessons
• Areas to Monitor
• Monitoring Tools
• Data Collection
• Resource Governor

Lab : Monitoring and Performance Tuning
• Create a SQL Server Profiler Trace
• Create a Table from Trace Data
• Identify Missing Indexes Using Database Engine Tuning Advisor

After completing this module, students will be able to:
• Monitor SQL Server using SQL Server Profiler, Activity Monitor and various logs
• Create a server-side trace
• Identify missing indexes using Database Engine Tuning Advisor
• Configure data collection and upload
• Limit resource usage with Resource Governor

Module 9: Auditing SQL Server 2008
Auditing SQL Server 2008

Lessons
• Triggers
• Service Broker and Event Notifications
• Policy-Based Management
• Change Data Capture
• Change Tracking
• SQL Server Audit

Lab : Auditing SQL Server 2008
• Create a DDL Trigger

After completing this module, students will be able to:
• Create DML and DDL triggers
• Configure Service Broker
• Create Event Notifications
• Create a Policy
• Configure change data capture and change tracking
• Configure auditing

Module 10: Upgrading to SQL Server 2008
This module explains how to successfully upgrade to SQL Server 2008.

Lessons
• Pre-Upgrade Tasks
• Upgrade Options
• Post-Upgrade Tasks

After completing this module, students will be able to:
• Understand upgrade options
• Identify pre- and post-upgrade tasks

Before attending this course, students must have:

• Basic knowledge of Windows XP, Vista, or Window Server 2003/2008