5-Day (SQ-DBAEXPT-301-EN)

Description
Target Audience
Prerequisites
Course Objectives
Course Summary Outline

Description

Database administrators often need information that goes beyond the simple CREATE, DROP, and ALTER statements.  This 5-day intensive course is designed for developers and database administrators who wish to learn more advanced database administrative techniques or who wish to learn components of SQL Server they do not yet understand fully and need to implement in their current environments. This course prepares individuals who have 2-3 years of experience with SQL Server databases with the skills they need to maintain and administrate larger or more complex Microsoft SQL Server 2005 and 2008 databases.

Target Audience

This course is intended for:

• SQL Server 2005 database administrators wishing to learn SQL Server 2008 database administration
• Developers who need to maintain and administrate SQL Server databases
• SQL Server database administrators who wish to learn more than just the basics of database administration.

Prerequisites

Before attending this course, it is recommended that students have the following skills:

• 2-3 years experience with SQL Server database administration
• Experience with SQL Server development
• Experience with Transact-SQL

Course Objectives

This course has been designed with the objective of providing advanced knowledge for the installation and administration of a SQL Server 2005 or SQL Server 2008 database. The course attendees will not only learn the topic listed in the course outline, they have the opportunity to learn SQL Server administrative best practices and tips/tricks from some of the most experienced SQL Server database administrators in the world.

Course Summary Outline

Module 01: Installing SQL Server

This module will discuss the installation process, how it differs in SQL Server 2005 and 2008, and some common best practices when it comes to installing SQL Server. The module will cover the following topics:

• Service Accounts
• Authentication Methods
• File locations
• Default Paths
• Files and file groups
• Database options
• Recovery model
• Collations

Module 02: Using SQL Server Instances

This module will provide the course attendees with the understanding of how to configure and use SQL Server instances. The module will cover the following topics:

• Multiple Instances
• sp_configure

Module 03: Configuring SQL Server Services

This module will provide the course attendees with the understanding of how to configure SQL Server services. The module will cover the following topics:

• Configuration Manager
• SQL Browser

Module 04: Configuring SQL Server Components

This module will provide the course attendees with the understanding of how to configure many of the SQL Server components that can be installed with each SQL Server installation.  The module will cover the following topics:

• SSIS
• RS
• AS
• Replication
• Database Mail
• Full-Text Search

Module 05: Using SQL Server Policy Management

This module will provide the course attendees with advanced-level understanding of using the SQL Server 2008’s Policy Management component.  The module will cover the following topics:

• Registration Server
• Multi-Server Policies

Module 06: Using the SQL Server Agent

This module will provide the course attendees with advanced-level understanding of how to configure SQL Server jobs using the SQL Server Agent.  The module will cover the following topics:

• Notification of job execution
• Proxy accounts
• Credentials

Module 07: Understanding Error Monitoring

This module will provide the course attendees with advanced-level understanding of SQL Server error monitoring.  The module will cover the following topics:

• Advanced SQL Server
• Extended Events

Module 08: Managing Alerts

This module will provide the course attendees with the understanding of how to configure and use SQL Server alerts. The module will cover the following topics:

• Performance condition alerts
• SQL event alerts

Module 09: Performing SQL Server Backups and Restores

This module will provide the course attendees with a more advanced-level understanding of SQL Server backups and restores.  The module will cover the following topics:

• Backing Up SQL Server
  • Full backups
  • Differential backups
  • Transaction log
  • Compressed backups
  • File and filegroup backups
  • Verifying backups
• Restoring SQL Server
  • Online restores
  • File and filegroup restores
  • Tail of the transaction log

Module 10: Understanding SQL Server Security

This module will provide the course attendees with a more advanced-level understanding of SQL Server security features. The module will cover the following topics:

• Encryption
  • Data Encryption
  • Transparent Data Encryption
• Impersonation
  • EXECUTE AS
  • IMPERSONATION
  • Code Signing

Module 11: Performing Database Maintenance

This module will provide the course attendees with the advanced understanding of SQL Server database maintenance.  The module will cover the following topics:

• DBCC Checks
• Suspect Pages
  • Online page restore

Module 12: Using SQL Server Performance Tools

This module will provide the course attendees with the understanding of how to use SQL Server and Widows performance tools. The module will cover the following topics:

• SQL Server Profiler
• System Monitor (Perfmon)
• Database Tuning Advisor

Module 13: SQL Server Data Types

This module will provide the course attendees with advanced-level understanding of the data types used in SQL Server 2005 and 2008.  The module will cover the following topics:

• FILESTREAM
• Spatial
• Structured and semi-structured
• Collations

Module 14: SQL Server Database Tables

This module will provide the course attendees with the understanding of database table creation options.  The module will cover the following topics:

• Computed and persisted columns

Module 15: SQL Server Database Views

This module will provide the course attendees with the understanding of database view creation options.  The module will cover the following topics:

• WITH ENCRYPTION
• Indexed Views

Module 16: SQL Server Constraints

This module will provide the course attendees with the understanding of database table constraints.  The module will cover the following topics:

• Cascading referential integrity

Module 17: SQL Server Stored Procedures

This module will provide the course attendees with the understanding of database stored procedure creation options.  The module will cover the following topics:

• WITH ENCRYPTION
• RECOMPILE

Module 18: SQL Server User-Defined Functions

This module will provide the course attendees with the understanding of Transact-SQL user-defined function creation options.  The module will cover the following topics:

• WITH SCHEMABINDING
• EXECUTE AS
• Manage permissions (GRANT, DENY, REVOKE)

Module 19: Indexes

This module will provide the course attendees with a deeper understanding of table indexes. The module will cover the following topics:

• Creating spatial indexes
• Creating partitioned indexes
• Fill Factor
• Pad Index

Module 20: Using Data Partitions

This module will provide the course attendees with understanding of using SQL Server partitioning. The module will cover the following topics:

• Creating table partitions
• Switching data table partitions
• Merging table partitions
• Splitting table partitions
• Database-Level partitioning

Module 21: Using the Resource Governor

This module will provide the course attendees with a deep understanding of how to configure and use SQL Server 2008 Resource Governor. The module will cover the following topics:

• Creating resource pools
• Configuring the resource governor

Module 22: Understanding High Availability

This module will provide the course attendees with an understanding of high availability concepts and how to implement high availability in SQL Server 2005 and 2008. The module will cover the following topics:

• Database Mirroring
  • Database mirroring types
  • Configuring database mirroring
  • Failing over database mirroring
• Clustering
  • Introduction to failover clustering
  • Installing SQL Server on a clustering
  • Cluster administration
• Replication
  • Configuring replication