Microsoft SQL Server 2000

MOC 2092: Populating a Data Warehouse with Microsoft SQL Server 2000 Data Transformation Services

This course provides individuals with the knowledge and skills necessary to design relational star schema structures; to design data extraction, transformation, and loading programs; and to populate relational data marts using Data Transformation Services. This encompasses implementing Data Driven Query solutions, DTS packages, workflow design, data pump tuning and optimization techniques, options of data loading, and ActiveX data transformations.

Modules include:

• Module 1: Using DTS in a Data Warehouse
  
• Module 2: Defining Data Warehouse Structures
  
• Module 3: Populating Data Warehouse Structures
  
• Module 4: Using the DTS Import/Export Wizard
  
• Module 5: Understanding DTS Package Elements
  
• Module 6: Copying and Managing Data
  
• Module 7: Performing Data Transformations
  
• Module 8: Extending Transformations
  
• Module 9: Implementing Data Driven Query Solutions
  
• Module 10: Storing DTS Packages and Metadata
  
• Module 11: Executing Packages
  
• Module 12: Managing Package Properties
  
• Module 13: Building Advanced Workflows
  
• Module 14: Applying Best Practices
  
• Module 15: Case Study - Populating the Shipments Star
  
  

MOC 2073: Programming a Microsoft SQL Server 2000 Database

This course provides individuals with the technical skills required to program a database solution by using Microsoft SQL Server 2000. This course teaches how to design a SQL Server enterprise application architecture; describe the conceptual basis of programming in Transact-SQL; create and manage databases and their related components; implement data integrity by using the IDENTITY column property, constraints, defaults, rules, and unique identifiers; plan for the use of, create and maintain indexes; create, use, and maintain data views; implement user-defined functions; design, create, and use stored procedures; create and implement triggers; program across multiple servers by using distributed queries, distributed transactions, and partitioned views; optimize query performance; analyze queries; and manage transactions and locks to ensure data concurrency and recoverability.

Modules include:

• Module 1: SQL Server Overview
  
• Module 2: Overview of Programming SQL Server
  
• Module 3: Creating and Managing Databases
  
• Module 4: Creating Data Types and Tables
  
• Module 5: Implementing Data Integrity
  
• Module 6: Planning Indexes
  
• Module 7: Creating and Maintaining Indexes
  
• Module 8: Implementing Views
  
• Module 9: Implementing Stored Procedures
  
• Module 10: Implementing User-defined Functions
  
• Module 11: Implementing Triggers
  
• Module 12: Programming Across Multiple Servers
  
• Module 13: Optimizing Query Performance
  
• Module 14: Analyzing Queries
  
• Module 15: Managing Transactions and Locks
  
  

MOC 2072: Administering a Microsoft SQL Server 2000 Database

This course provides individuals with the knowledge and skills required to install, configure, administer, and troubleshoot the client-server database management system of Microsoft SQL Server 2000. This includes planning for a SQL Server installation, determining hardware requirements, security methods, optimizing performance, replication, maintaining high availability as well as backup and restore procedures and options.

Modules include:

• Module 1: SQL Server Overview
  
• Module 2: Planning to Install SQL Server
  
• Module 3: Managing Database Files
  
• Module 4: Managing Security
  
• Module 5: Performing Administrative Tasks
  
• Module 6: Backing Up Databases
  
• Module 7: Restoring Databases
  
• Module 8: Monitoring SQL Server for Performance
  
• Module 9: Transferring Data
  
• Module 10: Maintaining High Availability
  
• Module 11: Introducing Replication
  
  

MOC 2071: Querying Microsoft SQL Server 2000 with Transact-SQL.

This course instructs on the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server™ 2000. This covers the use of and ways to execute the Transact-SQL language particularly stored procedures, functions, triggers, views, and nested queries. Also included instruction on the use SQL query analyzer, OSQL utility, and syntax issues.

Modules include:

• Module 1: Introduction to Transact-SQL
  
• Module 2: Using Transact-SQL Querying Tools
  
• Module 3: Retrieving Data
  
• Module 4: Grouping and Summarizing Data
  
• Module 5: Joining Multiple Tables
  
• Module 6: Working with Subqueries
  
• Module 7: Modifying Data
  
• Module 8: Querying Full-Text Indexes
  
• Module 9: Introduction to Programming Objects
  
  

MOC 1609: Designing Data Services and Data Models

This workshop teaches participants to derive conceptual data storage and data access requirements from business requirements. This includes developing entity/relationship (ER) models, normalizing a logical data models, creating optimized physical data models, selecting the appropriate types and locations of data integrity requirements given constraints, and selecting appropriate data access and data storage technologies for solutions.

Modules include:

• Module 1: Course Overview
  
• Module 2: Solution Design Processes
  
• Module 3: Using a Conceptual Design for Data Requirements
  
• Module 4: Deriving a Logical Data Design
  
• Module 5: Normalizing the Logical Data Design
  
• Module 6: : Deriving a Physical Data Design
  
• Module 7: Implementing Data Integrity
  
• Module 8: Designing Data Services
  
• Module 9: Data Storage Considerations
  
  

Introduction to Microsoft SQL and Relational Database Design

This course instructs on the general principles involved in designing a database that complies with the relational database model and how to utilize the Transact-SQL language to access and manipulate data.

• Module 1: Introduction to Relational Databases
  

• The Basics of Relational Databases
  
• Planning the Database
  
• Identifying Database Design Issues
  
• Creating Databases
  
• Managing Databases
  
• Module 2: Designing Tables
  

• Deciding on Tables and Fields
  
• Selecting Primary Key Fields
  
• Identifying Table Relationships
  
• More on Keys and Relationships
  
• Design and Create Tables
  
• Maintaining Tables
  
• Module 3: Data Normalization
  

• Solving Data Problems
  
• Higher Normal Forms
  
• Module 4: Data Integrity
  

• Understanding Data Integrity
  
• Implementing Constraints
  
• Referential Integrity
  
• Field-level Integrity
  
• Table-level Integrity
  
• Reviewing and Finalizing the Database Design
  
• Module 5: Query Basics
  

• Performing Queries
  
• Multiple Table Queries
  
• Module 6: More on Queries
  

• Other Types of Queries
  
• SQL
  
• Optimizing Query Performance
  
• Module 7: An Overview of SQL Server
  

• Exploring the Components of SQL Server
  
• SQL Server Database Structure
  
• SQL Server Architecture
  
• Identifying SQL Server Management Tasks
  
• Module 8: Exploring Transact-SQL
  

• Working with Transact-SQL
  
• Transact-SQL Statements
  
• Programming in Transact-SQL
  
• Module 9: Implementing Indexes
  

• Designing Indexing
  
• Implementing Indexes
  
• Maintaining Indexes
  
• Module 10: Joining Tables
  

• Querying Multiple Tables
  
• Implementing Advanced Table Joins
  
• Module 11: Creating Stored Procedures
  

• Designing Stored Procedures
  
• Creating Stored Procedures
  
• Using Parameters in Stored Procedures
  
• Managing Stored Procedures
  
• Module 12: Using Functions
  

• Working with Aggregate Functions
  
• Designing and Creating User-Defined Functions
  
• Module 13: Creating Triggers
  

• Designing and Implementing Triggers
  
• Module 14: Understanding Transactions and Locks
  

• Designing and Implementing Transactions
  
• Managing Locks
  
• Module 15: Optimizing Queries
  

• Exploring the Query Optimizer
  
• Using Indexes to Optimize Queries
  
• Module 16: Analyzing Queries
  

• Analyzing the Performance of Queries