|
|
 |
|
|
|
Microsoft® SQL Server™ 2000 Resource Kit
|
|
|
Author |
|
Microsoft Corporation
|
|
|
Pages |
1200
|
|
Disk |
2 Companion CD(s)
|
|
Level |
Advanced
|
|
Published |
03/28/2001
|
|
ISBN |
9780735612662
|
|
ISBN-10 |
0-7356-1266-8
|
|
Price(USD) |
$69.99
To see this book's discounted price, select a reseller below.
|
|
|
|
|
|
|
|
|
Index continued
E
eBooks 1103
editions of SQL Server
Desktop Engine 53, 62
Developer Edition 51
Enterprise Edition 48
Evaluation Edition 52
obtaining 54
overview 47
Personal Edition 51, 62
Standard Edition 50
Windows CE Edition 52
EFS 115, 261
electronic version of book 1103
ELSE 181
EM algorithm 760
embedded SQL 206
emergency preparedness 404
employees See data centers
empty members within dimension 676
empty tables and indexes 235
Encrypting File System 115, 261
encryption 115, 260
ending point for date ranges 733
English Query
basic model 938–942
data analysis questions 945
deploying applications 946
entities 940, 943, 947
examples 937
expanding model 942–946
full-text search 945
help text 942
logs 949
OLAP model 945
overview 935, 938
performance 949
preparing database 939
Question Builder 947
relationships 940, 943, 947
sample questions 947
samples 946
saving question and results 941
SQL Project Wizard 936, 942
starting SQL project 939
Suggestion Wizard 941
testing questions 940, 944
troubleshooting 950
tuning 949
tutorials 938
updating 949
user interface 947
what’s new 40–42
enterprise data warehouse 747
Enterprise Edition 48, 54
Enterprise Evaluation Edition 52, 54
Enterpise Manager See SQL Server Enterprise Manager
entities in English Query 935, 940, 943, 947
entity integrity 136–140
enumerated data types 904
EQLog 949
.eqr file 941, 944, 947
equipment for data centers
change control 403
communication 398
emergency preparedness 404
facilities 395
hardware 397
quality assurance 403
error handling
INSTEAD OF triggers 1071
optimizing your system and 962
error rate of data mining model 765
ESQL 206
estimated execution plan for query 631
estimating storage for indexed views 1058
estimation in data mining 755–758
ETL (Extraction/Transformation/Loading)
advanced techniques 559–562
architecture 535
code samples 544–574
common tasks 539
components 540
database configuration 542
design considerations 534
development 536–539
dimension code samples 548–551, 559–562
DTS 540
elements 531–535, 538
extraction 531, 538
fact table code samples 522–558
file growth 542
functional elements 538
heterogeneous architecture 536
homogenous architecture 535
identifying and mapping data 537
job audit 563, 567
loading data 533, 539
meta data 533, 539
meta data code samples 563–574
Meta Data Services 540
OLE DB 540
overview 529
RAID 542
scheduling 539
server configuration 542
SQL Server Agent 540
staging database 540–543
staging tables 544
step audit 565, 570
stored procedures 540
success or failure confirmation 539
surrogate keys 544
tables for code examples 544
temporary tables 544
Transact-SQL 540
transformation 532, 538
views 540
ETLM 529
evaluation case set 764
Evaluation Edition 52, 54
event class DLL 663
events
data pump phases 663
monitoring 67
optimizing your system 957, 962
viewing to point in time 638
exaggerated data view in data mining 766
examples
See also samples
English Query 937
indexed views 1063–1069
Except function 723
excessive page splitting 622
Execution Location property 781
Execution Plan 631–636, 650
executive data warehouse users 480
EXISTS clause 159
EXPAND VIEWS option 1053
expanding data warehouses 498
expanding view 1054, 1064
expectation-maximization algorithm 760
EXPLICIT mode 930
explicit transactions 150
exporting data 66, 91
Extensible Markup Language
defined 921
EXPLICIT mode 930
generating AUTO formatted results 924
generating over Internet 923
generating result set 923
generating with SELECT statement 921
Oracle migration 191
reserved characters 925
retrieving data from SQL Server 924–935
special characters 926
stored procedures 927
templates 928
Transact-SQL queries 926, 929
Transact-SQL statement in URL 924
XSL style sheets 933
extents 111, 242
external connections 342
external function libraries 735
extracting data 531, 538
extraction/transformation/loading process See ETL
F
facilities See data centers
fact tables
ETL system samples 552–558
granularity 497
keys 496
measures 496
multiple 495
overview 495
partitions in relational data warehouse 511
failback 367, 389, 461
failed database implementation 230
failed services and failover clustering 370
failed systems 418, 464
failover
immediate updating with queued updating 435
licensing 61
log shipping 389
planning 461
replication vs. other methods 879
failover clustering
additional information 373
Address Windowing Extensions 350
backing up 368
configuration worksheets 353
configuring 348–354
connectivity 358
controller configuration 352
disk configuration 351
enhancements 16, 339
failed services 370
failover/failback strategies 367
file placement 351
hardware 349
Hardware Compatibility List 353
implementing 354–367
installation order 356
IP addresses 358
location issues 353
log shipping 391
memory 349, 360
MS DTC 357
multiple-instance clusters 362, 365
networking 352
nodes 359, 365, 371
operating systems 348, 355
overview 339, 347
prerequisities 355
quorum drive 342, 351
requirements 348
restoring 368
single-instance clusters 360
software 348
system availability 426, 447, 449
system performance 582
TCP/IP addresses 370
thresholds 367
troubleshooting 372
virtual servers 352, 354, 370
Windows Clustering 341–348
failure/success confirmation for ETL processes 539
Failures/sec counter 828
fault tolerance 245, 455, 586, 588, 815
federation
See also scaling out
backing up 1099
data-dependent routing 1096
enhancements 14
high availability 1100
INSTEAD OF triggers 1098
overview 1085, 1088
replication 1097
restoring 1099
unique columns 1098
feedback 12, 741, 770
Fetch Source Row phase (Implied) 660
FETCH 187, 193
fetching data 187, 193–197
fiber channel technologies 420
Fibre Channel 342, 351, 455
File bytes written/sec counter 830
File Control Operations/sec counter 837
File rows written/sec counter 830
filegroups
data storage design 254
database partitioning 598
default 599
Oracle migration 109, 111, 113
primary 598
user-defined 599
files
backups 113
data storage design 254
database partitioning 598
ETL system 542
optimizing your system 977
Oracle migration 111
security 296
system availability 456
fill factor option 999
FILLFACTOR 129, 607, 624
Filter function 714, 734
Filter hits/sec counter 827
filters 517
fire suppression 395
FIRE_TRIGGERS 1081
fit 758
five nines 443
fixed database roles 95, 266
fixed server roles 95, 116, 266
float expressions 1060
flushing 645
fn_trace_geteventinfo 958
fn_trace_getfilterinfo 958
fn_trace_getinfo 958
fn_trace_gettable 958
fn_virtualfilestats 958
FOR LOOP 182
FOR triggers 1069
FOR XML AUTO 921, 924
FOR XML clause 921
FOR XML EXPLICIT 921, 930
FOR XML RAW 921
Force Protocol Encryption option 261
ForceRememberChange method 884
FOREIGN KEY constraints 142, 1080
foreign keys 143, 496, 939
Forms (Access migration) 81
forward-only cursors 193
forward-scrolling cursors 195
fragmentation 244, 621
free buffers 645
Free Pages counter 645
free-form text searches 945
full database backups 113
FULL OUTER JOIN 165
Full Recovery Model 248, 578, 602
full-text indexes 945
full-text queries 42
full-text searches 15, 316, 345, 391, 945
function shipping 1092
functions
aggregate 172
BLOBs and 318
character 169
conversion 171
date 170
DistinctCount aggregate 27
number/mathematical 168
optimizing your system 958
Oracle migration 168–176
other row-level 171
user-defined 14, 175
G
General Statistics counter 406
Generate function 723, 725
GetApplicationName method 895
GetBcpBatchSize method 895
GetCatalog method 895
GetChangeType method 884
GetCodePage method 895
GetColumnAttributes method 884
GetColumnDatatype method 884
GetColumnName method 884
GetColumnStatus method 884
GetComparisonStyle method 895
GetConnectName method 895
GetDatabase method 895
GetDatabaseStatus method 895
GetDatasource method 895
GetDatasourcePath method 895
GetDatasourceType method 895
GetDBCreatedThisSession method 895
GetDestinationColumnValue method 884
GetDestinationConnectionInfo method 884
GetDestinationOwnerName method 884
GetErrorInfo method 884
GetExtendedError method 884
GetHandledStates method 882
GetHostName method 895
GetInternetAddress method 895
GetInternetNetwork method 895
GetLCID method 895
GetLogin method 895
GetLoginTimeout method 895
GetMajorVersion method 895
GetMinorVersion method 895
GetNumColumns method 884
GetPacketSize method 895
GetPassword method 895
GetPriorityWinner method 884
GetProviderName method 895
GetProviderString method 895
GetQueryTimeout method 895
GetResolverProcedureName method 884
GetRowGuidColName method 884
GetRowIdentifier method 884
GetRowset method 791
GetSchemas method 791
GetSecurityMode method 895
GetServerName method 895
GetServerRole method 895
GetSourceColumnValue method 884
GetSourceConnectionInfo method 884
GetTableName method 884
GetTableOwnerName method 884
GetUseInprocLoader method 895
Giganet VIA SAN implementation 397
global temporary procedures 144
global temporary tables 130
global variables 1026
globally unique identifiers 281, 487
GOTO 182
granting permissions
database access 273
digital dashboard browser 856
GRANT 133, 277, 286
to users and roles 276
WITH GRANT OPTION 286
granularity 496
graphical administration enhancements 19
Graphical Execution Plan 577
graphical tools
SQL Profiler 67, 102
SQL Query Analyzer 67, 103
SQL Server Enterprise Manager 101
GROUP BY clause 100, 1061, 1066
group permissions 115, 263, 273, 281, 284
grouping clusters 343
grouping statements 649
guest accounts 115, 300, 404, 864
GUIDs 281, 487
H
hard paging 639
hardware
See also data tier
optimizing your system 971
partitioning 595
RAID 586, 594
requirements 396
system availability 457
Hardware Compatibility List 352, 422, 457
Hardware Specialist 401
hasaccess column 283
hasdbaccess column 287
HCL 352, 422, 457
Head function 717
heaps 242
heartbeat 342, 358
help text 942
heterogeneity 534
heterogeneous architecture 536
heterogeneous data sources 91
heterogeneous databases 876
heterogeneous replication 203
hidden cube elements 27
HideOthers function 1026
hierarchies
ADO MD 785
dimensions 487, 686
drilling down 723
DSO 785
ranking or reordering members 727
high availability
99.x solutions 414
application design 445
backups 430, 449
capacity planning 452
clustering 422 – 428
combining SQL Server solutions 437
corporate Web site scenario 465 – 466
costs 450, 452
data access 445
data centers 457
database maintenance 430
DBAs 415
design steps 437
disaster recovery plan 458 – 464
disconnected users 418
disk configuration 420
disk drives 419
disk storage 455
e-commerce Web site scenario 467 – 469
failback plan 461
failover clustering 447, 449
failover plan 461
file placement 456
five nines 443
goals 443
hardware 418 – 422, 452 – 458
load balancing 449
log shipping 435 – 449
manual procedures 417
memory 453
Message Queuing 436
mobile users 418, 432
most popular solution today 420
network design 457
Network Load Balancing 428, 438
operating systems 452
operations plan 416
overview 413
partitioned database scenario 470
partitions 1099
processors 452
product support 458
RAID 419, 420, 455
redundancy 416, 430, 432, 435
replication 432, 448
restoring 430, 449
risk management 414
SANs 420
scenarios 439, 465 – 471
segmentation 417
small company scenario 471
software 422 – 429
SQL farms 432
staffing 415, 462
system failures 418, 464
technology options 447, 449
two-phase commit 431
uptime 414
viewing database placement 427
VLDB with active connections scenario 439
Web scenario 439
HighMemoryLimit registry setting 805, 824
high-speed interconnects 398
histogram information 774
historical data 742, 819
hits per day 85
HOLAP 817
homogenous architecture 535
horizontal partitioning 599, 605
hot standby failover solution 879
HTC file 844, 845, 849
HTM file 844, 843, 850
HTTP 795
HTTPS 795
HugeLevelThreshold registry setting 805
humidity control 395
hybrid OLAP 817
I
I/O
disk I/O parallelism 595
indexes 610, 612, 619, 621
inequality operators 648
max async IO option 577
monitoring 642
nonsequential I/O operations 585
optimizing your system 973
partitioning 595
RAID 586 – 595
read-ahead 235, 647
sequential I/O operations 585
statistics 630
System Monitor 636
system performance 576
table scans 240
transfer rates 584
worker threads 644
IAM 647
ICommon interface 788
IConnectionInfo interface 884, 895
IConnectionInfo interface methods 895
ID attribute 862
IDBSchemaRowset interface 791
identifiers 119
identifying data for data mining 749
identifying data for ETL system 537
identity columns 139, 164
IDENTITY columns 98
IDENTITY keys 487
IDENTITY property 75, 139
IDENTITYCOL 98
IF 181
IGNORE_DUP_KEY 130
IIf function 736
IIS 795, 857, 923
image columns 242
image data type 305, 313
immediate data analysis 745
immediate updating 434, 871
implementation scripts 218, 220 – 224
implicit transactions 92, 150
importing data 66, 91
in disk order operations 585
increasing density of occurrences (oversampling) 753
incremental data loads 602, 607
incremental partition processing 521
incremental update option 821
Index Allocation Map 647
Index bytes counter 828
index create memory option 243
Index reads counter 828
index scans 243
Index Tuning Wizard 499, 577, 626, 1057
indexed views
Access migration 68, 72
aggregate functions 1063
aggregations 1051, 1064, 1065
applications 1052
columns from other tables 1065, 1066
creating 1058 – 1062
designing 1054 – 1058
deterministic functions 1060
examples 1056, 1063 – 1069
expanding view 1054, 1064
GROUP BY clause 1062, 1067
Index Tuning Wizard 1057
joins 1052, 1067
maintenance 1057
Oracle migration 125
overview 1049
performance benefits 1051
queries 1052, 1055, 1063 – 1069
query optimizer 1053
requirements 1061
result set 1049
search conditions 1066
search predicate 1068
SET options 1059
size 1056
storage 1058
syntax restrictions 1062
system performance 617 – 619
indexes
architecture 611
building 243
clustered indexes 611
computed columns 615
correct placement 627
covering indexes 619
creating 609
data warehouses 499
DBCC DBREINDEX 623
DBCC INDEXDEFRAG 622
defragmenting 244, 622, 623
DROP INDEX / CREATE INDEX 623
DROP_EXISTING 623
FILLFACTOR 607, 624
fragmentation 621
growth 242
intersection 610
maintenance 621 – 625
manually creating 623
nonclustered indexes 613
open space on index 624
optimizing your system 966
option values 1005
Oracle migration 111, 118, 123, 126 – 130
overview 609
PAD_INDEX 607, 624
page splitting 621, 624
pages 621
parallel operations 620
performance 243, 409
rebuilding index 623
reorganizing 244
secondary indexes 1052
selecting 619
size 611
statistics 652
Sybase migration 95
system performance 576, 608
types 608
unique indexes 615
what’s new 16, 235
index-organized tables 126
Indxview.lit 1104
inequality operators 648
information data warehouse users 479
information systems change management
contingencies 230
development environment 215 – 225
goals 214
levels of control 214
production implementations 227 – 231
QA environment 225 – 227
release readiness review 229
Initial Catalog property 781
initial data loads 602
Initialization property 1045
InitWorkerThreads registry setting 809
inline-table function 176
INNER JOIN 165
in-row BLOBs 306, 309
in-row text 235, 242
INSENSITIVE option 186
INSERT 160, 1069
Insert Batch Size property 662
Insert Failure phase 661
INSERT INTO 762
Insert Row phase (Implied) 661
Insert Success phase 661
inserted tables 1071
InsertRow method 884
installation security 255
installing stored procedures 996 – 998
instances of SQL Server 345, 579, 582
INSTEAD OF triggers
bcp utility 1081
BULK INSERT 1081
computed columns 1077
constraints vs. triggers 1080
customizing error messages 1071
designing 1079
NOT NULL values 1077
order of trigger execution 1081
overview 1069
partitioned views 1079
performance issues 1081
restrictions 1080
updatable views 1074, 1076
integrated security 291
integrating add-ins 33
integrity
domain 140
entity 136 – 140
Oracle migration 135 – 150
referential 142
user-defined 144 – 149
interactive digital dashboards
See also digital dashboard browser
building 846 – 853
code samples 841, 844, 848
creating 858 – 861
Customerlist.htc 851
Customerlist.htm 849
Customerlist.xml 850
Customerlist.xsl 850
defining dashboard 846
defining parts 847
Digital Dashboard Resource Kit 843
.htc file 844, 845
log in 861
Orderchart.htm 851
Orderchart.xsl 852
overview 839
physical directory 844
sample files 861 – 865
software required 842
testing 848, 861
virtual directory 844
.xsl file 845
interconnects 398
Internet
additional information 11
Analysis Services 795
Oracle migration 210
TerraServer 313 – 316
XML 923
Internet Connector Upgrade Advantage 59
Internet Guest Account 865
Internet Information Services 795, 857, 923
Internet Service Providers 410
INTERSECT operator 159
intranet 795
invalid user error 220
IO Data Bytes/sec counter 836
IO Data Operations/sec counter 836
IO Other Bytes/sec counter 835
IO Other Operations/sec counter 835
IO Read Bytes/sec counter 835
IO Read Operations/sec counter 835
IO Write Bytes/sec counter 835
IO Write Operations/sec counter 835
IP addresses 343, 357
IRepIRowChange interface 884
IRepIRowChange interface methods 884
isolation levels 93, 151
ISPs 410
IsUser method 1041
Item function 734
ItemByID method 1029
IVBCustomResolver interface 882, 915
J
Jet-SQL statement 73
job audit for ETL system 563, 567
jobs and log shipping 378, 386
joins
filters 871
indexed views and 1052, 1067
Oracle migration 165, 199
Sybase migration 99
junk dimensions 495
K
KEEP_REPLICATION 377, 390
Kerberos 17, 257
key columns 235
keys
date and time dimension keys 490
fact tables 496
GUIDs 487
IDENTITY 488
member 689
partitions 510
surrogate keys 487, 499, 544
keywords 90, 179
KILL command 235, 252
knowledge data warehouse users 479
known outcomes 756
L
language columns 283
large cell sets 785
large data See BLOBs
large fact tables 495
large memory support 235
Last Query object 828
LastPeriods function 734
latency 381, 398, 434, 457
lazy latching 241
lazy processing 821
lazy writer 645
Lazy Writes/sec counter 645
LEFT OUTER JOIN 165
levels in RAID 588 – 592
levels of dimensions 689, 724
libraries See Net-Libraries
licensing
Desktop Engine 61
failover cluster configuration 60
mixed-license environments 60
model changes 57
multi-instance configuration 60
multi-tier environments 61
overview 57
Personal Edition 61
Processor License 58
selecting 59
Server License 57
switching 62
Upgrade Advantage 58
lightweight pooling option 641, 999
linked cubes 26, 696
ListAvailableSQL Servers method 1025
listener threads 807
load balancing
federation 1088
log shipping 389, 438
system availability 428, 449
system maintenance 409
load speed 505, 513, 515
load testing 626
loading data 533, 539, 602 – 607
loading image file to BLOB column 336
local cubes 32
local Distributors 873
local monitoring for data tier optimization 963
local partitioned views 1090
local system accounts 295
local temporary procedures 144
local temporary tables 129
local user accounts 295
locating data for data mining 747
Lock Page in Memory option 580
locking
bulk data loads 604
concurrency 240
default locking 153
DSO 787
dynamic locking 153
enhancements 235
optimizing your system 967
Oracle migration 151 – 156, 165
row-level locking 97
Sybase migration 95
locking counter 407
Locks object 829
locks option 999
Log File property 781
log manager 646
log marks 235, 246
log shipping
application code 391
backing up 386, 388
components 373
connectivity 381
consistency checking 387
data synchronization 379
enhancements 19
failback 389
failover 389
failover clustering 391
full-text search 391
implementing 378, 379
installation considerations 382
interoperability between versions 387
limitations 373
load balancing 390
log_shipping_monitor user 377
login synchronization 386
monitor server 391
monitoring 386
overview 373
preparation worksheet 383
process 377
removing 386
replication 390
reporting 388
restoring 386, 388
secondary server switched to primary server 379
server capacities 385
server location 380
stored procedures 376
system availability 435, 438, 448, 449
tables 375
terminating user connections 388
thresholds 381
transaction logs 381
troubleshooting 392
vs. replication for failover 878.
warm standby servers 389
log shipping monitor 375, 391
log shipping pair 379
log threads 807
log_shipping_database table 375
log_shipping_monitor table 375
log_shipping_monitor user 377
log_shipping_plan_databases table 375
log_shipping_plan_history table 375
log_shipping_plans table 375
log_shipping_primaries table 375, 377
log_shipping_secondaries table 375, 377
LogConflict method 884
LogError method 884
logged bulk copy 602
logged monitoring sessions 637
logical drives 593
logical log marks 246
logical operators 719
Login object 1031
logins
after upgrade 291
creating 1020
dashboards 861
database access 273
development environment 219
log shipping 379, 386
Oracle migration 115, 116
packet encryption 261
viewing 1039
Logins collection 1029, 1040
logs
BLOBs 315
English Query 949
flushing 646
marking transactions 246
optimizing your system 978
recovery 246 – 249
shrinking logs 235, 246
System Monitor 636
LONG columns 167
LONG RAW columns 167
looks alive/keep alive 347
LookupCube function 712
Lookups/sec counter 827
LOOP 181
looping 181
LowMemoryLimit registry setting 805
LRU 95
Previous
| Top of Index
|
Next
Last Updated: Friday, July 6, 2001 |
