Cover image for Training guide MCSE. SQL Server 7 Administration, exam 70-028
Training guide MCSE. SQL Server 7 Administration, exam 70-028
Wille, Christoph.
Publication Information:
Indianapolis, IN : New Riders, [1999]

Physical Description:
xvii, 695 pages : illustrations ; 25 cm + 1 computer laser optical disc (4 3/4 in.)
Title Subject:
Added Author:
Format :


Call Number
Material Type
Home Location
Item Holds
QA76.3 .T73 1999 Book and Software Set Central Closed Stacks

On Order



Provides detailed coverage of each objective of the SQL Server 7 administration exam. The text also includes questions, study cards and a practice exam.

Author Notes

Christoph Wille, MCSE, MCSD, has an extensive background working with Microsoft SQL Server, Microsoft operating systems, Access, MFC, and OLE. He is currently a corporate Internet strategy consultant.
Damir Bersinic, MCT, MCSE+I, Oracle Certified DBA and Trainer, has more than 15 years of industry experience working with SQL Server, Oracle, Microsoft Windows NT and Back Office, and other advanced products. He is president and founder of Bradley Systems Incorporated, a Microsoft Certified Solution Provider.
Brad McGehee, MCT, MCSE, CNE, is a full-time computer trainer. He has written more than 100 articles on computers and has served as either author or editor on five computer books.
Rob Scrimger, MCT, MCSE+I, is currently the Product Stream Leader, Microsoft, at Learnix. He has more than ten years of training experience in the areas of network operating systems and client/server applications.

Table of Contents

Part I Exam Preparation
1 Planningp. 11
Introductionp. 16
Developing a Security Strategy for Microsoft SQL Server 7p. 17
SQL Server Authentication Methodsp. 18
Planning the Use of Windows NT Groups for Securityp. 24
Planning the Use of SQL Server Rolesp. 27
Application Considerations in Planning Securityp. 32
Planning SQL Server Service Startup Accountsp. 35
Linked Databases in Security Planningp. 37
Security Wrap Upp. 38
Developing a Microsoft SQL Server Capacity Planp. 39
Planning SQL Server Data File Placementp. 40
Planning for Data File Growthp. 41
Performance versus Fault Tolerancep. 43
General Guidelines for File Placementp. 46
Communication and Network Requirementsp. 46
Planning Communication Requirementsp. 48
Developing a Data Availability Solutionp. 48
Determining an Appropriate Backup Strategyp. 49
Assessing the Benefits of a Standby Serverp. 53
Clustering Microsoft SQL Server 7p. 53
Data Availability Guidelinesp. 56
Developing a Migration Planp. 56
Planning an Upgrade from Previous Versions of SQL Serverp. 56
Steps Required for Upgradingp. 60
Migrating Data from Other Sourcesp. 69
Summary Points on Migrating Other Source Datap. 77
Developing a Replication Strategyp. 78
Choosing the Appropriate Replication Modelp. 79
Choosing a Replication Typep. 87
Designing and Planning Replicationp. 91
Data Definition Considerationsp. 96
2 Installation and Configurationp. 119
Installing SQL Server 7p. 122
Installation Prerequisitesp. 122
Installing SQL Server 7 Componentsp. 125
Installing the Database Server Componentp. 127
Installing and Configuring SQL Server Clientsp. 141
Performing an Unattended Setupp. 144
Upgrading from a SQL Server 6.x Databasep. 148
Configuring Microsoft SQL Server 7p. 162
Using the Register Server Wizardp. 163
Configuring the Serverp. 167
Configuring SQL Mailp. 171
Configuring Default ANSI Settingsp. 174
Installing and Configuring the Full-Text Search Servicep. 175
Installing the Full-Text Search Enginep. 176
Configuring Full-Text Search Service Propertiesp. 177
Configuring Full-Text Search Catalogs and Enabling Full-Text Search for Specific Columns and Tablesp. 179
3 Configuring and Managing Securityp. 201
Introductionp. 205
SQL Server Security Basicsp. 205
Planning SQL Server Securityp. 206
Overview of the SQL Server Security Architecturep. 207
SQL Server Security Building Blocksp. 208
Barriers to Accessing SQL Server Datap. 209
User Authentication Methodsp. 211
What is a Security Mode?p. 213
Mixed Modep. 213
Windows NT Authentication Modep. 216
Authentication Methodsp. 218
How Does a User Gain Access to SQL Server?p. 219
Introduction to SQL Server Accountsp. 219
Creating and Managing Login IDsp. 221
Creating Login IDs Under Windows NT Authenticationp. 221
Creating Login IDs Under SQL Server Authenticationp. 224
Creating and Managing Database User IDsp. 224
Managing SQL Server Rolesp. 225
Fixed Server Rolesp. 225
Fixed Database Rolesp. 227
The Public Rolep. 228
User-Defined Database Rolesp. 228
Application Rolesp. 229
Managing Users with SQL Enterprise Managerp. 230
Using the SQL Server Create Login Wizard to Manage SQL Server Accountsp. 231
Manually Creating, Modifying, and Deleting SQL Server Login IDs and Database User IDsp. 235
Displaying SQL Server Account Informationp. 236
Creating and Configuring Login IDsp. 245
Deleting a Login IDp. 249
Creating and Configuring Database User IDsp. 250
Creating and Configuring User-Defined and Application Database Rolesp. 252
Deleting a Database User ID or Database Rolep. 253
Configuring a Server Rolep. 255
Creating and Managing IDs and Rolesp. 257
Using System Stored Procedures to Manage Accountsp. 257
Permissionsp. 259
Statement Permissionsp. 261
Object Permissionsp. 262
Implied Permissionsp. 263
Understanding Permission Precedencep. 263
Using Views and Stored Procedures to Enforce Securityp. 265
Understanding Ownership Chainsp. 267
Non-Broken Ownership Chainsp. 267
A Broken Ownership Chainp. 267
Preventing Broken Ownership Chainsp. 268
Best Practices for Managing Permissionsp. 268
Displaying Permissions Informationp. 270
Viewing Statement Permissionsp. 270
Viewing Object Permissionsp. 271
Granting and Revoking Permissionsp. 275
Granting and Revoking Statement Permissions Using SQL Enterprise Managerp. 276
Granting and Revoking Object Permissions Using SQL Enterprise Managerp. 278
Managing Permissions with Transact-SQLp. 281
Managing Permissionsp. 281
Using SQL Server Profiler to Audit Server and Database Activityp. 282
Features of the SQL Server Profilerp. 282
Using the SQL Server Profilerp. 283
4 Managing and Maintaining Datap. 307
Introductionp. 309
Creating a Databasep. 309
Creating Data Files and Filegroupsp. 309
Specify Growth Characteristicsp. 313
Managing a Databasep. 315
Shrinking a Databasep. 315
Deleting Data and Log Filesp. 317
Setting Database Optionsp. 318
Documenting and Scripting a Databasep. 321
Setting Up a Database Maintenance Planp. 323
Deleting a Databasep. 326
Loading Datap. 328
Insert and Select Intop. 329
Bulk Copying Datap. 331
Data Transformation Services (DTS)p. 339
5 Performing Backup and Restorep. 357
Introductionp. 359
User Databasesp. 360
Working with Backup Devicesp. 360
Database Backupsp. 362
Differential Backupsp. 368
Transaction Log Backupsp. 372
File or Filegroup Backupsp. 376
Scheduling Your Backupsp. 378
System Databasesp. 379
The master Databasep. 379
The model, msdb, and distribution Databasesp. 382
6 Managing Replicationp. 391
Introductionp. 393
Replication Reviewedp. 393
Design Requirementsp. 393
Types of Replicationp. 394
Replication Agentsp. 395
Configuring Serversp. 396
Distributorsp. 396
Publishersp. 398
Subscribersp. 400
Creating Publicationsp. 401
Snapshot Publicationp. 401
Transactional Publicationp. 405
Merge Publicationp. 406
Subscribing to Publicationsp. 407
Adding a Pull Subscriptionp. 408
Adding a Push Subscriptionp. 410
Adding an Anonymous Subscriptionp. 411
7 Automating Administrative Tasksp. 421
Introductionp. 423
Configuring the SQL Server Agentp. 423
Startup Accountp. 423
Connection Methodp. 425
SQL Server Agent Mailp. 426
Implementing Jobsp. 427
Creating Jobsp. 428
Multiserver Jobsp. 432
Scripting Jobsp. 438
Managing Server Eventsp. 438
Defining Operatorsp. 439
Defining Alertsp. 440
Forwarding Eventsp. 442
Copying Operators and Alerts to Other Serversp. 443
8 Using Linked Serversp. 451
Introductionp. 453
Linked Serversp. 453
Setting Up a Linked Serverp. 454
Managing Securityp. 457
Remote Serversp. 459
Setting Up a Remote Serverp. 459
Managing Securityp. 460
9 Monitoring and Optimizationp. 465
Introductionp. 467
Monitoring SQL Serverp. 467
Monitoring Basicsp. 468
Monitoring Engine Eventsp. 470
Monitoring Server Processes' Resource Usagep. 495
Monitoring with SQL Server Enterprise Managerp. 504
Monitoring the Error Logsp. 506
Monitoring with TSQL Statementsp. 507
Monitoringp. 509
Optimizing Server Performancep. 510
Tuning Guidelines for SQL Serverp. 510
Optimizing Memory Usagep. 510
Optimizing I/O Performancep. 512
Optimizing CPU Usagep. 513
10 Troubleshootingp. 525
Introductionp. 528
Overview of Troubleshootingp. 528
SQL Server Startup Parametersp. 529
SQL Server Command-Line Utilitiesp. 531
Installing and Upgrading to SQL Server 7p. 532
Installationp. 532
Upgradingp. 536
Troubleshooting SQL Server Access Problemsp. 543
Network Connectivityp. 543
NT Authenticationp. 546
Troubleshooting SQL Server Permissionsp. 549
Accessing Databasesp. 549
Permissionsp. 551
Troubleshooting Database Management Issuesp. 554
Troubleshooting Backup and Restoresp. 554
After a Database Restorep. 558
Troubleshooting Server Automationp. 559
Troubleshooting Replicationp. 565
Troubleshooting Linked Server Issuesp. 569
Part II Final Review
Study and Exam Prep Tipsp. 589
Study Tipsp. 590
Study Strategiesp. 590
Pre-Testing Yourselfp. 591
Exam Prep Tipsp. 591
The MCP Examp. 591
Exam Formatp. 592
New Question Typesp. 594
Putting It All Togetherp. 595
Final Considerationsp. 597
Fast Factsp. 599
Planningp. 599
Develop a security strategyp. 599
Develop a SQL Server capacity planp. 601
Develop a data availability solutionp. 602
Develop a migration planp. 603
Develop a replication strategyp. 603
Installation and Configurationp. 604
Install SQL Server 7p. 604
Configure SQL Serverp. 606
Implement full-text searchingp. 606
Configuring and Managing Securityp. 607
Assign SQL Server access through Windows NT accounts, SQL Server logins, and built-in administrator loginsp. 607
Create and assign SQL Server roles. Server roles include fixed server, fixed database, public, user-defined database, and applicationp. 607
Grant to database users and roles the appropriate permissions on database objects and statementsp. 608
Audit server and database activityp. 609
Managing and Maintaining Datap. 609
Create and manage databasesp. 609
Load data by using various methods. Methods include the INSERT statement, the SELECT INTO statement, the BCP utility, Data Transformation Services (DTS), and the BULK INSERT statementp. 609
Back up system databases and user databases by performing a full database backup, a transaction log backup, a differential database backup, and a filegroup backupp. 610
Restore system databases and user databases from a full database backup, a transaction log backup, a differential database backup, and a filegroup backupp. 610
Manage replicationp. 610
Automate administrative tasksp. 611
Enable access to remote datap. 612
Monitoring and Optimizationp. 612
Monitor SQL Server performancep. 612
Tune and optimize SQL Serverp. 612
Limit resources used by queriesp. 612
Troubleshootingp. 613
Diagnose and resolve problems in upgrading from SQL Server 6.xp. 613
Diagnose and resolve problems in backup and restore operationsp. 613
Diagnose and resolve replication problemsp. 613
Diagnose and resolve job or alert failuresp. 613
Diagnose and resolve distributed query problemsp. 613
Diagnose and resolve client connectivity problemsp. 614
Diagnose and resolve problems in accessing SQL Server, databases, and database objectsp. 614
Practice Examp. 615
Part III Appendixes
A Glossaryp. 645
B Overview of the Certification Processp. 655
Types of Certificationp. 655
Certification Requirementsp. 656
How to Become a Microsoft Certified Professionalp. 656
How to Become a Microsoft Certified Professional+Internetp. 656
How to Become a Microsoft Certified Professional+Site Buildingp. 657
How to Become a Microsoft Certified Systems Engineerp. 657
How to Become a Microsoft Certified Systems Engineer+Internetp. 659
How to Become a Microsoft Certified Solution Developerp. 659
Becoming a microsoft Certified Trainerp. 661
C What's On the CD-ROMp. 663
Top Scorep. 663
Exclusive Electronic Version of Textp. 663
Copyright Information and Disclaimerp. 663
D Using the Top Score Softwarep. 665
Getting Startedp. 665
Instructions on Using the Top Score Softwarep. 665
Using Top Score Practice Examsp. 666
Using Top Score Study Cardsp. 668
Using Top Score Flash Cardsp. 670
Summaryp. 671
Indexp. 673