Course 7033A:

Microsoft SQL Server 2005 for the Experienced Oracle Database Administrator

Length:3 Days
Published:July 12, 2006
Language(s):English
Audience(s):IT Professionals
Level:
Technology:Microsoft SQL Server 2005
Type:Course
Delivery Method:Instructor-led (classroom)
About this Course
The purpose of this 4-day instructor-led course is to 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..
Audience Profile
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.
Course OutlineModule 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 instanceModule 2: Database Architecture
This module examines the SQL Server 2005 database architecture.
Lessons
  • Schema and schema objects
  • Storage Architecture
  • Logging Model
  • Data Dictionary
After completing this module, students will be able to:
  • Understand schema and schema objects
  • Identify logical and physical structures using storage organization
  • 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
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 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 organize and store the content that most concerns the user; the business data.
Lessons
  • 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
After completing this module, students will be able to:
  • The organization of data in tables and the various forms of data
  • The supporting schema objects
  • Types of data that can be stored in tables
  • Organization and presentation of data in complex real-world forms
  • Storage organization 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 Optimization
  • Transaction Management
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 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
After completing this module, students 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
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 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
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.
  • 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
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 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
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 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
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 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
After completing this module, students 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
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 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
After completing this module, students 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
After completing this module, students 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
After completing this module, students 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
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.
Have Questions? For advice about training and certification, connect with peers: For questions about a specific certification, chat with a Microsoft Certified Professional (MCP): To find out about recommended blogs, Web sites, and upcoming Live Meetings on popular topics, visit our community site:
Course index

Find Training Near You

Location:
Eg: Seattle, WA or Paris, France

Related Services