4 Day Course
Introduction
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.
This workshop provides a quick start for the Oracle DBA to map, compare, and
contrast the realm of Oracle database management to SQL Server database
management. Each module also provides demos.
Audience Profile
This course 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.
At Course Completion
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
Prerequisites
Before attending this course,
students must have:
•
Oracle
DBA experience
•
Familiarity
with Microsoft Windows platforms
•
Understanding
of operating system fundamentals
Course Outline
Module 1: Database and Instance
This module provides an
understanding of the two major components of a database system. The database
constitutes the files that store data, and the instance is the collection of
server resources that provide a powerful, high performance interface to the
data. It also illustrates how the two interact to provide data requested by the
clients. Viewing the database and the instance as two separate interactive
components of the RDBMS helps us to divide-and-conquer the vast set of topics
covered in this workshop.
Lessons
•
Defining
a Database and an Instance
•
Introducing
Microsofts and Oracles implementation of a database and an instance
•
Understanding
client interaction
•
Key
database and instance limitations
After completing this module,
students will be able to:
•
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 in their product solutions.
•
Understand
client interaction between a database and an instance.
•
Recognize
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.
To be able to manage hundreds of gigabytes, terabytes, or even petabytes of
data, it is important to learn the techniques by which storage is viewed
(physical and logical) and allocated. Databases use various hierarchies of
storage structures such as blocks, extents, segments, and tablespaces to
control storage allocation. The definition of schema and the objects that
comprise the schema are introduced here. SQL Server uses similar techniques as
Oracle; however, the differentiation from Oracle is in the functionality.
Lessons
•
Schema
and Data Structure (Objects)
•
Storage
Architecture
•
Logging
Model
•
Data
Dictionary
Lab: Database Architecture
•
Working
with Filegroups and Data Structures
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.
•
Manage
storage structures.
•
Understand
how to build the database using physical and logical definitions storage
structures.
•
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 discusses the memory
and process architectures that are key to a databases performance. The module
goes into the details of the hierarchy of memory areas of an instance and its
configuration. This module also describes how the various functions of the
RDBMS are accomplished by the different processes running in the background.
Finally, in this module, we look at the changes Oracle has made in its internal
architecture on Microsoft Windows platform between 9i and 10g to exploit the
advantages offered by the operating system mechanisms and how they compare to
SQL Servers implementation.
Lessons
•
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
•
Using
the Resource Governor
•
View
Multi-Instances Shared Resources
After completing this module,
students will be able to:
•
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.
•
Detail
client interaction with database server.
•
Understand
Background Processes/Threads.
Module 4: Data Objects
This module examines in greater
detail the schema objects introduced in Module 2. While all schema objects are
mentioned, of particular interest are tables, the type of data they can hold,
and their storage layout. A proper understanding of data types and storage
architecture of tables and indexes is useful in many aspects of database design
and administration, such as fragmentation, capacity planning, and so on. A
mapping of the native data types from Oracle to SQL Server provides the student
with a very good reference on what data types are compatible and what are not.
Lessons
•
Tables
are the main objects that store data
•
Indexes,
views, stored programs, and other objects are the support structures
•
Various
table types compared based on data organization
•
Various
index types compared
•
Native
and non-native data type support
•
Block-level
storage architecture
Lab: Data Objects
•
Create
Partitioned Table
•
Constraints
and Triggers
After completing this module,
students will 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 focuses on how data
is accessed and manipulated by the clients. Important concepts such as
transaction, session, and so on are discussed here. This module describes the
various commands available through SQL for manipulating data, metadata,
transactions, sessions, and instances. An overview of procedural extensions to
the SQL language available in Oracle (PL/SQL) and SQL Server (Transact-SQL) is
given in this module. Cursors, which are data structures used to convey results
of user transactions, are discussed as well to provide insight into what SQL
Server supports and how they are used compared to Oracle.
Lessons
•
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
•
Generate
queries with a GUI utility
•
Concatenation
and SQL Injection
•
Stored
Procedures
•
Cursors
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 (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 fulfills the twin
tasks of protecting data against unauthorized access (database security) and
also from the destructive interaction between authorized users working
concurrently (concurrency control). Under security, the various features for
securing and auditing the database are discussed. Under concurrency control,
the most important topic is locking. Understanding the differences in the
concurrency models utilized by Oracle and SQL Server is important in order to
design and manage systems that can support a large user population, great
performance, and scalability.
Lessons
•
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
•
Working
with SQL Server security
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.
•
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. While basic duties such a creating,
starting, and shutting down a database are common to all databases, the options
available for these functions are the key differences. True to the words
Knowledge is Power, familiarity with the data dictionary and the different
ways a SQL Server DBA uses it compared to an Oracle DBA is an invaluable skill
for any database administrator.
Lessons
•
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
•
Define a
user 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 discusses the
administration of various types of resources such as system resources (such as
memory, processes, storage, and so on) as well as low-level database resources
(such as lock, latches, queues, and so on). As these resources are consumed by
transactions, it is pertinent to be able to relate user sessions to the transactions
they are running and to the resources they are consuming or requesting. Given
the significant difference in how resources are managed and utilized in SQL
Server compared to Oracle, it is important for the Oracle DBA to get a firm
understanding of what is under the SQL Server hood in order to appreciate and
best leverage the technology.
Lessons
•
Server-level
and instance-level resources such as memory and processes
•
Database-level
resources
•
User
sessions and their activity
•
Concurrency
structures
Lab: Resource Utilization
•
Understanding
threads
•
Filegroups
maintenance
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.
•
View
utilization data on storage structures.
Module 9: Managing Schema Objects
This module provides the
administrative aspect of schema objects described in Module 4. The discussion
covers planning, creation, and maintenance of many key schema objects. Choices
in terms of table and index types, column types, and storage greatly influence
the database growth, scalability, performance, and maintainability.
Lessons
•
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 level
Lab: Managing Schema Objects
•
Creating
tables and associated objects
•
Maintaining
tables and indexes
•
Creating
indexed views
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/identity columns.
•
Review
dependencies within the database.
Module 10: Database Security
This module continues the
discussion of data protection initiated in Module 6. This module examines the
various features available for providing security such as logins, roles,
profiles, and privileges. The topics such as encryption and auditing are also
briefly discussed in this module.
Lessons
•
Manage
access to database through user accounts
•
Control
access to data through privileges and roles
•
Manage
access to server using login accounts
Lab: Database Security
•
Create
logins and users
•
Grant
permissions
•
Revoke
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 examines the
non-transactional mechanisms for moving data into and out of a database. The
functionality varies based on homogeneous and heterogeneous databases. One key
feature in SQL Server is SQL Server Integration Services (SSIS). SSIS provides
users with the capabilities beyond complex ETL and high performance data
movement from heterogeneous data sources. It also adds data mining capability
to the process and more, all of which will be discussed in this module. Other
approaches to bulk data movement will also be covered in this module and
guidelines will be provided on which tools are appropriate for what scenarios.
Lessons
•
Tools
and functionality in Oracle and their equivalents in SQL Server in data
transport out and into the database
•
Tools
and functionality in SQL Server for data transport within SQL Server and across
multiple data sources and destinations
Lab: Transferring Data
•
Use SQL
Server Integration Services (SSIS) to migrate data into a flat file
•
Use SQL
Server Integration Services (SSIS) to import data from a business partners
flat file
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 lists the types of
errors encountered in a database and the various mechanisms that are available
to safeguard against these errors. This module discusses various types of
backups and recovery methods available. This module also covers Oracles
Recovery Manager (RMAN) and the equivalent functionality available in SQL
Server Management Studio.
Lessons
•
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
•
Create
and execute a maintenance plan for backup
•
Modify,
Back up, and Restore a database
After completing this module,
students will be able to:
•
Identify
database errors and various types of failure.
•
Understand
the various backup methods.
•
Obtain a
high-level 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. Tuning the instance
involves taking steps to ensure that system resources such as memory, CPU, and
I/O are used efficiently. Tuning an application starts with the design and
architecture of the application and involves tuning database operations and
access paths.
Lessons
•
Performance
tuning methodologies
•
Tools
and techniques for performance analysis and tuning
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 provides a high-level
overview of the scalability and high availability features available in each
RDBMS. Oracle and SQL Server are both enterprise class RDBMS, therefore the
topics of scalability and high availability are deemed important. However, due
to the scope of the course the discussion here is at a conceptual level and
does not go beyond the concepts. The features discussed include parallel query,
replication, clustering, table partitioning, database mirroring, and database
snapshots.
Lessons
•
Key high
availability features available in Oracle and SQL Server
•
Key
scalability features available in Oracle and SQL Server
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
This module shows the mechanisms
by which the database can be monitored for availability, errors, and
performance. Statistics that can be captured for both proactive and reactive
administration of the databases are reviewed here.
Lessons
•
Formulate
requirements and identify resources to monitor database
•
Types of
monitoring
•
Tools
for monitoring
Lab: Integrating Performance Monitor Data in SQL Profiler
•
Consolidating
performance data and trace data for monitoring activity
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: SQL Server Migration Assistant (SSMA)
This module focuses on automating
the process of migrating from Oracle to SQL Server. This module presents an
overview of the SSMA and will cover schema conversion, data migration, business
logic conversion, validation, integration, and performance analysis.
Lessons
•
Assessing
tasks
•
Converting
codes
•
Migrating
data
•
Testing
•
Deployment
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
