Implementing and Maintaining a Microsoft SQL Server 2005 Database

Boot Camp for Exam 70-431: Five days; Instructor-Led

Introduction

This five-day instructor-led course provides students with product knowledge and skills needed to implement and maintain a Microsoft SQL Server 2005 database. The course focuses on teaching individuals how to use SQL Server 2005 product features and tools related to implementing a database.

Audience

This course is intended for IT Professionals wanting to become skilled on SQL Server 2005 product features and technologies for implementing and maintaining a database.

At Course Completion

After completing this course, students will be able to:

• Create databases and database files.
• Create data types and tables.
• Use XML-related features in Microsoft SQL Server 2005.
• Plan, create, and optimize indexes.
• Implement data integrity in Microsoft SQL Server 2005 databases by using constraints, triggers, and XML schemas.
• Implement views.
• Implement stored procedures and functions.
• 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 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: Creating Databases and Database Files

This module explains how to create databases, filegroups, schemas, and database snapshots.

Lessons

• Creating Databases.
• Creating Filegroups.
• Creating Schemas.
• Creating Database Snapshots.

Lab 2: Creating a Database

• Creating a Database.
• Creating Schemas.

After completing this module, students will be able to:

• Create databases.
• Create filegroups.
• Create schemas.
• Create database snapshots.

Module 3: Creating Data Types and Tables

This module explains how to create data types and tables. It also describes how to create partitioned tables.

Lessons

• Creating Data Types.
• Creating Tables.
• Creating Partitioned Tables.

Lab 3: Creating Data Types and Tables

• Creating Data Types.
• Creating Tables.
• Creating Partitioned Tables.

After completing this module, students will be able to:

• Create data types.
• Create tables.
• Create partitioned tables.

Module 4: Using XML

This module explains how to use the FOR XML clause and the OPENXML function. It also describes how to use the xml data type and its methods.

Lessons

• Retrieving XML by Using FOR XML.
• Shredding XML by Using OPENXML.
• Using the xml Data Type.

Lab 4: Working with XML

• Mapping Relational Data and XML.
• Storing XML Natively in the Database.

After completing this module, students will be able to:

• Retrieve XML with FOR XML.
• Shred XML with OPENXML.
• Use the xml data type.
• Use the methods of the xml data type.

Module 5: Creating and Tuning Indexes

This module explains how to plan, create, and optimize indexes. It also describes how to create XML indexes.

Lessons

• Planning Indexes.
• Creating Indexes.
• Optimizing Indexes.
• Creating XML Indexes.

Lab 5: Creating Indexes

• Creating Indexes.
• Tuning Indexes.
• Creating XML Indexes.

After completing this module, students will be able to:

• Plan indexes.
• Create indexes.
• Optimize indexes.
• Create XML indexes.

Module 6: Implementing Data Integrity

This module explains how to implement constraints, triggers, and XML schemas.

Lessons

• Data Integrity Overview.
• Implementing Constraints.
• Implementing Triggers.
• Implementing XML Schemas.

Lab 6: Implementing Data Integrity

• Creating Constraints.
• Creating Triggers.
• Implementing XML Schemas.

After completing this module, students will be able to:

• Describe types of data integrity and options for enforcing it.
• Implement constraints.
• Implement triggers.
• Implement XML schemas.

Module 7: Implementing Views

This module explains how to create views.

Lessons

• Introduction to Views.
• Creating and Managing Views.
• Optimizing Performance by Using Views.

Lab 7: Creating Views

• Creating Views.
• Creating Indexed Views.
• Creating Partitioned Views.

After completing this module, students will be able to:

• Describe the purpose of views.
• Create and manage views.
• Design views for performance.

Module 8: Implementing Stored Procedures and Functions

This module explains how to create stored procedures and functions.

Lessons

• Implementing Stored Procedures.
• Creating Parameterized Stored Procedures.
• Creating Functions.
• Handling Errors.
• Controlling Execution Context.

Lab 8: Creating Stored Procedures and Functions

• Creating Stored Procedures.
• Creating Functions.

After completing this module, students will be able to:

• Implement stored procedures.
• Create parameterized stored procedures.
• Handle errors in a stored procedure.
• Implement Scalar Functions.
• Create Table Valued Functions.
• Control Execution Context.

Module 9: 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 9: 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 10: 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 10: 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 11: 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 11: 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 12: 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 12: 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 13: 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 13: 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 14: 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 14: 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 15: Introduction to Replication

This module explains considerations for implementing replication.

Lessons

• Overview of Replication.
• Replication Scenarios.

Lab 15: 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.

 
© Corporate Training Group, Inc.: 120 Wood Avenue, Suite 405, Iselin, NJ 08830
Phone: 732-635-9033 | Fax: 732-635-9032 | Email: info@ctgtraining.com | Directions

Home | About Us | Services | Consulting | Course Outlines | Class Schedule | Contact Us