Introduction

This three-day course will teach database professionals who are already proficient in the implementation and maintenance of enterprise databases about the core architecture of Microsoft SQL Server™ 2000 and how it works. The primary focus of this course is to give experienced database professionals a jump-start to better understanding SQL Server 2000. This is not an advanced course or a how-to course.

Audience
The primary audience for this course is database administrators who are experienced and proficient with enterprise database products such as Oracle, DB2, or Sybase. It will also help current database administrators who are experienced with Microsoft SQL Server 6.5 and earlier. This course is not for individuals who are experienced only in desktop database management systems such as Microsoft Access or individuals who are experienced in SQL Server 7.0.

At Course Completion
After completing this course, students will be able to:

Describe the architecture of SQL Server and its programming environment.
Describe the key components of SQL Server architecture.
Describe the key components of SQL Server storage subsystem architecture.
Describe the typical job tasks to administer and manage SQL Server in a multiple server environment.
Describe the process of developing a data recovery plan.
Describe the process of controlling data access and securing SQL Server.
Describe the tools and methods to monitor system and server activity.
Describe the tools and methods use to manage locks.
Describe the items that may affect performance in SQL Server.
Describe how to access homogenous and heterogeneous data sources.
Describe the processes and tools for moving data into and out of SQL Server.
Describe the processes and tools for analyzing data in SQL Server, using Analysis Services.

Prerequisites
This course requires that students meet the following prerequisites:

Database administrators who are experienced in Oracle, DB2, or Sybase. These database administrators have a technical depth of understanding of the database management systems (DBMS) that they currently administer, implement, and manage, and want to apply their database skills to SQL Server 2000.

Database administrators who are experienced in Microsoft SQL Server 4.2x, 6.0, or 6.5. (Secondary): These database administrators have a technical depth of understanding of the database systems that they currently administer, implement, and manage, and want to apply their current skills to SQL Server 2000.

To be successful in this class, students should have experience in administration and implementation of departmental or enterprise relational database management systems (RDBMS).

Microsoft Certified Professional Exams
Exam 70-228: Installing, Configuring, and Administering Microsoft SQL Server 2000 Enterprise Edition
Exam 70-229: Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

Course Materials
The student kit includes a Student Workbook and Student Materials compact disc.

Course Outline

Module 1: SQL Server Product Overview
This module introduces key components of SQL Server architecture and describes the structure of databases in SQL Server, related definitions, and terminology.

Lessons
Introduction to SQL Server Architecture
SQL Server Programming Environment

Lab A: Working with SQL Server Tools
Using SQL Server Enterprise Manager and SQL Query Analyzer
Creating Columns That Have the Identity Data Type
Creating Columns That Have the Uniqueidentifier Data Type
Working with Metadata
Retrieving XML Result Sets

After completing this module, students will be able to:

Describe the architecture of SQL Server and its programming environment.
Describe the architecture of SQL Server in terms of its major components and tools.
Describe the SQL Server programming environment.

Module 2: SQL Server Components
This module introduces the key components of SQL Server relational database management system (RDBMS) architecture and their use in configuring and optimizing SQL Server. The module also describes the structure of databases in SQL Server, related definitions, and terminology.

Lessons
Relational Engine
Analyzing Query Plans
Memory and Processes
Client Connectivity

Lab A: Creating Databases
Writing Scripts for Creating a Database
Creating a Database with Multiple Files and Filegroups

Lab B: Configuring, Troubleshooting, and Tuning
Configuring Server/Client Network Libraries
Using Extended Stored Procedures
Troubleshooting Queries by Using SHOWPLAN
Optimizing Queries by Using the Index Tuning Wizard

After completing this module, students will be able to:

Describe key components of SQL Server RDBMS architecture.
Describe SQL Server components.
Describe relational engine architecture.
Describe tools and methods that are used to maintain efficient queries.
Describe query execution plans and how SQL Server can optimize them.
Describe memory architecture and management.
Describe SQL Server threads and process management.
Describe the process, tools and techniques for connecting clients to SQL Server.

Module 3: SQL Server Storage Subsystems
This module describes the storage structures of SQL Server, index architecture and indexing strategies and how they are unique in SQL Server, how to manage indexes, and finally how to work with SQL Server statistics.

Lessons
SQL Server Storage Structures
SQL Server Index Architecture
Managing Indexes
Working With Statistics

Lab A: Examining Disk Storage Structures
Examining Indexes by Using DBCC Page
If Time Permits: Examining Heaps by Using DBCC Page

After completing this module, students will be able to:

Describe key components of SQL Server storage subsystem architecture.
Describe SQL Server storage architecture.
Describe SQL Server index architecture.
Manage SQL Server indexes.
Describe approaches to working with statistics.

Module 4: SQL Server Administration
This module provides students with the knowledge and skills necessary to administer and manage Microsoft® SQL Server™ in a multiple server environment. Topics include using scripting, SQL Server Enterprise Manager, and maintenance plans. The module also teaches how to use SQL Server Agent and jobs to automate administrative tasks.

Lessons
Administering SQL Server by Scripting and Using SQL Server Enterprise Manager
Automating Administration by Using SQL Server Agent and Jobs
Creating and Using Alerts
Using Multiserver Jobs

Lab A: osql Scripting
Creating a Script to Perform Administrative Actions
Executing an Administrative Script with the osql Utility
Converting the Script to a Stored Procedure

Lab B: Using SQL Server Agent
Creating and Scheduling Jobs with SQL Server Agent

Lab C: Creating Alerts and Multiserver jobs
Creating Alerts

After completing this module, students will be able to:

Describe typical job tasks to administer and manage SQL Server in a multiple server environment.
Describe typical administrative tasks for SQL Server using SQL Server Enterprise Manager, maintenance plans, and scripting.
Describe how to create and use SQL Server Alerts.
Describe how to administer multiple servers as master and target servers.

Module 5: Backup and Restore
This module provides students with the knowledge and skills to create a recovery plan, and to perform backup and restore procedures in SQL Server 2000.

Lessons
SQL Server Recovery Models
SQL Server Backups
Restoring Backups

Lab A: Backup and Restore
Creating Routine Database Backups
Restoring a Database Using Full, Differential, and Log Backups
If Time Permits: Restoring to a Point in Time

After completing this module, students will be able to:

Describe database recovery models.
Describe the SQL Server back up-and-restore process and operations.

Module 6: SQL Server Security
This module provides information about the process of controlling data access and securing a Microsoft SQL Server relational database management system. It explains security architecture and methods of control and how SQL Server implements logins, users, and roles. The module concludes with a discussion of how SQL Server applies permissions and uses ownership chains.

Lessons
SQL Server Security Architecture
Securing SQL Server
Common Security Issues

Lab A: Applying Security Measures to SQL Server and Database Objects
Assigning Statement Permissions
Assigning Object Permissions
Securing an Installation of SQL Server

After completing this module, students will be able to:

Describe the process of controlling data access and securing SQL Server.
Describe security architecture and methods of control.
Describe methods of securing SQL Server.
Describe common security issues and methods for resolving them.

Module 7: Monitoring SQL Server
This module describes the processes and procedures that are used to monitor SQL Server performance and then to tweak SQL Server to improve performance. It covers the use of query plans and indexing strategies and how they are unique in SQL Server.

Lessons
Monitoring System and Server Activity
Managing Locks
Additional Performance Considerations

Lab A: Monitoring and Troubleshooting SQL Server Performance
Using Performance Monitor
Using SQL Profiler
Troubleshooting Deadlocks

Lab B: Contrasting Cursors and Table Variables
Contrasting Cursors and Table Variables

After completing this module, students will be able to:

Describe tools and methods that you can use to monitor system and server activity.
Describe the tools and methods that you can use to manage locks.
Describe items that may affect performance in SQL Server.

Module 8: Transferring and Analyzing Data
This module explains the processes of accessing homogenous and heterogeneous data sources, moving data into and out of SQL Server, and the architecture and components of SQL Server 2000 Analysis Services. It describes data storage cubes, query language, and various data mining models and discusses various data analysis business needs and relevant SQL Server technology solutions.

Lessons
Distributed Queries
Data Extraction, Transformation, and Loading Tools
Analysis Services

Lab A: Working with External and Remote Data
Transferring Database Contents to a Database with a Different Collation
Transforming Data During Extraction and Load
Querying Distributed Data
If Time Permits: Transferring Bulk Data Into and Out of SQL Server

After completing this module, students will be able to:

Describe how to access homogenous and heterogeneous data sources.
Describe the processes and tools for moving data into and out of SQL Server.
Describe the processes and tools for analyzing data in SQL Server, using Analysis Services.

For more information or to give feedback, send e-mail to info@OneApex.com or call 949-597-2100