|
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
|
|
