MOC 7033 Microsoft SQL Server 2005 for Experienced Data Base Administrators - Seminare

Have thorough experience with SQL Server 2000

Have experience with SQL Server 2005 and a comprehensive understanding of all the new features and technologies

The relational engine

Storage engine

Integration Services

Analysis Services

Reporting Services

Be familiar with SQL Server 2005 features, tools, and technologies.

Concept of Database and Instance

Client interaction with Database and Instance

Database limits

Schema and schema objects

Storage Architecture

Logging Model

Data Dictionary

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

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

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

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

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

Relational engine

Structured Query Language

Data Definition Language (DDL)

Data Manipulation Language (DML)

Control Statements

Procedural SQL

Cursors

Error Handling

Query Optimization

Transaction Management

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

Concurrency and Consistency

Locking

Flashback Query in Oracle

Database Security

Privileges and Roles

Auditing

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

Installing SQL Server

Database System

SQL Server databases

Database Configuration

Database Maintenance

Data Dictionary

System Stored Procedures

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

Managing Memory

Managing Processes

Managing Storage

Managing Sessions and Transactions

Locks and Latches

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

Object Identifiers and Naming

Managing Tables

Managing Constraints

Managing Triggers

Managing Indexes

Managing Views

Sequences

Understand identifier and naming conventions

Manage tables and indexes

Select storage parameters

Manage constraints and triggers

Manage views and sequences

Managing Users

Managing Privileges

Managing Roles

Create and maintain login accounts

Create and maintain user accounts

Create and maintain user defined roles

Manage privileges for users and roles

Data out Overview

Data in Overview

SQL Server Data Transport

SQL Server Integration Services (SSIS)

Elements of Integration Services

SSIS Tools

Copy Database Wizard

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.

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

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

Tuning Methodologies—Tuning While Building

Tuning Methodologies—Performance Tuning

Application Tuning

Instance Tuning

SQL Server Performance Tuning Tools

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

High Availability

Clustering

Standby Database

Replication—Objects

Replication—Types of

Database Mirroring

Scalability

Scalability and HA in Backup and Recovery

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

Monitoring Availability

Monitoring Errors

Error Logs

Monitoring Performance – Server

Monitoring Performance – Database and Instance

Monitoring Tools

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

Installing SSMA

Configuring Options

Simulations

Assessment Reports

Schema Conversion and Migration

Data Migration

Converting Stored Programs

Viewing modes

Migration Testing

Test SQL

SSMA Workspace

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