|
|
 |
|
|
|
ALS Microsoft® SQL Server™ 2000 System Administration
|
|
|
Author
|
|
Microsoft Corporation
|
|
|
Pages
|
944
|
|
Disk
|
N/A
|
|
Level
|
Beg/Int
|
|
Published
|
07/03/2001
|
|
ISBN
|
9780735614260
|
|
|
|
|
|
|
|
|
Index continued
P
Page Free Space (PFS) page, 124, 125
pager, notification of operators by, 412–13
pages, 20
page splits, 407
PAL (publication access list), 524
Parallel Data Pump task, 184
Password Never Expires option, 33
passwords, saving DTS packages to SQL Server 2000 and, 215–16
performance bottlenecks, identifying, 477
performance condition alerts, 441
creating, with SQL Server Enterprise Manager directly, 450–52
Performance Monitor (Windows NT 4.0), 480
performance monitoring, 475–511
goals of, 476–78
methodology of, 476–79
resources and activities to monitor, 478
tools for, 479–89
DBCCs (Database Console Commands), 486
SNMP (Simple Network Management Protocol), 488
SQL Profiler, 482–84
SQL Query Analyzer, 484–85
SQL Server Enterprise Manager Current Activity window, 485
System Monitor, 480–81
system stored procedures, 486
Task Manager, 481–82
Transact-SQL, 485–88
performance objects, for tracking SQL Server activity, 480–81
Performance Options dialog box, 363, 364
permissions, 298–302, 329–59
additional permissions required for certain SQL Server 2000 functionality, 375
for administrator access, 355–56
conflicts, creating and testing (practice), 332–33
database-specific, 299–301, 330–51
creating and testing permission conflicts (practice), 332–33
implementing, 330–31
object permissions, 340–50
statement permissions, 333–40
inherited, 331
ownership of jobs and, 419–20
for replication, 524
server-wide, 298–99
set by Setup program in NTFS file system, 67–68
Permissions For Repldata dialog box, 528
permission validation. See authorization
per-processor licensing, 38
per-seat licensing, 39
Personal Edition of SQL Server 2000, 8
SQL Server authentication must be used with, 35
PFS (Page Free Space) page, 124, 125
physical filenames
of data files, 122
of transaction logs, 128
physical structure of databases, 20–21
point-in-time recovery, 239
populating databases, 179–223
DTS transformations, 181
importing data, 180–81
transferring and transforming data, 180–82
Post Download Instructions dialog box, 468
precedence constraints, for DTS tasks, 188
primary filegroups, 170–71
Priority boost option, 371
processadmin role, 299
Process Info container, 504–6
processor object counters, 495, 497
processors, 31
processor settings, 370
Product Support Services (PSS), 59
protocol encryption, 292
Provide Distribution Database Information page, Configure Publishing And Distribution Wizard, 530
Provide Target Server Description page, Make MSX Wizard, 464
proxy account, creating jobs and, 433
PSS (Product Support Services), 59
publication access list (PAL), 524
Publication Properties dialog box, 579, 582
publications
creating, 535–56
Create Publication Wizard, 535–37, 544–46, 553–55
merge publications (practice), 553–56
snapshot publications, 537–44
transactional publications (practice), 545–53
Replication Monitor and, 574–75
reviewing and modifying properties of, 579–80
public role, 300
published data, filtering, 524–25
Publisher And Distributor Properties dialog box, 578
Publishers, 515, 516–24. See also replication
configuring properties of, 526–35
pull subscriptions, 565–73
Pull Subscription Wizard, 565–73
push subscriptions, 556–65
Push Subscription Wizard, 556–65
Q
Query governor cost limit option, 373
querying
to specify the data to transfer, in DTS Import/Export Wizard, 195
system tables
directly (practice), 136–37
with information schema views, 140–42
with scripts, 136
with system functions (practice), 139–41
with system stored procedures (practice), 137–39
query windows, SQL Query Analyzer, 85
Queued Updating option, 518–20, 537, 538, 546
Queue Reader Agent, 516
QUIT command, in Osql, 82
QUOTED_IDENTIFIER settings, 116
R
RAID (redundant array of independent disks), 20, 21, 31, 595
hardware versus software, 170
levels, 170, 171
recovery from disk failure and, 239–40
restoration of a database from disk failure and, 269
RAID 0, 171, 174
RAID 1, 171, 173
RAID 5, 171, 173–74
RAID 10, 174
RAID 10 (1+0), 171
RAISERROR statement, 450
RAISERROR WITH LOG statement, 441
RDA (Remote Data Access), SQL Server CE and, 8–9
RDBMS (relational database management system), 2, 4
Read File transformation, 187
Read/Store User Independent check box, 383
rebuilding, the system databases, 287–88
Rebuild Master dialog box, 287
Rebuildm utility, 287
records, transaction log, 128, 129
recovery. See also restoration of data and databases
to an earlier point in time, 238–39
from disaster scenarios, 239–40
failover clustering and, 618
models of, 132–33
to a named mark, 239
transaction logs and, 128–30
recovery interval option, 368
RECOVERY option, 595
REFERENCES permission, 341
Regedt32.exe, 72
Regional Options dialog box, 37
Registered SQL Server Properties dialog box, 382
registering servers, 103
Register SQL Server Wizard, 378–82
registration
sharing registration information, 383–84
of SQL Server 2000 instances with SQL Server Enterprise Manager, 378–83
registry keys, permissions set by Setup program on, 71–74
Reinitialize Subscriptions Having Data Validation Failures Agent, 517
relational database applications, 15
relational databases. See databases
remote access option, 366
remote installation of SQL Server 2000, 58, 57, 57–58
Remote proc trans option, 367
Remote query timeout option, 367
Repldata folder, 525, 528, 534, 535
Repldata Properties dialog box, 528, 535
replication, 6, 513–91
description of, 514–17
implementation tools for, 522
implementing, 526–73
configuring Distributor and Publisher properties, 526–35
creating a publication, 535
pull subscriptions, 565–73
push subscriptions, 556–65
jobs containing replication tasks, 419
merge, 520–21
viewing and resolving conflicts, 584–85
monitoring and administering, 573–91
distributor properties, 578–79
practice, 577–78
publication properties, 579
replication agents, 580–82
Replication Monitor, 573–77
subscription properties, 582–89
Windows Synchronization Manager, 585–90
planning for, 523–26
filtering published data, 524–25
initial snapshot options, 525–26
security, 523–24
reasons for, 514–15
selecting a physical replication model, 521–22
snapshot, 517–19
terminology used with, 515–17
transactional, 519–20
types of, 515
upgrading from SQL Server 6.5 and, 113, 116
upgrading from SQL Server 7.0 and, 99
Replication Agent Profile Details dialog box, 581
replication agents, 516
reviewing and modifying, 580–82
Replication Agents Checkup Agent, 517
Replication Distributor Interface, 522
Replication Monitor, 573–77
repopulation, of full-text catalogs, 103, 110, 625
Reports To Generate page, Database Maintenance Plan Wizard, 458
repository, Meta Data Services, manually upgrading, 103
resource usage, monitoring, 489–97. See also counters
System Monitor, 491, 498–99
Task Manager, 489–91, 498–99
restoration of data and databases, 225–40. See also backups
acceptable length of time for, 227–28
to an earlier point in time, 238–39
automatic recovery process, 235–36
developing and implementing a plan for, 230–31
files and filegroups, 238
manual, 237–39
manual recovery options, 237–38
master databases, 285–87
process of, 235–40
with SQL Server Enterprise Manager, 268–81
backup sets, 269–70
complete database restoration (practice), 273–77
data files or filegroups, restoring, 272
different instance of SQL Server 2000, restoring a database to a, 275–77
disk failure, restoration of an entire database from, 268–72
point in time, recovery of a database to a, 277–81
transaction logs, 268–69
strategy for, 226–28
with Transact-SQL, 281–84
types of, 228–29
user databases, 264–85
sequence of backup files, 265
Restore Database dialog box, 269, 271, 272, 274, 275, 277, 278
RESTORE DATABASE Transact-SQL command, 281
RESTORE FILELISTONLY Transact-SQL command, 266
RESTORE HEADERONLY Transact-SQL command, 266
RESTORE LABELONLY Transact-SQL command, 266
RESTORE LOG Transact-SQL command, 281
REVOKE permission, 345, 352
REVOKE statement, 336
roles, 25
application, 300, 351–55
activating and using, 353
creating, 352
creating and testing (practice), 353
database
fixed, 300, 301
system stored procedures for managing, 316–17
user-defined, 300
server, 298–300
administrator access and, 356
modifying members of, 320–21
system stored procedures for adding or dropping logins to, 315
viewing, 320
rollbacks, 129
checkpoint process and, 130
rows, 20
ROWS_PER_BATCH hint, 222
S
SAN (system area network) protocols, 31
Save, Schedule, And Replicate Package page, DTS Import/Export Wizard, 197–98
Save Long Running Queries To The Log File check box, 398
saving, DTS packages, 188, 189, 197–98, 215–17
scatter-gather I/O, 12
Schedule The DTS Package page, Copy Database Wizard, 107, 109
scheduling
DTS packages, 197–98
jobs, 421–22
Scm (Service Control Manager), 15
SCP (Service Connection Point), 372
SCSI (Small Computer System Interface) disk subsystem, 172
Secondary Global Allocation Map (SGAM) page, 124
Secondary Server Log Shipping History dialog box, 608
security, 23–26. See also authentication; permissions
replication, 523–24
security account delegation, 11, 295–97
securityadmin role, 299
security decision tree, 24
Select A Destination Server page, Copy Database Wizard, 105, 106
Select An Authentication Mode page, Register SQL Server Wizard, 379
Select A Source Server page, Copy Database Wizard, 105, 106
Select A Subset Of Backup Sets option, 272
Select Authentication Mode For This Login page, Create Login Wizard, 303
Select Backup Destination And Action page, Create Database Backup Wizard, 252
Select Connection Option page, Register SQL Server Wizard, 379
Select Databases page, Database Maintenance Plan Wizard, 455, 596
Select Distributor page, Configure Publishing And Distribution Wizard, 527
Select File Format page, DTS Import/Export Wizard, 191, 192
Select Job Command Type page, Create Job Wizard, 422
Select Objects To Copy page, DTS Import/Export Wizard, 197
SELECT permission, 341, 342
Select Publication Name And Description page, Create Publication Wizard, 541
Select Publication Type page, Create Publication Wizard, 537
Select Related Objects page, Copy Database Wizard, 107, 108
Select Servers page, Database Maintenance Plan Wizard, 454, 596
Select Servers To Enlist page, Make MSX Wizard, 464
Select Source Tables And Views page, DTS Import/Export Wizard, 194–96
Select SQL Server Group page, Register SQL Server Wizard, 380
Select Wizard dialog box, 147
Send Mail task
in DTS Designer, 186, 207
DTS packages and, 214
serveradmin role, 299
Server and Client Tools installation type, 41, 42
Server Network Utility, 14, 37, 38, 64
server roles, 298–300
administrator access and, 356
modifying members of, 320–21
system stored procedures for adding or dropping logins to, 315
viewing, 320
Server Roles container, 319, 320
servers
linked, setting up, 389–96
with SQL Server Enterprise Manager, 390–91
and testing (practice), 393–96
with Transact-SQL, 392–93
master, 463–66
standby, 594–615
bringing online, 612–15
creating (practice), 604–7
log shipping and, 596–604
target
creating, 463–66
creating jobs for, 466–67
monitoring jobs on, 467–69
server settings, 370–74
service accounts, SQL Server and SQL Server Agent, 374–75
changing after setup, 80–81
Service Principal Name (SPN), 296–97
services, logon account for, 44
Services Accounts, 43
Services applet (Windows NT 4.0), 77
Services MMC (Windows 2000), 77, 80
Set Distribution Agent Location page, Push Subscription Wizard, 557
Set Distribution Agent page, Push Subscription Wizard, 558
Set Merge Agent Schedule page, Pull Subscription Wizard, 570, 571
Set Merge Agent Schedule page, Push Subscription Wizard, 558
Set Snapshot Agent Schedule page, Create Publication Wizard, 544
Setspn utility, 296
Set Subscription Priority Page, Pull Subscription Wizard, 571
Set Subscription Priority page, Push Subscription Wizard, 561
setupadmin role, 299
setup initialization files (.ISS files), creating, 53–55
Setup.iss, 54
Setup program, SQL Server 2000, 39–49. See also configuration, during setup of SQL Server 2000; installation of SQL Server 2000
collation settings and, 35–37
default configuration for SQL Server services, 75–76
default location for program and data files, 42
installation types, 41–42
log files, 59
selecting components of SQL Server, 42
starting, 40
types of setups, 42–47
upgrading from SQL Server 7.0 to SQL Server 2000 with, 98, 101–2
Set working set size option, 369
SGAM (Secondary Global Allocation Map) page, 124
shared memory Net-Library, 16
sharing, registration information, 383–84
shrinkage, of user databases, automatic, 165
Simple Network Management Protocol (SNMP), 488
Simple Recovery model, 130, 132–33, 233
SMP (symmetric multiprocessor) computers, 12
Snapshot Agents, 516–22, 524, 526, 528, 541, 544, 553, 560, 574, 576
Snapshot Delivery page, Pull Subscription Wizard, 569
snapshot folders, 517, 519–21, 524, 525, 528, 529, 531, 534, 535
snapshot publications, creating, 537–44
snapshot replication, 517–19
snapshots (snapshot files), initial, 525–26
SNMP (Simple Network Management Protocol), 488
sort order, collation settings and, 36
sp_ActiveDirectory_Obj system stored procedure, 373
sp_ActiveDirectory_SCP system stored procedure, 373
sp_add_alert system stored procedure, 452
sp_addapprole system stored procedure, 352
sp_add_jobschedule system stored procedure, 433
sp_add_jobstep system stored procedure, 433
sp_add_job system stored procedure, 433
sp_addlogin system stored procedure, 315
sp_addmessage system stored procedure, 440, 452
sp_add_notification system stored procedure, 452
sp_addrolemember system stored procedure, 317
sp_addrole system stored procedure, 317
sp_addserver system system stored procedure, 118
sp_ addsrvrolemember system stored procedure, 315
sp_addumpdevice system stored procedure, 247, 248
sp_altermessage system system stored procedure, 452
sp_attach_db system stored procedure, 122, 177
sp_changedbowner system stored procedure, 317
sp_change_monitor_role system stored procedure, 612
sp_changeobjectowner system stored procedure, 335
sp_change_primary_role system stored procedure, 612
sp_change_secondary_role system stored procedure, 612
sp_configure system stored procedure, 138, 299, 365
sp_dbcmptlevel system stored procedure, 119
sp_dboption system stored procedure, 138, 162
sp_defaultdb system stored procedure, 314, 315
sp_defaultlanguage system stored procedure, 314, 315
sp_denylogin system stored procedure, 314
sp_depends system stored procedure, 138
sp_detach_db system stored procedure, 176
sp_dropdevice system stored procedure, 247–48
sp_droplogin system stored procedure, 315
sp_droprolemember system stored procedure, 317
sp_droprole system stored procedure, 317
sp_dropserver system stored procedure, 118
sp_dropsrvrolemember system stored procedure, 315
Specify A Database Or Error Keywords page, Create Alert Wizard, 443
Specify Articles page, Create Publication Wizard, 539
Specify Backup Disk Directory page, Database Maintenance Plan Wizard, 456–57
Specify Column Delimiter page, DTS Import/Export Wizard, 191, 192
Specify DTS Package page, Push Subscription Wizard, 561
Specify Filter dialog box, 543
Specify Job Schedule page, Create Job Wizard, 424
Specify Publication page, Pull Subscription Wizard, 566
Specify Snapshot Folder page, Configure Publishing And Distribution Wizard, 528
Specify Subscriber Types page, Create Publication Wizard, 538
Specify Table Copy Or Query page, DTS Import/Export Wizard, 193
Specify The Database Backup Plan page, Database Maintenance Plan Wizard, 456
Specify The Log Shipping Destinations page, Database Maintenance Plan Wizard, 598, 599
Specify The Log Shipping Monitor Server Information page, 602
Specify The Transaction Log Backup Plan page, 457
Specify The Transaction Log Share page, Database Maintenance Plan Wizard, 598
Specify Transaction Log Backup Disk Directory page, Database Maintenance Plan Wizard, 458, 597
Specify Updating Subscription Login page, Pull Subscription Wizard, 568
sp_fulltext_service system stored procedure, 408
sp_fulltext_table system stored procedure, 408
sp_grantdbaccess system stored procedure, 301, 316
sp_grantlogin system stored procedure, 314
sp_helpdb system stored procedure, 138
sp_helpdevice system stored procedure, 247
sp_helpfile system stored procedure, 138
sp_help_jobhistory system stored procedure, 439
sp_help_jobstep system stored procedure, 439
sp_help_job system stored procedure, 439
sp_helplogins system stored procedure, 322, 323
sp_helpntgroup system stored procedure, 323, 326
sp_helprolemember system stored procedure, 323, 326
sp_helprotect system stored procedure, 337
sp_helpsrvrolemember system stored procedure, 323, 325
sp_help system stored procedure, 138
sp_helpuser system stored procedure, 323, 326
sp_lock system stored procedure, 138, 486
sp_monitor system stored procedure, 138, 486
SPN (Service Principal Name), 296–97
sp_password system stored procedure, 315
sp_resolve_logins system stored procedure, 613
sp_revokedbaccess system stored procedure, 316
sp_revokelogin system stored procedure, 314
sp_spaceused system stored procedure, 138, 486
sp_statistics system stored procedure, 138
sp_update_alert system stored procedure, 452
sp_update_jobstep system stored procedure, 433
sp_who2 system stored procedure, 486
sp_who system stored procedure, 138, 486
SQL-92 ANSI standard, 4
Sqlagent, 78
SQLAgentMail, 386
setting up, 388–89
SQLAgentMail has, notification of operators and, 412
SQL Agent Proxy Account dialog box, 420, 433
Sqlcli.iss, 54
Sqlcst.iss, 54
Sqldiag, 15
SQL-DMO (SQL Distributed Management Objects), 5
replication and, 522
Sqlins.iss, 54
SQL Mail, 385
setting up, 386–88
SQL Mail Configuration dialog box, 386, 387
Sqlmaint utility, 15, 462
SQL Profiler, 14, 77, 482–84, 499–504. See also SQL Server 7.0 Profiler
SQLProfilerSP_Counts template, 485
SQLProfilerStandard template, 485
SQLProfilerTSQL_Duration template, 485
SQLProfilerTSQL_Grouped template, 485
SQLProfilerTSQL_Replay template, 485
SQLProfilerTSQL_SPs template, 485
SQLProfilerTSQL template, 485
SQLProfilerTuning template, 485
SQL Query Analyzer, 14, 77, 82, 85–89, 484–85
interface of, 85–86
practice using, 87–89
querying system tables directly with (practice), 136
updating of distribution statistics and, 407
SQL Server 6.5, upgrading to SQL Server 2000 from, 111–19. See also upgrading to SQL Server 2000
backward compatibility level for upgraded databases, 119
choosing the appropriate upgrade process and method, 97–98
hardware and software requirements, 99–100
performing the upgrade, 111–17
selecting databases to upgrade, 115
troubleshooting, 118
SQL Server 6.5 SP5 (Service Pack 5), 51, 97, 99
SQL Server 7.0, upgrading to SQL Server 2000 from, 101–10
choosing the appropriate upgrade process and method, 98–99
hardware and software requirements, 100
Meta Data Services tables and the repository database, 103
online database upgrade, 98, 104–10
post-upgrade tasks, 103, 110
SQL Server 7.0 Profiler, 98
SQL Server 2000
administration tools and utilities, 15, 14, 14
Analysis Services, 2, 4, 13
features not supported on SQL Server 2000 Standard Edition, 7–8
application support, 4–5
client communication components, 14–18
components of, 2, 3, 12–14
for large enterprises, 5–7
editions of, 7–10
environment of, 2, 3
preparing to use, 63
reviewing the results of installation, 64–75
starting, stopping, pausing, and modifying services, 75–82
relational database engine of, 4
Setup program (See Setup program, SQL Server 2000)
upgrading to (See upgrading to SQL Server 2000)
SQL Server 2000 Analysis Services, 2, 4, 13
features not supported on SQL Server 2000 Standard Edition, 7–8
SQL Server 2000 services. See SQL Server services
SQL Server Agent error log, 438
SQL Server Agent Properties dialog box, 375–77, 388, 413, 415, 420
SQL Server Agent Properties-SelfPacedCPU dialog box, 433
SQL Server Agent service, 13, 14
configuring, 375–78
mail service of, 386
service accounts, 374–75
switching authentication modes and, 295
SQLServerAgent services. See SQL Server Agent services
SQL Server Agent services
configuration options, 32
default configuration for, 75–76
use of the term, 76
SQL Server authentication, 24, 34, 291
upgrading from SQL Server 6.5 and, 111
Windows authentication compared to, 291–93
SQL Server Books Online, 18
SQL Server CE (Windows CE Edition), 8–9
SQL Server Client Network Utility, 291, 292
SQL Server Connection group box, 378
SQL Server Enterprise Manager. See Enterprise Manager, SQL Server
switching authentication modes and, 294
SQL Server event alerts, 440–42
SQL Server Event Forwarding group box, 377
SQL Server Group container, 90
SQL Server Login Properties dialog box, 319–20
SQL Server Properties (Configure) dialog box, 365–68, 370, 372, 373
SQL Server Service Manager, 76–77
SQL Server services, 2, 13, 14, 63, 75–82
changing the service account used by, 80–81
configuring, 365–74
Active Directory, integration with, 372–74
connections settings, 365–67
database settings, 367–68
memory settings, 368–70
processor settings, 370
server settings, 370–72
default configuration for, 75–76
mail service of, 385
permissions set by Setup program
in NTFS file system, 67–68
on registry keys, 71–73
security account delegation and, 295–97
service accounts, 374–75
starting, stopping, and pausing, 76–80
switching authentication modes and, 294
use of the term, 76
SQL Server Upgrade Script Interpreter dialog box, 117
SQL Server Upgrade Wizard, 97–100
upgrading from SQL Server 6.5 and, 111–19
starting SQL Server Upgrade Wizard, 111–12
troubleshooting, 118
SQL Server Upgrade Wizard dialog box, 114, 115
Sqlservr, 78
SSEMDB Maintenance Plan, 461
SSL (Secure Sockets Layer), 11
network libraries and, 16, 38
Standard Edition, SQL Server 2000, 7–8
standby mode, restoring to, 283
STANDBY option, 594
standby servers, 594–615
bringing online, 612–15
creating (practice), 604–7
log shipping and, 596–604
starting, SQL Server services, 76–80
Start menu, programs added by Setup program to, 74
Start Required Services page, Pull Subscription Wizard, 571
Start Required Services page, Push Subscription Wizard, 562
statement permissions, 300, 333–40
changing object ownership and, 335
creating database objects and chain of ownership issues, 333–35
granting and testing (practice), 337–40
SQL Server Enterprise Manager used to grant, deny, or revoke, 335
Transact-SQL used to grant, deny, or revoke, 336
viewing, using Transact-SQL, 337
STATS_DATE system function, 139
stored procedures, 21. See also system stored procedures
monitoring, 499–504
replication and, 522
structured storage files, saving DTS packages to, 216
Subscriber Properties dialog box, 532
Subscribers, 516–26. See also replication
Subscription Properties dialog box, 582
subscriptions, 516
anonymous, 516, 517, 526, 541, 543, 544, 547, 553, 567, 568
Expired Subscription Clean Up Agent, 517
pull, 565–73
push, 556–65
Reinitialize Subscriptions Having Data Validation Failures Agent, 517
reviewing and modifying properties of, 582–89
transformable, 525, 526, 536, 538, 539, 561
Updatable Subscription options, 518, 520, 525, 537, 556, 561, 562, 565, 568, 569
sysadmin role, 299
replication and, 523
sysaltfiles table, 135
syscomments table, 136
sysconfigures table, 135
sysdatabases table, 135
sysdevices table, 135
sysindexes table, 136
syslockinfo table, 135
syslogins table, 135
sysmessages table, 135, 440
sysobjects table, 136
sysperfinfo table, 135
system catalogs, 135. See also system tables
System Configuration page, SQL Server Upgrade Wizard, 116
system databases, 19, 20
rebuilding, 287–88
System DSNs (data source names), 396, 397
system functions, querying system tables using (practice), 139–41
System Monitor, 12
monitoring resource usage with, 491–94
practice, 498–99
System Monitor (Windows 2000), 480–81
system stored procedures. See also entries starting with "sp_"
for changing a database owner, adding or dropping a security account, or creating a user-defined database role, 316
for logins
adding or dropping logins, users, or groups as permitted users, 316
adding or dropping logins to server roles, 315
SQL Server logins, 315
Windows logins, 314
querying system tables using (practice), 137–39
viewing SQL Server 2000 access information using, 322–26
system tables, 134–42
in database catalog, 135–36
querying
directly (practice), 136–37
with information schema views, 140–42
with scripts, 136
with system functions (practice), 139–41
with system stored procedures (practice), 137–39
in system catalog, 135
sysusers table, 136
T
Table Article Properties dialog box, 548
tables
allocating space for, 124–25
defined, 21
system (See system tables)
TABLOCK hint, 219, 220, 222
tape, for backups, 245–46
target servers
creating, 463–66
creating jobs for, 466–67
monitoring jobs on, 467–69
Task Manager, 481–82
monitoring resource usage with, 489–91
practice, 498–99
taskpad views, 90
TCP/IP Sockets Net-Library (network library), 16, 37, 38
authentication and, 291, 293
security account delegation and, 296
tempdb database, 20, 146
disk subsystem and, 174
text files, as data source
in DTS Designer, 204
in DTS Import/Export Wizard, 191–92
thick clients, 2
thin clients, 2
threads, operating system, 131–32
throughput, maximizing, for network operations, 362
time, restoring to a specified point in, 238–39
with SQL Server Enterprise Manager, 277–81
with Transact-SQL, 283
trace flags, 488
Trace Properties dialog box, 499–500
traces (trace files), 482, 499–504
trace templates, SQL Profiler, 484, 485, 500, 502
transactional message queuing, 214
transactional publications, creating (practice), 545–53
transactional replication, 519–20
transaction log records, 128, 129
transaction logs (transaction log files), 21
architecture of, 127–34
buffer cache, 129
checkpoint process, 129–30
dirty pages, 129
operating system threads, 131–32
recovery models, 132–33
rollback capacity, 129
truncations, 130
backups of, 130, 233–34, 262
creating additional, 168–69
disk subsystem and, 172–73
manually controlling size of, 167–68
recovery from user transaction log disk failure, 240
restoration of a database from disk failure and, 268–69
viewing the properties of (practice), 133–34
write-ahead, 129
Transaction Log tab, Database Properties dialog box, 153, 154
Transact-SQL, 4, 5
alerts, creating, 452
application roles and, 352
backups with, 249, 260–64
creating backup devices, 247–48
differential database backups, 261
file or filegroup backups, 261
full database backups, 260–61
practice, 262–64
detaching and attaching databases using, 176
jobs, creating, 433–34
linked servers, setting up, 392
monitoring SQL Server 2000 activity and performance with, 485–88
operators, creating, 415
restorations using, 281–84
system stored procedures (See system stored procedures)
viewing the contents of a backup device with, 266
Transact-SQL batches, monitoring, 499–504
Transact-SQL commands/statements. See also specific statements and commands
granting access to SQL Server 2000 and its databases using, 314–19
jobs containing, 419
logins created with, 314–19
permissions to run (statement permissions) (See statement permissions)
Transact-SQL scripts
object permissions and, 345–46
re-creating the TSQLDB database with, 159
SQL Server Enterprise Manager and, 158
Transfer Database Objects tasks, in DTS Designer, 185
transferring data, 180–82
with DTS graphical tools, 190–215 (See also DTS Designer; DTS Import/Export Wizard)
transformable subscriptions, 525, 526, 536, 538, 539, 561
Transform Data task, in DTS Designer, 204
Transform Published Data page, Create Publication Wizard, 538
.TRC filename extension, 482
triggers, 21
Trim String transformation, 187
troubleshooting
SQL Server 6.5 upgrade, 118
a SQL Server 2000 installation, 58–61
truncations, transaction log, 130
TSQLDB database, generating a Transact-SQL Script to re-create (practice), 159–60
T-SQL globals (built-in functions), 486, 488
Two-digit year cutoff option, 373
two-phase commit (2PC) transaction, 518, 519, 568, 571
Type Name And Description For Backup page, Create Database Backup Wizard, 251
Typical setup, 42, 43
installed components in, 47
U
unattended installation of SQL Server 2000, 53
Unconditional constraint, for DTS tasks, 188
UNDO_DatabaseName.DAT, 271
Unicode, collation settings and, 35–37
uniform extents, 124
Updatable Subscription options, 518, 520, 525, 537, 556, 561, 562, 565, 568, 569
Updatable Subscriptions page, Create Publication Wizard, 537
Updatable Subscriptions page, Pull Subscription Wizard, 568
Updatable Subscriptions page, Push Subscription Wizard, 561
Update Data Optimization Information page, Database Maintenance Plan Wizard, 455
UPDATE permission, 341, 342, 345
UPDATE STATISTICS statement, 406–7
updating distribution statistics, 406–7
version upgrades and, 103, 110
Upgrade, remove, or add installation option, 102
upgraded databases, backward compatibility level for, 119
Upgrade Wizard, SQL Server, 97–100
upgrading from SQL Server 6.5 and, 111–19
starting SQL Server Upgrade Wizard, 111–12
troubleshooting, 118
Upgrade your existing installation option, 102
upgrading to SQL Server 2000, 95–119
preparing for, 96–101
choosing the upgrade process and method, 97–99
hardware and software requirements, 99–100
multiple versions of SQL Server on the same computer, 96–97
tasks prior to commencing the upgrade, 100–101
from SQL Server 6.5, 111–19. See also upgrading to SQL Server 2000
backward compatibility level for upgraded databases, 119
choosing the appropriate upgrade process and method, 97–98
hardware and software requirements, 99–100
performing the upgrade, 111–17
selecting databases to upgrade, 115
troubleshooting, 118
from SQL Server 7.0, upgrading to SQL Server 2000 from, 101–10
choosing the appropriate upgrade process and method, 98–99
hardware and software requirements, 100
Meta Data Services tables and the repository database, 103
online database upgrade, 98, 104–10
post-upgrade tasks, 103, 110
Uppercase String transformation, 187
user accounts (user access), 25, 300
configuration options, 32–34
database access and, 357
user activity, monitoring, 499–508
user connections, 366
user databases, 19, 143–77
creating, 144–60
collation settings, 146
with CREATE DATABASE Transact-SQL statement, 155–57
Create Database Wizard, 147–52
directly using SQL Server Enterprise Manager, 152–55
example default properties, 145
identifiers, 144
with SQL Server Enterprise Manager, 147–55
managing size of, 163–69
automatic file growth, 163–65
automatic file shrinkage, 165
creating additional data and transaction log files, 168–69
manually controlling data file size, 165–66
manually controlling transaction log file size, 167
with SQL Server Enterprise Manager (practice), 166
placing on multiple disks, 170–77
restoring, 264–85
sequence of backup files, 265
selecting, in Copy Database Wizard, 107
setting database options, 161–63
user-defined database roles, 300
SQL Server Enterprise Manager used to create, 313–14
user-defined data types, 21
user-defined error messages, 449–50
user-defined filegroups, 171–72
user-defined functions, 21
User DSNs (data source names), 396–97
USER_ID system function, 139
USER_NAME system function, 139
user options, 366
Use The Failover SQL Server If The Primary SQL Server Is Not Available check box, 398
V
versions of SQL Server, multiple, on the same computer, 96–97
version switching, 96–97
version upgrade. See upgrading to SQL Server 2000
very large databases (VLDBs), 173, 174
file and filegroup backups for, 233
VIA GigaNet SAN Net-Library, 16
View Backup Media Contents dialog box, 266
views, defined, 21
virtual directory, IIS, 400–402, 404, 405
creating an (practice), 404–5
virtual log files (VLFs), 128, 130
shrinking transaction log files and, 167
transaction log backups and, 234
virtual memory, configuring, 363–64
virtual servers, on a server cluster, 617
Visual Basic
DTS packages created with, 189
programming templates, 214
Visual Basic files, saving DTS packages to, 216–17
Visual C++
DTS packages created with, 189
programming templates, 214
VLDBs (very large databases), 173, 174
file and filegroup backups for, 233
VLFs (virtual log files), 128, 130
shrinking transaction log files and, 167
transaction log backups and, 234
Vswitch.exe utility, 96
W
Welcome To The Create Alert Wizard page, 442
Welcome To The Database Maintenance Plan Wizard page, 454
Windows 98. See also Personal Edition of SQL Server 2000
Windows services not supported by, 33
Windows 2000
configuration of, 362–64
integration of SQL Server 2000 with, 10–12
Windows application log, troubleshooting installation of SQL Server 2000 and, 60
Windows authentication, 23, 24, 34, 35, 290–91, 293–94
Osql and, 83
SQL Server authentication compared to, 291–93
Windows CE Edition (SQL Server CE), 8–9
Windows groups, administrator access and, 356
Windows locale, 35
Windows logins, 314–15
Windows ME. See also Personal Edition of SQL Server 2000
Windows services not supported by, 33
Windows NT 4.0
configuration of, 362–64
integration of SQL Server 2000 with, 10–12
Windows operating system, collation settings and, 35–37
Windows Synchronization Manager
replication and, 522
resolving conflicts with, 585–90
practice, 589–90
WITH DIFFERENTIAL argument of BACKUP DATABASE command, 261
WITH GRANT OPTION clause, 345–46
WMI (Windows Management Instrumentation), 5
write-ahead transaction logs, 129
write-caching disk controllers, 31–32
Write File transformation, 187
X
XML (Extensible Markup Language), 5
support for, in IIS, 399–405
XPath queries, 5, 17, 402
xp_deletemail extended stored procedure, 385
xp_findnextmsg extended stored procedure, 385
xp_get_mapi _default_profile extended stored procedure, 386
xp_logevent extended stored procedure, 441
xp_readmail extended stored procedure, 385
xp_sendmail extended stored procedure, 385
Previous
Last Updated: Friday, July 6, 2001 |
