Course Number: 832B



The names of companies, products, people, characters, and/or data mentioned herein are fictitious
and are in no way intended to represent any real individual, company, product, or event, unless
otherwise noted.

Other product and company names mentioned herein may be the trademarks of their respective
owners.
Project Leads: Lori Oviatt, Margo Crandall
Instructional Designers: Homer Christensen (Working Knowledge),
Xandria Eykel (S&T OnSite), Cheryl Hoople (Write Stuff), Marilyn McGill
Lead Program Manager: Karal Dehmer
Program Managers: Dan Basica, Adam Shapiro, Stacey Dickinson (Solutions View),
Carl Rabeler (Shadow Mountain Computers)
Graphic Artist: Kimberly Jackson
Editing Manager: Tina Tsiakalis
Editor: Wendy Cleary (S&T OnSite)
Online Program Manager: Nikki McCormick
Online Support: Arlo Emerson (MacTemps), Tammy Stockton (Write Stuff)
Compact Disc Testing: ST Labs
Production Support: Kathy Ford (S&T OnSite)
Manaufacturing Manager: Bo Galford
Manufacturing Support: Rick Terek (S&T OnSite)
Lead Product Manager, Development Services: Elaine Stovall
Lead Product Manager: Heidi Tucker
Group Product Manager: Robert Stewart
SQL Server Databases................................................................................33
Types of Databases................................................................................34
Database Objects...................................................................................35
Referring to SQL Server Objects .............................................................36
System Tables.......................................................................................38
Metadata Retrieval.................................................................................39
Working with SQL Server...........................................................................41
Designing an Application for SQL Server.................................................42
Implementing a SQL Server Database......................................................44
Administering a SQL Server Database......................................................45
Lab 1.1: SQL Server Overview....................................................................46
Review .....................................................................................................47
Module 2: Installing and Configuring SQL Server
Overview ..................................................................................................51
Minimum Hardware and Software Requirements ...........................................52
SQL Server 7.0 Editions .........................................................................53
SQL Server Installation Options...................................................................54
Licensing Mode.....................................................................................55
Installation Path.....................................................................................56
Character Set........................................................................................57
Sort Order ............................................................................................59
Unicode Collation..................................................................................61
Network Support...................................................................................62
iv Contents

SQL Server Services Logon Account.......................................................64
Running SQL Server Setup.........................................................................66
Unattended Installation ..........................................................................68
Verifying the Installation ............................................................................70
Reviewing Installation Results................................................................71

Planning Security..................................................................................... 116
Demonstration: Assigning Login Accounts to User Accounts and Roles ......... 118
Lab 3.2: Managing Permissions ................................................................. 119
Managing Application Security ................................................................. 120
Managing Security with Views and Stored Procedures ............................. 121
Managing Client Application Security with Application Roles .................. 123
Creating Application Roles................................................................... 124
Activating Application Roles ................................................................ 125
Recommended Practices........................................................................... 127
Lab 3.3: Managing Application Security..................................................... 128
Review................................................................................................... 129
Contents v Module 4: Managing Database Files
Overview ................................................................................................133
Introduction to Databases..........................................................................134
How Data Is Stored..............................................................................135
Multimedia Presentation: Transactions........................................................136
How the Transaction Log Works ...........................................................137
Creating Databases...................................................................................138
Setting Database Options......................................................................140
Modifying Databases................................................................................142
Managing Data and Log File Growth.....................................................143
Expanding a Transaction Log................................................................145
Shrinking a Database or File .................................................................147
Dropping a Database............................................................................149
Managing Databases on Multiple Disks ......................................................151
Leveraging the Windows NT Server Fault Tolerance...............................152
Creating Filegroups .............................................................................154

vi Contents

Restrictions on Backing Up Database Files or Filegroups ......................... 201
Demonstration: Using SQL Server Enterprise Manager to
Perform Backups................................................................................. 202
Planning a Backup Strategy....................................................................... 203
Full Database Backup Strategy.............................................................. 204
Full Database and Transaction Log Backup Strategy................................ 207
Differential Backup Strategy................................................................. 209
Database File or Filegroup Backup Strategy............................................ 211
Performance Considerations...................................................................... 213
Recommended Practices........................................................................... 214
Lab 5.1: Backing Up Databases ................................................................. 215
Review................................................................................................... 216
Module 6: Restoring Databases
Overview................................................................................................ 221
SQL Server Recovery Process................................................................... 222
SQL Server Activities During the Restore Process................................... 223
Preparing to Restore a Database................................................................. 225
Verifying Backups............................................................................... 226
Performing Specific Tasks Before Restoring Backups.............................. 228
Restoring Backups................................................................................... 229
Using the RESTORE Statement ............................................................ 230
Initiating the Recovery Process ............................................................. 231
Specifying Restore Options .................................................................. 232
Restoring Databases from Different Backup Types....................................... 233
Restoring from a Full Database Backup.................................................. 234
Restoring from a Differential Backup..................................................... 236
Restoring a Transaction Log Backup...................................................... 237
Specifying a Point in Time ................................................................... 239

Creating Alerts ........................................................................................283
Using Alerts to Respond to Potential Problems........................................284
Creating Alerts to Respond to SQL Server Errors....................................285
Creating Alerts on a User-defined Error..................................................287
Responding to Performance Condition Alerts..........................................289
Assigning a Fail-Safe Operator..............................................................290
Troubleshooting SQL Server Automation....................................................291
Troubleshooting Alerts.........................................................................293
Lab 7.2: Creating Alerts............................................................................295
Automating Multiserver Jobs.....................................................................296
Defining Multiserver Jobs.....................................................................298
Demonstration: Creating a Master Job....................................................299
Publishing SQL Server Data on the Web.....................................................300
Creating a Web Page............................................................................301
Formatting the Web Page .....................................................................303
Generating the Web Page .....................................................................304
Managing Web Assistant Jobs...............................................................306
Recommended Practices ...........................................................................308
Lab 7.3: Publishing SQL Server Data on the Web........................................309
Review ...................................................................................................310
Module 8: Transferring Data
Overview ................................................................................................305
Introduction to Importing and Exporting Data..............................................306
Why to Import and Export Data.............................................................307
Why to Transform Data........................................................................308
Tools for Importing and Exporting Data in SQL Server.................................310
Introduction to Data Transformation Services..............................................312
DTS Overview....................................................................................313
DTS Process.......................................................................................314
DTS Tools..........................................................................................315

Developing a Database Maintenance Plan............................................... 361
Automating the Database Maintenance Plan Tasks .................................. 363
Recommended Practices........................................................................... 365
Lab 9.1: Monitoring SQL Server................................................................ 366
Review................................................................................................... 367
Module 10: Introducing Replication
Overview................................................................................................ 367
Introduction to Distributed Data................................................................. 368
The Need for Distributed Data .............................................................. 369
Considerations for Distributing Data...................................................... 370
Methods to Distribute Data................................................................... 371
Introduction to SQL Server Replication ...................................................... 373
The Publisher-Subscriber Metaphor....................................................... 374
Publications and Articles ...................................................................... 375
Filtering Data...................................................................................... 376
Subscriptions ...................................................................................... 377
SQL Server Replication Agents ................................................................. 378
SQL Server Replication Types................................................................... 379
Overview of the Replication Types........................................................ 380
Multimedia Presentation: SQL Server Replication........................................ 382
Considerations for Using Merge Replication........................................... 383
Physical Replication Models ..................................................................... 385
Overview of the Replication Models ...................................................... 386
Combining Replication Models and Types.............................................. 387
Central Publisher/Remote Distributor Example ....................................... 388
Central Subscriber/Multiple Publishers Example ..................................... 389
Multiple Publishers/Multiple Subscribers Example .................................. 390
Recommended Practices........................................................................... 391
Review................................................................................................... 392
Contents ix

Maintaining Replication .......................................................................434
Using Replication Scripts.....................................................................436
Monitoring SQL Server Replication Performance....................................437
Viewing Replication Agent Histories .....................................................439
Troubleshooting Replication .................................................................440
Replicating in Heterogeneous Environments................................................442
Replicating Data with ODBC ................................................................443
Publishing Data to Heterogeneous Subscribers........................................444
Replicating from Heterogeneous Databases.............................................445
Publishing on the Internet..........................................................................446
Recommended Practices ...........................................................................447
Lab 12.1: Maintaining Replication .............................................................448
Review ...................................................................................................449
Appendix A: Database Schemas
Contents x

THIS PAGE INTENTIONALLY LEFT BLANK
Introduction

This page left intentionally blank
Introduction 3

Ask students to introduce
themselves, addressing the
bulleted items on the slide.
Delivery Tip
As students introduce
themselves, use a white
board or flip chart to
record their expectations
of the course.
4 Introduction
Course Materials
? Name Card
? Student Workbook
? Lab Manual
? Student Materials Compact Disc
? Course EvaluationThe following materials are included with your kit:
?? Name card. Write your name on both sides of the name card.
?? Student workbook. The student workbook contains the material covered
in the course.
?? Lab manual. The lab manual contains the hands-on lab exercises used
during class.
?? Student Materials compact disc. The Student Materials compact disc
contains the Web page that provides students with links to resources
pertaining to this course, including additional readings, review and lab

name card.

Tell students that a
course evaluation must
be completed at the end
of the course.

Tell students where they
can send comments.
Delivery Tip
Demonstrate how to
open the Student Materials
Web page provided on
the Student Materials
compact disc.
Note
Introduction 5
Prerequisites
? The Supporting Microsoft Windows NT Server 4.0
Course or Equivalent Knowledge
? An Understanding of Basic Relational
Database Concepts
? Knowledge of Basic Transact-SQL Syntax
(SELECT, UPDATE, and INSERT Statements)
? Familiarity with the Role of the Database Administrator
knowledge is needed for
this course.
6 Introduction
Course Outline
? Module 1: SQL Server Overview
? Module 2: Installing and Configuring SQL Server
? Module 3: Managing Security
? Module 4: Managing Database Files
? Module 5: Backing Up Databases
? Module 6: Restoring DatabasesModule 1, “SQL Server Overview,” provides a high-level overview of
Microsoft SQL Server
™
version 7.0 platforms, architecture, and components. At
the end of this module, you will be able to describe the elements of SQL Server
and the environments in which it can operate.
Module 2, “Installing and Configuring SQL Server,” reviews the hardware and
software requirements of SQL Server and the installation options. At the end of
this module, you will be able to run SQL Server Setup, verify the installation,
and configure SQL Server.
Module 3, “Managing Security,” introduces you to the subject of security. It
describes how to set up an authentication mode for a server and how to assign
logins and permissions to users and roles. At the end of this module, you will be
able to describe how to plan security, manage security with views and stored
procedures, and use application roles to manage application security.

Course Outline (continued)
? Module 7: Automating Administrative Tasks
? Module 8: Transferring Data
? Module 9: Monitoring and Maintaining SQL Server
? Module 10: Introducing Replication
? Module 11: Planning and Setting Up Replication
? Module 12: Managing ReplicationModule 7, “Automating Administrative Tasks,” discusses how to automate
administrative tasks using SQL Server Agent. At the end of this module, you
will be able to use e-mail to notify operators and create jobs.
Module 8, “Transferring Data,” describes the process of transferring data into
and out of SQL Server. At the end of this module, you will be able to use Data
Transformation Services (DTS) to import, export, and transform schema and
data and to transfer SQL Server databases.
Module 9, “Monitoring and Maintaining SQL Server” discusses methods used
to monitor and maintain SQL Server activity to determine performance issues.
At the end of this module, you will be able to use various SQL Server
monitoring tools, analyze queries, and examine server activity.
Module 10, “Introducing Replication,” describes how SQL Server replication
distributes data, the various types of replication, and models for implementing
replication. At the end of this module, you will be able to describe the basic
concepts of replication and how to implement it in various business
environments.
Module 11, “Planning and Setting Up Replication,” describes the steps and
choices in planning and configuring replication. At the end of this module, you

MCSD
MCSD
2 Core Exams
2 Core Exams
1 Elective Exam
1 Elective Exam
MCP + Site Building
MCP + Site Building
2 Core Exams
2 Core ExamsMCP + Internet
MCP + Internet
3 Core Exams
3 Core ExamsMCP
MCP
1 Core Exam
1 Core ExamMCT
MCT
Instructional Skills
Course Preparedness
Technical Skill Qualification
Instructional Skills

(MCP + Site Building)
MCPs with a specialty in site building are qualified to plan, build, maintain,
and manage Web sites using Microsoft technologies and products.
Microsoft Certified Professional
+ Internet
(MCP + Internet)
MCPs with a specialty in the Internet are qualified to plan security, install and
configure server products, manage server resources, extend servers to run
Common Gateway Interface (CGI) scripts or Internet Server Application
Programming Interface (ISAPI) scripts, monitor and analyze performance, and
troubleshoot problems.
Microsoft Certified Professional
(MCP)
MCPs demonstrate in-depth knowledge of at least one product by passing any
one exam (except Networking Essentials).
Microsoft Certified Trainer
(MCT)
MCTs demonstrate the instructional and technical skills that qualify them to
deliver Microsoft Official Curriculum through Microsoft Certified Technical
Education Centers (Microsoft CTEC).

Slide Objective
To provide students with
information about the
Microsoft Certified
Professional Program.
Lead-in
The Microsoft Certified
Professional Program
includes these

You can also send e-mail to [email protected] if you have specific
certification questions.

Exam Preparation Guides
To help prepare for the MCP exams, you can use the preparation guides that are
available for each exam. Each Exam Preparation Guide contains exam-specific
information, such as a list of the topics on which you will be tested and
information about credit toward certification. These guides are available on the
Training and Certification Web site, located at the following address:
http://www.microsoft.com/mcp/

Microsoft Official Curriculum (MOC) helps you to prepare for
Microsoft Certified Professional (MCP) exams. However, no one-to-one
correlation exists between MOC courses and MCP exams. 1
Inside the United States and Canada call (800) 636-7544 for more information on becoming a
Microsoft Certified Trainer. Outside the United States and Canada, contact your local Microsoft
subsidiary.
For More Information
Important
10 Introduction
Facilities
Building Hours
Parking
Rest Rooms

Module 1: SQL Server Overview
This page is blank

Module 1: SQL Server Overview 13
Overview
? What Is SQL Server
? SQL Server Architecture
? SQL Server Security
? SQL Server Databases
? Working with SQL ServerObjectives
At the end of this module, you will be able to:
?? Describe Microsoft® SQL Server
™
version 7.0 and its supported operating
system platforms.
?? Describe SQL Server architecture.
?? Describe SQL Server security.
?? Describe SQL Server databases.
?? Describe SQL Server application design options, as well as SQL Server
implementation and administration activities.


SQL Server uses client/server architecture to separate the workload into tasks
that run on server computers and those that run on client computers:
?? The client is responsible for business logic and presenting data to the user.
The client typically runs on one or more client computers, but it also can run
on the server computer with SQL Server.
?? SQL Server manages databases and allocates the available server
resources—such as memory, network bandwidth, and disk operations—
among multiple requests.

Client/server architecture allows you to design and deploy applications to
enhance a variety of environments. Client programming interfaces provide the
means for applications to run on separate client computers and communicate to
the server over a network.

In this course, the term client by itself refers to a client application.

Relational Database Management System
The RDBMS is responsible for:?
?? Maintaining the relationships between data in the database.
?? Ensuring that data is stored correctly—that the rules defining the
relationships between data are not violated.
?? Recovering all data to a point of known consistency in the event of a
system failure.

Slide Objective
To introduce SQL Server.
Lead-in
SQL Server is a
client/server relational
database management