About This Course
Introduction
Introductions
Course Materials
Prerequisites
Course Outline
Microsoft Certified Professional Program
Facilities
Module 1:SQL Server Overview
What Is SQL Server
SQL Server Platforms
SQL Server Integration with Windows NT
SQL Server Integration with Microsoft BackOffice
SQL Server Services
SQL Server Software
SQL Server Architecture
Communication
Application Development
Administration
SQL Server Security
Login Authentication
Database User Accounts and Roles,
Permission Validation
SQL Server Databases
Types of Databases
Database Objects
Referring to SQL Server Objects
System Tables
Metadata Retrieval
Working with SQL Server
Designing an Application for SQL Server
Implementing a SQL Server Database
Administering a SQL Server Database
Lab 1.1: SQL Server Overview
Review
Module 2:Installing and Configuring SQL Server
Minimum Hardware and Software Requirements
SQL Server 7.0 Editions
SQL Server Installation Options.
Licensing Mode
Installation Path
Character Set
Sort Order
Unicode Collation
Network Support
SQL Server Services Logon Account
Running SQL Server Setup
Unattended Installation
Verifying the Installation
Reviewing Installation Results
Starting SQL Server Services
Connecting to SQL Server
Preparing to Use SQL Server
Configuring SQL Server Enterprise Manager
Configuring SQL Server
Troubleshooting
Recommended Practices
Lab 2.1: Installing and Configuring SQL Server
Review
Module 3: Managing Security
Implementing an Authentication Mode
Authentication Processing
Choosing an Authentication Mode
Steps in Implementing an Authentication Mode
Creating Login Accounts
Demonstration: Setting Up Login Accounts
Lab 3.1: Managing Security
Assigning Login Accounts to Users and Roles
Assigning Login Accounts to User Accounts
Assigning Login Accounts to Roles
Fixed Server Roles
Fixed Database Roles
User-defined Database Roles
Assigning Permissions to Users and Roles
Types of Permissions
Granting, Denying, and Revoking Permissions
Granting Permissions to Allow Access
Denying Permissions to Prevent Access
Revoking Granted and Denied Permissions
Planning Security
Demonstration: Assigning Login Accounts to
User Accounts and Roles
Lab 3.2: Managing Permissions
Managing Application Security
Managing Security with Views and Stored Procedures
Managing Client Application Security with Application Roles
Creating Application Roles
Activating Application Roles
Recommended Practices
Lab 3.3: Managing Application Security
Review
Module 4: Managing Database Files
Introduction to Databases
How Data Is Stored
Multimedia Presentation: Transactions
How the Transaction Log Works,
Creating Databases
Setting Database Options
Modifying Databases
Managing Data and Log File Growth
Expanding a Transaction Log
Shrinking a Database or File
Dropping a Database
Managing Databases on Multiple Disks
Leveraging the Windows NT Server Fault Tolerance
Creating Filegroups
Capacity Planning
Determining the Size of model Database and System Tables
Estimating the Amount of Data in Tables
Performance Considerations
Recommended Practices
Lab 4.1: Managing Database Files
Review
Module 5: Backing Up Databases
Preventing Data Loss
SQL Server Backup
Performing and Storing Backups
When to Back Up Databases
Backing Up System Databases
Backing Up User Databases
Activities That Are Restricted During Backup
Performing Backups
Creating Permanent Backup Files
Creating Temporary Backup Files
Using Multiple Backup Files to Store Backups
Using the BACKUP Statement
Backing Up to a Tape Device
Specifying Tape Options.
Types of Backup Methods
Performing a Full Database Backup
Performing a Differential Backup
Performing a Transaction Log Backup
Using the NO_TRUNCATE Option
Clearing the Transaction Log
Performing a Database File or Filegroup Backup
Restrictions on Backing Up Database Files or Filegroups
Demonstration: Using SQL Server Enterprise Manager to
Perform Backups
Planning a Backup Strategy
Full Database Backup Strategy
Full Database and Transaction Log Backup Strategy
Differential Backup Strategy
Database File or Filegroup Backup Strategy
Performance Considerations
Recommended Practices
Lab 5.1: Backing Up Databases
Review
Module 6: Restoring Databases
SQL Server Recovery Process
SQL Server Activities During the Restore Process
Preparing to Restore a Database
Verifying Backups
Performing Specific Tasks Before Restoring Backups
Restoring Backups
Using the RESTORE Statement
Initiating the Recovery Process
Specifying Restore Options
Restoring Databases from Different Backup Types
Restoring from a Full Database Backup
Restoring from a Differential Backup
Restoring a Transaction Log Backup
Specifying a Point in Time
Restoring from a File or Filegroup Backup
Using a Standby SQL Server
Setting Up a Standby SQL Server
Using the Standby SQL Server as a Read-Only Server
Using the STANDBY Option
Replacing a Production Server with a Standby SQL Server
Restoring Damaged System Databases
Recommended Practices
Lab 6.1: Restoring Databases
Review,
Module 7: Automating Administrative Tasks
Reasons to Automate
Introduction to SQL Server Automation
Multimedia Presentation: Automating SQL Server Administration
Writing Events to the Application Log
Preparing to Automate
Configuring Mail
Automating Routine Maintenance Tasks
Creating Jobs
Verifying Permissions
Defining Job Steps
Determining Action Flow Logic for Each Job Step
Scheduling Jobs
Creating Operators to Notify
Reviewing and Configuring Job History
Lab 7.1: Creating Jobs and Operators
Creating Alerts
Using Alerts to Respond to Potential Problems
Creating Alerts to Respond to SQL Server Errors
Creating Alerts on a User-defined Error
Responding to Performance Condition Alerts
Assigning a Fail-Safe Operator
Troubleshooting SQL Server AutomationTroubleshooting Alerts
Lab 7.2: Creating Alerts
Automating Multiserver Jobs
Defining Multiserver Jobs
Demonstration: Creating a Master Job
Publishing SQL Server Data on the Web
Creating a Web Page
Formatting the Web Page
Generating the Web Page
Managing Web Assistant Jobs
Recommended Practices
Lab 7.3: Publishing SQL Server Data on the Web
Review
Module 8: Transferring Data
Introduction to Importing and Exporting Data
Why to Import and Export Data
Why to Transform Data
Tools for Importing and Exporting Data in SQL Server
Introduction to Data Transformation Services
DTS Overview
DTS Process
DTS Tools
Transforming Data with DTS
Creating a DTS Package
Transforming and Mapping Data
Defining Transformation Tasks
Defining Workflows
Defining Data Lineage
Executing and Scheduling a DTS Package
Demonstration: Defining a DTS Package
Recommended Practices
Lab 8.1: Transferring Data
Review
Module 9: Monitoring and Maintaining SQL Server
Why Monitor SQL Server
Reasons to Monitor SQL Server
Factors That Affect Performance
Detecting Performance Bottlenecks
Tools for Monitoring SQL Server
Common Monitoring Tasks
Using the Microsoft Event Viewer
Using SQL Server Performance Monitor
Using Current Activity in SQL Server Enterprise Manager
Using Transact-SQL to Monitor SQL Server
Using SQL Server Profiler
Generating a Query History
Demonstration: Using SQL Server Profiler
Using SQL Server Query Analyzer
Creating a Maintenance Plan for SQL Server
Developing a Database Maintenance Plan
Automating the Database Maintenance Plan Tasks
Recommended Practices
Lab 9.1: Monitoring SQL Server
Review
Module lO:Introducing Replication
Introduction to Distributed Data
The Need for Distributed Data
Considerations for Distributing Data
Methods to Distribute Data
Introduction to SQL Server Replication
The Publisher-Subscriber Metaphor
Publications and Articles
Filtering Data
Subscriptions
SQL Server Replication Agents
SQL Server Replication Types
Overview of the Replication Types
Multimedia Presentation: SQL Server Replication
Considerations for Using Merge Replication
Physical Replication Models
Overview of the Replication Models
Combining Replication Models and Types
Central Publisher/Remote Distributor Example
Central Subscriber/Multiple Publishers Example
Multiple Publishers/Multiple Subscribers Example
Recommended Practices
Review
Module 11 :Planning and Setting Up Replication
Planning Replication
Design Considerations
Determining a Replication Solution
Data Definition Issues
Configuring Network Security Access
Preparing the Servers
Setting Up a Distributor
Configuring a Distributor
Setting Up a Publisher
Setting Up a Subscriber
Publishing
Creating Publications
Publishing Considerations
Initial Synchronization
Subscribing
Setting Up Push and Pull Subscriptions,
Using the Immediate Updating Subscribers Option
Performance Considerations
Recommended Practices
Demonstration: Implementing Replication
Lab 11.1: Implementing Replication
Review
Module 12:Managing Replication
Monitoring and Troubleshooting Replication
Using SQL Server Replication Monitor
Maintaining Replication
Using Replication Scripts
Monitoring SQL Server Replication Performance
Viewing Replication Agent Histories
Troubleshooting Replication
Replicating in Heterogeneous Environments
Replicating Data with ODBC
Publishing Data to Heterogeneous Subscribers
Replicating from Heterogeneous Databases
Publishing on the Intemet
Recommended Practices
Lab 12.1: Maintaining Replication
Review
Appendix A: Database Schemas
Lab 1.1 :SQL Server Overview
Objectives
Exercise 1 Using SQL Server Books Online
Lab 2.1: Installing and Configuring SQL Server
Objectives
Before You Begin
Exercise 1 Preparing to Install SQL Server
Exercise 2 Installing SQL Server
Exercise 3 Verifying the Installation
Exercise 4 Configuring SQL Server Agent and SQL Server
Enterprise Manager
Exercise 5 Creating Shared Registration Information
Exercise 6 Troubleshooting Installation Problems
Exercise 7 Creating a Database Diagram
Lab 3.1: Managing Security
Objectives
Before You Begin
Exercise 1 Configuring SQL Server Authentication Mode
Exercise 2 Authorizing Users to Access SQL Server
Exercise 3 Revoking Access from, and Denying
Access to, Windows NT Users or Groups
Exercise 4 Registering Your Partner's SQL Server
Lab 3.2:Managing Permissions
Objectives
Before You Begin
Exercise 1 Creating Database Roles
Exercise 2 Assigning Statement Permissions
Exercise 3 Assigning Object Permissions
Lab 3.3:Managing Application Security
Objectives
Before You Begin
Exercise 1 Creating and Activating an Application Role
Exercise 2 Implementing Permissions by Using Views and
Stored Procedures
Lab 4.1: Managing Database Files
Objectives
Before You Begin
Exercise 1 Using the Create Database Wizard
Exercise 2 Creating a Database
Exercise 3 Modifying a Database
Exercise 4 Viewing and Changing Database Options by Using
Transact-SQL Statements
Exercise 5 Deleting a Database
Lab 5.1: Backing Up Databases
Objectives
Before You Begin
Exercise 1 Creating Permanent Backup Files
Exercise 2 Backing Up Databases
Exercise 3 Backing Up a Tramaction Log
Exercise 4 Performing a Differential Backup
Lab 6.1: Restoring Databases
Objectives
Before You Begin
Exercise 1 Restoring from a Full Database Backup
Exercise 2 Simulating and Capturing Database Activity
Exercise 3 Restoring Full Database, Differential, and Transaction
Log Backups
Lab 7.1 :Creating Jobs and Operators
Objectives
Before You Begin
Exercise 1 Configuring SQL Server Agent to Send Messages
Exercise 2 Creating Operators
Exercise 3 Using the Create Job Wizard
Exercise 4 Creating a Job with Multiple Steps,
If Time Permits Using SQL Mail
Lab 7.2:Creating Alerts
Objectives
Before You Begin
Exercise 1 Using the Create Alert Wizard
Exercise 2 Creating an Alert on a User-defined Error Message
Exercise 3 Creating Performance Condition Alerts
If Time Permits Assigning a Fail-Safe Operator
Lab 7.3: Publishing SQL Server Data on the Web
Objectives
Before You Begin
Exercise 1 Creating a Web Page with the Web Assistant Wizard
Exercise 2 Updating a Web Page Automatically
When Data Changes
If Time Permits Creating a Web Page with a Template File and
Transact-SQL
Lab 8.1: Transferring Data
Objectives
Before You Begin
Exercise 1 Importing Data from a Query
Exercise 2 Modifying a DTS Package Using DTS Designer,
Exercise 3 Transferring SQL Server 7.0 Objects
If Time Permits Exporting Data to a Text File
Importing Data Using the Bulk Insert Task
Lab 9.1: Monitoring SQL Server
Objectives
Before You Begin
Exercise 1 Using Windows NT Performance Monitor
Exercise 2 Using SQL Server Profiler
Exercise 3 Using SQL Server Query Analyzer
Exercise 4 Monitoring Locking Information
Exercise 5 Creating a Database Maintenance Plan
If Time Permits Creaiing a Query History trace file
Lab 10.1:Implementing Replication
Objectives
Before You Begin
Exercise 1 Logging in to and Registering a Server
Exercise 2 Configuring Transactional Replication
Exercise 3 Creating a Merge Publication
Exercise 4 Creating a Pull Subscription and Resolving Conflicts
Lab 11.1:Maintaining Replication
Objectives
Before You Begin
Exercise 1 Generating a Replication Script,
Exercise 2 Modifying Replication Maintenance Properties
Classroom Setup Guide
Classroom Requirements
Classroom Corrfignration
Setup Instructions
鄂公网安备 42010302001612号 