Microsoft SQL Server 2000 for Experienced Database Professionals
Course 2723—Three days—Instructor-led
On This Page
 | Introduction |
| Audience |
| At Course Completion |
| Prerequisites |
| Microsoft Certification exams |
| Course Materials |
| Course Outline |
| Take This Training |
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 Certification 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. |
Additional Resources
For additional information and resources on SQL Server 2000, consider the following:
| • | For more information about Microsoft SQL Server 2000, go to http://www.microsoft.com/sql/default.asp |
| • | For SQL Server 2000 120-day Trial Software, go to http://www.microsoft.com/sql/downloads/trial-software.mspx |
| • | For books on SQL Server 2000, go to http://www.microsoft.com/sql/techinfo/books.mspx |
Take This Training
|