Site hosted by Angelfire.com: Build your free website today!
 
BLUEDATA image map
HOMEHOMECLASSESINFORMATION
 
       Microsoft Certified Professional Exams
 
This course will help the student prepare for the following Microsoft Certified Professional exam(s):

Exam 70-026, System Administration for Microsoft SQL Server 6.5
Exam 70-027, Implementing a Database Design on Microsoft SQL Server 6.5

Prerequisites

Due to the accelerated nature of this course, students should possess a current working knowledge of Microsoft Windows NT operating system version 3.51 or 4.0. Students should also have the following prerequisite knowledge and skills:

  • Two years of experience with relational databases, including designing a relational database
  • Understanding of ANSI-SQL statements
  • Experience working with database front-end query and reporting tools
  • Three to six months of experience with Microsoft SQL Server

    • The course materials, lectures, and lab exercises are in English. To benefit fully from our instruction, students need an understanding of the English language and completion of the prerequisites.

    The following software will be provided for use in the classroom:

  • Microsoft Windows NT Server version 4.0
  • Microsoft SQL Server version 6.5
    •  

    Course Outline

    Course 867, Module 1: Introduction to SQL Server Administration

  • Microsoft SQL Server 6.5 overview
    •  

    Course 867, Module 2: Installing and Configuring SQL Server

  • Where to install SQL Server
  • Installing server software
  • System databases
  • Post-installation configuration
    •  

    Lab:

  • Configuring SQL Server
    •  

    Skills:
    Students will be able to:

  • Install SQL Server.
  • Configure options using the Setup program, including MASTER database device, character set, sort order, network support, and auto-start options.
  • Locate configuration files, registry entries, default databases, and system tables.
  • Diagnose and solve common installation and configuration problems.
  • Configure SQL Executive to run as a Windows NT service.
    •  

    Course 867, Module 3: Managing Databases and Devices

  • Creating devices
  • Creating databases
  • Expanding devices
  • Resizing databases
  • Expanding the transaction log
  • Dropping devices and databases
  • Estimating storage requirements
    •  

    Lab:

  • Creating and managing devices and databases
    •  

    Skills:
    Students will be able to:

  • Create, resize, and drop devices and databases.
  • Assign the transaction log to a separate device.
  • Estimate storage requirements for databases, transaction logs, and indexes.
    •  
     

    Course 867, Module 4: Managing User Accounts

  • Introduction to database access
  • Planning for database access
    •  

    Lab:

  • Managing user accounts
    •  

    Skills:
    Students will be able to:

  • Add and drop login IDs, user names, groups, and aliases.
  • Change login IDs and passwords.
  • Change the default databases for a user.
  • Display information on login IDs, database users, groups, and aliases.
    •  
     

    Course 867, Module 5: Managing Login Security

  • Introduction to login security
  • Implementing integrated security
    •  

    Lab:

  • Managing login security
    •  

    Skills:
    Students will be able to:

  • Choose the appropriate security mode for a given situation.
  • Configure SQL Server for integrated security.
  • Test the security modes.
    •  

    Course 867, Module 6: Assigning User Permissions

  • Introduction to user permissions
  • Permission hierarchy
  • Granting and revoking permissions
  • Ownership chains
  • Permissions on views
  • Permissions on stored procedures
  • Displaying information on permissions
    •  

    Lab:

  • Assigning user permissions
    •  

    Skills:
    Students will be able to:

  • Grant and revoke statement and object permissions.
  • Test permissions.
  • Self Study - Module Reviews
    •  

    (Source: Course 867, Modules 1-5)

  • The Review page in each of the five modules
    •  

    Self Study - Course 750, Module 4: Retrieving Data

  • SELECT statement
  • Choosing columns
  • Manipulating character data
  • Choosing rows
  • Sorting results
    •  

    Skills:
    Students will be able to:

  • Write SELECT statements to retrieve specified columns.
  • Select specific rows based on comparisons, ranges, lists, character strings, and search arguments.
  • Manipulate character, numeric, and date/time data.
  • Format and sort query results.
    •  

    Self Study - Course 750, Module 4: Modifying Data

  • Inserting rows
  • Updating row data
  • Deleting rows
    •  

    Skills:
    Students will be able to:

  • Add new rows with the INSERT statement.
  • Update and remove rows based on information from other tables.
  • Use data in other tables to determine which rows to delete or update.
  • Use data in other tables as the values for updating.
    •  
     
     

    Course 750, Module 4: Retrieving Data
     

    Lab:

  • Retrieving data
    •  

    Course 750, Module 5: Retrieving Data-Advanced Topics

  • Generating summary data
  • Correlating data
  • Performing subqueries
    •  

    Labs:

  • Retrieving data-Advanced topics
  • Modifying data
    •  

    Skills:
    Students will be able to:

  • Generate summary data with aggregate functions and the GROUP BY and HAVING clauses.
  • Generate online analytical processing reports using the GROUP BY WITH CUBE and
  • GROUP BY WITH ROLLUP clauses.
  • Correlate data with natural joins, outer joins, and self-joins.
  • Write subqueries.
  • Combine results sets with the UNION operator.
    •  

    Course 750, Module 7: Implementing Indexes

  • Implementing indexes
  • Types and characteristics of indexes
  • Performance considerations
    •  

    Lab:

  • Creating indexes
    •  

    Skills:
    Students will be able to:

  • Determine when indexes are useful.
  • Create indexes using the CREATE INDEX statement.
  • Describe the difference between a clustered and nonclustered index, and explain when a clustered index would be advantageous.
  • Create unique and composite indexes.
  • Describe performance considerations when using indexes.
  • Define and describe the terms UPDATE STATISTICS, FILLFACTOR, and PAD_INDEX.
  • Create clustered indexes using SORTED_DATA and SORTED_DATA_REORG.
    •  

    Self Study - Module Reviews
     

    (Source: Course 750, Modules 4-7)
    The Review page in each of the four modules
     
     

    Self Study - Course 750, Module 8: Designing Data Integrity

  • What is data integrity?
  • Using the IDENTITY property to generate values
  • Creating and implementing defaults and rules
  • Using constraints to enforce data integrity
  • When to use data integrity components
    •  

    Skills:
    Students will be able to:

  • Define how SQL Server ensures data integrity.
  • Implement the IDENTITY property.
  • Define the purpose and function of defaults and rules.
  • Create, bind, unbind, and drop defaults and rules.
  • Define the purpose and function of constraints.
  • Identify the appropriate uses for the different types of constraints.
  • Implement constraints using the ALTER TABLE statement.
    •  

    Self Study - Course 750, Module 10: Programmability

  • Batches and scripts
  • Control-of-flow language
    •  

    Skills:
    Students will be able to:

  • Describe the differences and limitations of batches and scripts.
  • Identify control-of-flow statements.
    •  

    Self Study - Course 750, Module 11: Distributed Data

  • Overview
  • SQL Server Web Assistant
    •  

    Skill:
    Students will be able to:

  • Describe how to use SQL Server Web Assistant to publish data on the World Wide Web.
    •  
     

    Course 750, Module 8: Designing Data Integrity
     

    Lab:

  • Designing data integrity
    •  

    Course 750, Module 9: Implementing Views, Triggers, and Stored Procedures

  • Views
  • Triggers
  • Stored procedures
    •  

    Lab:

  • Creating views, triggers, and stored procedures
    •  

    Skills:
    Students will be able to:

  • Explain the purpose and benefits of using views.
  • Create a view using the CREATE VIEW statement.
  • Modify data through views.
  • Define triggers and explain how they differ from stored procedures.
  • Create triggers using the CREATE TRIGGER statement.
  • List the items that triggers enforce.
  • Describe when to use nested and nonnested triggers.
  • Create a stored procedure.
  • Describe the execution plan of a stored procedure.
    •  

    Course 750, Module 10: Programmability

  • Transaction management
  • EXECUTE statement
  • Cursors
    •  

    Lab:

  • Programmability
    •  

    Skills:
    Students will be able to:

  • Describe the characteristics of transaction management.
  • Describe how distributed transactions are implemented.
  • Use the CASE statement in a SELECT, UPDATE, or INSERT statement to perform condition checking.
  • Use cursors to work through a results set.
  • Describe the benefits of using the EXECUTE statement.
  • Dynamically build a Transact-SQL statement using the EXECUTE command.
    •  

    Self Study - Module Reviews
     

    (Source: Course 750, Modules 8-11)
    The Review page in each of the four modules
     

    Self Study - Course 867, Module 9: Importing, Exporting, and Distributing Data

  • Managing data overview
  • Using the Transfer Management Interface
  • Using the Bulk Copy Program
  • Moving data using the Backup and Restore commands
  • Distributing data on removable media
    •  

    Skills:
    Students will be able to:

  • Transfer data using the Transfer Management Interface.
  • Transfer data using the Bulk Copy Program.
  • Create and manage the use of databases on removable media.
    •  
     

    Course 867, Module 7: Backing Up System and User Databases

  • Preventing data loss
  • Backup overview
  • Implementing backups
  • Using SQL Enterprise Manager
  • Obtaining information on dump devices
    •  

    Lab:

  • Backing up system and user databases
    •  

    Skills:
    Students will be able to:

  • Back up a user database and transaction log.
  • Back up the master and msdb system databases.
  • Schedule backups using SQL Enterprise Manager.
    •  

    Course 867, Module 8: Restoring System and User Databases

  • Restore overview
  • Methods of recovery
  • Recovering user databases after a media failure
  • Restoring a database and its log
  • Restoring the master database
  • Restoring the msdb database
    •  

    Lab:

  • Restoring system and user databases
    •  

    Skills:
    Students will be able to:

  • Restore a database from a backup.
  • Recover from media failure by recreating lost devices and reloading the database.
  • Use the DISK REINIT and DISK REFIT commands to recreate system data.
  • Restore a table.
    •  
     

    Course 867, Module 9: Importing, Exporting, and Distributing Data
     

    Lab:

  • Importing data
    •  

    Course 867, Module10: Scheduling Tasks and Setting Alerts

  • Task scheduling and alerts overview
  • SQL Executive
  • Managing task scheduling
  • Managing alerts
  • Using an alerts management server
    •  

    Skills:

  • Students will be able to:
  • Schedule a task.
  • Set an alert.
    •  

    Course 867, Module 11: Monitoring and Tuning

  • Goals of monitoring and tuning
  • Factors that impact performance
  • SQL Performance Monitor overview
  • Monitoring and tuning memory
  • Creating tempdb in memory
  • Monitoring locks
    •  

    Skills:
    Students will be able to:

  • Monitor server activity using SQL Enterprise Manager.
  • Monitor and tune memory usage.
  • Monitor server performance using Windows NT Performance Monitor.
  • Set SQL Server performance counters in Windows NT Performance Monitor.
  • Monitor locks.
    •  

    Self Study - Module Reviews

    (Source: Course 867, Modules 7-11)

  • The Review page in each of the five modules
    •  
     

    Course 867, Module 11: Monitoring and Tuning
     

    Lab:

  • Monitoring and tuning
    •  

    Course 867, Module 19: Setting Up and Configuring Replication

  • Distributed data overview
  • Benefits of replication
  • SQL Server 6.5 replication overview
  • Types of replication scenarios
  • Replication server event processing
  • Setting up replication
  • Using SQL Enterprise Manager
    •  

    Lab:

  • Setting up and configuring replication
    •  

    Skill:
    Students will be able to:

  • Set up and configure replication using SQL Enterprise Manager.