View Larger Image	Microsoft Corp. Microsoft Press, Hardcover, 2nd Bk&CD edition, Published May 2003, 963 pages, ISBN 0735619611
	List Price: $59.99 Our Price: $37.50 You Save: $22.49 (37% Off)
	Availability: Out-Of-Stock

Read an Excerpt: Chapter 10: Managing Access to SQL Server 2000       Excerpt provided courtesy of Microsoft Press.

Write a Review and tell the world about this title!

People who purchase this book frequently purchase:

• MCAD/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 Database Design and Implementation, Exam 70-229, 2nd Edition; Microsoft Corp., $37.50, 37% Off!
• MCSA/MCSE Self-Paced Training Kit: Installing, Configuring, and Administering Microsoft Windows XP Professional, 2nd Edition (Exam 70-270); Walter Glenn, et al, $37.50, 37% Off!
• MCSA/MCSE Self-Paced Training Kit: Implementing and Managing Microsoft Exchange Server 2003, Exam 70-284; Will Willis, et al, $37.50, 37% Off!
• MCSE Self-Paced Training Kit: Designing Security for a Microsoft Windows Server 2003 Network (Exam 70-298); Roberta Bragg, $37.50, 37% Off!

• Certification : MCDBA
• Certification : MCSA
• Certification : MCSE
• Databases : Microsoft SQL Server
• Databases : SQL
• * Series - Exam Study : Self-Paced Training Kit

• Microsoft Press

Learn how to deliver exceptional performance and scalability to your business information systems-and prepare for MCP Exam 70-228-with this official Microsoft study guide. Exam 70-228 is an elective for MCSE/MCSA certification and a core exam on the MCDBA track. Build your expertise installing, configuring, and troubleshooting SQL Server 2000 by working at your own pace through the book’s lessons and hands-on exercises. The second edition of this popular Training Kit now features a testing tool to measure what you know and where to focus your studies-before taking the actual exam. Topics include installing or upgrading to SQL Server 2000, configuring database options, automating data transfer and manipulation with Data Transformation Services (DTS) packages, using SQL Server replication services, managing security features, monitoring and fine-tuning system performance, and performing backups and restorations. You also get a CD-ROM loaded with practice exercises and an easy-search electronic version of the study guide

• Official Microsoft study guide helps you build the skills and expertise for managing SQL Server databases.
• Provides in-depth preparation for MCP Exam 70-228, "Installing, Configuring, and Administering Microsoft SQL Server 2000 Enterprise Edition", a core credit for MCDBA certification and an elective for the MCSA and MCSE tracks.
• This Training Kit delivers self-paced training that maps to MCP exam goals and objectives, a testing tool that generates timed practice exams with automated scoring, and an electronic version of the study guide.

About the Technology:

Business today demands a different kind of data management solution. Performance, scalability, and reliability are essential, but businesses now expect more from their key IT investments.

SQL Server 2000 exceeds dependability requirements and provides innovative capabilities that increase employee effectiveness, integrate heterogeneous IT ecosystems, and maximize capital and operating budgets. SQL Server 2000 provides the enterprise data management platform your organization needs to adapt quickly in a fast-changing environment.

With the lowest implementation and maintenance costs in the industry, SQL Server 2000 delivers rapid return on your data management investment. SQL Server 2000 supports the rapid development of enterprise-class business applications that can give your company a critical competitive advantage.

Benchmarked for scalability, speed, and performance, SQL Server 2000 is a fully enterprise-class database product, providing core support for Extensible Markup Language (XML) and Internet queries.

Table of Contents

About This Book	xxvii
PART 1: SELF-PACED TRAINING FOR MICROSOFT SQL SERVER 2000 SYSTEM ADMINISTRATION
CHAPTER 1: Overview of SQL Server 2000	3
About This Chapter	3
Lesson 1: What Is SQL Server 2000?	4
The SQL Server 2000 Environment	4
SQL Server 2000 Components	5
SQL Server 2000 Relational Database Engine	6
SQL Server 2000 Analysis Services	6
Application Support	7
Additional Components	8
SQL Server 2000 Data Transformation Services (DTS)	8
SQL Server 2000 Replication	8
SQL Server 2000 English Query	8
Meta Data Services	9
SQL Server 2000 Editions	9
SQL Server 2000 Enterprise Edition	9
SQL Server 2000 Standard Edition	9
SQL Server 2000 Personal Edition	10
SQL Server 2000 Windows CE Edition	10
SQL Server 2000 Developer Edition	11
SQL Server 2000 Enterprise Evaluation Edition	11
SQL Server 2000 Desktop Engine	11
Installing SQL Server Editions on Various Windows Operating Systems	12
Integration with Windows 2000 and Windows NT 4.0	12
Windows Authentication	13
Memory Management	13
Active Directory	13
Failover Clustering	14
Microsoft Distributed Transaction Coordinator	14
SMP	14
Asynchronous and Scatter-gather I/O	14
Event Logs	14
System Monitor Counters	14
Lesson Summary	15
Lesson 2: What Are the SQL Server 2000 Components?	16
Server Components	16
Client-Based Administration Tools and Utilities	17
Graphical Tools	17
Command-Prompt Utilities	18
Client Communication Components	18
Relational Database Application Programming Interfaces	19
Net-Libraries	19
Open Data Services	20
Client–Server Communication	20
Internet Applications	21
SQL Server Books Online	22
Lesson Summary	22
Lesson 3: What Is the Relational Database Architecture?	23
System and User Databases	23
Physical Structure of a Database	24
Extents and Pages	24
Transaction Log Files	24
Logical Structure of a Database	25
Optimizing Logical Database Design	26
Lesson Summary	26
Lesson 4: What Is the Security Architecture?	27
Authentication	27
Windows Authentication	27
SQL Server Authentication	28
Authentication Modes	28
Authorization	28
User Accounts	29
Guest User Account	29
Roles	29
Lesson Summary	30
Review	31
CHAPTER 2: Installing SQL Server 2000	33
About This Chapter	33
Before You Begin	33
Lesson 1: Planning to Install SQL Server 2000	35
What Is the Minimum Hardware Required?	35
Exceeding Minimum Computer Hardware Requirements	37
Lesson Summary	39
Lesson 2: Deciding SQL Server 2000 Setup Configuration Options	40
Determining the Appropriate User Account for the SQL Server and SQL Server Agent Services	40
Practice: Creating a Dedicated Windows 2000 User Account	41
Choosing an Authentication Mode	42
Determining Collation Settings	42
Practice: Determining Your Windows Collation	44
Selecting Network Libraries	45
Deciding on a Client Licensing Mode	46
Lesson Summary	46
Lesson 3: Running the SQL Server 2000 Setup Program	47
Running the SQL Server 2000 Setup Program	47
Understanding Installation Types	48
Selecting a Setup Type	50
Practice: Installing a Default Instance of SQL Server 2000	56
Lesson Summary	57
Lesson 4: Using Default, Named, and Multiple Instances of SQL Server 2000	58
Installing Multiple Instances of SQL Server 2000	58
Using Multiple Instances of SQL Server 2000 Effectively and Appropriately	59
Understanding Shared Components Between Instances	60
Understanding Unique Components Between Instances	60
Working with Default and Named Instances of SQL Server 2000	61
Lesson Summary	61
Lesson 5: Performing Unattended and Remote Installations of SQL Server 2000	62
Performing an Unattended Installation of SQL Server 2000	62
Creating Setup Initialization Files for SQL Server 2000	62
Practice: Performing an Unattended Installation of a Named Instance of SQL Server 2000	64
Performing a Remote Installation of SQL Server 2000	65
Lesson Summary	67
Lesson 6: Troubleshooting a SQL Server 2000 Installation	68
Reviewing SQL Server 2000 Setup Program Log Files	68
Accessing SQL Server 2000 Troubleshooting Information Online	69
Reviewing the SQL Server Error Log and the Windows Application Log	69
Practice: Reviewing the SQL Server Error Log and the Windows Application Log	70
Lesson Summary	70
Review	71
CHAPTER 3: Preparing to Use SQL Server 2000	73
About This Chapter	73
Before You Begin	73
Lesson 1: Reviewing the Results of Installation	74
What Files and Folders Were Added?	74
What Permissions Were Set in the NTFS File System	77
Practice: Reviewing the Files and Folders That Were Created	78
What Registry Keys Were Added	80
What Permissions Were Set on Registry Keys	81
Practice: Reviewing Permissions on Registry Keys	83
What Programs Were Added to the Start Menu	84
Lesson Summary	84
Lesson 2: Starting, Stopping, Pausing, and Modifying SQL Server 2000 Services	85
What Is the Default Configuration for Each SQL Server Service?	85
Starting, Stopping, and Pausing SQL Server 2000 Services	86
Practice: Starting SQL Server Services	89
Changing the SQL Server or SQL Server Agent Service Account After Setup	90
Lesson Summary	92
Lesson 3: Working with Osql, SQL Query Analyzer, and SQL Server Enterprise Manager	93
Working with Osql	93
Practice: Using Osql to Query SQL Server 2000 Instances	94
Working with SQL Query Analyzer	96
Practice: Using SQL Query Analyzer to Query SQL Server 2000 Instances	98
Working with SQL Server Enterprise Manager	100
Practice: Working with the SQL Server Enterprise Manager MMC Console	102
Lesson Summary	104
Review	105
CHAPTER 4: Upgrading to SQL Server 2000	107
About This Chapter	107
Before You Begin	107
Lesson 1: Preparing to Upgrade	108
Working with Multiple Versions of SQL Server on the Same Computer	108
Version Switching	108
Named Instances	109
Choosing the Appropriate Upgrade Process and Method	109
SQL Server 6.5	109
SQL Server 7.0	110
Replication Issues	111
Determining Hardware and Software Requirements	111
SQL Server 6.5	111
SQL Server 7.0	112
Preparing for the Actual Upgrade	112
Lesson Summary	113
Lesson 2: Performing a Version Upgrade from SQL Server 7.0	114
Performing a Version Upgrade	114
Performing Post-Upgrade Tasks	115
Manually Upgrading Meta Data Services Tables and the Repository Database	116
Lesson Summary	116
Lesson 3: Performing an Online Database Upgrade from SQL Server 7.0	117
Performing an Online Database Upgrade	117
Performing Post-Upgrade Tasks	123
Lesson Summary	123
Lesson 4: Performing a Version Upgrade from SQL Server 6.5	124
Performing a Version Upgrade	124
Troubleshooting a SQL Server 6.5 Upgrade	131
Specifying a Backward Compatibility Level for Upgraded Databases	132
Lesson Summary	132
Review	133
CHAPTER 5: Understanding System and User Databases	135
About This Chapter	135
Before You Begin	135
Lesson 1: Understanding the Database Architecture	136
Introducing Data Files	136
Practice: Viewing the Properties of a Data File	137
Allocating Space for Tables and Indexes	138
Storing Index and Data Pages	139
Lesson Summary	141
Lesson 2: Understanding the Transaction Log Architecture	142
Introducing Transaction Log Files	142
How the Transaction Log Works	143
Checkpoint Process	144
Operating System Threads	145
Introducing Recovery Models	146
Full Recovery Model	146
Bulk-Logged Recovery Model	146
Simple Recovery Model	147
Practice: Viewing the Properties of a Transaction Log and a Database	147
Lesson Summary	148
Lesson 3: Understanding and Querying System and Database Catalogs	149
Introducing System Tables	149
System Catalog	149
Database Catalog	150
Retrieving System Information	151
Practice: Querying System Tables Directly	151
System-Stored Procedures	152
Practice: Querying System Tables Using System-Stored Procedures	152
System Functions	154
Practice: Querying System Tables Using System Functions	155
Information Schema Views	155
Practice: Querying System Tables Using Information Schema Views	156
Lesson Summary	157
Review	158
CHAPTER 6: Creating and Configuring User Databases	159
About This Chapter	159
Before You Begin	160
Lesson 1: Creating a User Database	161
Creating a User Database	161
Using SQL Server Enterprise Manager to Create a User Database	163
Using the Create Database Wizard	164
Practice: Creating a Database Using the Create Database Wizard in SQL Server Enterprise Manager	167
Using SQL Server Enterprise Manager Directly	169
Practice: Creating a Database Directly Using SQL Server Enterprise Manager	171
Using the CREATE DATABASE Transact-SQL Statement to Create a User Database	172
Creating a Simple Database Using Transact-SQL	172
Creating a Multiple File Database Using Transact-SQL	172
Practice: Creating a Database in SQL Query Analyzer Using the CREATE DATABASE Transact-SQL Statement	173
Scripting Databases and Database Objects Using SQL Server Enterprise Manager	174
Practice: Generating a Transact-SQL Script to Re-create the TSQLDB Database	176
Lesson Summary	177
Lesson 2: Setting Database Options	178
Introducing Database Options	178
Viewing Database Option Settings	179
Modifying Database Options	179
Lesson Summary	180
Lesson 3: Managing User Database Size	181
Using Automatic File Growth Appropriately	181
Practice: Configuring Automatic Filegrowth Settings Using SQL Server Enterprise Manager	182
Using Automatic File Shrinkage Appropriately	183
Controlling Data File Size Manually	183
Practice: Modifying Data File Size Using SQL Server Enterprise Manager	184
Controlling Transaction Log File Size Manually	184
Creating Additional Data and Transaction Log Files	186
Lesson Summary	187
Lesson 4: Placing Database Files on Multiple Disks	188
Introducing RAID	188
Introducing Filegroups	189
Configuring Your SQL Server 2000 Disk Subsystem for Performance, Fault Tolerance, and Recoverability	190
Transaction Log	190
Data Files	191
Tempdb	192
Moving Data and Transaction Log Files	193
Detaching and Attaching Databases Using SQL Server Enterprise Manager	193
Detaching and Attaching Databases Using Transact-SQL	195
Lesson Summary	195
Review	196
CHAPTER 7: Populating a Database	197
About This Chapter	197
Before You Begin	197
Lesson 1: Transferring and Transforming Data	199
Importing Data	199
DTS Data Transformations	200
Introducing the Data Transfer Tools	200
Lesson Summary	201
Lesson 2: Introducing Microsoft Data Transformation Services (DTS)	202
Understanding a DTS Package	202
DTS Connections	202
DTS Tasks	203
DTS Package Workflow	206
DTS Package Storage	207
Introducing DTS Tools	207
Lesson Summary	209
Lesson 3: Transferring and Transforming Data with DTS Graphical Tools	210
Using the DTS Import/Export Wizard	210
Copying Entire Tables and Views	214
Querying to Specify the Data	215
Copying Objects and Data Between SQL Server Databases	217
Saving and Scheduling Packages	217
Practice: Transferring Tables and Data from the Northwind Database Using the DTS Import/Export Wizard	218
Using DTS Designer	222
Practice: Creating a Data Transfer and Transform Package Using DTS Designer	228
Extending DTS Package Functionality	233
Transaction Support	233
Message Queue Task	233
Send Mail Task	234
Programming Templates	234
Lesson Summary	234
Lesson 4: Working with DTS Packages	235
Understanding DTS Package Storage Options	235
SQL Server 2000	235
Meta Data Services	236
Structured Storage File	236
Visual Basic File	236
Using DTS Package Execution Utilities	237
DTS Run Utility	237
Dtsrun	237
Using DTS Package Logs and Exception Files	237
Performing Disconnected Edits	237
Lesson Summary	238
Lesson 5: Using the Bulk Copy Program (Bcp) and the BULK INSERT Transact-SQL Statement	239
Copying Data in Bulk Using Text Files	239
Using Bcp	240
Practice: Importing Data Using Bcp	241
Using the BULK INSERT Transact-SQL Statement	242
Optimizing Bulk Copy Operations	242
Lesson Summary	243
Review	244
CHAPTER 8: Developing a Data Restoration Strategy	247
About This Chapter	247
Before You Begin	247
Lesson 1: Understanding Data Restoration Issues	248
What Are the Goals of a Data Restoration Strategy?	248
Provide Fault Tolerance	248
Monitor Your Database	249
Plan for All Forms of Failure and Disaster	249
Determine Acceptable Data Restoration Times	249
Be Able to Quickly Verify Proper Database Functionality	250
What Types of Backups Are Available?	250
What Types of Data Restorations Are Available?	250
Back Up All Necessary Databases	252
Develop and Implement a Data Restoration Plan	252
Lesson Summary	253
Lesson 2: Understanding the Types of Database Backups	254
Understanding Full Database Backups	254
Understanding Differential Database Backups	255
Understanding File and Filegroup Backups	255
Understanding Differential File and Differential Filegroup Backups	256
Understanding Transaction Log Backups	256
Lesson Summary	257
Lesson 3: Understanding the Restoration Process	258
Understanding the Recovery Process	258
Automatic Recovery Process	258
Manual Recovery Process	259
Understanding Manual Recovery Options	260
Restoring Databases	260
Restoring Files and Filegroups	261
Restoring and Recovering to an Earlier Point in Time	261
Recovery from Several Different Disaster Scenarios	262
User Data Disk Failure	262
User Transaction Log Disk Failure	262
Master Database Disk Failure	263
Lesson Summary	263
Review	264
CHAPTER 9: Backing Up and Restoring SQL Server	265
About This Chapter	265
Before You Begin	265
Lesson 1: Understanding Backup Terms, Media, and Devices	267
Defining Terms	267
Selecting Backup Media	267
Tape	268
Disk	269
Creating Permanent Backup Devices	269
SQL Server Enterprise Manager	269
Transact-SQL	270
Practice: Creating Backup Devices Using Transact-SQL	271
Lesson Summary	272
Lesson 2: Backing Up Databases, Files, Filegroups, and Transaction Logs	273
Perform Backups Using SQL Server Enterprise Manager	273
Using the Create Database Backup Wizard	273
Practice: Backing Up the Master Database Using the Create Database Backup Wizard	279
Using SQL Server Enterprise Manager Directly	280
Practice: Backing Up the msdb Database Directly Using SQL Server Enterprise Manager	283
Perform Backups Using Transact-SQL	284
Performing a Full Database Backup	284
Performing a Differential Database Backup	285
Performing a File or Filegroup Backup	285
Performing a Transaction Log Backup	286
Practice: Performing Backups Using Transact-SQL	286
Lesson Summary	288
Lesson 3: Restoring a User Database	289
Determining the Data Restoration Sequence	289
Practice: Retrieving Backup Media Information	291
Performing Restorations Using SQL Server Enterprise Manager	292
Restoration of an Entire Database from the Failure of a Data Disk	292
Restoring a Data File or Filegroup	296
Practice: Performing a Complete Database Restoration	298
Restoring a Database to a Different SQL Server 2000 Instance	299
Recovery of a Database to a Point in Time	302
Practice: Performing a Database Restoration to a Specified Point in Time	303
Performing Restorations Using Transact-SQL	306
Restoring a Complete Database	306
Restoring a Data File or Filegroup	307
Restoration Using the Move Option	307
Restoring to Standby Mode	307
Restoring to a Specified Point in Time	308
Practice: Performing Restorations Using Transact-SQL	308
Lesson Summary	309
Lesson 4: Restoring and Rebuilding System Databases	310
Restoring the Master Database	310
Rebuilding the System Databases	312
Lesson Summary	313
Review	314
CHAPTER 10: Managing Access to SQL Server 2000	315
About This Chapter	315
Before You Begin	315
Lesson 1: Understanding the Authentication Process	316
Understanding the SQL Server 2000 Authentication Process	316
Windows Authentication	316
SQL Server Authentication	317
Comparing Windows Authentication and SQL Server Authentication	317
Client Net-Libraries and Authentication	317
Selecting a SQL Server 2000 Authentication Mode	319
Windows Authentication Mode	319
Mixed Mode	320
Switching Authentication Modes	320
Practice: Switching SQL Server 2000 Authentication Modes	321
Understanding Security Account Delegation	321
Lesson Summary	324
Lesson 2: Understanding the Authorization Process	325
Understanding Server-Wide Permissions	325
Understanding Database-Specific Permissions	326
Fixed Database Roles	328
Lesson Summary	329
Lesson 3: Creating and Managing Logins	330
Granting Access Using SQL Server Enterprise Manager	330
Using the Create Login Wizard	330
Practice: Creating a Login Using the Create Login Wizard	334
Using SQL Server Enterprise Manager Directly to Create a Login	336
Practice: Creating a Login Directly Using SQL Server Enterprise Manager	339
Creating a User-Defined Database Role Using SQL Server Enterprise Manager	341
Granting Access Using Transact-SQL Statements	342
Windows Logins	342
SQL Server Logins	342
Server Roles	343
Database Access	343
Database Roles	344
Practice: Granting SQL Server 2000 and Database Access Using Transact-SQL	345
Viewing Access Information	346
Using SQL Server Enterprise Manager	346
Using Transact-SQL	350
Practice: Viewing SQL Server 2000 Access Information	351
Lesson Summary	354
Review	355
CHAPTER 11: Managing SQL Server Permissions	357
About This Chapter	357
Before You Begin	357
Lesson 1: Granting Database-Specific Permissions	358
Implementing Permissions	358
Inherited Permissions	359
Permission Actions and Conflicts	359
Practice: Creating and Testing Permission Conflicts	360
Managing Statement Permissions	361
Creating Objects and Chain of Ownership Issues	361
Changing Object Ownership	363
Using SQL Server Enterprise Manager to Grant, Deny, or Revoke Statement Permissions	363
Using Transact-SQL to Grant, Deny, or Revoke Statement Permissions	364
Viewing Statement Permissions Using Transact-SQL	365
Practice: Granting and Testing Statement Permissions	365
Managing Object Permissions	368
Using SQL Server Enterprise Manager	370
Using Transact-SQL to Grant, Deny, or Revoke Object Permissions	373
Viewing Permissions	374
Practice: Granting and Testing Object Permissions	374
Lesson Summary	379
Lesson 2: Using Application Roles	380
Understanding Application Roles	380
Creating Application Roles	380
Activating and Using Application Roles	382
Practice: Creating and Testing Application Roles	382
Lesson Summary	384
Lesson 3: Designing an Access and Permissions Strategy	385
Permitting Administrator Access	385
Using Windows Groups and SQL Server 2000 Server Roles	386
Providing SQL Server 2000 Access	386
Providing Database Access	386
Using Fixed Database Roles for Administrative Access	386
Providing Data Access	387
Lesson Summary	388
Review	389
CHAPTER 12: Performing Administrative Tasks	391
About This Chapter	391
Before You Begin	391
Lesson 1: Performing Configuration Tasks	392
Configuring Windows 2000 (and Windows NT 4.0)	392
Maximizing Throughput for Network Operations	392
Configuring Server Application Responsiveness	393
Configuring Virtual Memory	393
Configuring the SQL Server Service	394
Connections	395
Database	397
Memory	398
Processor	400
Server	400
Integration with Active Directory	402
Configuring the Service Account	404
Configuring the SQL Server Agent Service	405
General Properties	405
Advanced Properties	406
Connection Properties	407
Registering SQL Server 2000 Instances with SQL Server Enterprise Manager	408
Using the Register SQL Server Wizard	408
Using SQL Server Enterprise Manager	412
Sharing Registration Information	413
Lesson Summary	414
Lesson 2: Setting Up Additional Features	415
Setting Up SQL Mail and SQLAgentMail	415
Using SQL Mail	415
Using SQLAgentMail	416
Configuring a Messaging (Mail) Profile	416
Setting Up SQL Mail	417
Setting Up SQLAgentMail	418
Setting Up Linked Servers	419
Setting Up Linked Servers Using SQL Server Enterprise Manager	420
Setting Up Linked Servers Using Transact-SQL	422
Practice: Setting Up and Testing a Linked Server Configuration	423
Creating an ODBC SQL Server Data Source	426
Configuring SQL Server XML Support in IIS	429
Practice: Creating an IIS Virtual Directory	434
Lesson Summary	436
Lesson 3: Performing Maintenance Tasks	437
Updating Distribution Statistics	437
Maintaining Indexes	438
Maintaining Full-Text Indexes	438
Lesson Summary	439
Review	440
CHAPTER 13: Automating Administrative Tasks	441
About This Chapter	441
Before You Begin	441
Lesson 1: Defining Operators	443
Methods of Notification	443
E-mail	443
Pager	443
NET SEND	444
Fail-Safe Operators	444
Creating Operators	444
Using SQL Server Enterprise Manager	444
Using Transact-SQL	446
Creating a Fail-Safe Operator	446
Practice: Creating Operators and Setting a Fail-Safe Operator	447
Lesson Summary	449
Lesson 2: Creating Jobs	450
Implementing Jobs	450
Types of Job Steps	450
Permissions and Ownership of Jobs	451
Multiple Job Steps and Job Responses	451
Scheduling Jobs	453
Creating Jobs	453
Using the Create Job Wizard	453
Practice: Creating a Job Using the Create Job Wizard	458
Using SQL Server Enterprise Manager Directly	459
Practice: Creating a Job Using SQL Server Enterprise Manager Directly	463
Using Transact-SQL	464
Configuring the Proxy Account	464
Reviewing Jobs and Job History	466
Job Properties	467
Job History Log	467
Practice: Reviewing and Modifying a Job and Viewing Job History	469
SQL Server Agent Error Log	470
Transact-SQL	471
Lesson Summary	471
Lesson 3: Configuring Alerts	472
Defining Alerts	472
SQL Server Event Alerts	472
Performance Conditions	473
Responses	474
Configuring Alerts	474
Using the Create Alert Wizard	474
Practice: Creating an Event Alert Using the Create Alert Wizard	478
Using SQL Server Enterprise Manager Directly	480
User-Defined Error Messages	481
Practice: Creating a Performance Condition Alert Using SQL Server Enterprise Manager Directly	482
Using Transact-SQL	484
Reviewing Alerts and Alert History	484
Alert Properties	485
Transact-SQL	485
Lesson Summary	485
Lesson 4: Creating a Database Maintenance Plan	486
Using the Database Maintenance Plan Wizard	486
Viewing and Modifying Database Maintenance Plans	493
Using the Sqlmaint Utility	494
Lesson Summary	495
Lesson 5: Creating Multiserver Jobs	496
Creating Master and Target Servers	496
Creating Jobs for Target Servers	500
Monitoring Jobs on Target Servers	501
Practice: Creating a Multiserver Job	504
Lesson Summary	506
Review	507
CHAPTER 14: Monitoring SQL Server Performance and Activity	509
About This Chapter	509
Before You Begin	509
Lesson 1: Developing a Performance Monitoring Methodology	510
Establishing Monitoring Goals	510
Identifying Performance Bottlenecks	511
Determine Trends	511
Determining Resources and Activities to Monitor	512
Isolating the Problem	513
Lesson Summary	513
Lesson 2: Choosing Among Monitoring Tools	514
Using System Monitor	514
Using Task Manager	516
Using SQL Profiler	516
Using SQL Query Analyzer	518
Using the SQL Server Enterprise Manager Current Activity Window	519
Using Transact-SQL	519
System Stored Procedures	519
DBCC	520
Built-in Functions	520
Trace Flags	522
Using SNMP	522
Lesson Summary	522
Lesson 3: Performing Monitoring Tasks	523
Monitoring Resource Usage	523
Task Manager	523
System Monitor	525
Memory Objects and Counters	528
I/O Objects and Counters	529
Processor Objects and Counters	529
Practice: Monitoring System Resources Using System Monitor and Task Manager	532
Monitoring Stored Procedures, Transact SQL Batches, and User Activity	533
Practice: Monitoring SQL Batches and Stored Procedures	537
Monitoring Current Locking and User Activity	538
Practice: Monitoring Blocking Problems Using the Current Activity Window and System Stored Procedures	543
Lesson Summary	545
Review	546
CHAPTER 15: Using SQL Server Replication	547
About This Chapter	547
Before You Begin	547
Lesson 1: Introducing Replication	548
Describing Replication	548
Reasons to Replicate Information	548
Types of Replication	549
Replication Terminology	549
Understanding the Types of Replication	551
Snapshot Replication	551
Transactional Replication	553
Merge Replication	554
Selecting a Physical Replication Model	555
Choosing Replication Implementation Tools	556
Lesson Summary	557
Lesson 2: Planning for Replication	558
Planning for Replication Security	558
Filtering Published Data	559
Choosing Initial Snapshot Options	560
Lesson Summary	561
Lesson 3: Implementing Replication	562
Configuring Distributor and Publisher Properties	562
Practice: Configuring a Distributor	570
Creating a Publication	571
Creating a Snapshot Publication	573
Creating a Transactional Publication	581
Practice: Creating a Transactional Publication	582
Configuring Merge Replication	583
Practice: Creating a Merge Publication	590
Configuring Push Subscriptions	593
Practice: Creating a Push Subscription	600
Configuring a Pull Subscription	602
Practice: Creating a Pull Subscription	608
Lesson Summary	610
Lesson 4: Monitoring and Administering Replication	611
Monitoring with Replication Monitor	611
Publications	612
Agents	613
Alerts	614
Practice: Monitoring Replication	615
Reviewing and Modifying Distributor Properties	616
Reviewing and Modifying Publication Properties	617
Reviewing and Modifying Replication Agent Profile	618
Reviewing and Modifying Subscription Properties	620
Viewing and Resolving Merge Replication Conflicts	622
Using Windows Synchronization Manager	623
Practice: Resolving Conflicts Interactively Using Windows Synchronization Manager	627
Lesson Summary	628
Review	629
CHAPTER 16: Maintaining High Availability	631
About This Chapter	631
Before You Begin	631
Lesson 1: Using Standby Servers	632
Using a Standby Server	632
Automating Log Shipping	634
Practice: Creating a Standby Server	642
Monitoring Log Shipping	645
Practice: Monitoring Log Shipping	649
Bringing a Standby Server Online	650
Practice: Bringing a Standby Server Online as the Primary Server	651
Lesson Summary	654
Lesson 2: Using Failover Clustering	655
Introducing MSCS	655
Creating a Virtual Server on a Server Cluster	656
Planning Issues	657
Disaster Recovery	658
Lesson Summary	658
Review	659
PART 2: PREPARATION FOR MCP EXAM 70-228
OBJECTIVE DOMAIN 1: Installing and Configuring SQL Server 2000	663
Tested Skills and Suggested Practices	663
Further Reading	666
Objective 1.1: Install SQL Server 2000.	669
Questions	671
Answers	673
Objective 1.2: Upgrade to SQL Server 2000	677
Questions	679
Answers	681
Objective 1.3: Create a linked Server.	683
Questions	685
Answers	686
Objective 1.4: Configure SQL Mail and SQLAgentMail.	689
Questions	691
Answers	693
Objective 1.5: Configure network libraries.	695
Questions	697
Answers	698
Objective 1.6: Troubleshoot failed installations.	701
Questions	702
Answers	703
OBJECTIVE DOMAIN 2: Creating SQL Server 2000 Databases	707
Tested Skills and Suggested Practices	708
Further Reading	709
Objective 2.1: Configure database options for performance.	711
Questions	712
Answers	714
Objective 2.2: Attach and detach databases.	717
Questions	718
Answers	719
Objective 2.3: Create and alter databases.	721
Questions	722
Answers	724
Objective 2.4: Create and manage objects.	727
Questions	729
Answers	731
OBJECTIVE DOMAIN 3: Managing, Monitoring, and Troubleshooting SQL Server 2000 Databases	735
Tested Skills and Suggested Practices	736
Further Reading	738
Objective 3.1: Optimize database performance	741
Questions	743
Answers	745
Objective 3.2: Optimize data storage.	749
Questions	750
Answers	751
Objective 3.3: Modify the database schema.	755
Questions	756
Answers	757
Objective 3.4: Perform disaster recovery operations	759
Questions	760
Answers	763
Objective 3.5: Perform integrity checks.	765
Questions	767
Answers	768
Objective 3.6: Troubleshoot transactions and locking by using SQL Profiler, SQL Enterprise Manager, or Transact-SQL	771
Questions	772
Answers	774
OBJECTIVE DOMAIN 4: Extracting and Transforming Data with SQL Server 2000	777
Tested Skills and Suggested Practices	777
Further Reading	779
Objective 4.1: Set up Internet Information Services (IIS) virtual directories to support XML	783
Questions	784
Answers	786
Objective 4.2: Import and export data.	789
Questions	790
Answers	791
Objective 4.3: Develop and manage Data TransformationServices (DTS) packages	795
Questions	796
Answers	799
Objective 4.4: Manage linked servers	803
Questions	805
Answers	806
Objective 4.5: Convert data types	809
Questions	810
Answers	811
Objective 4.6: Configure, maintain, and troubleshoot replication services	813
Questions	815
Answers	817
OBJECTIVE DOMAIN 5: Managing and Monitoring SQL Server 2000 Security	821
Tested Skills and Suggested Practices	821
Further Reading	823
Objective 5.1: Configure mixed security modes or Windows Authentication	827
Questions	828
Answers	829
Objective 5.2: Create and manage logons	831
Questions	832
Answers	834
Objective 5.3: Create and manage database users	837
Questions	838
Answers	839
Objective 5.4: Create and manage security roles	843
Questions	844
Answers	846
Objective 5.5: Enforce and manage security by using stored procedures, triggers, views, and user-defined functions	849
Questions	850
Answers	851
Objective 5.6: Set permissions in a database	853
Questions	854
Answers	855
Objective 5.7: Manage security auditing	859
Questions	860
Answers	861
OBJECTIVE DOMAIN 6: Managing, Monitoring, and Troubleshooting SQL Server 2000	863
Tested Skills and Suggested Practices	863
Further Reading	865
Objective 6.1: Create, manage, and troubleshoot SQL Server Agent jobs	867
Questions	869
Answers	871
Objective 6.2: Configure alerts and operators by using SQL Server Agent	873
Questions	875
Answers	876
Objective 6.3: Optimize hardware resource usage	879
Questions	880
Answers	881
Objective 6.4: Optimize and troubleshoot SQL Server system activity	883
Questions	885
Answers	886
APPENDIX: Questions and Answers	889
GLOSSARY	905
INDEX	939

About the Author

Founded in 1975, Microsoft (NASDAQ 'MSFT') is the worldwide leader in software for personal and business computing. The company offers a wide range of products and services designed to empower people through great software-- any time, any place, and on any device.

Jul 2, 2004     A review from Madison, WI
Not a good book
One of the poorest SQL Server books I've read. Very few practical examples, no explanation on how to use many of the Transact-SQL statements. It may give you the "Big Picture" of what SQL Server is all about, but it really doesn't teach you how to use it in a practical manner.

Dec 5, 2003     Giacomo (giacomo.gozzi@katamail.com) from Italy (Siena)
so and so
The material in the book is not enough to pass the exam. You will have to integrate it with SQL Server books on line and other books. Every argument is not trated deeply.

Jul 18, 2003     Fred Nance (fred.nance@comcast.net) from Los Angeles, California
Great book for MCSD, MCDBA and MCAD certification
Great book, make sure you have the Windows 2000 Server training kit to help in setting up Active Directory.

Forgot your password? FAQs Shipping Options Returns Your Orders Your Account