MCSE Readiness Review, Exam 70-028: Administering Microsoft SQL Server 7.0 Book

Special Characters
and Numbers

@@IO_BUSY function, 157-58
@@SERVICENAME function, 303-4
% Privileged Time counters, 270, 277-80
% Processor Time counters, 270, 277-78
% Total Processor Time counters, 270, 277-78
% User Time counters, 271, 277-80
6.x (SQL Server version)
    replicate to, from 7.0, 329-30
    upgrading from, 297, 299-307

A

Access. See Microsoft Access
access
    add NT group to SQL, 111-12
    assignment, 103-4, 117-25
Access Methods objects, 256
accounts, login, 103-5, 107
    access assignment, 117-25
    assignment, 107, 123-24
    BUILTIN/Administrators, 108
    dbo, 117
    dropping, 131-32
    groups, 107
    guest, 117, 121-22
    sa (System Administrator), 107
    security folder, 119-20
    sp_addlogin, 109-10
    sp_grantdbaccess, 110, 119-20
    SQLAgentCmdExec, 122
    SQL login, 111-12, 119-20
    upgrading from 6.x, 303-6
    user, 119-20
administration, 163-64
    accounts, 103
    alerts, 163
    backup strategy, 31-42
    jobs, 163
    logins, 135-36
    minimizing, 25-26
    operators, 164
    replication, 215-25
    SQLAgentMail, 164
    SQL Mail, 89-90
    SQL Server Agent, 227-37
    transaction log maintenance, 55-56
    user access, 9-10
Administrators local group, 6, 11-12
Affinity Mask options, 271
Agent, 227-37
    alerts, 227, 231-32
    error log, 335-36
    History dialog box, 321
    jobs, 227
    notifications, 228, 232-33
    replication troubleshooting, 321
    SQLAgentMail, 227, 229
alerts, 163, 227, 231-32
    failing to fire, 337-38
    history values, 337-38
    notification, 335-36, 339-40
    transaction logs, 335-36, 339-40
    troubleshooting, 333-41
aliases, 133-34
ALTER DATABASE statement, 161, 166-67, 175-77
ANSI database options, 70, 83
    NULL default, 83-84, 173-74
    sp_dboption, 91-92
    warnings, 84, 91-92
ap_add_jobstep, 227
ap_addapprole, 129
AppleTalk network library, 19
application log, Windows NT, 235-36
applications
    custom data input, 131-32
    roles, 7, 104-5
    security, 5
audits, 105, 151-59
    @@IO_BUSY function, 157-58
    connections, 158
    DBCC, 157-58
    events, 153-54
    Index Tuning Wizard, 151, 155-56
    page allocation, 154
    Profiler, 151, 155-56
    queries, 153-56
audits (continued)
    Query Analyzer, 151, 155-56
    SHOWCONTIG, 157-58
    system stored procedures, 153-54
    Transact-SQL, 157-58
    Transaction-log space, 158
    user, 153-54
    Windows NT Server, 151
    xp_logevent, 154
    xp_trace_setqueryhistory, 153-54
authentication, 1, 5, 347-48
    Mixed mode, 5, 9-10
    network libraries, 19
    SQL Server Authentication login, 5
    Web site, 15
    Windows NT, 5, 9-10
automatic growth, 166
Available Bytes counters, 269, 273-74

B

BACKUP DATABASE Transact-SQL statement, 162, 190-91, 195-98
Backup Device objects, 256
BACKUP LOG Transact-SQL statement, 162, 190, 199-200
BACKUP statement, 35-38
backups, 2, 31-33, 35-38, 162, 189-92
    appending, 37-38, 195-96
    BACKUP DATABASE statement, 162, 190-91, 195-98
    BACKUP LOG statement, 162, 190, 199-200
    BACKUP statement, 35-38
    complete database backups, 189-90, 193-94, 197-98
    Create Backup Wizard, 162, 190
    creating file, 39-40
    db_backupoperator, 128
    devices, 189, 197-98, 203
    differential database, 39-42, 191, 197-98
    error messages, 309, 311-12
    filegroup backups, 191
    full database backup, 31, 39-40
    header information, 207-8
    INIT option, 37-38
    jobs, 227-28
    master databases, 169-70
    media header information, 195-96
    media sets, 35-36
    MEDIANAME option, 35-36
    minimizing time, 39-40
    multiple files, 209-10
    names, display, 207-8
    naming devices, 189
    NOINIT option, 37-38, 196-97
    NOUNLOAD option, 37-38
    point of failure, 195-96
    RECOVERY option, 163
    Restart option, 196-97
    restoring, 41-42, 162-63, 203-13
    roles, 193-94
    sp_add_job, 40
    sp_addumpdevice, 39-40
    speed, 35-36
    tape, 196-97, 301-2
    tape devices, 31, 37-38
    testarch, 40
    transaction log, 39-42, 190-91, 197-200, 203, 209-10, 317-18
    troubleshooting, 309-19
    Truncate Log On Checkpoint option, 317-18
    Upgrade Wizard, 301-4
    USE function, 39-40
    verifying, 203, 207-8
Banyan VINES network libraries, 19
bcp utility, 162, 179, 185-86
    command, 46
    sort order restore, 311-12
bd_backupoperator, 131-32
Binary order, 74
Books Online Web site, 15
Buffer Cache Hit Ratio counters, 257, 269, 275-76
Buffer Manager objects, 256-57
buffers, 269-70
bulkcopy. See bcp utility
bulk copy libraries, 46
BULK INSERT statements, 46, 162, 179, 187, 311-12

C

caches
    hit ratio, 269
    write, 27-28
Cache Manager objects, 256
capacity strategy, 2
catalogs, 98, 239
character sets, 71, 73-74
Client Network utility, 71, 88, 298, 355, 361-62
clients
    configuration, 71, 355-63
    connections, 355-63
    installing, 69
    lock-out, 85-86
    Windows 98, 359-62
clustering, 2, 32
    Clustering Service, 32
    Microsoft Search service, 100
columns
    error messages, 349-50
    linked server, 239-40
    NOT NULL, 173
communication topologies, 59
complete database backups, 31, 39-40, 162, 189-90,
193-94, 197-98
    restoring, 163, 203-4
components run as NT services, 6
configuration, 69-81
    ANSI, 83, 91-92
    Client Network, 71, 88
    CPU optimization, 253
    databases, 185-86
    data files, 161
    log files, 161
    memory optimization, 253
    network libraries, 87-90
    profiles, 89-90
    remote connections, 87-88
    replication, 163
    Server Network, 88
    sp_configure Allow Updates option, 86
    sp_dboption, 83, 91-92
    sp_processmail, 89-90
    SQLAgentMail, 70, 83, 89-90
    SQL Mail, 70, 83, 89-90
Configure Publishing and Distribution Wizard, 163, 215
connections
    audit, 158
    Client Network utility, 355, 361-62
    groups, 371-72
    IPX/SPX, 359-62
    MAKEPIPE utility, 357-58
    Multiprotocol Net-Library, 359-60
    Named Pipes Net-Library, 355, 357-60
    Net Library, 355, 359-62
    NET VIEW, 357-58
    protocol stack, 359-60
    READPIPE utility, 357-58
    Server Alias Configurations list, 355
    Server Network utility, 361-62
    TCP/IP, 359-60
    testing, 357-58
    troubleshooting, 298, 355-63
    Windows 95, 361-62
    Windows 98, 359-60
consolidate databases, 79-80
constraint, 134
Cost Threshold for Parallelism, 271
counters, 151, 253, 257
    Agent polling of, 337-40
    CPU, 254
CPUs
    counters, 254, 270-71
    Processor Queue Length counters, 278-80
    usage, 255
    usage rates, 277-80
Create Backup Wizard, 162, 190
CREATE DATABASE statements, 161, 165, 171-72, 177
Create Job Wizard, 227, 231-32
Create Publication Wizard, 163, 216
Create Trace Wizard, 263-65
creating
    backup devices, 162
    data files, 165
    databases, 161, 165, 169-74
    filegroups, 161, 165
    indexes, 244-45
    publications, 163
    transaction logs, 161, 165
cross-database dependencies, 51-52
Current Activity windows, 151, 255, 260
custom sort order, upgrading, 53-54

D

damaged database recovery, 85-86. See also restoring; troubleshooting
data
    availability, 2, 31-43
    file types, 17
    automatic file growth, 161
    creating files, 165
    fragmentation, 157-58
    loading, 46, 162, 179-86
    types changing, 55-56
Database Creation Wizard, 161, 165
databases
    access assignment, 104, 117-20, 123-24, 173-74, 203-4
    adding data files, 175-76
    backups. See backups
    bulk copying, 185-86
    columns, 173
    configuring, 185-86
    creating, 161, 165, 169-74
    DBCC (Database Consistency Checker), 255
    DBO Use Only option, 203-4
    defaults, 165
    filegroups, 161
    Filegrowth option, 171-72
    files, 17
    growth increment, 171-72, 176
    guest user accounts, 121-22
    jobs, 227-28
    linked, 164, 239
    master, 111-12, 117
    master database backup, 169-70
    model, 112
    name, display, 207-8
    objects, permission to modify, 11-12
    options, 161, 165, 173-74
    primary data files, 22, 165-66
    primary filegroups, 167, 171-72
    Properties window, 185-86
    recovering, 169-70
    restoring, 162-63, 203-13
    roles, fixed, 11-12
    secondary data files, 21-22, 165-66
    Select Into/Bulk Copy checkbox, 185-86, 191
    size, 18, 165-66, 171-72, 176
    sp_dboption, 161
    standby, 310-11
    SYSDATABASES table, 170
    tempdb, 112, 117
    transaction log files, 165-66
    Truncate Log On Checkpoint option, 317-18
Databases objects, 256
Data Transformation Services. See DTS (Data Transformation Services)
db_accessadmin, 128
db_backupoperator, 128, 193-94
db_datareader, 11-12, 128
db_datawriter, 11-12, 128
db_ddladmin, 11-12, 128, 133-34
db_denydatareader, 128
db_denydatawriter, 128
db_owner, 14, 128-29
    backups, 193-94
    pull subscriptions, 223-24
    push subscriptions, 221-22
db_securityadmin, 11-12, 128-29
DBCC (Database Consistency Checker), 151, 157-58, 255
dbcreator role, 9-10, 127
DBO Use Only option, 173-74, 203-4
dbo user accounts, 104
deadlocks, 263-64, 369-70
deadlocks/sec, 259-60
default filegroups, 18, 167
deleting devices, 51-52
DENY permission state, 140, 147-48
devices
    backups, 189, 197-98, 203
    deleting, 51-52
Dictionary order, 74
differential database backups, 31, 39-42, 162, 191, 197-98
    restoring, 163, 205
disk
    devices, 31
    mirroring, 18, 23-24
    space, upgrade requirements, 301-2
    striping, 18
    striping with parity, 25-26
diskadmin role, 9-10, 127
distributed file system, 17
distributed queries. See queries, distributed
Distributors, 163, 215
Distributor servers, 59
domains
    remote systems communications, 21-22
    user accounts, 11-12
DTS (Data Transformation Services), 46, 162, 187
    Designer, 162, 180
    Wizards, 162, 179, 181-84
    loading packages, 183-84
    security, 181-82
    sort order restore, 311-12
DTS Designer, 180

E

encryption, passwords, 145-46
Enterprise Manager, 161-64
    access assignment, 117
    account assignment, 107
    application roles, 129
    complete database backups, 190
    Configure Publishing and Distribution Wizard, 215
    creating databases, 165, 173-74
    Current Activity, 151
    differential database backups, 191
    enable full-text indexing, 99-100
    filegroup backups, 191
    fixed database roles, 128
    fixed server roles, 127
    group access assignment, 119-20
    job alerts, 228
    linked database login mappings, 239
    login creation, 104
    migrating Access objects, 55-56
    naming backup devices, 189
    optimization options, 270-71
    options, changing, 173-74
    query cost limits, 285
    remote administration, 85-86
    restricting database access, 203-4
    server registration, 85-86
    size of databases, 166
    truncating transaction logs, 190
    user-defined database roles, 129
    verify backup devices, 203
error messages
    ANSI, 84
    backups, 309, 311-12
    columns, 349-50
    jobs, 228
    Msreplication_subscriptions invalid object, 329-30
    notification failures, 335-36
    provider, 343
    queries, 343-53
    restoring, 309, 311-12
    send to EventLog service, 235-36
    sp_addmessage, 154
    storage, 233-34
    transaction log backup not allowed, 317-18
    warm-standby state, 311-12
EventLog service, 235-36, 337-38
Event Viewer, 151
events
    auditing, 153-54
    notifications, 233-34, 335-40
EXEC statement, 145-46
Executive settings
    transferring, 49-50
    upgrading, 77-78
exporting data. See data, loading

F

-f option, 86
failover, 33
fault tolerance, 23-26
file/filegroup backups, 31
filegroups, 17-18, 166-67
    administration, 26
    backups, 162, 191
    creating, 161, 165
    distribution on disks, 18
    primary, 167, 171-72
    restoring backups, 163
    size, 175-76
files, 17, 161
fixed database roles, 6, 11-12
fixed server roles, 6, 9-10, 127, 135-36
fragmentation, 157-58
Free Buffers counters, 270
Full-Text Indexing Wizard, 95
full-text searching. See Microsoft Search service
functions, Transact-SQL, 151

G

General Statistics objects, 256-57
GRANT permission state, 140, 143-44, 147-48
groups, 9-10. See also roles
    access assignment, 117-20, 131-32
    accounts, 107, 371-72
    Administrators local, 6, 11-12
    connections, 371-72
    names, determining, 113-14
    organization, 13-14
    roles as, 123-24
    security ID numbers, 113-14
    SQL login accounts, 111-12
growth, automatic, 166
guest accounts, 104

H

hackers, unauthorized login attempts, 261-62
history values, alerts, 337-38

I

I/O requests, maximum, 271
importing data. See data, loading
Index Create Memory option, 270
indexes, 154. See also Microsoft Search service
    creating, 244-45
    fragmentation, 157-58
    Index Tuning Wizard, 151, 155-56, 253, 256, 262-63
    linked servers, 240, 243-44
    memory, 270
Index Tuning Wizard, 151, 155-56, 253, 256, 262-63
INIT option, 37-38
INSERT statement, 46, 162, 179, 187
installation, 69-81
    character sets, 71, 73-74
    client network connection, 71
    custom, 97
    full-text searching, 70, 95
    instancing, 71
    Management Tools, 71
    Microsoft Search service, 95-100
    services account, 71-72
    setup initialization files, 72, 75-76
    SMS (Systems Management Server), 72
    sort order, 73-74
    unattended, 69, 71-72, 75-76
    Unicode, 69, 71
    Upgrade Wizard, 70
instancing
    installation, 71
    object, 257
IPX/SPX, 19, 359-60
ISQL utility, 358

J

jobs, 134, 163, 227-28
    defining, 227-28, 231-32
    scheduling, 228, 231-32
    SQLAgentMail, 229
    troubleshooting, 333-41

K

keys, linked servers, 240
KILL command, 368

L

latches, 256
Level 0 RAID, 18
Level 1 RAID, 18, 23-24
Level 5 RAID, 19, 25-26
libraries. See network libraries
linked servers, 1, 7, 9-10, 164, 239-51
    default mapping, 239
    logins, creating, 245-46
    OLE DB data source initialization failure, 345-46
    Oracle, 247-48
    registration, 351-52
    remote servers, 249-50
    security, 245-46
    troubleshooting, 345-52
loading
    bcp utility, 185-86
    data, 42, 162, 179-86
    Use a Query option, 183-84
locks, 154, 255-56
    lock-out clients, 85-86
    objects, 256
    wait time, 339-40
log files, 2. See also transaction logs
    automatic growth, 161
    upgrade, 297, 299-300, 305-6
    xp_logevent, 154
LoginFailed event class, 261-62
logins
    accounts, 103-4, 107-8, 119-20
    administration, 135-36
    default database, 111-12
    error messages, 347-48
    folders, 119-20
    group, 371-72
    guest, 121-22
    linked servers, 245-46
    LoginFailed event class, 261-62
    modes available, 5
    object, 119-20
    remove entries, 110
logins (continued)
    sp_addlogin, 109-10, 120
    sp_change_users, 110
    sp_grantbaccess, 110
    sp_grantlogin, 109-10
    sp_revokelogin, 110
    specified hours, 11-12
    SQL Server, 107, 109-12
    unauthorized attempts, 261-62
    upgrades, 303-6
    Windows NT, 9-10

M

-m option, 85-86
Macintosh clients, AppleTalk network library, 19
mail. See SQL Mail; SQLAgentMail
MAKEPIPE utility, 357-58
Management Tools, installation, 71
master databases, 111-12
    backup, 169-70
    user-defined error message storage, 233-34
master domain, remote systems communications, 21-22
Max Async IO options, 271
Max Degree of Parallelism, 271
Max Server Memory options, 270, 276
Max Worker Threads options, 270-71
MEDIANAME option, 35-36
media sets, 35-36
memory
    Available Bytes counters, 273-74
    Buffer Cache Hit Ratio counters, 275-76
    counters, 254, 269-70
    distribution database, 221-22
    indexes, 270
    optimization, 281-83
    Page Faults/sec counters, 281-82
    queries, 270
    shortage, 273-76
    Total Server Memory (KB) counters, 257, 270
Memory Manager, 256-57
merge replication, 59-62, 325-26, 329-30
metadata, linked servers, 164
Microsoft Access, migrating to SQL Server 7.0, 55-56
Microsoft Exchange Server, 89-90
Microsoft Search service, 6, 95-101
    clustering, 100
    index repopulation, 96
    installation, 70, 95-100
    sp_fulltext_database, 97-98
    sp_fulltext_table, 97-98
Microsoft Transaction Server, 7
Microsoft Windows 95
    connections, 361-62
    replication, 327-28
Microsoft Windows 98, connections, 359-62
Microsoft Windows NT
    Event Viewer, 151, 337-38
    login, 5
    remote systems communications, 21-22
    SID (security identifier), 371-72
migrating data, 45-57. See also upgrading
    methods available, 46
    Microsoft Access, 55-56
    named pipe method, 45
    settings, transfer, 49-50
    strategy, 3
    tape method, 45
    Upgrade Wizard, 49-50
    upgrading, 45, 49-52
    Web sites, 57
Min Memory Per Query option, 270
Min Server Memory option, 270
monitoring. See audits;