SQL Server 2000 Complete Course

Outcomes Upon successful completion of this course the Student will have received all the information necessary to understand SQL Server 2000 and function in a work in a medium to enterprise computing environment that uses SQL Server 2000. Student should be sufficiently trained take and pass Microsoft SQL Server 2000 exams 70-228 and 70-229 which are required for the Microsoft Certified Database Administrator (MCDBA) Certification. Assessment This set of courses indluced quizzes for every level as well as pre and post course tests. This set also includes practice exams for Microsoft exams 70-228 and 70-229. Instructors: Paul Sherriff and Michael Lee. Paul has over nine years of experience programming for various businesses in the aerospace, real estate, medical & hotel areas. Paul is a designer of courseware for Application Developers Training Company & tours with the Visual Basic Master Seminar Series in both the USA & Canada. He has published over 25 articles in major publications. Michael is a Senior Trainer and Consultant with Saqqara Technology, Inc, a Microsoft Certified Partner and has over 10 years technology training experience and has published numerous books about SQL Server and Client/Server development. He is a popular speaker and presenter, and is in demand at client sites and conferences all over the US and Europe.Course Outline: DESIGNING Lesson 1 Relational Databases

• What is a Database?
• Relational Algebra
• Relational Database Terms

Database Normalization

• How to Normalize a Database
• Case Study: Employee Time Tracking System

An Overview of SQL Server

• Features & Capabilities

Creating Databases

• SQL Server Databases & Logs
• Files & File Groups
• Creating a Database
• Database Size Planning
• Using T-SQL to create a database

DESIGNING Lesson 2 Creating Tables

• SQL Server Data Types
• Creating Tables
• Adding Data to a Table
• Creating Tables Using T-SQL

Constraints & Indexes

• Data Integrity
• Indexes
• Creating CHECKS & Rules
• Using T-SQL to Create Constraints

Retrieving Data

• Select Statement
• WHERE Clause
• DISTINCT & Calculated Columns
• ORDER BY Clause
• Using SQL Query Analyzer

DESIGNING Lesson 3 Advanced Queries

• Aggregated Functions
• Grouping Data
• Advanced Queries
• Distributed Queries

Joining Tables

• Inner Joins
• Outer Joins
• Cross Join & Self Join
• Sub-Queries
• Unions

Data Modification

• INSERT Statements
• Creating Temporary Tables
• UPDATE Statements
• DELETE Statement

System Functions

• Date Function
• Mathematical Functions
• String Functions
• System Functions
• Security Functions
• Conversion Functions
• User-Defined Functions
• System Variables

DESIGNING Lesson 4 Stored Procedures

• Intro to Stored Procedures
• Passing Parameters
• Returning Information from Stored Procedures
• OUTPUT Parameters
• Dynamic SQL
• Cursors

Triggers & Views

• Update Triggers
• Insert Triggers
• Delete Triggers
• INSTEAD OF Triggers
• Views

Locking Issues

• Locking Scenarios
• Isolation Levels
• Resource Lock Types
• Lock Modes
• Lock Escalation
• Viewing Locks
• Setting the Isolation Level
• Locking Hints

DESIGNING Lesson 5 Transaction Processing

• What is a Transaction?
• Nesting Transactions
• Trying Out Transactions
• Deadlocks

Data Transfer

• Services Available
• BCP
• BULK INSERT
• DTS

Full Text Search

• What is Full Text Search?
• Settting Up Full Text Search
• Performing a Full Text Search
• Full Text System Procedures
• FTS Enhancements in SQL 2000

Optimization

• Optimizing Your Server
• Database Design
• Query Optimization
• Index Usage
• Statistics & Index Analyzer
• SQL Server Profiler
• Performance Monitor

System Databases & Tables

• System Databases
• System Tables

DESIGNING Lesson 6 Using XML Extensions

• Creating a Virtual Root
• Using a Virtual Root
• Returning Elements Based XML
• Passing Parameters
• Multiple Result Sets
• Using Stored Procedures
• Inserting Data Using XML
• Joining Tables
• Retrieving Data from VB

Application Development

• OLE DB, ODBC & ADO
• USING ADO

ADMINISTRATION Lesson 1 An Overview of SQL Server

• Why use SQL Server
• Graphical Tools
• Transact SQL
• Database Objects
• Security
• Services
• Capacities
• Connectivity
• Other Features

Installing SQL Server 2000

• Operating Systems
• Versions & Configurations
• Multiple Instances
• Licensing
• File Locations
• Sort Orders
• Types of Installation
• Installation Checklist
• Performing an Installation
• Troubleshooting an Upgrade
• Unattended Installations

Database Setup & Maintenance

• Database Objects
• System Stored Procedures & Functions
• Database Layout
• Database Files & File Groups
• Transaction Log
• Database Growth
• Creating Databases using Enterprise Manager
• Creating Databases using T-SQL
• Sp-helpbd
• Expanding a Database
• Shrinking a Database
• Database Options
• Recovery Models
• Database Spacing Planning
• Multiple Disk Usage

ADMINISTRATION Lesson 2 Security

• Introduction to Security
• Authentication
• Creating Logins
• Server Roles
• Assigning Server Roles
• Database Roles
• Managing Database Roles
• Permissions
• Managing Permissions
• Views & Security
• Stored Procedures & Security
• Application Roles

Operators, Jobs, Alerts & Mail

• Definitions
• SQL Server Agent Configuration
• Operators
• Jobs
• Alerts
• SQL Mail
• Troubleshooting

ADMINISTRATION Lesson 3 DBCC

• Maintenance Statements
• Pin Statements
• Trace Flags
• Information Statements
• Check Statements
• Old Statements

Backup & Restore

• Why do Backup?
• Considerations of the Backup Process
• Managing the Backup Devices
• System Database Backup
• Multiple Backup Devices
• Backup Planning
• Media Choices
• Backup Types
• Backup Scenarios
• Copying/Moving
• Stand-by servers & Clustering
• Performing a Backup
• Restore a Backup

Database Maintenance Plan Wizard

• What is the DMPW?
• Running the Wizard
• Jobs created by the Wizard

Web Assistant

• What is the Web Assistant?
• Running the Web Assistant
• Using a Template File
• Triggers Generated by Web Assistant

ADMINISTRATION Lesson 4 Transferring Objects & Scripting

• Methods of Data Transfer
• DTS Designer
• Copy Database Wizard
• sp_attach_db & sp_detach_db
• Scripting Objects

Error Logs

• Windows Event Logs
• SQL Server Error Logs
• Demo of Windows Logs
• Demo of SQL Server Logs

Distributed Management Objects

• DMO Object Hierarchy
• Logging into SQL Server
• Loading a List of Databases
• Loading Tables
• Users & Table Scripts
• Indexes & Columns

Tips & Tricks

• General Tips & Tricks

ADMINISTRATION Lesson 5 Replication Concepts

• What is Replication?
• Using a Loosely Coupled Model Replication vs. Distributed Transactions
• The Publisher / Subscriber Metaphor
• Role of the Publisher Role of the Distributor Role of the Subscriber Publications Articles
• Replication Types
• Snapshot Transactional Merge Updating Subscribers

Planning a Replication Strategy

• Basic Requirements
• Autonomy vs. Integrity
• Evaluating Business Objectives
• Physical Replication Models
• Central Publisher with Distributor Central Publisher with Remote Distributor Republisher Central Subscriber
• Network Considerations
• Security Issues
• Capacity Planning
• Triggers and Constraints
• Planning Updating Subscribers

Implementing Snapshot Replication

• How Snapshot Replication Works
• Configuring Snapshot Replication

Implementing Transactional Replication

• How Transactional Replication Works
• Configuring Transactional Replication

Merge Replication

• How Merge Replication Works
• Configuring Merge Replication
• Conflict Detection and Resolution
• Using Default Resolvers
• Building Custom Resolvers

ADMINISTRATION Lesson 6 Monitoring and Tuning Replication

• Introduction to Replication Monitor
• Overview of Replication Agents
• Snapshot Agent Distribution Agent Log Reader Agent Queue Reader Agent Merge Agent Cleanup Agents
• Viewing Agent History
• Replication Alerts
• Performance Issues

Replication with Non-SQL Data Sources

• Heterogeneous Subscribers
• Using ODBC/OLE DB for replication Replicating to Microsoft Access Using Merge Replication
• Heterogeneous Publishers

Replication and Database Maintenance

• Backup Issues
• Backing Up the Publisher Backing Up the Subscriber Backing Up the Distributor
• Transactional Replication and Log-Shipping

Overview of Programmatic Replication

• Using ActiveX Controls
• Using the SQL DMO
• The Replication Distributor Interface
• Using Transact SQL Stored Procedures

DATA WAREHOUSE Lesson 1: Introduction & Planning Introduction

• Topic Overview
• Transactional vs. Analytical Data Systems
• Data Warehouse Defined
• Data Warehouses vs. Data Marts/OLAP

Dimensional Modeling & Planning

• Entity-Relationship Modeling vs. Dimensional Modeling
• Elements of the Dimensional Model
• The Role of User Requirements
• Identifying Source Data
• Implementing the Data Warehouse Bus

Planning Facts

• What is a fact?
• Measures

Planning Dimensions

• Planning Dimension Tables
• Planning a Hierarchy
• Special Dimensions

DATA WAREHOUSE Lesson 2: Data Marts Building a Data Mart

• Creating the Database
• Creating the Fact Table
• Creating Dimension Tables
• Creating Constraints and Indexes

Loading the Data Mart

• Using Staging Tables
• Using Transact SQL for Data Migration
• Loading Data using Data Transformation Services (DTS)
• Advanced DTS Issues
• Evaluating a Typical Load Scenario

Maintaining the Data Mart

• Backup and Restoration Considerations
• Handling Changing Facts
• Handling Changing Dimensions
• Securing the Data Mart

DATA WAREHOUSE Lesson 3: Introduction to OLAP, Cube and Dimension Introduction to OLAP

• OLAP Concepts
• Analysis Services Architecture

Cube Basics

• Cube Basics & Measures
• Dimensions & Hierarchies
• Aggregations
• Using the Analysis Manager

Creating Dimensions

• Defining Terms
• Creating Dimensions with the Dimension Wizard
• Creating Time Dimensions
• Using the Dimension Editor
• Basic Dimension Properties
• Using Member Properties
• Virtual Dimensions
• Multiple Hierarchies

Creating Cubes

• Selecting Facts & Measures
• Choosing Dimensions
• Creating Calculated Measures
• Storage Models
• Processing Dimensions
• Processing Cubes
• Using Cube Properties

DATA WAREHOUSE Lesson 4: Cubes and Special Features Managing Cube Partitions

• Partitions Concepts
• Creating Cube Partitions
• Merging Partitions

Optimizing Cubes

• Optimizing Aggregations with Storage Models
• Usage-Based Optimizations
• Tuning the Analysis Server

Special Features

• Using Drillthrough
• Creating and Using Actions

Connecting Clients Using OLE DB

• Analysis Application Architecture
• Using Excel as an Analysis Server Client
• Overview of Client Interfaces

Introduction to Data Mining

• Data Mining Basics
• Creating Data Mining Models

DATA WAREHOUSE Lesson 5: MDX MDX Concepts Overview

• What is MDX?
• Role of MDX in Application Architecture
• Creating Calculated Members Within Cubes Using MDX in Client Applications
• MDX Examples

Creating MDX Expressions

• Understanding the Hierarchy
• Using Brackets
• Identifying Members
• Qualifying Sets of Members
• Creating Calculated Members

Interacting with Cubes

• Anatomy of an MDX Query
• Basic MDX Queries
• Tuple Expressions

Performing Aggregations

• Calculating Data in an MDX Query
• Using Aggregate Functions
• Using Time Set Functions

Using MDX with ADOMD/div>

• Connecting to an Analysis Server
• Executing an MDX Query
• Manipulating Results

XML Enabled APPS Lesson 1: Introduction to XML Apps Using SQL SQL Primer

• Using Hierarchical Data
• XML Document Structure
• Formatting with XSL
• Validating Data with Schema

XPath Concepts for SSQL Server

• General Syntax & SQL Limitations
• Location Paths
• Filters
• Operators & Functions

Using the FOR XML Transact SQL

• Basic Concepts
• Using RAW Mode
• Using AUTO Mode
• Using the EXPLICIT Mode

Using the OPENXML Transact SQL

• OPENXML Basics
• Mapping Columns to XML
• Defining Complex Mappings
• Performance Issues

XML Enabled APPS Lesson 2: Using SQL and XML Accessing SQL Data through HTTP

• Working with Virtual Directions
• Executing SQL Statements
• Executing Stored Procedures

Using XML Templates

• Creating Templates
• Passing Parameters to Templates
• Using XSL Style Sheets with Templates
• Accessing Database Objects

Using Annotated XDR Schemas

• Annotated Schema Concepts
• Mapping XML Elements to Columns
• Specifying Explicit Mappings
• Specifying Relationships
• Other Annotations

Using XML with ADO

• Persisting ADO Data as XML
• Executing XML Queries Using Command Stream
• Attaching an XLS Style to XML Stream

Using the XML for SQL Update

• Enhancements
• Using Updategrams
• Using the XML Bulk Load Utility

Contact Hours: 76