SQL SERVER DEVELOPER Available Features and Tools New Capabilities SQL Services Product Licensing Product Editions Will teach in class room

SQL SERVER DEVELOPER

Server 2005/2008  Available Features and Tools  New Capabilities

 SQL Services  Product Licensing  Product Editions

Lab Exercise  Will teach in class room Configuring and

Installing SQL Server 2005/2008

 Preparing for Installation

 Creating Users for Service Accounts  Choosing Appropriate File Locations  Choosing Instance Names

 Installing SQL Server 2008  Choosing Appropriate Features  Specifying Service User Accounts  Choosing Licensing Mode

 Choosing Appropriate Collation  Installing Multiple Instances  Upgrading to SQL Server 2008  Service Packs and hot fix

 Configuring the Surface Area of SQL Server

Lab Exercise  Will teach in class room Using SQL Server

2005/2008 Management Tools

 SQL Server Management Studio  SQL Server Configuration Manager  SQL Server Books Online

 SQLCMD

Lab Exercise  Will teach in class room RDBMS Concepts  Database

 DBMS  RDBMS

 Advantages of RDBMS  Levels of Data Abstraction  Integrity Rules

 Data Model  E-R model  ACID Properties.

Lab Exercise  Will teach in class room

Database design  Software Development Life Cycle

 SDLC Phases  Logical design  Physical Design  Normalization  Denormalization  Relationships in RDBMS

Lab Exercise  Will teach in class room ERWIN  Database design using Erwin

 Physical design in Erwin  Forward engineering  Reverse Engineering.

Lab Exercise  Will teach in class room T-SQL Concepts  Database Creation

 Data Types  DDL  DML  Keys  Constraints  SQL Key Words

Lab Exercise  Will teach in class room Joins, Indexes,

Views  Inner Join  Left Outer Join

 Right outer Join  Full Join  Cross Join  Self Join.  Clustered Index  Non-Clustered index  General view  Indexed View

Lab Exercise  Will teach in class room Queries, Variables,

Scripts  Sub-Queries  Correlated Query

 Derived Table  Variables

 IF, While, Case expressions.

Lab Exercise  Will teach in class room SQLCMD  SQL Command Prompt Basics

 BCP

 Bulk INSERT  Error Handling  Transactions  Isolation levels

Lab Exercise  Will teach in class room Triggers, Cursors  Before Trigger

 After Trigger  Cursor  Scroll Cursor

Lab Exercise  Will teach in class room Function, Stored

Proc

 System Function  UDF

 Multi-statement Table Valued  Stored procedures

 Insert Stored procedure  Update stored procedure  Delete stored procedure

 Output value from stored procedure  Automatically run stored proc.

Lab Exercise  Will teach in class room SSIS/SSRS

SQL SERVER INTEGRATION SERVICES

 Introduction to the SSIS environment  Tasks

 Drilling into the Data Flow  Expressions and Variables  Containers

 Advanced Tasks

 Event Handling and Logging in SSIS  Migration from DTS

 Administrating SSIS

 Enterprise package development

SQLSERVER 2005INTEGRATION SERVICES (SSIS)TRAINING COURSE OUTLINE

Introduction to SQL Server Integration Services

Product History

SSIS Package Architecture Overview Development and Management Tools Deploying and Managing SSIS Packages Source Control for SSIS Packages

SSIS for DBAs: Using SQL Server Management Studio

The Import and Export Wizard Importing and Exporting Data Working with Packages

Database Maintenance Plans

Creating Database Maintenance Plans using SSMS Scheduling and Executing Plans

Examining Database Maintenance Plan Packages Changes in SQL Server 2005 Service Pack 2

Business Intelligence Development Studio

Launching BIDS Project templates The package designer The Toolbox

The SSIS menu

Introduction to Control Flow

Control Flow Overview Precedence Constraints The Execute SQL Task The Bulk Insert Task The File System Task The FTP Task

The Send Mail Task

Advanced Control Flow

Containers - grouping and looping The Web Service Task

The WMI tasks

The Analysis Services tasks The Execute Process Task The Execute Package Task

Introduction to Data Flow

Data Flow Overview Data Sources Data Destinations Data Transformations

The Copy Column Transformation The Derived Column Transformation The Data Conversion Transformation The Conditional Split Transformation The Aggregate Transformation The Sort Transformation Data Viewers

Variables and Configurations

Variables Overview Variable scope

SSIS system variables

Using variables in control flow Using variables in data flow

Using variables to pass information between packages Property expressions

Configuration Overview Configuration options Configuration discipline

Debugging, Error Handling and Logging

SSIS debugging overview Breakpoints in SSIS SSIS debugging windows

Control Flow: The OnError event handler Data Flow: Error data flow

Configuring Package Logging Built-in log providers

Advanced Data Flow

Revisiting data sources and destinations The Lookup Transformation

Getting Fuzzy: The Fuzzy Lookup and Fuzzy Grouping Transformations The Multicast Transformation

The Data Mining Query Transformation The Data Mining Model Training Destination The Slowly Changing Dimension Transformation

Extending SSIS through Custom Code

Introduction to SSIS scripting The SSIS script editor

The SSIS object model

Script in Control flow: The Script Task Script in Data flow: The Script Component Introduction to SSIS component development

SSIS Package Deployment

Configurations and deployment The deployment utility

Deployment options Deployment security

Executing packages - DTExec and DTExecUI

SSIS Package Management

The SSIS Service

Managing packages with DTUtil

Managing packages with SQL Server Management Studio Scheduling packages with SQL Server Agent

SQL SERVER REPORTING SERVICES

 SSRS Architecture and components  Installation and Setup

 BIDS environment and the Report Designer IDE  Report Development Lifecycle

 Reporting Services Report Designer

 Designing and Developing Reports with Reporting Services  Report Deployment

 Report Builder  Grouping and Sorting  Parameters

 Expressions, Functions, and Custom Code  Report Management