50006 - SQL Server 2005 for the Oracle DBA
Course Code:	50006
Course Duration:	4 days
Course Price:	2720.00
Availability:	18 Jan 2012 - 18 Jan 2012 22 Feb 2012 - 24 Feb 2012 27 Feb 2012 - 27 Feb 2012 14 Mar 2012 - 16 Mar 2012 4 Jun 2012 - 8 Jun 2012 6 Jun 2012 - 8 Jun 2012

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:
• Understand the purpose of this workshop
• Evaluate the general similarities between Oracle and SQL Server
• Identify the new features available in SQL Server 2005

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:
• Clearly define database and instance
• Discuss the components of a database and an instance
• Examine the relationship between a database and an instance
• Understand client interaction between a database and an instance

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:
• Understand schema and schema objects
• Identify logical and physical structures that have storage organization
• Explain the architecture of data storage components and their hierarchy and relationships
• Manage stage structures
• Understand how to build the database by using files and table spaces
• Comprehend the transaction logging model employed to perform transaction recovery and rollback
• Distinguish major differences between the construction of the data dictionary in Oracle and SQL Server

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:
• Configure a database server
• Understand the Memory Address Space of a database server
• Identify key database memory structures
• Identify memory areas inside the Oracle System Global Area (SGA) and their SQL Server equivalents
• Understand process and thread-based architecture relevant to a relational database management system (RDBMS)
• Detail client interaction with a database server
• Understand database server process/threat and the work performed

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:
• Understand the organization of data in tables and the various forms of data
• Understand the supporting schema objects
• Understand the types of data that can be stored in tables
• Understand organization and presentation of data in complex real-world forms
• Understand storage organization of the schema objects

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:
• Identify the components of the relational engine and their roles in processing SQL
• Understand the basic concepts of SQL
• Define procedural SQL constructs and their mechanisms
• Identify query optimization by the relational engine and user overrides
• Understand transaction management

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:
• Understand the issues of concurrency and consistency of a multiuser environment.
• Explain how different levels of isolation are achieved by using different types of locks.
• Identify the mechanisms in place to accomplish security in the hierarchical structure from database to data dictionary, schema objects, and application data.
• Monitor database activity under auditing.

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:
• Plan and install SQL Server software
• Create and configure an instance
• Plan and create a database
• Identify the various states in which a database can exist
• Understand the data dictionary

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:
• Configure and measure memory usage of a database instance and its components
• Configure and monitor database processes
• Understand storage management at various levels of the storage hierarchy
• Identify resource utilization by sessions and transactions
• Understand statistics on low-level database structures

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:
• Understand identifier and naming conventions
• Manage tables and indexes
• Select storage parameters
• Manage constraints and triggers
• Manage views and sequences

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:
• Create and maintain login accounts
• Create and maintain user accounts
• Create and maintain user-defined roles
• Manage privileges for users and roles

Module 12: Data Transport
This module gives an overview of data transport using SQL Server and explains the elements of integration services.Lessons

• Overview
• Data Out Overview
• Data In Overview
• SQL Server Data Transport
• SQL Server Integration Services (SSIS)
• Elements of Integration Services
• SSIS Tools
• Copy Database Wizard
• Review

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:
• Understand the tools and functionality in Oracle and their equivalents in SQL Server for data transport in and out of the database.
• Understand the functionality in SQL Server for data transport into, out of, and within a database, and across multiple databases, multiple file formats, and other data sources and destinations.

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:
• Indentify database errors and types of failure
• Understand the various backup methods
• Obtain a high-level understanding of recovery methods
• Use RDBMS native tools used for backup and recovery
• Understand vendor solutions for backup and redundancy

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:
• Create a methodology to develop an application and the related database with optimal performance
• Understand the methodologies involved in tuning a running instance
• Identify key elements in instance, database and application tuning

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:
• Understand the high-availability definition and requirements
• Compare high availability features in Oracle and SQL Server
• Define scalability and understand its requirements
• Compare scalability features in Oracle and SQL Server

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:
• Identify the monitoring requirements of a database.
• Identify sources of information on server, database, and instance activity.
• Understand server and database components that can be monitored.
• Use SQL Server tools for monitoring.

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:
• Install SSMA and extension packs
• Configure SSMA
• Emulate Oracle packages, sequences and Oracle-style exception handling within SQL Server
• Generate migration assessment reports
• Convert and migrate schemas
• Convert procedures, functions, view and triggers
• Understand migration testing

Before attending this course, students must have:

• Basic knowledge of Oracle DBA