fbpx

Database

ADMINISTRATION (NETWORK & SYSTEM)

Documentum

Enterprise Content Management Fundamentals

Enterprise Content Management (ECM) Overview

ECM Challenges

Need for ECM

ECM Components

ECM Key players

Documentum Overview

EMC Documentum Overview

EMC Documentum Solutions

Documentum Key Characteristics

Documentum Product Overview

Documentum Products

Content services

Process Services

Repository Services

Integration Services

Administration & Development

Documentum CenterStage

MyDocumentum Offline

What’s new in Documentum

D6.0 Features Overview

D6.5 Features Overview

D6.6 Features Overview

Documentum Concepts Overview

Content Server

Docbase

Docbroker

DFC, WDK

Documentum Objects

Cabinets & Folders

Users and Groups

Virtual Document

Alias Set

ACLs

Virtual Document

Workflow

Lifecycle

Objects and Types

Introduce and describe Object

Introduce and describe Types

Type Hierarchy and inheritance

Define object ID

Type Storage

Lighweight SysObjects

Shareable Objects

DQL

Users and Privileges

Users

User Authentication

Client capability

Basic privileges

Extended privileges

Creating Users

Useful DQL Queries

Groups and Roles

Groups

Creating Groups

Roles

Creating Roles

Useful DQL Queries

Object Security

Object Permissions

Permission Sets

Special Conditions

Folder Security

Creating a Permission Set

Assigning Permissions

Advanced Security

Useful DQL Queries

Documentum Component Architecture

Documentum layered Architecture

The Kernel Group

The Kernel Group Components

System Administration Overview

System Administrator Tasks

Documentum Administrator

Content Server Installation and Configurations

Methods & Jobs

Alias Sets

Introduction to Aliases

Alias Set

Defining Alias

Kinds of Alias

Creating Alias Set

Alias Scope

Alias Resolution Scopes

Useful DQL Queries

Custom Types

Introduction to Custom Types, Super Types, Permissions & Null type

Types and Data dictionary

Benefits of the Data Dictionary

Creating Custom Type using DAB

Document Templates

Modifying Custom Types

Dropping Custom Types

Useful DQL Queries

Virtual Documents

Virtual Documents Definition and Benefits

Create Virtual Documents

Create a Snapshots

Add components to a virtual document

Virtual Document Preferences

XML Management

Useful DQL Queries

Object and Content Relationships

Describe document content

Introduction to content object

How content files and objects are related

Brief on Primary content & Renditions

Page number

Rendition source

Life cycles

Lifecycles Overview

Difference between Lifecycle & Workflows

Describe Entry Criteria, Entry Actions, and

Post-entry Actions

Extended Permissions with lifecycles

Lifecycles & Alias sets

Lifecyle Related Actions

Lifecycle States

Useful DQL Queries

Workflow Basics and Process Builder

Workflow Concepts

Workflow Templates

Activities

Performers

Flows and packages

Building a complex workflow

Simple workflow

Path selection

Dynamic performer

Advanced Features

Automated activities

Workflow Manager

Search

Searching Overview

Simple Search

Advanced Search

Search Results

Saving Searches

Search Preferences

Subscriptions

Shortcuts

Useful DQL Queries

Documentum Query language (DQL)

DQL Overview

Advanced DQL

DQL tools: IDQL

xCelerated Composition Platform (xCP) Overview

xCP Overview

Process Builder

Forms Builder

Taskspace

Business Activity Monitor & Process Reporting Services

Documentum Foundation Classes (DFC)

DFC Overview

Clients and Sessions

Session Management

Type related DFC Interfaces

Common DFC Tools

DFC Operations

Business Object Framework (BOF)

BOF Overview

Global registry

Service Based Objects (SBO)

Type Based Objects (TBO)

Aspects

Documentum web development Kit (WDK)

WDK Overview and Installation

Application Layer configuration

Component Configuration

Component Customization

Behavior, Events and Controls

Presets

Documentum Webtop

Documentum Administrator

Documentum Foundation Services (DFS)

DFS Overview

OOTB Platform Services

DFC Vs DFS

Building DFS Service

Documentum Composer, Application builder

DocApps Overview

Documentum Application Builder

DocApp Archives

Installing DocApps

Documentum Composer & Documentum Application Archive (dar)

Installing Composer

Documentum Artifacts

Build and deploy Documentum Application Archive (dar)

IBM DB2 DBA

DB2 DBA Topics

 

Part 1:- RELATIONAL DATA MODEL

  • Data and Databases
  • Database Management Systems (DBMS)
  • Information Model & Data Model
  • Relational Data Model & Relational Databases
  • Normalization

Part 2:- DB2 FUNDAMENTALS AND IBM DATA STUDIO TOOL

  • Product Overview
  • Editions & Features
  • Licensing
  • Fundamentals
  • Architecture
  • Users
  • Instances
  • DB2 Client
  • Storage
  • Table spaces
  • Buffer pools
  • Security
  • SQL and XQuery
  • IBM Data Studio
  • DSAC

Part 3:- INSTALLATION AND CONFIGURATION (WORKING WITH WINDOWS OS)

Installation
    • Configuration
    • Database creation
    • Instance creation
    • Table space Creation
    • Working with Buffer pool

Part 4:- WORKING WITH DATABASES AND DATABASE OBJECT IN DB2

  • Servers, Instances, and Databases
  • Managing Instances
  • Creating a DB2 Database
  • Cataloging
  • Database (Data) Objects

Part 5:- INTRODUCTION TO SQL

SQL
  • Data Definition Language
  • Data Manipulation Language
  • JOINs
  • Data Control Language
  • Transaction Control Statements

Part 6:- DATA CONCURRENCY

  • Database Transactions
  • Concurrency
  • Concurrency Issues
  • Concurrency Control
  • Isolation Levels
  • Locking
  • Specifying Isolation Levels

Part 7:- DB2 SECURITY

  • Authentication
  • Trusted Context
  • Authorization
  • Authorities
  • Privileges
  • Label-Based Access Control (LBAC)
  • ROLES

Part 8:- DB2 BACKUP AND RECOVERY

  • Backup
  • Recovery

Part 9:- DB2 PURE XML

  • Overview of XML
  • PureXML in DB2
  • XML Data Movement in DB2
  • XQuery and SQL/XML
  • XML Indexes in DB2
  • Application Development

Part 10:- DB2 PROGRAMMING FUNDAMENTALS

  • Application Development Environment
  • Embedded SQL
  • Static SQL
  • Dynamic SQL
  • Routines
  • Types
  • Benefits
  • Usage
  • Tools for Developing Routines
  • Triggers

MS Exchange Server 2010 & 2013

MS Exchange Server 2010 & 2013

Exchange Server 2010

Deploying Microsoft Exchange Server 2010

  • Overview of Exchange Server 2010 Requirements
  • Installing Exchange Server 2010 Server Roles
  • Installing Exchange Server 2010

Configuring Mailbox Server

  • Overview of Exchange Server 2010 Administrative Tools
  • Configuring Mailbox Server Roles
  • Configuring Public Folders

Managing Client Access

  • Configuring the Client Access Server Role
  • Configuring Client Access Service for Outlook Clients
  • Configuring for Outlook Web App
  • Configuring Outlook Anywhere

Managing Message Transport

  • Overview of Message Transport
  • Configuring Message Transport
  • Configuring Internet Message Transport
  • Troubleshooting Message Transport
  • Troubleshooting Internet Message Delivery

Implementing Messaging Security

  • Deploying Edge Transport Servers
  • Deploying an Antivirus Solution
  • Configuring an Anti-Spam Solution
  • Configuring Secure SMTP Messaging

Implementing Backup and Recovery

  • Planning Backup and Recovery
  • Backing Up Exchange Server 2010
  • Restoring Exchange Server 2010

Exchange Server 2013

Module 1:

Overview of Exchange Server 2013 Unified Messaging

This module explains the basic concept of Unified Messaging in Exchange Server 2013.

Lessons
  • Lesson 1: Telephony technologies overview
  • Lesson 2: Unified Messaging in Exchange Server 2013
  • Lesson 3: Unified Messaging components
Lab : Unified Messaging Overview
  • Exercise 1: Identifying Unified Messaging components

After completing this module, students will be able to explain the basic concept of Unified Messaging in Exchange Server 2013.

Module 2:

Designing and implementing Exchange Server 2013 Unified Messaging

This module explains how to design and implement Exchange Server 2013 Unified Messaging.

Lessons
  • Lesson 1: Designing a Unified Messaging deployment
  • Lesson 2: Deploying and configuring Unified Messaging components
  • Lesson 3: Integrating Exchange Server 2013 Unified Messaging with Lync
Lab : Designing and Implementing Exchange Server 2013 Unified Messaging
  • Exercise 1: Designing the Unified Messaging implementation
  • Exercise 2: Configuring Unified Messaging features
  • Exercise 3: Configuring Unified Messaging integration with Lync Server 2013

After completing this module, students will be able to design and implement Exchange Server 2013 Unified Messaging.

Module 3:

Designing and implementing site resiliency

This module explains how to design and implement site resiliency for Exchange Server 2013.

Lessons
  • Lesson 1: Site resiliency in Exchange Server 2013
  • Lesson 2: Planning a site resilient implementation
  • Lesson 3: Implementing site resiliency
Lab : Designing and implementing site resiliency
  • Exercise 1: Designing a site resilient solution
  • Exercise 2: Implementing site resiliency
  • Exercise 3: Validating site resiliency

After completing this module, students will be able to design and implement site resiliency for Exchange Server 2013.

Module 4:

Planning virtualization for Exchange Server 2013

This module explains how to plan a virtualization strategy for Exchange Server 2013 roles.

Lessons
  • Lesson 1: Hyper-V 3.0 overview
  • Lesson 2: Virtualizing Exchange Server 2013 server roles
Lab : Planning virtualization of Exchange Server roles
  • Exercise 1: Designing an Exchange Server 2013 deployment for a large organization
  • Exercise 2: Designing an Exchange Server 2013 deployment for a medium organization
  • Exercise 3: Designing an Exchange Server 2013 deployment for a medium complex organization

After completing this module, students will be able to plan a virtualization strategy for Exchange Server 2013 roles.

Module 5:

Designing and implementing message transport security

This module explains how to design and implement message transport security.

Lessons
  • Lesson 1: Overview of policy and compliance requirements
  • Lesson 2: Designing and implementing transport compliance
  • Lesson 3: Designing and implementing AD RMS integration with Exchange Server 2013
Lab : Designing and implementing message transport security
  • Exercise 1: Planning a message transport implementation
  • Exercise 2: Implementing message transport security
  • Exercise 3: Implementing AD RMS and Exchange Server integration

After completing this module, students will be able to design and implement message transport security.

Module 6:

Designing and implementing message retention

This module explains how to design and implement message retention in Exchange Server 2013.

Lessons
  • Lesson 1: Message records management and archiving overview
  • Lesson 2: Designing in-place archiving
  • Lesson 3: Designing and implementing message retention
Lab : Designing and implementing message retention
  • Exercise 1: Designing message retention and archiving
  • Exercise 2: Implementing message retention and archiving

After completing this module, students will be able to design and implement message retention in Exchange Server 2013

Module 7:

Designing and implementing messaging compliance

This module explains how to design and implement messaging compliance.

Lessons
  • Lesson 1: Designing and implementing data loss prevention
  • Lesson 2: Designing and implementing an in-place hold
  • Lesson 3: Designing and implementing in-place e-discovery
Lab : Designing and implementing messaging compliance
  • Exercise 1: Designing messaging compliance
  • Exercise 2: Implementing data loss prevention
  • Exercise 3: Implementing in-place e-discovery
  • Exercise 4: Comparing messaging policy and compliance options

After completing this module, students will be able to design and implement messaging compliance.

Module 8:

Designing and implementing administrative security and auditing

This module explains how to design and implement administrative security in an Exchange Server 2013 environment.

Lessons
  • Lesson 1: Designing and implementing role based access control
  • Lesson 2: Designing and implementing split permissions
  • Lesson 3: Planning and implementing audit logging
Lab : Designing and implementing administrative security and auditing
  • Exercise 1: Designing a solution to fulfill requirements
  • Exercise 2: Implementing role based access control
  • Exercise 3: Implementing mailbox access and administrative logging
  • Exercise 4: Implementing and testing split permissions

After completing this module, students will be able to design and implement administrative security in an Exchange Server 2013 environment.

Module 9:

Managing Exchange Server 2013 with Exchange Management Shell

This module explains how to use Windows PowerShell 3.0 to manage Exchange Server 2013.

Lessons
  • Lesson 1: Overview of Windows PowerShell 3.0
  • Lesson 2: Using Exchange Management Shell to manage Exchange Server recipients
  • Lesson 3: Managing Exchange Server 2013 with Exchange Management Shell
Lab : Managing Exchange Server 2013 with Exchange Management Shell
  • Exercise 1: Exploring Exchange Management Shell
  • Exercise 2: Using Exchange Management Shell to manage recipients

After completing this module, students will be able to use Windows PowerShell 3.0 to manage Exchange Server 2013.

Module 10:

Designing and implementing integration with Exchange Online

This module explains how to design and implement integration with Exchange Online.

Lessons
  • Lesson 1: Planning for Exchange Online
  • Lesson 2: Planning and implementing the migration to Exchange Online
  • Lesson 3: Planning coexistence with Exchange Online
Lab : Designing integration with Exchange Online
  • Exercise 1: Designing integration with Exchange Online
  • Exercise 2: Implementing integration with Exchange Online

After completing this module, students will be able to design and implement integration with Exchange Online.

Module 11:

Designing and implementing messaging coexistence

This module explains how to design and implement messaging coexistence.

Lessons
  • Lesson 1: Designing and implementing federation
  • Lesson 2: Designing coexistence between Exchange organizations
  • Lesson 3: Designing and implementing cross-forest mailbox moves
  • Exercise 1: Implementing Message Routing Coexistence
  • Exercise 2: Migrating user mailboxes
  • Exercise 3: Configuring administrative coexistence

After completing this module, students will be able to design and implement messaging coexistence.

Module 12:

Designing and implementing Exchange Server migrations and upgrades

This module explains how to design and implement Exchange Server migrations from non-Exchange messaging systems, and upgrades from previous Exchange Server versions.

Lessons
  • Lesson 1: Designing migration from non-Exchange email systems
  • Lesson 2: Planning the upgrade from previous Exchange versions
  • Lesson 3: Implementing the migration from previous Exchange versions
Lab : Designing and implementing Exchange Server migrations
  • Exercise 1: Planning the migration strategy
  • Exercise 2: Deploying Exchange Server 2013 in an Exchange Server 2007 organization
  • Exercise 3: Migrating all messaging components to Exchange Server 2013
  • Exercise 4: Removing the Exchange Server 2007 server

After completing this module, students will be able to design and implement Exchange Server migrations from non-Exchange messaging systems, and upgrades from previous Exchange Server versions.

MS SQL DBA

SQL DBA

1:  Starting with SQL Server

•  Responsibilities of Database Admin

•  Types of DBAs

•  History of SQL Server

•  Editions of SQL Server

•  Tools of SQL Server

•  Differences between Standard and Enterprise editions

•  Instances types in SQL Server

  •   Default Instance
  •   Named Instance

•  SQL Server Services

  •   Instance aware services
  •   Instance unaware services
2:  Installing SQL Server

•  Pre-requisites

•  Installation

•  Post Installation verification Session

3:  Functioning of Databases

•  Database

•  Types of Database and Brief explanation

  •   System Databases
  •   User Database

•  Database Architecture

  •   Pages
  •   Extents
  •   File groups
  •   Database

•  Transaction Architecture

•  Creating Database

•  Modifying Database

  •   Adding Files
  •   Moving and renaming of Database files
  • Database modes
  • Real time Scenario
4:  Security

•  Authentication Types in SQL Server

•  Types of Login

  •   Windows Login
  •   SQL Login

•  Creating Users and Logins

  •   Server roles
  •   Password policy
  •   Understanding Database and Server Roles
  •   Permissions
  •   Working on access level issues
  •   Orphan users Finding and Fixing
  •   Important Queries
5:  Database Backups and Restoration

•  Database Backups

  •   Why we need backups

•  Types of Backup

  •   Full Backup
  •   Differential Backup
  •   Transaction Log Backup

•  Copy-only, Mirrored, Split and Tail log Backups

•  Differences between backups

•  Backup Strategy

•  Understanding how the data moving from Log to Data File, CHECKPOINT

•  Monitoring the space usage of Log File and fixing

•  Checking the backup files VALID or CORRUPTED

•  Backup storage tables

•  Important Queries

•  Performing Restorations

  •   Types of Restoration
  •   Attach and Detach
  •   Shrinking files
6:  Recovery Models of Database

•  Types of Recovery Models

  •   Full
  •   Bulk Logged
  •   Simple

•  Differences between Recovery Models

•  Setting Recovery Models according to the Scenarios with examples

7:  Automation of SQL SERVER

•  Working with SQL SERVER Agent

  •   Creating Jobs
  •   Managing Jobs and Resolving issues
  •   Monitoring Jobs
  •   Monitoring Tables
    • Log Shipping
    • Mirroring
    • Replication
    • Clustering

HIGH AVAILABILTY CONCEPTS

1:  Log Shipping

•  Log Shipping

    •   Working with Log Shipping
    •   Terminology
    •   Pre-requisites to implement Log Shipping
    •   Different modes of Log Shipping
    •   Configuring Log Shipping
    •   Monitoring
    •   Manually Performing Failover
    •   Log Shipping Tables

•  Discussing Real Time Scenarios

2:  Database Mirroring

•  Working with Database Mirroring

  •   Terminology
  •   Pre-requisites to implement Mirroring
  •   Pre check mirroring ports ENABLED or DISABLED
  •   Architecture
  •   Operating Modes
  •   Differences between Modes
  •   Port numbers involved in Mirroring
  •   Requirements for Mirroring
  •   Configuring Mirroring
  •   Mirroring failover
  •   Issues on Mirroring
3:  Replication

•  Working with Replication

  •   Terminology
  •   Pre-requisites to implement to Replication
  •   Types of Replications
  •   DISTRIBUTOR Database
  •   Agents involved in Replication
  •   Configuring Replication
  •   Resolving Issues
4:  Clustering
  • Introduction about clustering
  • Clustering Architecture
  • Importance of IP’s involved in Clustering
  • SAN importance
  • Drive dependency
  • Nodes in Clustering
  • Important commands
5:  Always On
  • What is availability group
  • Advantages of HADRON(Always on)

DIFFERENCES BETWEEN ABOVE HIGH AVAILABILTY CONCEPTS and ADVANCED CONCEPTS

1:  Monitoring Tools
  • System Monitor
  • SQL Server Profiler
  • SQL Server and Windows Error Logs
2:  Database Performance Tanning
  • Indexes
  • Types of indexes
  • Fragmentation in Indexes
  • Types of Fragmentations
  • Database Tuning Advisor
  • Dead Locks
  • Blockings
  • Terminating Processes
3:  Managing the SQL Server and Databases
  • Starting SQL Server in Single user mode
  • Changing the Default path of Database files
  • Modifying the Logical and Physical names of database files
High Lights:
  • Discussions on Real time scenarios
  • Replying to Questioner mails on subject doubts

* Windows and SQL cluster configuration

* SQL2012 new feature (Always On) Configuration

MS SQL DEVELOPER

MS SQL Developer

Part 1

Chapter 1: SQL SERVER OVERVIEW

Understanding Client Server Architecture

Introducing the Tools

Selecting an Edition

Creating Sample Databases

Chapter 2: USING MANAGEMENT STUDIO

Arranging the Workspace

Registering Servers

Explaining Items in the Object Explorer

Explaining the Database Properties Dialog

Explaining the Columns Properties Dialog

Continuing with Object Explorer Items

Using the Query Editor

Chapter 3: DESIGNING RELATIONAL DATABASES

Understanding Normalization

Discovering Patterns

Avoiding Over Complexity

Managing Optional Data

Creating Physical Keys

Using the Database Diagrammer Tool

Final Comments

Part 2: Selecting Data

Chapter 1: WORKING WITH SINGLE TABLE QUERIES

Understanding Query Flow

Filtering Data with the Where Clause

Columns, Expressions & Scalar Functions

Using Case Expressions

Working with Nulls & Coalesce

Selecting Environment Functions

Working with Dates & Times

Parsing Strings

Converting Data

Sorting Data

Topping Off the Query with Predicates

Chapter 2: MERGING DATA WITH JOINS & UNIONS

Working with Inner Joins

Using Left Outer Joins

Multiplying Data with Cross Joins

Referencing Self-Joins

Working with Complex Joins

Finding the Set Difference

Stacking Data with Unions

Chapter 3: WORKING WITH SUBQUERIES

Using Subqueries as Expressions

Including Subqueries as Lists

Adding Subqueries as Derived Tables

Working with Correlated Subqueries

Solving for X with Relations Division

Chapter 4: AGGREGATING DATA

>

Using the New Windowing Functions

Using Aggregate Functions

Grouping a Data Set

Nesting Aggregations

Filtering Groups & Adding Grand Totals

Generating Cross Tab Tables (Pivot)

Calculating Cumulative Sums

Chapter 5: USING FULL-TEXT SEARCH

Creating a Full-Text Index

Searching for Words

Using a Fuzzy Search to Search for Phrases

Chapter 6: SELECTING DATA THROUGH VIEW

Creating Views

Preventing Disappearing Rows

Protecting from Schema Changes

Nesting Views

Chapter 7: DISTRIBUTED QUERIES

Linking to External Data Sources

Executing Distributed Queries

Looking at Where the Work Takes Place

Using Pass Through Queries

Controlling Distributed Transactions

Chapter 8: MODIFYING DATA

Inserting Data

Updating Data

Deleting Data

Using the Output Clause

Final Comments & Credits

Part 3: Developing Solutions

Chapter 1: T-SQL & STORED PROCEDURES

Introducing T-SQL

Working with Variables

Controlling Procedural Flow

Examining SQL Server with Code

Using Temp Tables

Developing Dynamic SQL

Introducing Stored Procedures

Passing Parameters

Returning Parameters

Working with a Cursor

Handling Errors

Explaining the Raise Error

Controlling Transactions

Chapter 2: TRIGGERS & AUDIT TRAILS

Introducing After Triggers

Using “Instead of” Trigger

Disabling Triggers

Using the Update ( ) Function

Selecting Transaction Data

Nesting & Recursion

Enforcing Complex RI

Auditing Data Changes

Chapter 3: USER-DEFINED FUNCTIONS

Creating Scalar Functions

Working with Inline Table-Valued Functions

Using Multi-Statement Table-Valued UDFs

Navigating a Hierarchy

Having Fun with Apply

Exploring Functions in Nordic

Chapter 4: INDEXING FOR PERFORMANCE

Introducing Indexes

Creating Indexes

Examinging Query Execution Plans, Pt. 1

Examinging Query Execution Plans, Pt. 2

Chapter 5: TRANSACTIONS, LOCKING & BLOCKING

Introducing ACID

Understanding Transactional Faults

Stepping through a Transaction

Exploring Locks

Working with Locks

Viewing Deadlocks

Using Snapshot Isolation

Chapter 6: INTRODUCTION TO OPTIMIZATION THEORY

Deconstructing the Database System

Walking through Optimization Theory

Optimization Theory Continued

Final Comments & Credits

SQL Performance Tuning

Performance Tuning Overview

The Performance-Tuning Process

Performance vs. Price

Performance Baseline

Where to Focus Efforts

SQL Server Performance Killers

System Performance Analysis

Performance Monitor Tool

Dynamic Management Views

Hardware Resource Bottlenecks

Memory Bottleneck Analysis

Memory Bottleneck Resolutions

Disk Bottleneck Analysis

Disk Bottleneck Resolutions

Processor Bottleneck Analysis

Processor Bottleneck Resolutions

Network Bottleneck Analysis

Network Bottleneck Resolutions

SQL Server Overall Performance

Creating a Baseline

System Behavior Analysis Against Baseline

SQL Query Performance Analysis

Extended Events Wizard

Extended Events Automation

Extended Events Recommendations

Other Query Performance Metrics Methods

Costly Queries

Execution Plans

Index Analysis

What Is an Index?

Index Design Recommendations

Clustered Indexes

Nonclustered Indexes

Clustered vs. Nonclustered Indexes

Advanced Indexing Techniques

ColumnStore Indexes

Special Index Types

Additional Characteristics of Indexes

Database Engine Tuning Advisor

Database Engine Tuning Advisor Mechanisms

Database Engine Tuning Advisor Examples

Database Engine Tuning Advisor Limitations

Bookmark Lookup Analysis

Purpose of Bookmark Lookups

Drawbacks of Bookmark Lookups

Analyzing the Cause of a Bookmark Lookup

Resolving Bookmark Lookups

Statistics Analysis

The Role of Statistics in Query Optimization

Statistics on an Indexed Column

Statistics on a Nonindexed Column

Analyzing Statistics

Statistics Maintenance

Analyzing the Effectiveness of Statistics for a Query

Recommendations

Fragmentation Analysis

Causes of Fragmentation

Fragmentation Overhead

Analyzing the Amount of Fragmentation

Fragmentation Resolutions

Significance of the Fill Factor

Automatic Maintenance

Execution Plan Cache Analysis

Execution Plan Generation

Execution Plan Caching

Components of the Execution Plan

Aging of the Execution Plan

Analyzing the Execution Plan Cache

Execution Plan Reuse

Query Plan Hash and Query Hash

Execution Plan Cache Recommendations

Query Recompilation

Benefits and Drawbacks of Recompilation

Identifying the Statement Causing Recompilation

Analyzing Causes of Recompilation

Avoiding Recompilations

Query Design Analysis

Query Design Recommendations

Operating on Small Result Sets

Using Indexes Effectively

Avoiding Optimizer Hints

Using Domain and Referential Integrity

Avoiding Resource-Intensive Queries

Reducing the Number of Network Round-Trips

Reducing the Transaction Cost

Blocking Analysis

Blocking Fundamentals

Understanding Blocking

Locks

Isolation Levels

Effect of Indexes on Locking

Capturing Blocking Information

Blocking Resolutions

Recommendations to Reduce Blocking

Automation to Detect and Collect Blocking Information

Deadlock Analysis

Deadlock Fundamentals

Using Error Handling to Catch a Deadlock

Deadlock Analysis

Avoiding Deadlocks

Cursor Cost Analysis

Cursor Fundamentals

Cursor Cost Comparison

Default Result Set

Analyzing SQL Server Overhead with Cursors

Cursor Recommendations

Database Performance Testing

Performance Testing Overview

Capturing Data with the Server Side Trace

Distributed Replay for Database Testing

Summary and SQL Server Optimization Checklist

Conclusion

SQL & PL/SQL

1.  DBMS Concepts

•  What is Database?

•  What is Database System?

•  E. F. CODD’s Rules

•  RDBMS

•  ORDBMS

•  Difference Between DBMS, RDBMS & ORDBMS

2.  SQL

•  What is SQL?

•  DDL Commands

•  Create, Alter, Drop

•  Truncate, Rename

•  DML Command – Insert

SQL SELECT statements

a.  Order by Clause

b.  Distinct Clause

c.  Where Clause

d.  Operators

e.  Comparison Conditions

Constraints

a.  Primary Key Constraint

b.  Unique Constraint

c.  Not Null Constraint

d.  Foreign Key Constraint

e.  Check Constraint

f.  Cascade Constraints

Joins

a.  What is a Join?

b.  Cartesian Join

c.  Equi Join

d.  Non – Equi Join

e.  Self Join

f.  Outer Join

g.  Left Outer Join

h.  Right Outer Join

i.  Full Outer Join

j.  Pseudo Columns

Functions

a.  Numeric Functions

b.  Character Functions

c.  Date Functions

d.  Conversion Functions

e.  General Functions

f.  Aggregating Data Using Group Functions

Sub Queries

a.  Simple Sub Queries

b.  Multiple Row Sub Queries

Update

a.  Data Updating

b.  Updating data using Sub Queries

Delete

a.  Deleting Data

b.  Deleting data using Sub Queries

c.  Deleting data using join

Transaction Control Language (TCL)

a.  Commit

b.  Roll back

c.  Save point

Database Objects

a.  View

b.  Synonym

c.  Sequence

d.  Index

e.  Privileges

View

a.  Simple View

b.  Complex View

c.  In Line View

Synonym

a.  Private Synonym

b.  Public Synonym

Privileges

a.  Grant

b.  Revoke

3.  PL/SQL

•  Block Structure of PL/SQL

•  Data Types, Variables & Constants

Flow Control Statements

a.  Simple IF

b.  IF-THEN-ELSE Statements

c.  IF-THEN-ELSIF Statements

Iterative Statements

a.  Simple Loop

b.  While Loop

c.  For Loop

Attribute Data Types

a.  %type

b.  %Rowtype

Cursor Management

a.  What is Cursor?

b.  Implicit Cursor

c.  Explicit Cursor

d.  Cursor with parameters

e.  Cursor with For Loop

Exceptions

a.  What is an Exception?

b.  Predefined Exception

c.  User defined Exception

Sub Programs

a.  Named PL/SQL Block

b.  Stored Procedures

c.  Types of Parameters

d.  Stored Function

e.  Difference between Function and Procedure

Package

a.  What is package?

b.  Importance of package

Triggers

a.  What is a Trigger?

b.  Row Level Trigger

c.  Table Level Trigger

d.  Instead of Trigger

e.  DML Triggers

f.  System Triggers

Transact-SQL

T-SQL Topics

Relational Database Basics

o  Brief History of SQL

o  Relational Databases [may be compressed due to attendee experience]

  •   Tables
  •   Rows
  •   Columns
  •   Relationships
  •   Datatypes
  •   Primary Keys
  •   Foreign Keys
  •   Relational Database Management System

o  SQL Server Objects

  •   Valid Object References

o  SQL Statements

  •   Database Manipulation Language (DML)
  •   Database Definition Language (DDL)
  •   Database Control Language (DCL)

Simple SELECTs with SQL Server

o  Introduction to the Northwind Database

o  Some Basics

  •   Comments
  •   Whitespace and Semi-colons
  •   Case Sensitivity

o  SELECTing All Columns in All Rows

o  Exploring the Tables

o  SELECTing Specific Columns

o  Sorting Records

    •   Sorting By a Single Column
    •   Sorting By Multiple Columns
    •   Sorting By Column Position
    •   Ascending and Descending Sorts

o  The WHERE Clause and Operator Symbols

        •   Checking for Equality
        •   Checking for Inequality
        •   Checking for Greater or Less Than
        •   Checking for NULL
        •   WHERE and ORDER BY

o  The WHERE Clause and Operator Words

        •   The BETWEEN Operator
        •   The IN Operator
        •   The LIKE Operator
        •   The NOT Operator

o  Checking Multiple Conditions

        •   AND
        •   oR
        •   order of Evaluation

o  Writing SELECTs with Multiple Conditions

Advanced SELECTs with SQL Server

o  Calculated Fields

        •   Concatenation
        •   Mathematical Calculations
        •   Aliases

o  Aggregate Functions and Grouping

        •   Aggregate Functions
        •   Grouping Data

o  Built-in Data Manipulation Functions

        •   Common Math Functions
        •   Common String Functions
        •   Common Date Functions

Subqueries, Joins, and Unions with SQL Server

o  Subqueries

o  Joins

        •   Table Aliases
        •   Multi-table Joins

o  outer Joins

o  Unions

        •   UNION ALL
        •   UNION Rules

Inserting, Updating and Deleting Records with SQL Server

o  UPDATE

o  DELETE

Creating and Modifying Tables with SQL Server

o  Creating Tables

o  Datatypes

o  Altering Tables

        •   Adding a Column
        •   Dropping a Column

o  Renaming Tables

o  Dropping Tables

Views and Stored Procedures in SQL Server

o  Creating Views

o  Dropping Views

o  Creating Stored Procedures

        •   Designing a stored procedure
        •   Specifying input parameters
        •   Returning a recordset
        •   Returning output parameters

o  Testing stored procedures

Conclusion

Show Buttons
Hide Buttons