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

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.