2780: Maintaining a Microsoft SQL Server 2005 Database
5 days: Instructor-Led Course Syllabus

Introduction 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. Audience This course is intended for IT Professionals wanting to become skilled on SQL Server 2005 product features and technologies for maintaining a database. At Course Completion After completing this course, students will be able to:   "The instructor  is incredibly knowledgeable. Excellent teaching skill. Absolutely want to take more classes taught by her!!" "She is the best instructor I ever had!! I would take a class just because she is teaching it." "She is very knowledgeable of the product and presents it well." 7/21/2006 MTM Report   "The instructor is wonderful it is great to have an instructor has good articulation and is easy to understand." 6/19/2006 MTM Report • 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.   Prerequisites Before attending this course, students must have: • 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. Course Outline Module 1: Installing and Configuring SQL Server 2005 This module explains how to plan for and install SQL Server 2005, how to manage a SQL Server 2005 installation, and how to use the SQL Server 2005 administrative tools. Lessons   • Preparing to install SQL Server. • Installing SQL Server. • Managing a SQL Server 2005 Installation. Lab 1: Installing SQL Server 2005 • Performing an Installation. • Managing SQL Server. After completing this module, students will be able to: • Prepare to install SQL Server. • Install SQL Server. • Manage a SQL Server installation. Module 2: Managing Databases and Files This module explains how to manage databases and files. Lessons • Planning databases. • Creating databases. • Managing databases. Lab 2: Creating and Managing Databases • Creating a Database. • Managing Filegroups. • Viewing Metadata. After completing this module, students will be able to: • Plan databases. • Create databases. • Manage databases. Module 3: Disaster Recovery This module explains how to plan and implement a backup and restore strategy. Lessons • Planning a Backup Strategy. • Backing Up User Databases. • Restoring User Databases. • Recovering Data from Database Snapshots. • System Database and Disaster Recovery. Lab 4: Implementing a Disaster Recovery Strategy • Implementing a Backup Strategy. • Restoring Databases. • Rebuild The Master Database. After completing this module, students will be able to: • Plan a backup strategy. • Back up user databases. • Restore user databases. • Recover data from database snapshots. • Back up and restore system databases. Module 4: Managing Security This module explains how to manage principals, securables, and permissions, and how to implement cryptography in a SQL Server database. Lessons • Overview of SQL Server Security. • Securing the Server Scope. • Securing the Database Scope. • Managing Keys and Certificates in SQL Server. Lab 3: Securing SQL Server • Creating Logins. • Creating and Managing Users. • Using a Certificate to Encrypt Data. After completing this module, students will be able to: • Describe the SQL Server security architecture. • Implement security at the server scope. • Implement security at the database and schema scopes. • Use cryptographic functionality in SQL Server. Module 5: Monitoring SQL Server This module explains how to monitor SQL Server performance and activity. Lessons • Viewing Current Activity. • Using System Monitor. • Using SQL Server Profiler. • Using DDL Triggers. • Using Event Notifications. Lab 5: Monitoring SQL Server • Monitoring SQL Server Performance. • Tracing SQL Server Activity. • Implementing DDL Trigger. After completing this module, students will be able to: • View current activity in SQL Server. • Monitor SQL Server performance with System Monitor. • Monitor SQL Server events with SQL Server Profiler. • Implement DDL Triggers. • Implement Event Notifications. Module 6: Transferring Data This module explains how to transfer and transform data. Lessons • Overview of Data Transfer. • Introduction to SQL Server Integration Services. • Using SQL Server Integration Services. Lab 6: Transferring Data with SQL Server Integration Services (SSIS) • Create an SSIS Package. • Deploying an SSIS Package. After completing this module, students will be able to: • Describe the tools and technologies for transferring data. • Describe the main features of SSIS. • Transfer and transform data with SSIS. Module 7: Automating Administrative Tasks This module explains how to use the SQL Server Agent to automate administrative tasks. Lessons • Automating Administrative Tasks in SQL Server 2005. • Configuring the SQL Server Agent. • Creating Jobs and Operators. • Creating Alerts. • Managing Multiple Servers. • Managing SQL Server Agent Security. Lab 7: Automating Database Administration • Configuring the SQL Server Agent. • Creating Operators and Jobs. • Creating Alerts. After completing this module, students will be able to: • Describe automation options in SQL Server. • Configure the SQL Server Agent. • Create jobs and operators. • Create alerts. • Manage multiple servers. • Manage SQL Server Agent security. Module 8: Maintaining High Availability This module explains how to implement high availability technologies with SQL Server 2005. Lessons • Introduction to High Availability. • Implementing Server Clustering. • Implementing Database Mirroring. • Implementing Log Shipping. Lab 8: Configuring Database Mirroring • Setting the Recovery Model. • Backing Up and Restoring the Database. • Starting Database Mirroring. • Performing an Automatic and Manual Failover. After completing this module, students will be able to: • Describe high availability options for SQL Server. • Implement Server Clustering. • Implement Database Mirroring. • Implement Log Shipping. Module 9: Introduction to Replication This module explains considerations for implementing replication. Lessons • Overview of Replication. • Replication Scenarios. Lab 9: Implementing Replication • Creating a Publication. • Creating a Subscription. After completing this module, students will be able to: • Describe replication and its components. • Describe common replication scenarios.