NOTE:
** Course Modules and Labs denoted by ** are assigned as self study and not covered during class hours.
Modules Covered from Course 2779
Module 1: Creating Databases and Database Files
Lessons
- Creating Databases
- Creating Filegroups
- Creating Schemas
- Creating Database Snapshots
Lab 1: Creating Databases and Database Files
- Creating a Database
- Creating Schemas
- Creating a Database Snapshot
After completing this module, students will be able to:
- Create databases
- Create filegroups
- Create schemas
- Create database snapshots
Module 2: Creating Data Types and Tables
Lessons
- Creating Data Types
- Creating Tables
- Creating Partitioned Tables
Lab 2: Creating Data Types and Tables
- Creating Data Types
- Creating Tables
- Creating Partitioned Tables
After completing this module, students will be able to:
- Create new data types
- Create new tables
- Create partitioned tables
Module 3: Using XML
Lessons
- Retrieving XML by Using FOR XML
- Shredding XML by Using OPENXML
- Introducing XQuery
- Using the xml Data Type
Lab 3: Using XML
- Mapping Relational Data and XML
- Storing XML Natively in the Database
- Using XQuery with xml Methods
After completing this module, students will be able to:
- Retrieve XML by using the FOR XML clause
- Shred XML by using the OPENXML function
- Use XQuery expressions
- Use the xml data type
Module 4: Creating and Tuning Indexes
Lessons
- Planning Indexes
- Creating Indexes
- Optimizing Indexes
- Creating XML Indexes
Lab 4: Creating and Tuning 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 by Using Triggers and XML Schemas
Lessons
- Implementing Triggers
- Implementing XML Schemas
Lab 6: Implementing Data Integrity by Using Triggers and XML Schemas
- Creating Triggers
- Implementing XML Schemas
After completing this module, students will be able to:
- Implement data integrity in SQL Server 2005 databases by using triggers
- Implement data integrity in SQL Server 2005 databases by using XML schemas
Module 8: Implementing Stored Procedures
Lessons
- Implementing Stored Procedures
- Creating Parameterized Stored Procedures
- Working With Execution Plans
- Handling Errors
Lab 8: Implementing Stored Procedures
- Creating Stored Procedures
- Working With Execution Plans
After completing this module, students will be able to:
- Implement stored procedures
- Create parameterized stored procedures
- Work with execution plans
- Handle errors in stored procedures
Module 9: Implementing Functions
Lessons
- Creating and Using Functions
- Working with Functions
- Controlling Execution Context
Lab 9: Implementing Functions
- Creating Functions
- Controlling Execution Context
After completing this module, students will be able to:
- Create and use functions
- Work with functions
- Control execution context
Module 10: Implementing Managed Code in the Database
Lessons
- Introduction to the SQL Server Common Language Runtime
- Importing and Configuring Assemblies
- Creating Managed Database Objects
Lab 10: Implementing Managed Code in the Database
- Importing an Assembly
- Creating Managed Database Objects
After completing this module, students will be able to:
- Identify appropriate scenarios for managed code in the database
- Import and configure assemblies
- Create managed database objects
Module 12: Using Service Broker
Lessons
- Service Broker Overview
- Creating Service Broker Objects
- Sending and Receiving Messages
Lab 12: Using Service Broker
- Creating Service Broker Objects
- Implementing the Initiating Service
- Implementing the Target Service
After completing this module, students will be able to:
- Describe Service Broker functionality and architecture
- Create Service Broker objects
- Send and receive Service Broker messages
Modules Covered from Course 2780
Module 1: Installing and Configuring SQL Server 2005
Lessons
- Preparing to Install SQL Server
- Installing SQL Server 2005
- Managing a SQL Server 2005 Installation
Lab 1: Installing and Configuring SQL Server 2005
- Performing an Installation
- Managing SQL Server
After completing this module, students will be able to:
- Explain how to prepare the hardware and other resources necessary to install SQL Server 2005
- Install SQL Server 2005
- Manage and configure a SQL Server 2005 installation
Module 3: Disaster Recovery
Lessons
- Planning a Backup Strategy
- Backing Up User Databases
- Restoring User Databases
- Performing Online Restore Operations
- Recovering Data from Database Snapshots
- System Database and Disaster Recovery
Lab 3: Disaster Recovery
- Implementing a Backup Strategy
- Restoring and Recovering a Database
- Performing Piecemeal Backup and Restore Operations
- Restoring the master Database
After completing this module, students will be able to:
- Plan a backup strategy for a database
- Back up user databases
- Restore user databases from backups
- Restore data in a user database while it is online
- Recover data for a user database from a database snapshot
- Restore and recover systems databases
Module 4: Managing Security
Lessons
- Overview of SQL Server Security
- Protecting the Server Scope
- Protecting the Database Scope
- Managing Keys and Certificates in SQL Server
Lab 4: Managing Security
- Creating Logins and Assigning Server-Scope Permissions
- Creating and Managing Users
- Using a Certificate to Protect Data
After completing this module, students will be able to:
- Describe how SQL Server manages security
- Protect SQL Server at the server level
- Protect SQL Server databases
- Use keys and certificates to protect SQL Server objects
Module 5: Monitoring SQL Server
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 Triggers
After completing this module, students will be able to:
- Examine the current activity in a SQL Server instance
- Use System Monitor to obtain performance data about your computer and the instances of SQL Server running on your computer
- Use SQL Server Profiler to trace server and database activity
- Implement DDL triggers that enable you to audit changes to the structure of database objects
- Use event notifications to capture and monitor significant events for a SQL Server instance
Module 6: Transferring Data
Lessons
- Overview of Data Transfer
- Introduction to SQL Server Integration Services
- Using SQL Server Integration Services
- Features of SQL Server Integration Services
Lab 6: Transferring Data
- Creating an SSIS Package
- Deploying an SSIS Package
- Using SSIS to Extract Data, Perform Lookups, Sort, and Split Data
After completing this module, students will be able to:
- Describe the problems surrounding data transfer and the tools that SQL Server 2005 provides to perform data transfer
- Describe the purpose of SQL Server Integration Services
- Use SQL Server Integration Services to transfer data into a SQL Server database
- Describe the features of SQL Server Integration Services
Module 7: Automating 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 Administrative Tasks
- Configuring SQL Server Agent
- Creating Operators and Jobs
- Creating Alerts
After completing this module, students will be able to:
- Define SQL Server 2005 administrative tasks and schedule these tasks to run automatically
- Configure SQL Server Agent to support automatic task scheduling
- Script tasks by using SQL Server jobs, and define operators for managing these jobs
- Define alerts to warn operators about events raised by SQL Server
- Define and manage administrative tasks that span multiple servers
- Configure SQL Server Agent security
Module 9: Maintaining High Availability
Lessons
- Introduction to High Availability
- Implementing Server Clustering
- Implementing Database Mirroring
- Implementing Log Shipping
- Implementing Peer-to-Peer Replication
Lab 9:
- Configuring Database Mirroring to Support Failover
- Implementing Distributed High Availability
After completing this module, students will be able to:
- Describe the factors affecting database availability
- Explain how to implement clustering to support fast failover of computers running Microsoft SQL Server instances
- Describe how to use SQL Server mirroring to implement a software solution for fast failover
- Describe how to implement log shipping to support fast recovery of a standby SQL Server database
- Explain how to use peer-to-peer replication to implement high availability in a distributed environment
**Course Modules assigned as self study (after class hour)
**Module 5(2779): Implementing Data Integrity by Using Constraints
Lessons
- Data Integrity Overview
- Implementing Constraints
**Lab 5: Implementing Data Integrity by Using Constraints
- Creating Constraints
- Disabling Constraints
After completing this module, students will be able to:
- Describe the options for enforcing data integrity in SQL Server 2005
- Implement data integrity in SQL Server 2005 databases by using constraints
**Module 7(2779): Implementing Views
Lessons
- Introduction to Views
- Creating and Managing Views
- Optimizing Performance by Using Views
Lab 7: Implementing 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
- Explain how to optimize query performance by using views
**Module 11(2779): Managing Transactions and Locks
Lessons
- Overview of Transactions and Locks
- Managing Transactions
- Understanding SQL Server Locking Architecture
- Managing Locks
**Lab 11: Managing Transactions and Locks
- Using Transactions
- Managing Locks
After completing this module, students will be able to:
- Describe how SQL Server 2005 transactions use locks
- Execute and cancel a transaction
- Describe concurrency issues and SQL Server 2005 locking mechanisms
- Manage locks
**Module 13(2779): Using Notification Services
Lessons
- Introduction to Notification Services
- Developing Notification Services Solutions
After completing this module, students will be able to:
- Describe how Notification Services operates
- Develop a Notification Services application
**Module 8(2780): Implementing Replication
Lessons
- Overview of Replication
- Implementing Replication
- Configuring Replication in Some Common Scenarios
Lab 8: Implementing Replication
- Creating a Publication
- Creating a Subscription
- Implementing HTTP Merge Replication
After completing this module, students will be able to:
- Describe replication and its components
- Configure and implement replication
- Use replication to meet the requirements of some common scenarios
|
Printable version
