|
|
 |
|
|
|
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.
|
|
|
|
|
|
|
|
|
Index
2PC (two-phase commit) transaction, 518, 519, 568, 571
80 folder, 64, 65
A
access. See also permissions
data, 357–58
database (See database access)
strategies for, 355–58
access information, viewing, 319–26
active/active clusters, 617
Active Directory, 11
integration of SQL Server service with, 372–74
replication and, 522
security account delegation and, 296
active/passive clusters, 617
ActiveX controls, replication and, 522
ActiveX scripts, jobs containing, 419
ActiveX Script task, in DTS Designer, 186
ActiveX Script transformation, 187
Add Destination Database dialog box, 598, 599
administration tools and utilities, 14, 15, 82
administrative access, fixed database roles for, 357
administrative tasks, 103, 361–472
automating, 411–72
alerts, 439–53
jobs, 418–39
multiserver jobs, 462–72
operators, 412–18
configuration tasks
registering SQL Server 2000 instances with SQL Server Enterprise Manager, 378–83
SQL Server Agent service, 375–78
SQL Server and SQL Server Agent service accounts, 374–75
SQL Server service (See SQL Server service, configuring)
Windows 2000 (and Windows NT 4.0), 362–64
linked servers, setting up, 389–96
maintenance tasks, 406–9
SQL Mail and SQLAgentMail, 385–89
SQL Server XML support in IIS, 399–405
administrator access (administrative access), permitting, 355, 357
administrators. See database administrators
Administrators group, local, installation of SQL Server 2000 and, 67, 69, 71, 73, 75
ADO (ActiveX Data Objects), 5
Affinity mask option, 371
Agent History Clean Up Agent, 517
Agent History dialog box, 574, 575
alerts, 377, 439–53
configuring, 442–46
defining, 440–42
log shipping alert thresholds, 601–2
performance condition, 441
responses to, 441–42
reviewing alert history and, 452–53
SQL Server event, 440
Allow Anonymous Subscription page, Pull Subscription Wizard, 568
Allow Anonymous Subscriptions page, Create Publication Wizard, 543
Allow updates option, 373
ALTER DATABASE statement, 162, 164–67
creating additional data and transaction log files with, 168–69
Analysis Services, 4, 13
features not supported on SQL Server 2000 Standard Edition, 7–8
anonymous subscriptions, 516, 517, 526, 541, 543, 544, 547, 553, 567, 568
ANSI_NULLS settings, 116
ANSI standard, SQL-92, 4
APIs (Application Programming Interfaces), 5
database, 15–16
AppleTalk ADSP Net-Library, 16
application responsiveness, 363, 364
application roles, 300, 351–55
activating and using, 353
creating, 352
creating and testing (practice), 353
applications, client, 2, 4–5, 14–18
Article Issues page, Create Publication Wizard, 540
asynchronous I/O, 12
Attach Database dialog box, 176
attaching databases, 175–77
authentication (authentication modes), 10, 23–24, 34, 44, 290–98
choosing, during setup, 34–35, 43
client Net-Libraries and, 291–93
Kerberos, 10, 291
Mixed Mode, 24, 34, 43, 83, 294
access and permissions strategy and, 356
selecting, 293–95
SQL Server, 24, 34, 291
upgrading from SQL Server 6.5 and, 111
Windows authentication compared to, 291–93
switching, 294
switching (practice), 295
Windows, 23, 24, 34, 35, 290–91, 293–94
Osql and, 83
SQL Server authentication compared to, 291–93
Authentication With SQL Server page, Create Login Wizard, 304
Authentication With Windows page, Create Login Wizard, 303
authorization (permission validation), 23, 25–26, 298–302. See also permissions
autogrowth (automatic file growth), 163–65
automatic file shrinkage, 165
automatic recovery process, 235–36
Auto Restart SQL Server Agent If It Stops Unexpectedly check box, 376–77
AWE (Address Windowing Extensions) API, 11
B
background tasks, 363
Backup Alert Threshold alert, 602
BACKUP DATABASE BACKUP LOG Transact-SQL statement, 334
BACKUP DATABASE command, 260–62
Backup Device Properties dialog box, 266
Backup Device Properties-New Device dialog box, 246
backup devices
creating, 246–48
defined, 245
retrieving backup media information from, 267
backup files, defined, 245
BACKUP LOG Transact-SQL statement, 260
backup media, defined, 245
backups. See also restoration of data
of all necessary databases, 230
defined, 245
differential database, 232
differential file, 233
differential filegroup, 233
file, 233
filegroup, 233
full database, 231–32
permanent backup devices, 246
selecting media for, 244–46
with SQL Server Enterprise Manager, 249–54
creating backup devices, 246
msdb database (practice), 259–60
retrieving backup media information (practice), 267
using Enterprise Manager directly, 256–58
terms used with, 244
transaction log, 130, 233–34, 262
with Transact-SQL, 260–64
types of, 228, 229, 231–35
Backup Set Properties dialog box, 270
backup sets
defined, 245
restoration of a database from disk failure and, 269–70
Backup Verification And Scheduling page, Create Database Backup Wizard, 253
Backup Wizard. See Create Database Backup Wizard
backward compatibility, for upgraded databases, 119
Banyan VINES Net-Library, 16
BCM (Bulk Changed Map) pages, 234
Bcp (Bulk Copy Program) command, 15, 182, 218, 219–21
blocking locks, 479
blocking problems, monitoring, 509–11
Books Online, SQL Server, 18
as shared component in multiple instances of SQL Server, 52
Browse For Folder dialog box, 528
B-tree structure, 125
buffer cache, 129
built-in functions (T-SQL globals), 486, 488
bulkadmin role, 299
Bulk Changed Map (BCM) pages, 234
bulk copy operations, optimizing, 222
Bulk Insert task, in DTS Designer, 185
BULK INSERT Transact-SQL statement, 182, 218, 219, 222
Bulk-Logged Recovery model, 130, 132
transaction log backups and, 233–34
C
C2 Audit Mode, SQL Profiler and, 484
CAL (Client Access License), 39
catalogs
database, 135–36
system, 135
chain of ownership issues, creating database objects and, 333–35
Change Job Target Servers dialog box, 466
Change The Default Database To check box, 398
character sets, 35
checkpoints (checkpoint process), purpose and function of, 129–30
checksum verification, byte-by-byte, 113
Choose A Data Source page, DTS Import/Export Wizard, 190–91
Choose A Destination page, DTS Import/Export Wizard, 192
Choose Destination Database page, Pull Subscription Wizard, 567
Choose Destination Database page, Push Subscription Wizard, 557
Choose Publication Database page, Create Publication Wizard, 536
Choose Publication page, Pull Subscription Wizard, 567
Choose Subscribers page, Push Subscription Wizard, 556
Client Access License (CAL), 39
client communication components, 17
client licensing modes, 38–39
client management tools, 41, 42, 52
as shared component in multiple instances of SQL Server, 52
Client Network Utility, 14, 38
clients (client applications), 2, 4–5, 14–18
client-server communication, 17
Client Tools Only installation type, 41–42
clustered indexes, 125, 126
clustering, failover, 615–19
CmdExec commands, jobs containing, 419
code pages, 35, 36
Code Page Selection page, SQL Server Upgrade Wizard, 114
collation, determining settings for, 35–37, 46
collation settings, creating user databases and, 146
Column Mappings And Transformations dialog box, 195–97
command-prompt utilities, SQL Server 2000, 14, 15
Completing The Copy Database Wizard page, 109
Completing The Create Alert Wizard page, Create Alert Wizard, 445
Completing The Create Database Wizard page, 150
Completing The Create Job Wizard page, 426
Completing The Create Login Wizard page, 305
Completing The Create Publication Wizard page, 544
Completing The Database Maintenance Plan Wizard page, 460
Completing The Move Database Wizard page, 109
Completing The Pull Subscription Wizard page, 571
Completing The Push Subscription Wizard page, 562
Completing The Register SQL Server Wizard page, Register SQL Server Wizard, 380
Completing The SQL Server Upgrade Wizard page, 117
Computer Name page, 57, 617
configuration (configuration tasks)
registering SQL Server 2000 instances with SQL Server Enterprise Manager, 378–83
during setup of SQL Server 2000, 32–41
authentication modes, 34–35
client licensing modes, 38–39
collation settings, 35–37
network libraries, 37–38
user accounts, 32–34
of SQL Server Agent service, 375–78
of SQL Server and SQL Server Agent service accounts, 374–75
of SQL Server service, 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
SQL Server XML support in IIS, 399–405
of Windows 2000 (and Windows NT 4.0), 362–64
Configure Publishing And Distribution Wizard, 526, 527, 533–35
Configure Publishing And Distribution Wizard page, 533
Connection Properties dialog box, DTS Designer, 203
connections
DTS, 183–84
SQL Server Agent service properties, 377–78
connection settings, configuration of, 365–67
Connectivity Only installation type, 41
Connect To SQL Server To Obtain Default Settings For The Additional Configuration Options check box, 398
console trees, 90
constraints, 21
Copy Column transformation, 187
Copy Database Wizard, 98–100, 104, 105, 107, 109
running, 104–5
copying, with DTS Import/Export Wizard, 188
Copy Table(s) And View(s) From The Source Database option, 193
entire tables and views, 194
objects and data between SQL Server databases, 197
Specify Table Copy Or Query page, 193
Copy Objects And Data Between SQL Server Databases option, 193
Copy SQL Server Objects task, in DTS Designer, 185
Cost threshold option, 371
counters
I/O object, 495, 497
memory object, 494–96
processor object, 495, 497
System Monitor, 480–81, 492–99
T-SQL global, 486, 488
CPU idle condition, scheduling jobs and, 421
Create Alert Wizard, 442–46
creating an event alert using (practice), 446–50
Create Database Backup Wizard, 249–51, 254
backing up the master database using, 254–59
CREATE DATABASE Transact-SQL statement, 155–57, 334
Create Database Wizard, 147–52
practice creating a database using, 151
Create Database Wizard dialog box, 150
CREATE DEFAULT Transact-SQL statement, 334
CREATE FUNCTION Transact-SQL statement, 334
Create Job Wizard, 422–28
practice, 426–28
Create Login Wizard, 302–7
Create ‘MSXOperator’ page, Make MSX Wizard, 463
Create New Transformation dialog box, DTS Designer, 206
CREATE PROCEDURE Transact-SQL statement, 334
Create Publication Wizard, 535–37, 544–46, 553–55
creating a merge publication with (practice), 553–56
creating a transactional publication with (practice), 545–53
CREATE RULE Transact-SQL statement, 334
CREATE STATISTICS statement, 406
CREATE TABLE statement permission, 336
Create Table task, in DTS Designer, 207
CREATE TABLE Transact-SQL statement, 334
CREATE TRIGGER Transact-SQL statement, 334
CREATE VIEW Transact-SQL statement, 334
Current Activity window, SQL Server Enterprise Manager, 485, 489, 504–6, 509–11
Current Connection Properties button, 86
Customize The Configuration page, Configure Publishing And Distribution Wizard, 529
Customize The Properties Of The Publication page, Create Publication Wizard, 541
Custom setup, 42, 43
installed components in, 47
selecting components in a, 46
D
data access, providing, 357–58
Data And Object Transfer page, SQL Server Upgrade Wizard, 112–13
database(s), 19–22
architecture of, 122–27
allocating space for tables and indexes, 124
data files, 122–24
storing index and data pages, 125
detaching and attaching, 175–77
master (See master databases)
msdb, 20
backing up, with SQL Server Enterprise Manager (practice), 259–60
physical structure of, 20–21
populating, 179–223
DTS transformations, 181
importing data, 180–81
transferring and transforming data, 180–82
restoring, 237–38
system, 19, 20
user (See user databases)
very large (VLDBs), 173, 174
file and filegroup backups for, 233
viewing the properties of (practice), 133–34
database access, 357
permissions, database-specific, 299–301
system stored procedures for granting, 316
practice, 318–19
viewing access information, 319–26
practice, 324–26
with SQL Server Enterprise Manager, 319–22
with Transact-SQL system stored procedures, 322–23
database administrators, 22–25. See also administrative tasks; Administrators group, local
permissions for administrator access, 355–56
database APIs, 15–16
database catalogs, 135–36
Database Consistency Checker statements (DBCCs) (See DBCC (Database Consistency Checker) statements)
Database Creation page, SQL Server Upgrade Wizard, 115
Database File Location page, Copy Database Wizard, 107, 108
Database Integrity Check page, Database Maintenance Plan Wizard, 456
Database Maintenance Plan History dialog box, 608
database maintenance plans, 453–62
viewing and modifying, 460–61
Database Maintenance Plan Wizard, 407, 454–60, 462
log shipping and, 596–604
database objects, 21. See also specific types of objects
changing ownership of, 335
creating, chain of ownership issues and, 333–35
database owner, 301. See also dbo role (db_owner role)
inherited permissions of, 331
system stored procedure for changing a, 316, 317
database owner permission, 300
Database Properties dialog box, 152–53
General tab, 152, 153
Transaction Log tab, 153, 154
DATABASEPROPERTYEX system function, 139, 161, 162
database roles
fixed, 300, 301
system stored procedures for managing, 316–17
user-defined, 300
database settings, 367–68
data destinations
in DTS Designer, 204
in DTS Import/Export Wizard, 192
Data Driven Query task, in DTS Designer, 186
data files, 122–24
allocating space for tables and indexes, 124–25
creating additional, 168–69
disk subsystem and, 172–74
manually controlling size of, 165–66
modifying size of, with SQL Server Enterprise Manager (practice), 166
recovery from data disk failure, 239–40
restoring
with SQL Server Enterprise Manager, 272
with Transact-SQL, 282
viewing the properties of (practice), 123–24
data marts, 4
data pages, storing, 125–26
data restoration. See restoration of data
data source names (DSN), 396–99
data sources, 180
DTS and, 182, 184
in DTS Designer, 203
DTS Import/Export Wizard and, 190–91
ODBC SQL Server, creating, 396–99
OLE DB, linked servers and, 389
Data Source Wizard, 397–98
data transfer tools, 182
Data Transformation task, in DTS Designer, 204–7
data warehouses, 4
Date Time String transformation, 187
db_accessadmin role, 301, 316
db_backupoperator role, 301, 334
DBCC (Database Consistency Checker) statements (Database Console Commands), 226–27, 486, 487
DBCC CHECKALLOC statement, 487
DBCC CHECKCATALOG statement, 487
DBCC CHECKCONSTRAINTS statement, 487
DBCC CHECKDB statement, 487
DBCC CHECKFILEGROUP statement, 487
DBCC CHECKTABLE statement, 487
DBCC CONCURRENCYVIOLATION statement, 487
DBCC DBREINDEX statement, 407
DBCC DROPCLEANBUFFERS statement, 487
DBCC OPENTRAN statement, 487
DBCC PROCCACHE statement, 487
DBCC SHOWCONTIG statement, 487
DBCC SHOW_STATISTICS statement, 487
DBCC SHRINKDATABASE command, 166
DBCC SHRINKDATABASE statement, 167
DBCC SHRINKFILE command, 166
DBCC SHRINKFILE statement, 165, 167
DBCC SQLPERF (LOGSPACE) statement, 487
DBCC TRACEON statement, 488
DBCC UPDATEUSAGE statement, 487
dbcreator role, 299
db_datareader role, 301, 331, 341, 357–59, 631
db_datawriter role, 301, 331, 341, 357–59
db_ddladmin role, 301, 331, 334, 335
db_denydatareader role, 301, 341
db_denydatawriter role, 301, 341
DB_ID system function, 139
DB-Library, 16
DB_NAME system function, 139
dbo role, 300
dbo role (db_owner role), 301, 316, 317, 331, 334–35, 338–41, 523, 532, 536–37
db_owner role (dbo role), 301, 316, 317, 331, 334–35, 338–41, 523, 532, 536–37
db_securityadmin role, 301, 317, 331, 335, 340, 357
DCM (Differential Changed Map) pages, 232
deadlocks, 479, 502
dedicated domain user account. See domain user account
Default Article Type dialog box, 539, 547
default filegroups, 171–72
default instances of SQL Server 2000
installing (practice), 48–49
version switching and, 96
defaults, in databases, defined, 21
Default Table Article Properties dialog box, 539, 547
Define Alert Notification Message page, Create Alert Wizard, 444
Define Alert Response page, Create Alert Wizard, 444
Define The Alert page, Create Alert Wizard, 442
Define The Database File Growth page, Create Database Wizard, 148
Define The Transaction Log File Growth page, Create Database Wizard, 149
DELETE permission, 341, 345
DENY permission, 333, 335, 336, 345, 352
DENY statement, 336
Desktop Engine, SQL Server 2000, 9
Detach Database dialog box, 175
detaching databases, 175–77
details pane, MMC console, 90
Developer Edition, SQL Server 2000, 9
Differential Changed Map (DCM) pages, 232
differential database backups, 232, 261
differential file backups, 233
differential filegroup backups, 233
dirty pages, 129
disaster
planning for all forms of, 227
recovery from, 239
Disconnected Edit feature, DTS Designer, 218
diskadmin role, 299
disk controllers, write-caching, 31–32
disk drives. See also disk failure; hard drives
backups to, 246
disk failure
recovery from, 239–40
restoration of an entire database from, with SQL Server Enterprise Manager, 268
disk striping, 171
disk subsystem, configuring, 172–74
Distributed Transaction Coordinator (DTC) (See MS DTC)
distributed transactions, DTS packages and, 213
Distribution Agent Profiles dialog box, 581
Distribution Agents, 516–20, 522, 524, 525, 528, 532, 533, 557–60, 570, 581
Distribution Clean Up Agent, 517, 519
Distribution Database Properties dialog box, 579
distribution statistics, updating, 406–7
Distributors, 516–31, 533–35, 556. See also Configure Publishing And Distribution Wizard
configuring properties of, 526–35
practice, 534–35
Replication Monitor for, 574
reviewing and modifying properties of, 578–79
domain user account, 33
DROP_EXISTING clause of the CREATE INDEX statement, 407
DSN (data source names), 396–99
DSO (Decision Support Objects), 5
DTC (Distributed Transaction Coordinator) (See MS DTC)
DTS (Data Transformation Services), 5–6, 182, 183–90
Copy Database Wizard and, 99, 103, 107
graphical tools of, 190–215 (See also DTS Designer; DTS Import/Export Wizard)
DTS connections, 183–84
DTS Designer, 184, 188, 189, 202–13
creating a new transformation in, 206–7
Disconnected Edit feature, 218
practice: creating a data transfer and transform package, 208–13
selecting a data source in, 203
selecting and configuring a data destination in, 204
tasks in, 185–86, 204
user interface for, 202–3
DTS Import/Export Wizard, 188, 190–202
copying entire tables and views with, 193–95
copying objects and data between SQL Server databases, 197
practice: transferring tables and data from the Northwind database with, 198–202
querying to specify the data to transfer in, 195–96
saving and scheduling packages, 197–98
selecting the data source in, 190–91
DTS Information Model, 216
DTS packages, 183–88, 215–18
creating new, with DTS Designer, 202–13 (See also DTS Designer)
execution utilities for, 217
extending the capabilities of, 213–14
logs and exception files, logs, 217
saving (storing), 188, 189, 197–98, 215–17
scheduling, 197–98
DTS package workflow, 188
DTS Query Designer, 195–96
Dtsrun command, 15, 189, 217
DTS Run utility, 189, 217
DTS tasks, 183, 184–87
precedence constraints for, 188
DTS transformations, 181
Dynamic Properties task, in DTS Designer, 186
E
editions of SQL Server 2000, 7–10
e-mail, notification of operators by, 412
Embedded SQL, 16
Enable Publication Databases page, Configure Publishing And Distribution Wizard, 531
Enable Publishers page, Configure Publishing And Distribution Wizard, 530
Enable Subscribers page, Configure Publishing And Distribution Wizard, 532
English Query, 6
Enter Active Script Command page, Create Job Wizard, 424
Enter Operating-System Shell Command page, Create Job Wizard, 423
Enterprise Edition, SQL Server 2000, 7, 11
Enterprise Evaluation Edition, SQL Server 2000, 9
Enterprise Manager, SQL Server, 14, 77, 89–93, 147. See also Create Database Wizard
alerts, creating, 448–49
application roles and, 352
automatic filegrowth settings configured with, 164–65
backups and, 249–54
creating backup devices, 246
msdb database (practice), 259–60
retrieving backup media information (practice), 267
using Enterprise Manager directly, 256–58
changing the SQL Server or SQL Server Agent service account after setup with, 80–81
configuring the SQL Server Agent service with, 375–78
configuring the SQL Server service with, 365–74
creating a database directly using (practice), 152–55
Current Activity window, 485
database settings and, 367
detaching and attaching databases with, 175
detaching and reattaching databases with, 175
generating a Transact-SQL Script to re-create the TSQLDB database (practice), 159–60
interface of, 89
jobs, creating, 428–32
linked servers, setting up, 390–92
and testing (practice), 393–96
logins created with, 302–14
Create Login Wizard, 302–7
using SQL Server Enterprise Manager directly, 308–14
MMC console, 89–92
object permissions and, 342–45
operators, creating, 413–14
registering SQL Server 2000 instances with, 378–83
sharing registration information, 383–84
scripting databases and database objects using, 158–59
size of data files modified with (practice), 166
size of transaction log files and, 167
viewing access information with, 319–22, 324–26
viewing database option settings with, 161–62
Enter Transact-SQL Statement page, Create Job Wizard, 422
error logs (error log files)
SQL Server Agent, 438
troubleshooting installation of SQL Server 2000 and, 60–61
error messages. See also alerts
searching for, 443, 444
system, 440
user-defined, 449–50
event alerts, 440–42
creating, with the Create Alert Wizard (practice), 446–50
event logs, 12
events, SQL Server, monitoring with SQL Profiler, 482–84
Event Viewer, troubleshooting installation of SQL Server 2000 and, 60
Exchange Server, Microsoft, 385
Execute Package task, in DTS Designer, 186
EXECUTE permission, 341, 343
Execute Process task, in DTS Designer, 186
Execute SQL task, in DTS Designer, 185, 204
EXIT command, in Osql, 82
Expired Subscription Clean Up Agent, 517
extents, 20, 124
F
failover clustering, 11, 615–19
fail-safe operators, 413
creating, 415–18
failure, planning for all forms of, 227
fault tolerance, 170
data restoration strategy and, 226
File And Printer Sharing For Microsoft Networks Properties dialog box, 362
File DSNs (data source names), 396, 397
filegroup backups, 233, 261
filegroups, 170–72
restoring, 238
with SQL Server Enterprise Manager, 272
with Transact-SQL, 282
FILE_ID system function, 139
filenames, logical and physical, 122, 128
FILE_NAME system function, 139
FILEPROPERTY system function, 139
files
added during installation, 64–70
backups of, 233, 261
log (See log files)
restoring, 238
File Transfer Protocol task, in DTS Designer, 186
fill factor, 407
fill factor option, 368
Filter Data page, Create Publication Wizard, 542
filtering, published data, 524–25
Filter Table Columns page, Create Publication Wizard, 542, 544
Filter Table Rows page, Create Publication Wizard, 542, 544
Find SQL Server Publications dialog box, 566
fixed database roles, 300, 301
for administrative access, 357
flushing the page, 129
folders, created during installation, 64–70
full database backups, 231–32, 260–61
Full Recovery model, 130, 132
transaction log backups and, 233–34
full-text catalogs, repopulation of, 103, 110, 625
full-text indexes, maintaining, 407–8
Full-Text Indexing Wizard, 408
Full-Text Search feature, 362, 363, 370
functions, system, querying system tables using (practice), 139–41
G
GAM (Global Allocation Map) page, 124
General tab, Database Properties dialog box, 152, 153
Generate SQL Scripts dialog box, 158–59
GET DATE system function, 139
Global Allocation Map (GAM) page, 124
global counters, T-SQL, 486, 488
GO command, in Osql, 82
Grant Access To Databases page, Create Login Wizard, 305
Grant Access To Security Roles page, Create Login Wizard, 304
GRANT permission, 345, 352
GRANT statement, 336
graphical tools, SQL Server 2000, 14
guest user accounts, 25, 300
H
hard drives
backups to, 246
optimizing performance and, 31
space requirements for SQL Server 2000, 30
Hardware Compatibility List (HCL), 30
hardware requirements, for installing SQL Server 2000, 28–32
exceeding requirements, 31–32
HCL (Hardware Compatibility List), 30
heaps, 125
HOST_NAME system function, 139
HTTP (Hypertext Transfer Protocol), 5
I
I2O (Intelligent Input/Output) hardware, 12
IAM (Index Allocation Map) page, 124–25
identifiers, rules for, 144
Idle CPU Condition group box, 377
IIS (Internet Information Services) server, 17
IIS Virtual Directory Management For SQL Server utility, 400, 404
Immediate Updating option, 518–20, 537, 546
importing, data, 180–82. See also data destinations; data sources; DTS Import/Export Wizard; transferring data
with Bcp, 220–21
Index Allocation Map (IAM) page, 124–25
indexes, 125
allocating space for, 124–25
clustered, 125, 126
Database Maintenance Plan Wizard and, 456
defined, 21
maintaining, 407–8
nonclustered, 125, 127
index pages, storing, 125–26
individual worker threads, 131, 132
information schema views, 140–42
inherited permissions, 331
Initialize Media page, Create Database Backup Wizard, 252
Initialize Subscription page, Pull Subscription Wizard, 569
Initialize Subscription page, Push Subscription Wizard, 559
Initialize The Destination Databases page, Database Maintenance Plan Wizard, 600
INSERT permission, 341, 345
installation of SQL Server 2000, 9, 10, 27–61. See also Setup program, SQL Server 2000
configuration options, 32–41
authentication modes, 34–35
client licensing modes, 38–39
collation settings, 35–37
network libraries, 37–38
user accounts, 32–34
hard drive space required for, 30
hardware requirements for, 28–32
practice installing a default instance of SQL Server 2000, 48–49
remote, 58, 57, 57–58
reviewing the results of, 64–75
files and folders added during installation, 64–70
permissions set on registry keys, 71–74
permissions that were set in the NTFS file system, 67–68
practice: reviewing permissions on registry keys, 73–74
practice: reviewing the files and folders that were created, 68–70
programs added to the Start menu, 74
registry keys added during installation, 70
troubleshooting, 58–61
error logs, 60–61
online troubleshooters, 59–60
practice: reviewing the SQL Server error log and the Windows Application log, 60–61
reviewing SQL Server 2000 program log files, 59
unattended, 53–56
creating setup initialization files, 53–55
practice, 55–56
installation types, 41–42
instances of SQL Server 2000
default
installing (practice), 48–49
working with, 52
multiple
installing, 50–51
shared components in, 51, 52
unique components in, 51
using, 51
named
error logs, 60
installing, 50–51
working with, 52
Osql used to query, 84–85
registering with SQL Server Enterprise Manager, 378–83
restoring a database to a different instance, 275–77
Internet applications, 14, 17
Internet Explorer 5.0 or later, 30
Internet Information Services (IIS) server, 17
I/O object counters, 495, 497
I/O performance, disk system and, 173
ISAPI DLL, 17
Isql, 15, 82
.ISS files (setup initialization files), creating, 53–55
Items To Synchronize dialog box, 585
J
Job Notifications page, Create Job Wizard, 425
jobs
creating, 418–39
Create Job Wizard, 422–28
implementing jobs, 418–22
multiple job steps and job responses, 420–21
ownership of jobs, 419–20
scheduling jobs, 421–22
with SQL Server Enterprise Manager directly, 428–32
with Transact-SQL, 433–34
types of job steps, 419
multiserver, 462–72
creating a multiserver job (practice), 470–72
creating jobs for target servers, 466–67
creating master and target servers, 463–66
monitoring jobs on target servers, 467–69
reviewing job history and information regarding, 434–39
practice, 437–39
K
Kerberos authentication, 10, 291
keys, defined, 21
L
languages, collation settings and, 35–37
lazywriter threads, 131, 132
.LDF filename extension, 128
leaf level, of indexes, 125
licensing modes, 38–39, 43–45
Lightweight pooling option, 371
Linked Server Properties-New Linked Server dialog box, 390–91
linked servers, setting up, 389–96
with SQL Server Enterprise Manager, 390–91
and testing (practice), 393–96
with Transact-SQL, 392–93
Local Area Connection Properties dialog box, 362
local system account, 33
locking activity, monitoring, 504–9
Locks/Object container, 504, 508, 509
Locks/Process ID container, 504, 507, 508
Log Detail dialog box, Copy Database Wizard, 109, 110
logical filenames
of data files, 122
of transaction logs, 128
logical structure of databases, 21–22
login ID (identifier), 23
logins, 302–26
SQL Server Enterprise Manager used to create, 302–14
Create Login Wizard, 302–7
using SQL Server Enterprise Manager directly, 308–14
Transact-SQL statements used to create, 314–19
server roles, adding or dropping logins to, 315
SQL Server logins, 315
viewing, with SQL Server Enterprise Manager, 319
Windows, 314–15
Logins container, 319
Log ODBC Driver Statistics To The Log File check box, 398
Logon page, SQL Server Upgrade Wizard, 113
Log Reader Agent, 516, 519, 520, 524
logs (log files)
DTS package, 217
Setup program, 59
transaction, 21
troubleshooting installation of SQL Server 2000 and, 60
virtual (VLFs), 128, 130
shrinking transaction log files and, 167
transaction log backups and, 234
log sequence number (LSN), 128
minimum (MinLSN), 130
log shipping
automating, 596–607
monitoring, 607–12
Log Shipping Pair Properties dialog box, 608
Log Shipping Schedules page, Database Maintenance Plan Wizard, 600
Log Shipping Thresholds page, Database Maintenance Plan Wizard, 601
Look For Publications page, Pull Subscription Wizard, 565
Lowercase String transformation, 187
LSN (log sequence number), 128
minimum (MinLSN), 130
M
mail profiles (messaging profiles), 385–88
Mail program in Control Panel, 386
mail service
of SQL Server Agent service, 386
of SQL Server service, 385
Maintenance Plan History page, Database Maintenance Plan Wizard, 459
maintenance plans for databases, 150, 453–62
viewing and modifying, 460–61
maintenance tasks, 406–9
Make MSX Wizard, 463–65, 470
Manage SQL Server Messages dialog box, 442–43
manual recovery, 237–39
MAPI (Messaging Application Programming Interface), Send Mail task and, 214
MAPI clients, 385–86
MAPI messaging profile, 385
master databases, 20
backing up (practice), 254–58
recovery from disk failure, 240
restoring, 285
master servers, creating, 463–66
Max degree of parallelism option, 371
Maximize Data Throughput For Network Applications option, 362–63
Max server memory option, 369
Max worker threads option, 371
MDAC 2.6 (Microsoft Data Access Components), 30, 103
.MDF filename extension, 122
MDX (Multidimensional Expressions) queries, 5
media family, defined, 245
media header, defined, 245
media retention option, 368
media set, defined, 245
media set name, 252
memory, 10–11
optimization of, 362–64
optimizing performance and, 31
recommended minimum requirements, 29
settings for, 368–70
memory objects, counters and, 494–96
Merge Agent, 516, 517, 520–22, 524, 525
Merge Agent In This Set Merge Agent Location page, Push Subscription Wizard, 558
merge publications, creating (practice), 553–56
merge replication, 520–21
viewing and resolving conflicts, 584–85
Message Queue task, DTS packages and, 214
Message Queuing (MSMQ), DTS packages and, 214
messaging profiles (mail profiles), 385–88
Meta Data Services, 6–7
manually upgrading tables and the repository database, 103
saving DTS packages to, 189, 216
Microsoft Cluster Service (MSCS), 615–19
Microsoft Data Access Components (MDAC), 30, 103
Microsoft Distributed Transaction Coordinator (MS DTC), 11, 13, 14, 52, 76
DTS packages and, 213
Microsoft Exchange Server, 385
Microsoft Management Console (MMC), 89–92
Microsoft Outlook, 386
Microsoft Replication Conflict Viewer dialog box, 584, 585
Microsoft Search service, 13, 14, 52, 76, 81, 370, 408
Microsoft Web site, troubleshooting information on, 59
Middle of String transformation, 187
minimum LSN (MinLSN), 130
Minimum setup, 42, 43
installed components in, 47
MinLSN (minimum LSN), 130
Min server memory option, 369
mixed extents, 124
Mixed Mode authentication, 24, 34, 43, 83, 294
access and permissions strategy and, 356
MMC (Microsoft Management Console), 89–92
MMC console
details pane, 90
toolbars, 90
MMC toolbar, 90
model database, 20
monitoring
current locking and user activity, 504–9
databases, 226–27
jobs on target servers, 467–69
log shipping, 607–12
resource usage, 489–97. See also counters
System Monitor, 491, 498–99
Task Manager, 489–91, 498–99
SQL Server performance and activity, 475–511 (See also performance monitoring)
blocking problems (practice), 509–11
current locking activity and user activity, 504–9
resource usage, 489–99
stored procedures, Transact-SQL batches, and user activity, 499–504
tools for, 479–89
MOVE option, restoration using, 283
MSCS (Microsoft Cluster Service), 615–19
msdb database, 20
backing up, with SQL Server Enterprise Manager (practice), 259–60
MS DTC (Microsoft Distributed Transaction Coordinator), 11, 13, 14, 52, 76
DTS packages and, 213
MSMQ (Message Queuing), DTS packages and, 214
Mssql folder, 66–69
Mssql$InstanceName folder, 66, 67
MSSQLServerADHelper service, 372
MSSQLServerAdHelper service, 76
MSSQLServer registry key, 71
MSSQLServer service. See SQL Server service
MSSQLServer services. See SQL Server services
MSXOperator, 463–65
multidimensional cubes, 4
multiple disks, placing user databases on, 170
multiple instances of SQL Server 2000, installing, 50–51
multiprocessor systems, 31
Multiprotocol Net-Library, 293
multiprotocol Net-Library, 16
Multi Server Job Execution Status dialog box, 469
multiserver jobs, 462–72
MYNAMEDINSTANCE registry key, permissions on, 72
N
named instances, upgrading to SQL Server 2000 and, 97
named instances of SQL Server 2000, 52
connecting to, 52
error logs, 60
installing, 50–51
Named Pipes Net-Library (network library), 16, 37, 38, 293
authentication and, 291–93
Name The Database And Specify Its Location page, Create Database Wizard, 147
Name The Database Files page, Create Database Wizard, 148
Name The Transaction Log Files page, Create Database Wizard, 149
.NDF filename extension, 122
Nested triggers option, 373
NET command, 78
NET NAME utility, 413
.NET platform, 2, 14
NET SEND, 413, 416
network drives, backups to, 246
network libraries (Net-Libraries)
authentication and client Net-Libraries, 291–93
configuration options, 37–38
Open Data Services and, 17
selecting, 47
networks, optimizing performance and, 31
New SQL Server Message dialog box, 449–50
New SQL Server Registration, 92, 378, 382
New Virtual Directory Properties dialog box, 400–403
nonclustered indexes, 125, 127
non-Unicode data, collation settings and, 35–37
NORECOVERY backup option, 594, 595
normalization, optimizing logical database design and, 22
notification of operators, 412–13
NTFS file system, permissions set by Setup program in, 67–68
NWLink IPX/SPX Net-Library, 16
O
Object Browser, 86
object permissions, 300, 340–50
granting and testing (practice), 346–50
SQL Server Enterprise Manager and, 342–45
Transact-SQL and, 345–46
types of, 341
viewing, 346
ODBC (Open Database Connectivity), 5, 15–16
ODBC Data Source Administrator utility, 396
ODBC drivers, 5, 396, 398
ODBC Microsoft SQL Server Setup dialog box, 398
ODBC SQL Server data sources, creating, 396–99
OLE DB, 5, 15
OLE DB data sources, linked servers and, 389
OLE DB Provider for SQL Server (SQLOLEDB), 17
OLE DB providers, 5
linked servers and, 389, 390
OLTP (online transaction processing) systems, 4
On Failure constraint, for DTS tasks, 188
Only If This Error Occurs option, 442
On Success constraint, for DTS tasks, 188
On Success precedence constraint, in DTS Designer, 207, 208
Open Data Services, 17
operating system commands, jobs containing, 419
operating systems, supported by different SQL Server editions, 9
operators
creating, 413
defining, 412–18
fail-safe, 413
creating, 415
notifications to, 412
optimizing performance, hardware considerations for, 31
Osql, 15, 82–85
arguments supported by, 83
Out Of Sync Alert Threshold alert, 602
ownership
chain of, creating database objects and, 333–35
of jobs, 419
Next
Last Updated: Friday, July 6, 2001 |
