Module 1: Workshop Introduction

This module introduces the workshop, its purpose, and its objectives.

Lessons

• Overview
• Workshop Objectives
• Workshop Prerequisites
• Workshop Outline
• RDBMS Similarities
• Introduction to SQL Server 2005
• SQL Server 2005 Manageability
• SQL Server 2005 High Availability
• SQL Server 2005 Scalability
• SQL Server 2005 Security
• SQL Server 2005 Programmability
• SQL Server 2005 Data Movement
• SQL Server 2005 Tools

After completing this module, students will be able to:

Module 2: Database and Instance

This module explains how to identify the various components of a database management system.

Lessons

• Overview
• Concept of Database and Instance
• Client Interaction with Database and Instances
• Database Limits
• Review

After completing this module, students will be able to:

Module 3: Database Architecture

This module defines schema and schema objects, examines storage architecture and its physical and logical components, details the hierarchy in the logical structures, examines the structures used in the implementation of the logging model, and takes a brief look at the location and composition of the data dictionary system catalog.

Lessons

• Overview
• Schema and Schema Objects
• Storage Architecture
• Logging Model
• Data Dictionary
• Review

After completing this module, students will be able to:

Module 4: Instance Architecture

This module explains the details of the instance architecture, and explains how the two main components of an instance, the memory and process, are used to archive ?high performance? in a large multiuser environment.

Lessons

• Overview
• Configuration
• Memory Architecture - Overview
• Process and Thread-Based Architecture
• Oracle Client-Database Interaction
• SQL Server 2005 Client-Database Interaction
• Oracle Background Processes
• SQL Server Background Processes
• Review

After completing this module, students will be able to:

Module 5: Data Objects

This module explains the objects in the database that are used to organize and store the content that most concerns the user, the business data.

Lessons

• Overview
• Schema Objects - Table
• Schema Objects - Table
• Clustered Index
• Constraints
• Triggers
• Indexes
• Views
• Character-Based Data Types
• Numeric Data Types
• Binary Data Types
• Object Data Storage
• Review

After completing this module, students will be able to:

Module 6: Data Access

This module explains various components of the relational engine that parses, optimizes, and executes database calls and fetches data. It discusses the different types of statements that constitute the Structured Query Language (SQL). It introduces the procedural SQL offered by Oracle and SQL Server. It discusses that the cost-based optimizer in Oracle and SQL Server optimization can be superseded by hints from the user. And lastly, it explains the concepts of local and distributed transactions and the two-phase commit.

Lessons

• Overview
• Relational Engine
• Structure Query Language (SQL)
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Control Statements
• Procedural SQL
• Cursors
• Error Handling
• Query Optimization
• Transaction Management
• Review

After completing this module, students will be able to:

Module 7: Data Protection

This module explains the intricate internal protection mechanisms used to ensure that users accessing the same database are protected from the effects of each other's transactions. These mechanisms include concurrency and consistency, logins and authentication, and auditing functionality.

Lessons

• Overview
• Concurrency and Consistency
• Locking
• Flashback Query in Oracle
• Database Security
• Privileges and Roles
• Auditing
• Review

After completing this module, students will be able to:

Module 8: Basic Administration

This module introduces the actions that need to be performed before the actual start of enrolling users and getting down to day-to-day administrative duties.

Lessons

• Overview
• SQL Server Installation
• Database System
• SQL Server Databases
• Database Configuration
• Database Maintenance
• Data Dictionary
• System Stored Procedures
• Review

Lab: Creating Databases and Setting Configuration Parameters

• Creating a Database
• Setting Configuration Parameters

After completing this module, students will be able to:

Module 9: Server Management

This module applies what was learned in Modules 2 and 3 with regard to system and storage resources within the administration of the system. Many of the aspects of files and file groups were covered in previous modules. In this module, these resources are tied to other logical storage structures and to schema objects. The module explains how to tie all the resources to sessions and transactions, which is a prerequisite to performance tuning.

Lessons

• Overview
• Memory Management
• Process Management
• Storage Management
• Session and Transaction Management
• Locks and Latches
• Review

Lab: Utilizing Resources

• Understanding Threads
• Maintaining Filegroups
• Maintaining Data Files

After completing this module, students will be able to:

Module 10: Schema Object Management

This module explains how Oracle and SQL Server differ in interpretation of identifier names in schema and object definition. It explains how to create and maintain the types of tables and indexes found in Oracle and SQL Server. It explains the differences in selection of storage parameters between Oracle and SQL Server. It compares other schema objects in Oracle and SQL Server, such as constraints, triggers, views, and sequences, and explains how to manage them.

Lessons

• Overview
• Object Identifiers and Naming
• Table Management
• Constraint Management
• Trigger Management
• Index Management
• View Management
• Sequences
• Review

Lab: Creating and Maintaining Tables and Associated Objects

• Creating Tables and Associated Objects
• Maintaining Tables and Indexes

After completing this module, students will be able to:

Module 11: Database Security

This module explains how an administrator can create and manage logins, users, roles, and profiles. It also explains how to implement security policies and access through system and object privileges.

Lessons

• Overview
• User Management
• Privilege Management
• Role Management
• Review

Lab: Securing Databases

• Creating Logins and Users

Lab: Granting Permissions

• Granting Permissions

Lab: Revoking Permissions

• Revoking Permissions

After completing this module, students will be able to:

Module 12: Data Transport

This module gives an overview of data transport using SQL Server and explains the elements of integration services.Lessons

Lab: Transferring Data

• Perform Data Transfer by Using BCP and BULK INSERT Commands

Lab: Exporting Data by Using the Import/Export Wizard

• Transfer Data by Using the Import/Export Wizard

After completing this module, students will be able to:

Module 13: Backup and Recovery

This module explains the backup and recovery concepts in SQL Server.

Lessons

• Overview
• Database Errors
• Backup Methods
• Recovery Models
• Factors Affecting Backup Strategy Decisions
• Database Error Recovery
• Backup and Recovery Tools
• Third-Party Backup and Recovery Solutions
• Review

Lab: Creating and Executing a Maintenance Plan for Backup

• Creating and Executing a Maintenance Plan for Backup

After completing this module, students will be able to:

Module 14: Performance Tuning

This module presents a high-level discussion on performance tuning methodologies and an overview of the tools and techniques for performance analysis and tuning.

Lessons

• Overview
• Tuning Methodologies?Tuning While Building
• Tuning Methodologies?Performance Tuning
• Application Tuning
• Instance Tuning
• SQL Server Performance Tuning Tools
• Review

Lab: Using Query Analyzer

• Using Profiler and Query Editor

After completing this module, students will be able to:

Module 15: Scalability and High Availability

This module defines high availability and the role it plays in modern day businesses and discusses the key features, including clustering, standby databases, and replication. It also discusses the meaning of scalability with respect to very large database (VLDB) and online transaction processing (OLTP) environments and examines the key scalability features in Oracle and SQL Server.

Lessons

• Overview
• High Availability
• Clustering
• Standby Database
• Replication?Objects
• Replication?Types of
• Database Mirroring
• Scalability
• Scalability and High Availability in Backup and Recovery
• Review

After completing this module, students will be able to:

Module 16: Monitoring

This module explains the various monitoring activities that need to be performed against the database and its server, and the tools that are available.

Lessons

• Overview
• Availability Monitoring
• Error Monitoring
• Error Logs
• Performance Monitor?Server
• Performance Monitor?Database and Instance
• Tool Monitoring
• Review

After completing this module, students will be able to:

Module 17: Microsoft SQL Server SSMA

This module explains how to install and configure SQL Server Migration Assistant (SSMA) and extension packs. It also explains how the SSMA tool helps assess a migration task, convert PL/SQL code to T-SQL code, migrate data, test the migrated objects, and deploy them.

Lessons

• Overview
• SSMA Installation
• Option Configuration
• Simulations
• Assessment Reports
• Schema Conversion and Migration
• Data Migration
• Stored Program Converting
• Viewing Modes
• Migration Testing
• SQL Testing
• SSMA Workspace
• Review

After completing this module, students will be able to: