7033: Microsoft SQL Server 2005 for Experienced Oracle DBAs
Four days—Instructor-led



 

Introduction 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 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. Prerequisites Before attending this course, students must: Have experience working with Microsoft Windows Server 2003 Have thorough knowledge and experience in relational database design Have experience with programming or scripting language Have experience with Oracle database software Have thorough knowledge of ANSI SQL Have some familiarity with SQL Server 2005 features, tools, and technologies. He is a dynamic speaker and very knowledgeable instructor. He was always willing to take questions and encouraged student interaction. The fact that he did not just read from the course outline and drew from real world experience was a great asset which made for a excellent learning experience. 4/19/2007 MTM Report One of the best I've had. uncommon combination of great presentation /communication skills with excellent technical knowledge. 10/26/2006 MTM Report Course Outline 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 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 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 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 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 Lab After completing this module, you will be able to understand: • 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 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