 |
 |
 |
 |
Microsoft SQL Server 2005 for the Oracle DBA
Course 50006A: Four days; Instructor-Led
On This Page
| Introduction | |
| Audience | |
| Prerequisites | |
| Course Outline | |
| Take This Training |
Introduction
Elements of this syllabus are subject to change.
This four-day instructor-led course provides students with the knowledge and skills to capitalize on their skills and experience as an Oracle DBA to manage a Microsoft SQL Server system.
Audience
This course is intended for IT professionals.
Prerequisites
Before attending this course, students must have:
| • | Basic knowledge of Oracle DBA |
Course Outline
Module 00: Workshop Introduction
This module introduces the workshop, its purpose, and its objectives.
Lesson
| • | 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 1: 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 2: 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 3: 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 4: 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 5: 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 6: 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 7: 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 1: 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 8: 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 1: 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 9: 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 1: 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 10: 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 1 Securing Databases:
| • | Creating Logins and Users |
Lab 2: Granting Permissions
| • | Granting Permissions |
Lab 3: 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 11: 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 1: Transferring Data
| • | Perform Data Transfer by Using BCP and BULK INSERT Commands |
Lab 2: 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 12: 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 1: 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 13: 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 1: 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 14: 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 15: 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 16: 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. |
We are glad to anwer questions regarding our business and course offerings.
Call us at (800) 453-5961 or Send an Email.
DynamicsEdge, Inc. All Rights Reserved. © 2010
Now Hiring!
We are currently looking for Microsoft Certified Trainers (MCT) for training assignments. You must be a MCT in good standing with Microsoft and have a current high-level Microsoft Certification (MCITP, MTS, MCSD) with two years of training experience.
To apply please send an email to MCT@ DynamicsEdge.com
We support Dynamics CRM
Microsoft Dynamics CRM 4.0 is a CRM system your employees will use! Dynamics Edge can provide customization to make Dynamics CRM adapt to your business processes.
Support for Dynamics AX, NAV, SL, and RMS
Dynamics Edge provides support and training for the Dynamics products and the underlying technologies: SQL Server 2005 - 2008, Exchange Server 2003 - 2007 and Sharepoint 2007. .
Classes at Your Site
We offer private on-site classes for up to 8 people for most courses:
5 day private class: $10,995
4 day private class: $8,795
3 day private class: $6,595
1 & 2 day Private classes:
Call (800) 453-5961 or email!
Course Listings
Microsoft Dynamics
- Microsoft Dynamics CRM
- Microsoft Dynamics AX
- Microsoft Dynamics NAV
- Microsoft Dynamics GP
- Microsoft Dynamics SL
Other Products
- Windows Server 2008
- Windows Server 2003
- Windows 7 & Vista
- Exchange
- Share Point
- SQL Server
- SCCM / SMS Server
- Visual Studio & .Net
- Other Server Products