Cover image for MCSE administering SQL server 7
Title:
MCSE administering SQL server 7
Author:
Talbert, Brian.
Personal Author:
Publication Information:
Scottsdale, Ariz. : Coriolis, [2000]

©2000
Physical Description:
liv, 737 pages : illustrations ; 25 cm + 1 computer optical disc (4 3/4 in.)
General Note:
Includes index.
Language:
English
Title Subject:
ISBN:
9781576102695
Format :
Book

Available:*

Library
Call Number
Material Type
Home Location
Status
Item Holds
Searching...
QA76.3 .T35 2000 Book and Software Set Central Closed Stacks
Searching...

On Order

Summary

Summary

Written by an MCSE author team, this title covers SQL Server 7 configuration, implementation, and administration using clear and concise explanations of concepts and terminology. The CD-ROM features two complete practice exams featuring questions formatted like those on the actual exam.


Table of Contents

Exam Insightsp. xxv
Self-Assessmentp. xlvii
Chapter 1 Introduction To Microsoft SQL Serverp. 1
A Brief Introduction To SQLp. 2
Database Frameworksp. 2
The File Server Frameworkp. 3
The Host System Frameworkp. 3
The Client/Server Frameworkp. 3
Basic RDBMS Conceptsp. 4
A Brief History Of Microsoft SQL Serverp. 6
Transact-SQL (T-SQL)p. 7
SQL Server Featuresp. 8
Scalabilityp. 8
Reliabilityp. 8
Dynamic Managementp. 9
Performancep. 9
Internet Integrationp. 9
Replicationp. 10
Data Warehousingp. 10
NT Integrationp. 11
Back Office Integrationp. 13
SQL Server Componentsp. 13
MSSQL Server Servicep. 14
SQL Server Agentp. 14
Distributed Transaction Coordinator (DTC)p. 14
Microsoft Searchp. 14
Chapter Summaryp. 14
Review Questionsp. 15
Chapter 2 Installing SQL Server 7p. 19
The Flavors Of SQL Serverp. 20
Standard Editionp. 20
Enterprise Editionp. 20
Small Business Server Editionp. 20
Desktop Editionp. 21
Running SQL Server On Windows 95/98p. 21
Hardware Requirementsp. 22
Software Prerequisitesp. 23
Installation Of SQL Server 7p. 23
Installing Prerequisitesp. 24
Installing SQL Server Componentsp. 25
Choosing The Character Set, Sort Order, And Unicode Collationp. 33
Reviewing System Changesp. 41
Directory Structuresp. 41
Registry Modificationsp. 41
Installed Servicesp. 41
Unattended Installationp. 42
Troubleshooting Setupp. 44
Partition-Related Problemsp. 44
Service-Related Problemsp. 44
Recovering From A Previously Failed Installp. 44
Useful Troubleshooting Filesp. 45
Installing From An Imagep. 45
Chapter Summaryp. 45
Review Questionsp. 46
Hands-On Projectsp. 49
Case Projectsp. 54
Chapter 3 Exploring SQL Server 7p. 57
Reviewing The Microsoft SQL Server 7 Program Groupp. 58
Books Onlinep. 58
Client Network Utilityp. 58
Enterprise Managerp. 59
Import And Export Datap. 59
MSDTC Administrative Consolep. 59
Performance Monitorp. 59
Profilerp. 59
Query Analyzerp. 59
Readme.txtp. 60
Server Network Utilityp. 60
Service Managerp. 60
Uninstall SQL Server 7.0p. 60
Using Service Manager To Start And Stop SQL Serverp. 60
Getting Help With Books Onlinep. 61
Using Enterprise Managerp. 64
Registering And Connecting To Serversp. 65
Getting Comfortable With The Enterprise Manager Interfacep. 68
The Menusp. 69
Examining Objectsp. 71
Accessing Data With Query Analyzerp. 74
Forming And Executing A Queryp. 76
Opening And Saving Queriesp. 78
Command-Line Toolsp. 78
osql.exep. 78
sqlserver.exep. 80
sqldiag.exep. 81
regrebld.exep. 81
rebuildm.exep. 82
Chapter Summaryp. 82
Review Questionsp. 83
Hands-On Projectsp. 85
Case Projectsp. 88
Chapter 4 An Overview Of SQL Server Architecturep. 89
The Storage Enginep. 90
Database Objectsp. 90
Referencing Objects By Namep. 97
Physical Database Structurep. 97
Transaction Managementp. 100
The Lock Managerp. 102
The Relational Enginep. 103
Displaying The Estimated Execution Planp. 105
Memory Managementp. 108
Configuring Memory Settingsp. 111
Symmetric Multiprocessing Supportp. 112
Configuring Processor Usagep. 113
Network Connectivityp. 115
Interprocess Communicationp. 116
Transport Protocolsp. 118
Network Librariesp. 119
Network Library Configurationp. 121
Chapter Summaryp. 123
Review Questionsp. 124
Hands-On Projectsp. 127
Case Projectsp. 131
Chapter 5 Security Planningp. 133
Security Principalsp. 134
Physical Securityp. 135
System Securityp. 136
Application Securityp. 138
Overview Of SQL Server Securityp. 138
Login Authenticationp. 139
SQL Server Authenticationp. 139
Integrated (NT) Authenticationp. 140
Authentication Modesp. 141
Managing Login IDsp. 142
Managing SQL Server Authenticated Login IDsp. 143
Managing Integrated Loginsp. 145
Database User Accountsp. 148
Managing Database Usersp. 150
Managing Security With Rolesp. 151
Server Rolesp. 152
Database Rolesp. 155
Application Rolesp. 158
Chapter Summaryp. 160
Review Questionsp. 160
Hands-On Projectsp. 163
Case Projectsp. 166
Chapter 6 The Permissions Systemp. 169
Permissions System Basicsp. 170
Permission Classesp. 171
Implied Permissionsp. 171
Object Permissionsp. 171
Statement Permissionsp. 172
How The Permissions System Worksp. 172
The System Tablesp. 172
Permission Statesp. 175
Managing Permissionsp. 176
Using Enterprise Manager To Assign Permissionsp. 176
Using Transact-SQL To Assign Permissionsp. 178
Securing Actions Through Views And Stored Proceduresp. 181
Viewsp. 181
Stored Proceduresp. 182
Troubleshooting Ownership Chain Issuesp. 183
Chapter Summaryp. 185
Review Questionsp. 186
Hands-On Projectsp. 188
Case Projectsp. 190
Chapter 7 Client Connectivityp. 193
Application Frameworksp. 194
Intelligent Serverp. 195
Intelligent Clientp. 195
Multitieredp. 196
SQL Server Solutionsp. 197
Data Access Interfacesp. 198
Low-level Interfacesp. 198
High-Level Interfacesp. 200
Putting It Togetherp. 200
Supporting Data Accessp. 201
Remote Data Accessp. 204
Remote Serversp. 205
Linked Serversp. 206
Ad Hoc Accessp. 208
Chapter Summaryp. 209
Review Questionsp. 209
Hands-On Projectsp. 211
Case Projectsp. 214
Chapter 8 Managing Databasesp. 217
Managing Database Filesp. 218
Creating Databasesp. 219
Managing Database Sizesp. 224
Database Optionsp. 232
Options Affecting Null Characteristicsp. 233
Options Affecting Accessp. 235
Options Relating To Maintenancep. 236
Options Relating To Replicationp. 237
Other Optionsp. 237
Setting Database Optionsp. 239
Maintaining The Databasep. 240
Database Consistencyp. 240
Index Maintenancep. 243
Chapter Summaryp. 246
Review Questionsp. 246
Hands-On Projectsp. 249
Case Projectsp. 251
Chapter 9 SQL Server Backupp. 253
Why Is There A Need To Back Up?p. 254
Natural Disastersp. 254
Media Failuresp. 255
Server Failuresp. 255
Erroneous Updatesp. 256
Malicious Intentp. 256
Creating A Backup Planp. 257
What Should Be Backed Up?p. 258
When Will Backups Occur?p. 258
Where Will The Backups Be Stored?p. 259
How Long Will The Backups Be Retained?p. 260
How Should Backup Tapes Be Rotated?p. 260
How Long Will The Backup Take?p. 261
Who Will Perform The Backup?p. 261
How Will The Backups Be Tested?p. 261
How SQL Backup Worksp. 262
The Actual Backup Processp. 262
Backup Devicesp. 263
Backup Mediap. 263
Types Of Backupsp. 266
Full Database Backupp. 266
Transaction Log Backupp. 266
Differential Backupp. 268
Data File/Filegroup Backupp. 269
Choosing A Backup Strategyp. 270
Full Database Backups Onlyp. 270
Full Database Backup With Transaction Log Backupsp. 271
Full Database Backup With Differential And Transaction Log Backupsp. 272
File/Filegroup Backupp. 273
Performing The Backupp. 274
Creating Backup Devicesp. 274
Executing The Basic Backupp. 276
Performing A File Or Filegroup Backupp. 280
Performing A Transaction Log Backupp. 281
Using Enterprise Manager To Perform A Backupp. 283
Using Wizards To Establish A Database Maintenance Planp. 286
Chapter Summaryp. 291
Review Questionsp. 292
Hands-On Projectsp. 295
Case Projectsp. 297
Chapter 10 Disaster Recoveryp. 299
Disaster Recovery Planningp. 300
Problem Determinationp. 300
Assignment Of Responsibilityp. 300
Procedural Instructionsp. 300
Testing The Restore Processp. 301
Damage Assessmentp. 302
Determining The Extent Of The Problemp. 303
Automatic Recoveryp. 303
Using The Error Logs For Problem Determinationp. 304
Restoring A Databasep. 306
Obtaining Information From Existing Backupsp. 306
Verifying The Integrity Of The Backupp. 309
Performing The Full Database Restorep. 309
Performing A Differential Restorep. 312
Performing The Transaction Log Restorep. 312
Performing A File/Filegroup Restorep. 313
Using Enterprise Manager For The Restorep. 314
Disaster Recovery Scenariosp. 317
Media Loss With Loss Of A User Databasep. 318
Media Loss With Loss Of The Master Databasep. 321
Recovering From A Lost Master Without A Backupp. 323
Erroneous Or Malicious Data Modificationp. 323
Chapter Summaryp. 324
Review Questionsp. 324
Hands-On Projectsp. 328
Case Projectsp. 330
Chapter 11 Database Dependabilityp. 333
Toward A Dependable Systemp. 334
Availabilityp. 334
Reliabilityp. 334
Fault Tolerancep. 335
Securityp. 336
Data Integrityp. 336
Use Of Raid Technology To Increase Dependabilityp. 336
Raid 0p. 336
Raid 1p. 337
Raid 2p. 338
Raid 3p. 338
Raid 4p. 339
Raid 5p. 340
Hardware Vs. Software Solutionsp. 341
Those Other Raid "Levels"p. 341
SQL Server Recommendationsp. 342
Standby Serversp. 342
Using A Standby Server For Read Activityp. 342
Implementing The Standby Serverp. 343
Handling A Failure Of The Primary Serverp. 344
Failover Serversp. 345
How Cluster Server Worksp. 345
Active/Passive Vs. Active/Activep. 346
Dependability Through Replicationp. 347
File Replicationp. 347
Content Replicationp. 347
Chapter Summaryp. 348
Review Questionsp. 348
Case Projectsp. 351
Chapter 12 SQL Server Automationp. 353
The Value Of Task Automationp. 354
SQL Server Agent Overviewp. 354
Jobsp. 355
Alertsp. 355
Operatorsp. 356
Basic Agent Configurationp. 357
Mail Integrationp. 361
Messaging API (MAPI)p. 362
Creating Messaging Profilesp. 362
SQL Mailp. 366
SQL Agent Mailp. 369
Operatorsp. 370
Email Addressesp. 370
Pager Addressesp. 370
Net Send Addressesp. 371
Creating Operatorsp. 372
Jobsp. 374
Defining General Job Propertiesp. 374
Creating Stepsp. 375
Establishing A Schedulep. 377
Setting Notification Optionsp. 380
Multiserver Jobsp. 381
Job Managementp. 381
Job System Configurationp. 382
Alertsp. 384
SQL Server Errorsp. 384
Creating Alertsp. 389
Problematic Alertsp. 392
Setting Alert System Optionsp. 393
Using The Web Assistantp. 394
Choosing A Data Source and Destinationp. 395
Scheduling The Web Assistant Jobp. 396
Formatting The Web Pagep. 398
Chapter Summaryp. 402
Review Questionsp. 402
Hands-On Projectsp. 405
Case Projectsp. 408
Chapter 13 Data Migrationp. 411
What Is Data Migration?p. 412
Upgrading From Previous Versions Of SQL Serverp. 412
Requirements, Recommendations, And Restrictionsp. 412
One-Computer Upgradep. 413
Two-Computer Upgradep. 414
Data Transfer Methodsp. 414
Using The Upgrade Wizardp. 415
Traditional Data Transfer Techniquesp. 422
Select Intop. 422
Insert Select And Insert Executep. 423
Bulk Copy Program and Bulk Insertp. 424
Data Formatsp. 425
Using Format Filesp. 426
Using BCPp. 427
Using Bulk Insertp. 430
Data Transformation Servicesp. 431
DTS Packagesp. 431
Saving DTS Packagesp. 435
Using The DTS Wizardsp. 436
Using The DTS Designerp. 441
Chapter Summaryp. 447
Review Questionsp. 448
Hands-On Projectsp. 451
Case Projectsp. 454
Chapter 14 Replication Methodology And Designp. 457
Why Replicate?p. 458
Getting Data Closer To Usersp. 458
Providing Organizational Autonomyp. 458
Separating OLTP And DSSp. 459
Decreasing Conflictsp. 459
Factors Influencing Replication Methodologyp. 459
Autonomyp. 460
Consistencyp. 460
Latencyp. 461
Replication Terminologyp. 462
Publisherp. 462
Distributorp. 463
Subscriberp. 463
Publicationsp. 464
Articlesp. 464
Types Of Replicationp. 466
Snapshot Replicationp. 467
Transactional Replicationp. 468
Merge Replicationp. 470
Comparing Replication Typesp. 471
Replication Agentsp. 472
Snapshot Agentp. 472
Log Reader Agentp. 472
Distribution Agentp. 473
Merge Agentp. 473
Agents And Security Requirementsp. 474
Communication Topologiesp. 475
Central Publisher/Distributorp. 475
Central Publisher With Remote Distributorp. 476
Publishing Subscriberp. 477
Central Subscriber With Multiple Publishersp. 478
Multiple Publishers And Subscribersp. 480
Chapter Summaryp. 482
Review Questionsp. 482
Case Projectsp. 485
Chapter 15 Implementing Replicationp. 489
Replication Requirementsp. 490
Configuration Requirementsp. 490
Hardware Requirementsp. 491
Mixing Versionsp. 492
Installing Replicationp. 492
Setting Up The Publisher And Distributorp. 493
Creating Publicationsp. 499
Subscribing To Publicationsp. 507
Pushing New Subscriptionsp. 507
Pulling New Subscriptionsp. 511
Managing Replicationp. 513
Modifying Existing Publishers, Subscribers, And Distributorsp. 513
Modifying Default Agent Profilesp. 515
Modifying Publicationsp. 521
Modifying Pull Subscriptionsp. 523
Using Replication Monitorp. 524
Merge Replicationp. 527
Removing Replicationp. 531
Heterogeneous Replicationp. 531
Internet Replicationp. 532
Chapter Summaryp. 534
Review Questionsp. 535
Hands-On Projectsp. 538
Case Projectsp. 543
Chapter 16 SQL Server Performance Monitoringp. 547
Measuring Performancep. 548
Throughputp. 548
Response Timep. 549
Resource Utilizationp. 550
What Affects Performance?p. 551
Applicationp. 551
Infrastructurep. 553
Tools For Monitoringp. 556
Infrastructure Monitoring Toolsp. 556
RDBMS Monitoring Toolsp. 557
Application Monitoring Toolsp. 558
Monitoring Current Activity With Enterprise Managerp. 559
Using Performance Monitorp. 561
Performance Monitor Countersp. 561
Performance Monitor Viewsp. 565
Using SQL Server Profilerp. 571
Using The Create Trace Wizardp. 572
Trace Propertiesp. 574
Replaying A Tracep. 579
Running The Index Tuning Wizardp. 580
Using Transact-SQL To Monitor SQL Serverp. 580
sp_whop. 580
sp_who2p. 581
KILLp. 581
sp_lockp. 581
xp_trace_setqueryhistoryp. 582
xp_trace_flushqueryhistoryp. 582
xp_verp. 582
sqldiagp. 582
Chapter Summaryp. 583
Review Questionsp. 583
Hands-On Projectsp. 586
Case Projectsp. 589
Chapter 17 Sample Testp. 591
Chapter 18 Answer Keyp. 609
Appendix A Solutions To Review Questions And Case Projectsp. 625
Appendix B Objectives For Exam 70-028p. 673
Appendix C SQL Primerp. 677
Glossaryp. 705
Indexp. 717