Microsoft SQL Server 2008 for the Experienced Oracle Database Administrator

Target Audience
This 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. It is intended for experienced Oracle database administrators (DBAs) who work in an enterprise-level environment and require the skills to begin supporting and maintaining a SQL Server database.

Pre-Requisites
Before attending this course, students must have the following pre-requisites:Experience as an Oracle DBA.A familiarity with Microsoft Windows platforms.An understanding of operating system fundamentals.

Purpose
After completing this course, students will be able to extend their existing competencies as Oracle DBAs to SQL Server; manage SQL Server using the same perspective of an Oracle DBA; understand the underlying architecture of SQL Server; manage the SQL Server system, databases, and users; manage database files by backing up or migrating to other systems; define and implement monitoring and tuning solutions to the SQL Server system; express High Availability options to SQL Server; explain the process and tool to migrate Oracle schemas to SQL Server databases.

Course outline

1. Database and Instance
2. Database Architecture
3. Instance Architecture
4. Data Objects
5. Data Access
6. Data Protection
7. Basic Administration
8. Server Management
9. Managing Schema Objects
10. Database Security
11. Data Transport
12. Backup and Recovery
13. Performance Tuning
14. Scalability and High Availability
15. Monitoring
16. SQL Server Migration Assistant (SSMA)

Module 1:Database and Instance

• This module provides an understanding of the two major components of a database system.
• Defining a Database and an Instance
• Introducing Microsoft's and Oracle's implementation of a database and an instance
• Understanding client interaction
• Key database and instance limitations

• Clearly define a database and an instance within the context of this course.
  
• Introduce some key differences and similarities in how Microsoft and Oracle implement the database and instance.
  
• Understand client interaction between a database and an instance.
  
• Recognise some key limitations of the database and instance components within Oracle Database and SQL Server products.
  

Module 2:Database Architecture

• This module goes in-depth into structure, components, and contents of the files that constitute the database.
• Schema and Data Structure (Objects)
• Storage Architecture
• Logging Model
• Data Dictionary
• Lab: Database Architecture

• Understand schema and schema objects.
  
• Identify logical and physical structures using storage organization.
  
• Explain the architecture of data storage components and their hierarchy.
  
• Manage storage structures.
  
• Understand how to build the database using storage structures.
  
• Comprehend the transaction logging model employed to perform transaction recovery.
  
• Distinguish major differences between the construction of the data dictionary in Oracle and SQL Server.
  

Module 3:Instance Architecture

• This module discusses the memory and process architectures that are key to a database's performance.
• Configure a Database server
• Memory Architecture Overview
• Memory Areas and Their Functions
• Process and Thread Architecture
• Controlling Resources in SQL Server 2008
• Client and RDBMS Interactions
• Background Processes/Threads and Their Functions
• Lab: Instance Architecture

• Configure a database server.
  
• Identify key database memory structures.
  
• Identify memory areas inside the Oracle SGA and their SQL Server equivalents.
  
• Process-based and thread-based architecture relevant to RDBMS.
  
• Control Resources in SQL Server.
  

Module 4:Data Objects

• This module examines in greater detail the schema objects introduced in Module 2.
• Tables are the main objects that store data
• Indexes, views, stored programs, and other objects are the support structures
• Various table types compared
• Various index types compared
• Native and non-native data type support
• Block-level storage architecture
• Lab: Data Objects

• 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 focuses on how data is accessed and manipulated by the clients.
• Introduce database components involved in data access
• Introduce concepts of Transact-SQL and Procedural SQL as tools to access and manipulate data
• Discuss transactional management concepts
• Lab: Data Access

• 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 fulfils the twin tasks of protecting data against unauthorized access and also from the destructive interaction between authorized users working concurrently.
• Protecting data from unauthorized users and authorized users
• Using locking modes to achieve concurrency and consistency
• Implementing database security features
• Using auditing features to monitor database activity
• Lab: Data Protection

• 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.
  
• Implement security using the in the hierarchical structure of each.
  
• Monitor database activity with auditing.
  

Module 7:Basic Administration

• This module contains discussion on planning and installation of SQL Server.
• Planning and preparatory steps for a SQL Server environment
• Creation and configuration of an instance of SQL Server and databases
• Characteristics of a database
• Basic database maintenance tasks
• Lab: Creating Database

• 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 discusses the administration of various types of resources including system resources and low-level database resources.
• Server-level and instance-level resources
• Database-level resources
• User sessions and their activity
• Concurrency structures
• Lab: Resource Utilisation

• 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.
  
• View utilization data on storage structures.
  

Module 9:Managing Schema Objects

• This module provides the administrative aspect of schema objects described in Module 4.
• Naming guidelines for identifiers in schema object definitions
• Storage and structure of schema objects
• Implementing data integrity using constraints
• Implementing business rules at the database
• Lab: Managing Schema Objects

• Understand identifier and naming conventions.
  
• Manage tables and indexes.
  
• Select storage parameters.
  
• Manage constraints and triggers.
  
• Manage views and sequences/identity columns.
  
• Review dependencies within the database.
  

Module 10:Database Security

• This module continues the discussion of data protection initiated in Module 6.
• Manage access to database through user accounts
• Control access through privileges and roles
• Manage access to server using login accounts
• Lab: Database Security

• 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 examines the non-transactional mechanisms for moving data in a database.
• Tools and functionality in Oracle and their equivalents in SQL Server
• Tools and functionality in SQL Server for data transport within SQL Server
• Lab: Transferring Data

• Understand the tools and functionality in Oracle and their equivalents in SQL Server.
  
• 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.
  

Module 12:Backup and Recovery

• This module lists the types of errors encountered in a database and the various mechanisms that are available to safeguard against these errors.
• Backup and recovery methods available in Oracle and SQL Server 2008
• Types of failure
• Types of recovery
• Lab: Create and Execute a Maintenance Plan for Backup

• Identify database errors and types of failure.
  
• Understand the various backup methods.
  
• Obtain an understanding of recovery methods.
  
• Identify RDBMS native tools used for backup and recovery.
  
• Vendor solutions for backup and redundancy.
  

Module 13:Performance Tuning

• This module is divided into two sections: tuning an instance and tuning an application.
• Performance tuning methodologies
• Tools and techniques for performance analysis and tuning

• Create a methodology to develop an application and 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 provides a high-level overview of the scalability and high availability features available in each RDBMS.
• Key high availability features available in Oracle and SQL Server
• Key scalability features available in Oracle and SQL Server

• Understand high availability definition and requirements.
  
• Compare high availability features in Oracle and SQL Server.
  
• Define scalability and its requirements.
  
• Compare scalability features in Oracle and SQL Server.
  

Module 15:Monitoring

• This module shows the mechanisms by which the database can be monitored. Formulate requirements and identify resources to monitor database
• Types of monitoring
• Tools for monitoring
• Lab: Integrating Performance Monitor Data in SQL Profiler

• 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:SQL Server Migration Assistant (SSMA)

• This module focuses on automating the process of migrating from Oracle to SQL Server.
• Assessing tasks
• Converting codes
• Migrating data
• Testing and Deployment

• Assess the migratory tasks.
  
• Convert codes from Oracle to SQL Server.
  
• Test and deploy the migration.
  

Module 17:..

• ..

• ..