Course Description
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
course provides training for Oracle DBA to compare and contrast Oracle database
management to SQL Server database management.
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 instances
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
·
Terminology concepts
·
Client interaction with database and
instance
·
Understanding database limits
After completing this module, you will be
able to:
·
Clearly define database and instance within
the context of this course.
·
Understand 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 instance.
·
Recognize some key limitations of the
database and instance components within Oracle Database and SQL Server systems.
Module 2: Instance architecture
This module discusses the memory
and process architectures that are key to a database’s 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 12c to exploit the
advantages offered by the operating system mechanisms and how they compare to
SQL Server’s implementation.
Lessons
·
Configuring a database server
·
Memory architecture overview
·
Understanding processes and threads in the
database engine
·
Background processes
Lab: Instance architecture
·
Using the Resource Governor
·
View multi-instance shared resources
After completing this module, you will be
able to:
·
Configure a database server.
·
Identify key database memory structures.
·
Identify memory areas inside the Oracle
System Global Area (SGA) and their equivalents in Microsoft SQL Server.
·
Understand process- and thread-based
architecture relevant to a relational database management system (RDBMS).
·
Control resources in SQL Server.
·
Detail client interaction with the database
server.
·
Understand background processes and
threads.
Module 3: 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 table spaces 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. Also
covered in this module is how SQL Server 2014 supports placing data and log
files on Microsoft Azure storage.
Lessons
·
Schema and data storage
·
Tablespaces and datafiles
·
Logging and data dictionary
Lab: Database architecture
·
Working with filegroups
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.
·
Manage storage structures.
·
Understand how to build the database using
physical and logical definition 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 Microsoft SQL Server
Module 4: Data objects
This module examines the schema objects in
both databases and introduces the new SQL Server 2014 In-Memory OLTP tables and
clustered columnstore index objects. 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, etc. 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
·
Database tables
·
Schema objects
·
Data and data types
·
Non-native data types – Beyond relational
Lab: Data objects
·
Create a partitioned table
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 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
·
Comparing structured query language
·
Control and procedural statements
·
Developing robust queries
Lab: Data access
·
Query designer
·
Concatenation and SQL injection
·
Stored procedures
·
Cursors
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 (SQL).
·
Define procedural SQL constructs and their
mechanisms.
·
Identify query optimization by the
relational engine and user overrides.
·
Understand transaction management.
Module 6: 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
·
Installing SQL Server
·
Managing and configuring SQL Server
·
Working with SQL Server databases
Lab: Basic administration
·
Defining a user database
·
Setting configuration parameters
·
Filegroup maintenance
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 7: 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
·
Managing tables, constraints, object
identifiers, and naming
·
Managing triggers
·
Managing indexes and views
Lab: Managing schema objects
·
Creating tables and associated objects
·
Creating indexed views
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/identity
columns.
·
Review dependencies within the database.
Module 8: Data protection and security
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. In addition, this module examines the
various features available for providing security such as logins, roles,
profiles, and privileges. This includes the new capabilities of SQL Server 2014
to support the separation of duties that can be used to provide the lowest
level of privileges for server roles.
Lessons
·
Securing the database
·
Managing users
·
Understanding privileges
·
Managing roles
Lab: Data protection and security
·
Creating logins and users
·
Grant permissions
·
Revoke permissions
·
Separation of duties
After completing this module, you will be
able to:
·
Implement security using the hierarchical
structure of each login, user, role, and profile.
·
Monitor database activity with auditing.
·
Create and maintain login accounts.
·
Create and maintain user accounts.
·
Create and maintain user defined roles.
·
Manage privileges for users and roles.
Module 9: Data transport
This module examines the non-transactional
mechanisms for moving data into and out of a database. Included in this module
is a discussion of how to copy databases to a Microsoft Azure VM using the SQL
Server 2014 Deploy database to Azure VM wizard. Another key feature covered 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
·
Getting data into and out of SQL Server
·
Understanding SQL Server Integration
Services
·
Other transfer methods
Lab: Data transport
·
Use SQL Server Integration Services to
migrate data into a flat file
·
Use SQL Server Integration Services to
import data from a flat file
After completing this module, you will be
able to:
·
Understand the tools and functionality in
Oracle and their equivalents in SQL Server for moving data in and out of the
database.
·
Understand the tools and functionality in
SQL Server to transport data into, out of, and within a database, as well as
across multiple databases, multiple file formats, and other data sources and
destinations.
Module 10: 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 Oracle’s Recovery
Manager (RMAN) and the equivalent functionality available in SQL Server
Management Studio. In addition, this module covers the new capabilities with
SQL Server 2014 for managed backups and manual backups to Microsoft Azure
storage.
Lessons
·
Understanding database backups
·
Data recovery
·
Backup and recovery tools and solutions
Lab: Backup and recovery
·
Create and execute a maintenance plan for
backup
·
Modify, backup, and restore a database
After completing this module, you 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 tools native to a relational
database management system (RDBMS) that are used for backup and recovery.
·
Explore vendor solutions for backup and
redundancy.
Module 11: Monitoring and performance
tuning
This module discusses the different
approaches to instance and application tuning. It then 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). This session features new
capabilities of SQL Server 2014 that improve performance including In-Memory
OLTP tables, delayed durability for transactions, online operations and buffer
pool extension. 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. Finally, it 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
·
SQL Server performance tuning
·
Managing memory and processes for SQL
Server
·
Managing database interactions
·
Monitoring availability and errors
·
Monitoring performance
Lab: Part 1: Monitoring and performance
tuning
·
Understanding threads
Lab: Part 2: Monitoring and performance
tuning
·
SQL Server Profiler
·
Hints (T-SQL)
·
Plan guides
·
Setting alerts to automate monitoring
Lab: Part 3: Monitoring and performance
tuning
·
Capture a SQL trace for use with
Distributed Replay
·
Prepare trace data for use with Distributed
Replay
·
Replay trace data using Distributed Replay
After completing this module, you will be
able to:
·
Identify the monitoring requirements of a
database.
·
Distinguish sources of information for
server, database, and instance activity.
·
Identify server and database components
that can be monitored.
·
Examine SQL Server tools for monitoring.
·
Create a methodology to develop an
application and its related database with optimal performance.
·
Understand the methodologies involved in
tuning a running instance.
·
Identify key elements in instance,
database, and application tuning.
Module 12: 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 AlwaysOn, parallel
query, replication, clustering, table partitioning, database mirroring, and
database snapshots.
Lessons
·
Understanding availability
·
Replicating databases
·
Other methods to obtain availability
After completing this module, you will be
able to:
·
Define high availability and understand its
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 13: Microsoft SQL Server Migration
Assistant
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
·
Install and configure SQL Server Migration
Assistant
·
Migration projects
·
Migrating the data
After completing this module, you 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 a schema.
·
Migrate data.
·
Convert procedures, functions, views, and
triggers.
·
Perform migration testing.