SQL Server Training

Our SQL Server training program includes two five-day SQL Server courses that are currently designed for database administrators and developers using SQL Server 2000.

SQL Server classes are offered at our training center in Indianapolis and at customer sites upon request.

Course 201| Course 202

SQL 201

Microsoft SQL Server 2000 System Administration: A 5-day course designed to teach students how to install, configure, administer, and troubleshoot Microsoft SQL Server 2000 in a Windows 2000 environment. This course will help prepare students for MCP exam 70-228. Prerequisites: Working knowledge of 2000, Fundamental relational database concepts, and Basic SQL statements. Fee: $1,750. 

SQL Server Overview

What Is SQL Server

SQL Server Integration

SQL Server Databases

SQL Server Security

Working with SQL Server

Lab:

SQL Server Overview

Skills:

After completing this module, students will be able to:

·    Describe SQL Server 2000 and its supported operating system platforms.

·    Describe SQL Server architecture.

·    Describe SQL Server databases.

·    Describe SQL Server security.

·    Describe SQL Server querying, implementation, administration, and data warehousing activities, as well as client application design options.

Planning to Install SQL Server

Topics:

Hardware Installation Considerations

SQL Server 2000 Editions

Software Installation Considerations

Methods of Installing SQL Server

Verifying the Installation

Configuring SQL Server Enterprise Manager

Troubleshooting

Lab:

Installing SQL Server

Skills:

After completing this module, students will be able to:

·    Determine hardware requirements for SQL Server 2000 and the SQL Server management tools.

·    Describe the various SQL Server Editions.

·    Describe the different types of licensing.

·    Determine software installation options that are appropriate for your environment.

·    Describe various methods of installing an instance SQL Server and install it by using SQL Server Setup.

·    Verify the installation of SQL Server.

·    Configure SQL Server Enterprise Manager.

·    Troubleshoot the installation.

Managing Database Files

Topics:

Introduction to Data Structures

Creating Databases

Managing Databases

Placing Database Files and Logs

Optimizing the Database Using Hardware-based RAID

Optimizing the Database Using Filegroups

Optimizing the Database Using Filegroups with Hardware-based RAID

Capacity Planning

Performance Considerations

Lab:

Managing Database Files

Skills:

After completing this module, students will be able to:

·    Describe how SQL Server stores data and handles transactions.

·    Create a database, including specifying options during and after database creation.

·    Grow, shrink, or delete a database.

·    Determine the placement of database files and transaction logs for performance and fault tolerance.

·    Optimize a database by using hardware-based RAID.

·    Determine when and how to use filegroups to optimize a database.

·    Optimize a database by using filegroups with hardware-based RAID.

·    Estimate the amount of space that a database requires.

Managing Security

Topics:

Implementing an Authentication Mode

Assigning Logins to Users and Roles

Assigning Permissions to Users and Roles

Managing Security Within SQL Server

Managing Application Security

Managing SQL Server Security in theEnterprise

Labs:

Managing Security

Managing Permissions

Managing Application Security

Skills:

After completing this module, students will be able to:

·    Implement Windows Authentication Mode and Mixed Authentication Mode.

·    Assign login accounts to database user accounts and roles.

·    Assign permissions to user accounts and roles.

·    Manage security within SQL Server.

·    Manage security with views and stored procedures.

·    Create and use application roles to manage application security.

·    Manage SQL Server security in the enterprise environment.

Performing Administrative Tasks

Topics:

Configuration Tasks

Routine SQL Server Administrative Tasks

Automating Routine Maintenance Tasks

Creating Alerts

Troubleshooting SQL Server Automation

Automating Multiserver Jobs

Labs:

Configuring SQL Server

Creating Jobs and Operators

Creating Alerts

Skills:

After completing this module, students will be able to:

·    Perform common SQL Server configuration tasks.

·    Describe how to upgrade SQL Server version 6.5 and SQL Server 7.0 to SQL Server 2000.

·    Describe routine database administration tasks.

·    Automate routine maintenance tasks by creating and scheduling jobs.

·    Create alerts and operators.

·    Troubleshoot automated jobs, alerts, or notifications.

·    Automate administrative jobs in a multiserver environment.

Backing Up Databases

Topics:

Preventing Data Loss

Setting and Changing a Database Recovery Model

SQL Server Backup

When to Back Up Databases

Performing Backups

Types of Backup Methods

Planning a Backup Strategy

Performance Considerations

Lab:

Backing Up Databases

Skills:

After completing this module, students will be able to:

·    Create backup files and backup sets.

·    Back up user and system databases by using Transact-SQL and SQL Server Enterprise Manager.

·    Back up databases that are created on multiple files and filegroups.

·    Apply the appropriate backup options to each of the different SQL Server 2000 backup methods.

·    Use the BACKUP LOG statement to back up and clear transaction logs.

·    Design an appropriate backup strategy.

Restoring Databases

Topics:

SQL Server Recovery Process

Preparing to Restore a Database

Restoring Backups

Restoring Databases from Different Backup Types

Restoring Damaged System Databases

Lab:

Restoring Databases

Skills:

After completing this module, students will be able to:

·    Describe the SQL Server recovery process.

·    Verify backups and perform specific tasks that enable the restore process.

·    Use the RESTORE statement to get information about a backup file before you restore a database, file, or transaction log.

·    Restore backups from different backup types and use the appropriate options.

·    Restore damaged system databases.

Monitoring SQL Server for Performance

Topics:

Why to Monitor SQL Server

Performance Monitoring and Tuning

Tools for Monitoring SQL Server

Common Monitoring and Tuning Tasks

Lab:

Monitoring SQL Server

Skills:

After completing this module, students will be able to:

·    Describe the reasons why monitoring SQL Server 2000 is important.

·    Develop a performance monitoring and tuning methodology.

·    Describe the tools available for monitoring SQL Server.

·    Perform common monitoring and tuning tasks by using counters and appropriate tools.

Transferring Data

Topics:

Introduction to Transferring Data

Tools for Importing and Exporting Data in SQL Server

Introduction to DTS

Transforming Data with DTS

Lab:

Transferring Data

Skills:

After completing this module, students will be able to:

·    Describe the rationale for, and the process of, importing, exporting, and transforming data.

·    Describe the tools for importing and exporting data in SQL Server 2000.

·    Transform data by using Data Transformation Services (DTS).

·    Create and edit a DTS package by using the DTS Import and DTS Export Wizards.

Maintaining High Availability

Topics:

Introduction to Availability

Increasing Availability Using Failover Clustering

Standby Servers and Log Shipping

Lab:

Automating the Maintenance of a Standby Server

Skills:

After completing this module, students will be able to:

·    Determine availability requirements and strategies for a Microsoft .NET Enterprise Server environment.

·    Use SQL Server failover clustering.

·    Configure a standby server and use log shipping to maintain its integrity.

Introducing Replication

Topics:

Introduction to Distributed Data

Introduction to SQL Server Replication

SQL Server Replication Agents

SQL Server Replication Types

Physical Replication Models

Labs:

Implementing Replication

Skills:

After completing this module, students will be able to:

·    Describe the various methods to distribute data in SQL Server 2000.

·    Explain the publisher-subscriber metaphor, including articles, publications, and subscriptions.

·    Describe SQL Server replication agents.

·    Explain the SQL Server replication types.

·    Describe the physical replication models

SQL202

Microsoft SQL Server 2000 Database Implementation:  A 5-day course designed to teach students how to implement database solutions using the latest version of the Microsoft SQL Server relational database management system. Students will gain an understanding of the product's architecture and, through the accompanying labs, attain hands-on experience with the procedures to plan, configure, and implement database solutions.  This course will help prepare students for MCP exam 70-229.  Prerequisites: Familiarity with Relational Database Concepts. Fee: $1,750.

In this course, you will design and implement enterprise database solutions as you learn how to...

SQL Server Overview

Topics:

What Is SQL Server?

SQL Server Integration

SQL Server Databases

SQL Server Security

Working with SQL Server

Lab:

SQL Server Overview

Skills:

After completing this module, students will be able to:

·    Describe SQL Server 2000 and its supported operating system platforms.

·    Describe SQL Server integration with Microsoft Windows 2000 and other server applications.

·    Describe SQL Server databases.

·    Describe SQL Server security.

·    Describe SQL Server administration and implementation activities, as well as SQL Server application design options.

Overview of Programming SQL Server

Topics:

Designing Enterprise Application Architecture

SQL Server Programming Tools

The Transact-SQL Programming Language

Elements of Transact-SQL

Additional Language Elements

Ways to Execute Transact-SQL Statement

Lab:

Overview of Transact-SQL

Skills:

After completing this module, students will be able to:

·    Describe the concepts of enterprise-level application architecture.

·    Describe the primary SQL Server programming tools.

·    Explain the difference between the two primary programming tools in SQL Server.

·    Describe the basic elements of Transact-SQL.

·    Describe the use of local variables, operators, functions, control of flow statements, and comments.

·    Describe the various ways to execute Transact-SQL statements.

Creating and Managing Databases

Topics:

Creating Databases

Creating Filegroups

Managing Databases

Introduction to Data Structures

Lab:

Creating and Managing Databases

Skills:

After completing this module, students will be able to:

·    Create a database.

·    Create a filegroup.

·    Manage a database.

·    Describe data structures.

Creating Data Types and Tables

Topics:

Creating Data Types

Creating Tables

Generating Column Values

Generating Scripts

Lab:

Creating Data Types and Tables

Skills:

After completing this module, students will be able to:

·    Create and drop user-defined data types.

·    Create and drop user tables.

·    Generate column values.

·    Generate a script.

Implementing Data Integrity

Topics:

Types of Data Integrity

Enforcing Data Integrity

Defining Constraints

Types of Constraints

Disabling Constraints

Using Defaults and Rules

Deciding Which Enforcement Method to Use

Lab:

Implementing Data Integrity

Skills:

After completing this module, students will be able to:

·    Describe the types of data integrity.

·    Describe the methods to enforce data integrity.

·    Determine which constraint to use and create constraints.

·    Define and use DEFAULT, CHECK, PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints.

·    Disable constraints.

·    Describe and use defaults and rules.

·    Determine which data integrity enforcement methods to use.

Planning Indexes

Topics:

Introduction to Indexes

Index Architecture

How SQL Server Retrieves Stored Data

How SQL Server Maintains Index and Heap Structures

Deciding Which Columns to Index

Lab:

Determining the Indexes of a Table

Skills:

After completing this module, students will be able to:

·    Describe why and when to use an index.

·    Describe how SQL Server uses clustered and nonclustered indexes.

·    Describe how SQL Server index architecture facilitates the retrieval of data.

·    Describe how SQL Server maintains indexes and heaps.

·    Describe the importance of selectivity, density, and distribution of data when deciding which columns to index.

Creating and Maintaining Indexes

Topics:

Creating Indexes

Creating Index Options

Maintaining Indexes

Introduction to Statistics

Querying the sysindexes Table

Setting Up Indexes Using the Index Tuning Wizard

Performance Considerations

Labs:

Creating and Maintaining Indexes

Viewing Index Statistics

Skills:

After completing this module, students will be able to:

·    Create indexes and indexed views with unique or composite characteristics.

·    Use the CREATE INDEX options.

·    Describe how to maintain indexes over time.

·    Describe how the query optimizer creates, stores, maintains, and uses statistics to optimize queries.

·    Query the sysindexes table.

·    Describe how the Index Tuning Wizard works and when to use it.

·    Describe performance considerations that affect creating and maintaining indexes.

Implementing Views

Topics:

Introduction to Views

Advantages of Views

Defining Views

Modifying Data Through Views

Optimizing Performance by Using Views

Performance Considerations

Lab:

Implementing Views

Skills:

After completing this module, students will be able to:

·    Describe the concept of a view.

·    List the advantages of views.

·    Define a view with the CREATE VIEW statement.

·    Modify data through views.

·    Optimize performance by using views.

Implementing Stored Procedures

Topics:

Introduction to Stored Procedures

Creating, Executing, Modifying, and Dropping Stored Procedures

Using Parameters in Stored Procedures

Executing Extended Stored Procedures

Handling Error Messages

Performance Considerations

Labs:

Creating Stored Procedures

Creating Stored Procedures Using Parameters

Skills:

After completing this module, students will be able to:

·    Describe how a stored procedure is processed.

·    Create, execute, modify, and drop a stored procedure.

·    Create stored procedures that accept parameters.

·    Execute extended stored procedures.

·    Create custom error messages.

Implementing User-defined Functions

Topics:

What Is a User-defined Function?

Defining User-defined Functions

Examples of User-defined Functions

Lab:

Creating User-defined Functions

Skills:

After completing this module, students will be able to:

·    Describe the three types of user-defined functions.

·    Create and alter user-defined functions.

·    Create each of the three types of user-defined functions.

Implementing Triggers

Topics:

Introduction to Triggers

Defining Triggers

How Triggers Work

Examples of Triggers

Performance Considerations

Lab:

Creating Triggers

Skills:

After completing this module, students will be able to:

·    Create a trigger.

·    Drop a trigger.

·    Alter a trigger.

·    Describe how various triggers work.

·    Evaluate the performance considerations that affect using triggers.

Programming Across Multiple Servers

Topics:

Introduction to Distributed Queries

Executing an Ad Hoc Query on a Remote Data Source

Setting Up a Linked Server Environment

Executing a Query on a Linked Server

Managing Distributed Transactions

Modifying Data on a Linked Server

Using Partitioned Views

Lab:

Using Distributed Data

Skills:

After completing this module, students will be able to:

·    Describe distributed queries.

·    Write ad hoc queries that access data that is stored in a remote SQL Server 2000 or in an OLE DB data source.

·    Set up a linked server environment to access data that is stored in a remote SQL Server 2000 or in an OLE DB data source.

·    Write queries that access data from a linked server.

·    Execute stored procedures on a remote server or linked server.

Optimizing Query Performance

Topics:

Introduction to the Query Optimizer

Obtaining Execution Plan Information

Using an Index to Cover a Query

Indexing Strategies

Overriding the Query Optimizer

Lab:

Optimizing Query Performance

Skills:

After completing this module, students will be able to:

·    Explain the role of the query optimizer and how it works to ensure that queries are optimized.

·    Use various methods for obtaining execution plan information so that they can determine how the query optimizer processed a query and validate that the most efficient query plan was generated.

·    Create indexes that cover queries

·    Identify indexing strategies that reduce page reads.

·    Evaluate when to override the query optimizer.

Analyzing Queries

Topics:

Queries That Use the AND Operator

Queries That Use the OR Operator

Queries That Use Join Operations

Labs:

Analyzing Queries That Use the AND and OR Operators

Analyzing Queries That Use Different Join Strategies

Skills:

After completing this module, students will be able to:

·    Analyze the performance gain of writing efficient queries and creating useful indexes for queries that contain the AND logical operator.

·    Analyze the performance gain of writing efficient queries and creating useful indexes for queries that contain the OR logical operator.

·    Evaluate how the query optimizer uses different join strategies for query optimization.

Managing Transactions and Locks

Topics:

Introduction to Transactions and Locks

Managing Transactions

SQL Server Locking

Managing Locks

Lab:

Managing Transactions and Locks

Skills:

After completing this module, students will be able to:

·    Describe transaction processing.

·    Execute, cancel, or roll back a transaction.

·    Identify locking concurrency issues.

·    Identify resource items that can be locked and the types of locks.

·    Describe lock compatibility.

·    Describe how SQL Server 2000 uses dynamic locking.

·    Set locking options and display locking information.

	For more information on our services, feel free to Contact Us. Our associated companies: Network Services Group provides training in SQL Server and other server products and developer tools, Information Engineering develops custom Web and PC based software applications for commercial use, and Network Engineering, provides assistance in developing network/hardware solutions. Products and technologies featured in our training include...
			VB.NET, C# , ASP.NET, ADO.NET, XML, Web Services
	Microsoft, Visual FoxPro, SQL Server, Visual Studio, .NET, Visual Basic, Visual C#, ASP.NET, ActiveX, and ADO.NET, are either trademarks or registered trademarks of Microsoft Corporation. Copyright © 2001-08 LEAD Associates, Inc. All rights reserved.