About this Course
This five-day instructor-led course provides students with the knowledge and skills to administer SQL Server 2008. The course focuses on support tasks, including installation, upgrading, backups, restores, monitoring, and auditing.
|
Audience Profile
This course is intended for database support personnel and database administrators who have basic Windows navigation skills.
|
At Course Completion
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.
|
Prerequisites
Course Outline
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 BackupsLab : Differential Database BackupsLab : Transaction Log BackupsLab : File and Filegroup BackupsLab : 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
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.
For more information or to give feedback, send e-mail to info@OneApex.com or call 949-597-2100 |
