SQL Server 2012

Microsoft SQL Server for Oracle Database Administrators
(Microsoft Training Course: 40045) - 4 days - £1885 exc VAT

> Target Audience
This 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. 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.
> 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
  • 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 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
  • Schema and Data Structure (Objects)
  • Storage Architecture
  • Logging Model, Data Dictionary
  • Lab: Database Architecture
  • Working with Filegroups
  • 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.
  • 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
  • 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
  • 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.
  • 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
  • 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 a Partitioned Table
  • Working with Constraints and Triggers
  • 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
  • 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
  • Query Designer
  • Concatenation and SQL Injection
  • Stored Procedures, Using Cursors
  • 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
  • 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
  • Applying Permissions in a SQL Server Database
  • 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
  • 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 Databases and Setting Configuration Parameters
  • Defining a User Database
  • Setting Configuration Parameters
  • Plan and install SQL Server software.
  • Create and configure an instance.
  • Plan and create a database.
  • Understand the data dictionary.
Module 8: Server Management
  • 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
  • Filegroup maintenance
  • Configure and measure memory usage of a database instance and its components.
  • Configure and monitor database process.
  • Understand storage management at various levels of the storage hierarchy.
  • Identify resource utilization by sessions and transactions and view utilization data on storage structures.
Module 9: Managing Schema Objects
  • Naming guidelines for identifiers in schema object definitions
  • Storage and structure of schema objects
  • Data integrity using constraints
  • Implementing business rules at the database level
  • Lab: Managing Schema Objects
  • Creating Tables and Associated Objects
  • Maintaining Tables
  • Creating Indexed Views
  • 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
  • Manage access to database through user accounts and control access to data through privileges and roles
  • Manage access to server using login accounts
  • Lab: Database Security
  • Create Logins and Users
  • Grant and Revoke Permissions
  • Create and maintain login accounts, user accounts, and user defined roles.
  • Manage privileges for users and roles.
Module 11: Data Transport
  • 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 and across multiple data sources and destinations
  • Lab: Transferring Data
  • Use SSIS to Migrate Data Into a Flat File
  • Use SSIS to Import Data from a Business Partner’s Flat File
  • Understand the tools and functionality in Oracle and their equivalents in SQL 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
  • 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
  • Modify, Back up, and Restore a database
  • Identify database errors failures.
  • 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
  • Performance tuning methodologies
  • Tools for performance analysis and tuning
  • Lab: Distributed Replay
  • Capture a SQL Trace for use with Distributed Replay and prepare Trace Data for use with Distributed Replay
  • Replay Trace data using Distributed Replay
  • 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
  • Key high availability and 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 understand its requirements.
  • Compare scalability features in Oracle and SQL Server.
Module 15: Monitoring
  • Formulate requirements and identify resources to monitor database
  • Types of monitoring
  • Tools for monitoring
  • Lab: Integrating Performance Monitor Data in SQL Profiler
  • Integrating Performance Monitor Data
  • 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)
  • Queue Management
  • Lab: Create and Manage Queues
  • Create a New Public Queue for Incoming Questions
  • Assessing tasks, Converting codes
  • Migrating data, Testing, Deployment
  • 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.
> Pre-Requisites
Before attending this course, students must have the following pre-requisites:
  • Oracle DBA experience
  • Familiarity with Microsoft Windows platforms
  • 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.