7033 - Microsoft SQL Server 2005 for the Experienced Oracle Database Administrator

This four-day instructor-led course will teach Oracle DBAs how to leverage their skills and experience as an Oracle DBA to manage a SQL Server system. This course provides a quick start for the Oracle DBA to map, compare and contrast the realm of Oracle database management to SQL Server database management.

Module 1: Database and Instance
In this module we will provide terminologies with proper definitions to unambiguously identify the various components of a database management system.

Lessons
• Concept of Database and Instance
• Client interaction with Database and Instance
• Database limits
• Lab

After completing this lab, you will be able to clearly define database and instances, discuss the components of a database and an instance, examine the relationship between a database and an instance and understand client interaction between a database and an instance

Module 2: Database Architecture
This module examines the SQL Server 2005 database architecture.

Lessons
• Schema and schema objects
• Storage Architecture
• Logging Model
• Data Dictionary
• Lab

After completing this module you will be able to:
• Understand schema and schema objects
• Identify logical and physical structures using storage organisation
• Explain the architecture of data storage components and their hierarchy and relationships
• Management of storage structures
• Understand how to build the database using files and tablespaces
• 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 3: Instance Architecture
This module describes the instance architecture and explains how the two main components of an instance, the memory and processes, are leveraged to achieve "high performance" in large multi-user environment.

Lessons
• 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
• Lab

After completing this module you 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 Oracle SGA and their SQL Server equivalents
• Process and thread-based architecture relevant to RDBMS
• Detail client interaction with database server
• Understand database server processes/threat and the work performed

Module 4: Data Objects
This module discusses the objects in the database that are used to organise and store the content that most concerns the user; the business data.

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

After completing this module you will be able to understand:
• The organisation of data in tables and the various forms of data
• The supporting schema objects
• Types of data that can be stored in tables
• Organisation and presentation of data in complex real-world forms
• Storage organisation of the schema objects

Module 5: Data Access
This module explains how data access works and the many components required to access the data from an application.

Lessons
• Relational engine
• Structured Query Language
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Control Statements
• Procedural SQL
• Cursors
• Error Handling
• Query Optimisation
• Transaction Management
• Lab

After completing this module you will be able to:
• Identify the components of the relational engine and their roles in processing SQL
• Understand the basic concepts of Structured Query Language
• Define procedural SQL constructs and their mechanisms
• Identify query optimization by the relational engine and user overrides
• Understand Transaction Management

Module 6: Data Protection
This module examines in detail the data protection mechanisms in SQL Server 2005.

Lessons
• Concurrency and Consistency
• Locking
• Flashback Query in Oracle
• Database Security
• Privileges and Roles
• Auditing
• Lab

After completing this module you will be able to:
• Understand the issues of concurrency and consistency of a multi-user environment
• Explain how different levels of isolation are achieved 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 7: Basic Administration
This module we will examine the data available in the data dictionary and the functionality available to be able to use the information for administration.

Lessons
• Installing SQL Server
• Database System
• SQL Server databases
• Database Configuration
• Database Maintenance
• Data Dictionary
• System Stored Procedures
• Lab

After completing this module you 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 8: Server Management
This module describes how to tie resources to other logical storage structures and to schema objects and the resources to sessions and transactions.

Lessons
• Managing Memory
• Managing Processes
• Managing Storage
• Managing Sessions and Transactions
• Locks and Latches
• Lab

After completing this module you 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
• Statistics on low level database structures

Module 9: Managing Schema Objects
This module describes how to implement naming guidelines for identifiers in schema object definition, manage the storage and structure of schema objects and implement data integrity using constraints.

Lessons
• Object Identifiers and Naming
• Managing Tables
• Managing Constraints
• Managing Triggers
• Managing Indexes
• Managing Views
• Sequences
• Lab

After completing this module you 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 10: Database Security
This module describes how an administrator can create and manage logins, users, roles and profiles. You will also learn to implement security policies and access through system and object privileges.

Lessons
• Managing Users
• Managing Privileges
• Managing Roles
• Lab

After completing this module you 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 11: Data Transport
This module explores one of the common functions of the database administrator, which is, moving data in and out of a database.

Lessons
• Data out Overview
• Data in Overview
• SQL Server Data Transport
• SQL Server Integration Services (SSIS)
• Elements of Integration Services
• SSIS Tools
• Copy Database Wizard
• Lab

After completing this module you 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 tools and functionality in SQL Server for data transport into, out of, within a database and across multiple databases, multiple file formats and other data sources and destinations

Module 12: Backup and Recovery
This module we delve into backup and recovery concepts in SQL Server.

Lessons
• Database Errors
• Backup methods
• Recovery Models
• Factors affecting backup strategy decisions
• Recovering from database errors
• Backup and Recovery tools
• Third-party backup and recovery solutions
• Lab

After completing this module you will be able to:
• Identify database errors and types of failure
• Understand the various backup methods
• Obtain a high level understanding of recovery methods
• RDBMS native tools used for backup and recovery
• Vendor solutions for backup and redundancy

Module 13: Performance Tuning
This module describe two performance tuning methodologies; application tuning and instance tuning of SQL Server 2005.

Lessons
• Tuning Methodologies—Tuning While Building
• Tuning Methodologies—Performance Tuning
• Application Tuning
• Instance Tuning
• SQL Server Performance Tuning Tools
• Lab

After completing this module you 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 14: Scalability and High Availability
This module discusses scalability and high availability of SQL Server 2005.

Lessons
• High Availability
• Clustering
• Standby Database
• Replication—Objects
• Replication—Types of
• Database Mirroring
• Scalability
• Scalability and HA in Backup and Recovery
• Lab

After completing this module you will be able to:
• Understand 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 15: Monitoring
In this module we will discover the various monitoring activities that need to be performed against the database and its server, and the tools at our disposal.

Lessons
• Monitoring Availability
• Monitoring Errors
• Error Logs
• Monitoring Performance – Server
• Monitoring Performance – Database and Instance
• Monitoring Tools
• Lab

After completing this module you will be able to:
• Identify the monitoring requirements of a database
• Sources of information on server, database and instance activity
• Server and database components that can be monitored
• SQL Server Tools for monitoring

Module 16: Microsoft SQL Server Migration Assistant (SSMA)
This module you will learn how the Microsoft SQL Server Migration Assistant (SSMA) tool helps assess a migration task, convert PL/SQL code to T-SQL code, migrate data, test the migrated objects and deploy them. SSMA can, significantly reduce the overall time needed to complete a migration by automating several processes.

Lessons
• Installing SSMA
• Configuring Options
• Simulations
• Assessment Reports
• Schema Conversion and Migration
• Data Migration
• Converting Stored Programs
• Viewing modes
• Migration Testing
• Test SQL
• SSMA Workspace
• Lab

After completing this module will be able to:
• Install SQL Server Migration Assistant (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 schema
• Migrate data
• Convert procedures, functions, views, triggers
• Migration testing

This course is intended for experienced database professionals currently implementing and administering non Microsoft enterprise database management systems who need to expand their database skill-set to SQL Server 2005.

Before attending this course students must have:

• Thorough experience with SQL Server 2000
• Have experience with SQL Server 2005 and a comprehensive understanding of all the new features and technologies
• The relational engine
• Storage engine
• Integration Services
• Analysis Services
• Reporting Services
• Be familiar with SQL Server 2005 features, tools and technologies