Special Offers

MCSE Training Kit (Exam 70-228): Microsoft® SQL Server™ 2000 System Administration
Author Microsoft Corporation
Pages 720
Disk 1 Companion CD(s)
Level All Levels
Published 03/28/2001
ISBN 9780735612471
ISBN-10 0-7356-1247-1
Price(USD) $59.99
To see this book's discounted price, select a reseller below.

More Information

About the Book
Table of Contents
Sample Chapter
Related Series
Related Books
About the Author

Support: Book & CD

Rate this book
Barnes Noble Amazon Quantum Books


Table of Contents

About This Bookxvii
CHAPTER 1  Overview of SQL Server 20001
    About This Chapter1
Lesson 1: What Is SQL Server 2000?1
    The SQL Server 2000 Environment2
    SQL Server 2000 Components2
    SQL Server 2000 Relational Database Engine4
    SQL Server 2000 Analysis Services4
    Application Support4
    Additional Components5
    SQL Server 2000 Editions7
    Integration with Windows 2000 and Windows NT 4.010
    Lesson Summary12
Lesson 2: What Are the SQL Server 2000 Components?12
    Server Components13
    Client-Based Administration Tools and Utilities14
    Client Communication Components14
    SQL Server Books Online18
    Lesson Summary19
Lesson 3: What Is the Relational Database Architecture?19
    System and User Databases19
    Physical Structure of a Database20
    Logical Structure of a Database21
    Lesson Summary22
Lesson 4: What Is the Security Architecture?23
    Lesson Summary26
CHAPTER 2  Installing SQLServer 200027
    About This Chapter27
    Before You Begin28
Lesson 1: Planning to Install SQL Server 200028
    What Is the Minimum Hardware Required?28
    Exceeding Minimum Computer Hardware Requirements31
    Lesson Summary32
Lesson 2: Deciding SQL Server 2000 Setup Configuration Options32
    Determining the Appropriate User Account for the SQL Server and SQL Server Agent Services32
    Practice: Creating a Dedicated Windows 2000 User Account34
    Choosing an Authentication Mode34
    Determining Collation Settings35
    Practice: Determining Your Windows Collation37
    Selecting Network Libraries37
    Deciding on a Client Licensing Mode38
    Lesson Summary39
Lesson 3: Running the SQL Server 2000 Setup Program39
    Running the SQL Server 2000 Setup Program40
    Understanding Installation Types41
    Selecting a Setup Type42
    Practice: Installing a Default Instance of SQL Server 200048
    Lesson Summary49
Lesson 4: Using Default, Named, and Multiple Instances of SQL Server 200049
    Installing Multiple Instances of SQL Server 200050
    Using Multiple Instances of SQL Server 2000 Effectively and Appropriately51
    Understanding Shared Components Between Instances51
    Understanding Unique Components Between Instances51
    Working with Default and Named Instances of SQL Server 200052
    Lesson Summary52
Lesson 5: Performing Unattended and Remote Installations of SQL Server 200053
    Performing an Unattended Installation of SQL Server 200053
    Creating Setup Initialization Files for SQL Server 200053
    Practice: Performing an Unattended Installation of a Named Instance of SQL Server 200055
    Performing a Remote Installation of SQL Server 200057
    Lesson Summary58
Lesson 6: Troubleshooting a SQL Server 2000 Installation58
    Reviewing SQL Server 2000 Setup Program Log Files59
    Accessing SQL Server 2000 Troubleshooting Information Online59
    Reviewing the SQL Server Error Log and the Windows Application Log60
    Practice: Reviewing the SQL Server Error Log and the Windows Application Log60
    Lesson Summary61
CHAPTER 3  Preparing to Use SQL Server 200063
    About This Chapter63
    Before You Begin63
Lesson 1: Reviewing the Results of Installation64
    What Files and Folders Were Added?64
    What Permissions Were Set in the NTFS File System67
    Practice: Reviewing the Files and Folders That Were Created68
    What Registry Keys Were Added 70
    What Permissions Were Set on Registry Keys71
    Practice: Reviewing Permissions on Registry Keys73
    What Programs Were Added to the Start Menu74
    Lesson Summary74
Lesson 2: Starting, Stopping, Pausing, and Modifying SQL Server 2000 Services 75
    What Is the Default Configuration for Each SQL Server Service?75
    Starting, Stopping, and Pausing SQL Server 2000 Services76
    Practice: Starting SQL Server Services79
    Changing the SQL Server or SQL Server Agent Service Account After Setup80
    Lesson Summary81
Lesson 3: Working with Osql, SQL Query Analyzer, and SQL Server Enterprise Manager82
    Working with Osql82
    Practice: Using Osql to Query SQL Server 2000 Instances84
    Working with SQL Query Analyzer85
    Practice: Using SQL Query Analyzer to Query SQL Server 2000 Instances87
    Working with SQL Server Enterprise Manager89
    Practice: Working with the SQL Server Enterprise Manager MMC Console91
    Lesson Summary93
CHAPTER 4  Upgrading to SQL Server 2000 95
    About This Chapter95
    Before You Begin95
Lesson 1: Preparing to Upgrade96
    Working with Multiple Versions of SQL Server on the Same Computer96
    Choosing the Appropriate Upgrade Process and Method97
    Determining Hardware and Software Requirements99
    Preparing for the Actual Upgrade100
    Lesson Summary101
Lesson 2: Performing a Version Upgrade from SQL Server 7.0101
    Performing a Version Upgrade101
    Performing Post-Upgrade Tasks103
    Manually Upgrading Meta Data Services Tables and the Repository Database103
    Lesson Summary104
Lesson 3: Performing an Online Database Upgrade from SQL Server 7.0104
    Performing an Online Database Upgrade104
    Performing Post-Upgrade Tasks110
    Lesson Summary110
Lesson 4: Performing a Version Upgrade from SQL Server 6.5111
    Performing a Version Upgrade111
    Troubleshooting a SQL Server 6.5 Upgrade118
    Specifying a Backward Compatibility Level for Upgraded Databases119
    Lesson Summary119
CHAPTER 5  Understanding System and User Databases121
    About This Chapter121
    Before You Begin121
Lesson 1: Understanding the Database Architecture122
    Introducing Data Files122
    Practice: Viewing the Properties of a Data File123
    Allocating Space for Tables and Indexes124
    Storing Index and Data Pages125
    Lesson Summary126
Lesson 2: Understanding the Transaction Log Architecture127
    Introducing Transaction Log Files128
    How the Transaction Log Works129
    Introducing Recovery Models132
    Practice: Viewing the Properties of a Transaction Log and a Database133
    Lesson Summary134
Lesson 3: Understanding and Querying System and Database Catalogs134
    Introducing System Tables134
    Retrieving System Information136
    Practice: Querying System Tables Directly136
    Practice: Querying System Tables Using System Stored Procedures137
    Practice: Querying System Tables Using System Functions140
    Practice: Querying System Tables Using Information Schema Views141
    Lesson Summary142
CHAPTER 6  Creating and Configuring User Databases143
    About This Chapter143
    Before You Begin143
    Lesson 1: Creating a User Database144
    Creating a User Database144
    Using SQL Server Enterprise Manager to Create a User Database147
    Practice: Creating a Database Using the Create Database Wizard in SQL Server Enterprise Manager151
    Practice: Creating a Database Directly Using SQL Server Enterprise Manager154
    Using the CREATE DATABASE Transact-SQL Statement to Create a User Database155
    Practice: Creating a Database in SQL Query Analyzer Using the CREATE DATABASE Transact-SQL Statement157
    Scripting Databases and Database Objects Using SQL Server Enterprise Manager158
    Practice: Generating a Transact-SQL Script to Re-create the TSQLDB Database159
    Lesson Summary160
    Lesson 2: Setting Database Options161
    Introducing Database Options161
    Viewing Database Option Settings161
    Modifying Database Options162
    Lesson Summary163
    Lesson 3: Managing User Database Size Using Automatic File Growth Appropriately163
    Practice: Configuring Automatic Filegrowth Settings Using SQL Server Enterprise Manager164
    Using Automatic File Shrinkage Appropriately165
    Controlling Data File Size Manually165
    Practice: Modifying Data File Size Using SQL Server Enterprise Manager166
    Controlling Transaction Log File Size Manually167
    Creating Additional Data and Transaction Log Files168
    Lesson Summary169
    Lesson 4: Placing Database Files on Multiple Disks170
    Introducing RAID170
    Introducing Filegroups170
    Configuring Your SQL Server 2000 Disk Subsystem for Performance, Fault Tolerance, and Recoverability172
    Moving Data and Transaction Log Files174
    Lesson Summary177
CHAPTER 7  Populating a Database179
    About This Chapter179
    Before You Begin179
Lesson 1: Transferring and Transforming Data180
    Importing Data180
    DTS Data Transformations181
    Introducing the Data Transfer Tools182
    Lesson Summary182
Lesson 2: Introducing Microsoft Data Transformation Services (DTS)183
    Understanding a DTS Package183
    DTS Package Storage188
    Introducing DTS Tools188
    Lesson Summary190
Lesson 3: Transferring and Transforming Data with DTS Graphical Tools190
    Using the DTS Import/Export Wizard190
    Practice: Transferring Tables and Data from the Northwind Database Using the DTS Import/Export Wizard198
    Using DTS Designer202
    Practice: Creating a Data Transfer and Transform Package Using DTS Designer208
    Extending DTS Package Functionality213
    Lesson Summary214
Lesson 4: Working with DTS Packages215
    Understanding DTS Package Storage Options 215
    Using DTS Package Execution Utilities217
    Using DTS Package Logs and Exception Files217
    Performing Disconnected Edits218
    Lesson Summary218
Lesson 5: Using the Bulk Copy Program (Bcp) and the BULK INSERT Transact-SQL Statement218
    Copying Data in Bulk Using Text Files219
    Using Bcp219
    Practice: Importing Data Using Bcp220
    Using the BULK INSERT Transact-SQL Statement222
    Optimizing Bulk Copy Operations222
    Lesson Summary223
CHAPTER 8   Developing a Data Restoration Strategy225
Lesson 1: Understanding Data Restoration Issues226
Lesson 2: Understanding the Types of Database Backups231
Lesson 3: Understanding the Restoration Process235
CHAPTER 9  Backing Up and Restoring SQL Server243
    About This Chapter243
    Before You Begin243
Lesson 1: Understanding Backup Terms, Media, and Devices244
    Defining Terms244
    Selecting Backup Media244
    Creating Permanent Backup Devices246
    Practice: Creating Backup Devices Using Transact-SQL248
    Lesson Summary249
Lesson 2: Backing Up Databases, Files, Filegroups, and Transaction Logs249
    Perform Backups Using SQL Server Enterprise Manager249
    Practice: Backing Up the master Database Using the Create Database Backup Wizard254
    Practice: Backing Up the msdb Database Directly Using SQL Server Enterprise Manager259
    Perform Backups Using Transact-SQL260
    Practice: Performing Backups Using Transact-SQL262
    Lesson Summary264
Lesson 3: Restoring a User Database264
    Determining the Data Restoration Sequence 265
    Practice: Retrieving Backup Media Information267
    Performing Restorations Using SQL Server Enterprise Manager268
    Practice: Performing a Complete Database Restoration273
    Practice: Performing a Database Restoration to a Specified Point in Time278
    Performing Restorations Using Transact-SQL281
    Practice: Performing Restorations Using Transact-SQL283
    Lesson Summary285
Lesson 4: Restoring and Rebuilding System Databases285
    Restoring the Master Database285
    Rebuilding the System Databases287
    Lesson Summary288
CHAPTER 10  Managing Access to SQL Server 2000289
    About This Chapter289
    Before You Begin289
Lesson 1: Understanding the Authentication Process290
    Understanding the SQL Server 2000 Authentication Process290
    Comparing Windows Authentication and SQL Server Authentication291
    Selecting a SQL Server 2000 Authentication Mode293
    Practice: Switching SQL Server 2000 Authentication Modes295
    Understanding Security Account Delegation295
    Lesson Summary298
Lesson 2: Understanding the Authorization Process298
    Understanding Server-Wide Permissions298
    Understanding Database-Specific Permissions299
    Lesson Summary301
Lesson 3: Creating and Managing Logins302
    Granting Access Using SQL Server Enterprise Manager302
    Practice: Creating a Login Using the Create Login Wizard306
    Practice: Creating a Login Directly Using SQL Server Enterprise Manager311
    Granting Access Using Transact-SQL Statements314
    Practice: Granting SQL Server 2000 and Database Access Using Transact-SQL318
    Viewing Access Information319
    Practice: Viewing SQL Server 2000 Access Information324
    Lesson Summary326
CHAPTER 11  Managing SQL Server Permissions329
    About This Chapter329
    Before You Begin329
Lesson 1: Granting Database-Specific Permissions330
    Implementing Permissions330
    Practice: Creating and Testing Permission Conflicts332
    Managing Statement Permissions333
    Practice: Granting and Testing Statement Permissions337
    Managing Object Permissions340
    Viewing Permissions346
    Practice: Granting and Testing Object Permissions346
    Lesson Summary351
Lesson 2: Using Application Roles351
    Understanding Application Roles351
    Creating Application Roles352
    Activating and Using Application Roles353
    Practice: Creating and Testing Application Roles353
    Lesson Summary355
Lesson 3: Designing an Access and Permissions Strategy355
    Permitting Administrator Access355
    Using Windows Groups and SQL Server 2000 Server Roles356
    Providing SQL Server 2000 Access356
    Providing Database Access357
    Using Fixed Database Roles for Administrative Access357
    Providing Data Access357
    Lesson Summary358
CHAPTER 12  Performing Administrative Tasks361
    About This Chapter361
    Before You Begin361
Lesson 1: Performing Configuration Tasks362
    Configuring Windows 2000 (and Windows NT 4.0) 362
    Configuring the SQL Server Service365
    Configuring the Service Account374
    Configuring the SQL Server Agent Service375
    Registering SQL Server 2000 Instances with SQL Server Enterprise Manager378
    Sharing Registration Information383
    Lesson Summary384
Lesson 2: Setting Up Additional Features384
    Setting Up SQL Mail and SQLAgentMail385
    Setting Up Linked Servers389
    Practice: Setting Up and Testing a Linked Server Configuration393
    Creating an ODBC SQL Server Data Source396
    Configuring SQL Server XML Support in IIS399
    Practice: Creating an IIS Virtual Directory404
    Lesson Summary406
Lesson 3: Performing Maintenance Tasks406
    Updating Distribution Statistics406
    Maintaining Indexes407
    Maintaining Full-Text Indexes407
    Lesson Summary408
CHAPTER 13  Automating Administrative Tasks411
    About This Chapter411
    Before You Begin411
Lesson 1: Defining Operators412
    Methods of Notification412
    Fail-Safe Operators413
    Creating Operators413
    Creating a Fail-Safe Operator415
    Practice: Creating Operators and Setting a Fail-Safe Operator416
    Lesson Summary418
Lesson 2: Creating Jobs418
    Implementing Jobs418
    Creating Jobs422
    Practice: Creating a Job Using the Create Job Wizard426
    Practice: Creating a Job Using SQL Server Enterprise Manager Directly431
    Using Transact-SQL433
    Reviewing Jobs and Job History434
    Practice: Reviewing and Modifying a Job and Viewing Job History437
    Lesson Summary439
Lesson 3: Configuring Alerts439
    Defining Alerts440
    Configuring Alerts442
    Practice: Creating an Event Alert Using the Create Alert Wizard446
    Practice: Creating a Performance Condition Alert Using SQL Server Enterprise Manager Directly450
    Reviewing Alerts and Alert History452
    Lesson Summary453
Lesson 4: Creating a Database Maintenance Plan453
    Using the Database Maintenance Plan Wizard454
    Viewing and Modifying Database Maintenance Plans460
    Using the Sqlmaint Utility462
    Lesson Summary462
Lesson 5: Creating Multiserver Jobs462
    Creating Master and Target Servers463
    Creating Jobs for Target Servers466
    Monitoring Jobs on Target Servers467
    Practice: Creating a Multiserver Job470
    Lesson Summary472
CHAPTER 14  Monitoring SQL Server Performance and Activity475
    About This Chapter475
    Before You Begin475
Lesson 1: Developing a Performance Monitoring Methodology476
    Establishing Monitoring Goals476
    Determining Resources and Activities to Monitor478
    Lesson Summary479
Lesson 2: Choosing Among Monitoring Tools479
    Using System Monitor480
    Using Task Manager481
    Using SQL Profiler482
    Using SQL Query Analyzer484
    Using the SQL Server Enterprise Manager Current Activity Window485
    Using Transact-SQL485
    Using SNMP488
    Lesson Summary488
Lesson 3: Performing Monitoring Tasks489
    Monitoring Resource Usage489
    Practice: Monitoring System Resources Using System Monitor and Task Manager498
    Monitoring Stored Procedures, Transact SQL Batches, and User Activity499
    Practice: Monitoring SQL Batches and Stored Procedures503
    Monitoring Current Locking and User Activity504
    Practice: Monitoring Blocking Problems Using the Current Activity Window and System Stored Procedures509
    Lesson Summary511
CHAPTER 15  Using SQL Server Replication513
    About This Chapter513
    Before You Begin513
Lesson 1: Introducing Replication514
    Describing Replication514
    Understanding the Types of Replication517
    Selecting a Physical Replication Model521
    Choosing Replication Implementation Tools522
    Lesson Summary523
Lesson 2: Planning for Replication523
    Planning for Replication Security523
    Filtering Published Data524
    Choosing Initial Snapshot Options 525
    Lesson Summary526
Lesson 3: Implementing Replication526
    Configuring Distributor and Publisher Properties526
    Practice: Configuring a Distributor534
    Creating a Publication535
    Practice: Creating a Transactional Publication545
    Practice: Creating a Merge Publication553
    Configuring Push Subscriptions556
    Practice: Creating a Push Subscription563
    Configuring a Pull Subscription565
    Practice: Creating a Pull Subscription571
    Lesson Summary573
Lesson 4: Monitoring and Administering Replication573
    Monitoring with Replication Monitor573
    Practice: Monitoring Replication577
    Reviewing and Modifying Distributor Properties 578
    Reviewing and Modifying Publication Properties579
    Reviewing and Modifying Replication Agent Profile580
    Reviewing and Modifying Subscription Properties582
    Practice: Resolving Conflicts Interactively Using Windows Synchronization Manager589
    Lesson Summary590
CHAPTER 16  Maintaining High Availability593
    About This Chapter593
    Before You Begin593
Lesson 1: Using Standby Servers594
    Using a Standby Server 594
    Automating Log Shipping596
    Practice: Creating a Standby Server604
    Monitoring Log Shipping607
    Practice: Monitoring Log Shipping611
    Bringing a Standby Server Online612
    Practice: Bringing a Standby Server Online as the Primary Server613
    Lesson Summary615
Lesson 2: Using Failover Clustering615
    Introducing MSCS 616
    Creating a Virtual Server on a Server Cluster617
    Lesson Summary619

Top of Page

Last Updated: Saturday, July 7, 2001