|
|
 |
|
|
|
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
% Disk Time counter 406, 643
% Free Space counter 406
% Privileged Time counter 836
% Processor Time counter 407, 641, 836, 837
% User Time counter 836
24x7 See high availability
2PC See two-phase commit
64-GB memory support 16
80/20 Rule 953
99.x uptime 443
A
Access See migrating Access databases
accounts
administrator account for clustering 343
domain user accounts 296
guest accounts 301
guest login accounts 115
local system accounts 295
local user accounts 295
login accounts 115
Oracle migration 115
renaming 282, 286
service accounts 294
actions 27, 705, 783, 785
Active Server Pages 795, 863
active servers 61
ActiveX Data Objects
Access migration 80
Analysis Services data 801
BLOBs 327
meta data 791
overview 782
ActiveX Data Objects (Multidimensional)
Analysis Services data 801
meta data 793
overview 784
ActiveX scripting 663, 670
ad hoc queries 477, 510, 935
Add method 1035
adding
attributes to data 751
constraints 137
dimension record 491
fields in dimension record 492
help text 942
items to English Query model 949
nodes 371
servers for scalability 1085
SQL Replication Conflict Resolver Library 882
TCP/IP addresses 370
users to server roles 265
additive measures 496
Additive Resolver 915
Address Windowing Extensions 16, 350, 453, 580
administrative tasks See stored procedures for administrative issues
administrator account for clustering 343
ADO
Access migration 80
Analysis Services data 801
BLOBs 327
meta data 791
overview 782
ADO MD
Analysis Services data 801
meta data 793
overview 784
affinity mask option 641, 999
AFTER triggers 14, 147, 1069
aged buffers 645
Agg Cache object 827
Aggregate function 720
aggregate functions 172, 1063
aggregation threads 812
aggregations 519, 675, 701, 728, 818, 1015
algorithm provider 754, 757–761
aliasing 293
allocated writeback 32
allocations 988
allow updates option 999
ALTER DATABASE 251
ambiguous relationships 943
Analysis Manager 801
Analysis Manager Usage-Based Optimization Wizard 676
Analysis Services
See also cubes; data mining; digital dashboard browser; dimensions
Active Directory 33
ADO MD 782, 791, 801
ADO 784, 793, 801
aggregations 818, 824
Analysis Manager 801
architecture 800–813
benefits of OLAP 797
client cache management 806
client thread management 810
counters 826–837
data access overview 777
disk storage 815, 823, 835
distributing load 814
drilling down 723
DSO 786–790
English Query 41
hardware configuration 814
historical data 819
HOLAP 818
Internet 795
memory 801–806, 814, 822
meta data 786–795
MOLAP 817
object hierarchy 786
OLE DB 790
OLE DB for OLAP specification 779
overall performance 814–820
overview 797
partitions 514–523, 819
PivotTable Service 32, 779–782, 790, 801, 806, 811
processing interaction 811
processing performance 819, 821–823, 836
processors 814
querying interaction 813
querying performance 819, 823–826
RAID 815
ROLAP 817
scaling out 816
scaling up 814, 816
schemas 819
security enhancements 31
server memory management 801
server thread management 807–810
storage modes 816
threads 807–811
usage pattern analysis 825
what’s new 25–33
Ancestor function 725
AND logical operator in WHERE clause 719
anonymous subscriptions 874
ANSI NULL 97
ANSI_NULLS 1059
ANSI_PADDING 1059
ANSI_WARNING 1059
append queries 70, 75
application lock manager 235
Application object 1025
application roles 267–270
Application Service Providers 410
Application Specialist 400
application storage method 223
applications
See also data tier
Access migration 80–82
indexed views and 1052
system availability 445
system hosting 410
tasks 1043
Architec.lit 1104
architecture
See also storage engine
Analysis Services 800–813
data warehouses 478, 498
ETL 535
indexes 611
SQL Server vs. Sybase 88–90
archiving data 504, 515, 523
argument substitution 97
ARITHABORT 1059
array of disks 455
ascending order key columns 235
ASPs 795, 863
assigning variables 180
association in data mining 755, 760
attaching database files 301
attribute columns 489
attributes
data mining 747, 750–753
dimension tables 486, 489
auditing
C2 auditing 259
overview 258
profiling for 298
settings 297
Sybase migration 103
authentication
Kerberos 257
Mixed Mode Authentication 264, 299
options 31, 255
Oracle migration 115
overview 264
planning 405
SQL Server Authentication Mode 115, 264
Windows Authentication Mode 264, 300
Authoring object model in English Query 41
auto create statistics option 1005
Auto Synch Period property 779, 811
auto update statistics option 1005
AUTO_CREATE_STATISTICS 652
autoclose option 1005
autocommit mode 201
autocommit transactions 150
automatic member grouping 29
automatic recovery 112
automatic snapshot 875
autoshrink option 1005
availability/uptime 49
Average Disk Queue Length counter 644
Avg Disk Queue Length counter 406
Avg function 729
Avg time/query counter 832
Avg. Disk Bytes/Read counter 835
AWE 16, 350, 453, 580
Axes collection 785
B
BackgroundInterval registry setting 809
BackOffice CAL Upgrade Advantage 59
BACKUP 101
backup alert threshold 381
backup devices 113
Backup Devices collection 1043
backup media security 299
backup media sets 262
Backup/Restore Throughput/sec counter 407
backups
backup systems 395
BLOBs 316
disaster recovery planning 458
DUMP 101
duplicating production database 219
enhancements 235, 248
failover clusters 368
log shipping 377, 386, 388
Oracle migration 113
partitions 1099
planning 409
QA testing 225
security 299–301
shared disk backup 425
snapshot backups 235, 425, 431
system availability 430, 449
what’s new 18
backward compatibility
log shipping 387
replication 877
balanced hierarchies 687
base tables 1061, 1074
baseline for data tier optimization 956, 963
basket analysis 713
Batch Complete phase 661
batches
bulk data loads 603
reusing execution plans 651
worker threads 644
bcp utility 603, 1081
BEFORE triggers 147
BEGIN DISTRIBUTED TRANSACTION 157
BEGIN TRANSACTION 92, 151
BEGIN…END 181
bigint data type 243
binary large objects See BLOBs
bldmstr utility 103
BLOBs
ADO 327
backups 316
columns 325
cursors 325
data types 305, 313
database APIs 327
database system vs. file system 314
implementing 317
in-row BLOBs 306, 309
loading image file to 336
ODBC 327, 334
OLE DB 327, 329
on client 326–337
on server 319–326
Oracle migration 167
out-row BLOBs 306, 309
overview 303
restoring 316
saving 328–329, 334
small-sized BLOBs 305
storage 305–312, 315
tables 319–325
TerraServer 313–316
text in row option 306–312
Transact-SQL elements listed 318
block size 988
blocking 1013
blocks 111, 967
bonsai technique 759
bookmarks 235
Books Online 11
boosting priorites 583
bottlenecks
monitoring 641
parallel data scans 601
Sybase migration 95
BREAK 183
broad relationships 944
btnGo_Click subroutine 1026
Buffer Cache Hit Ratio counter 406, 639
buffer flushing 645
Buffer manager counter 406
buffer memory 803
bulk copy APIs 603
bulk copy operations 1081
bulk data loads 603
BULK INSERT 603, 1081
bulkadmin fixed server role 260, 266
Bulk-Logged Recovery model 248, 578, 602
business rule application 532
Bytes Total/sec counter 835
C
C2 auditing 259
C2 security 257–259
caching
Analysis Services 698, 801–806, 823
flushing 646
RAID controllers 587
system performance 576, 961, 987
calculated cells 26, 704
calculated measures 496, 728, 731
calculated members 704
calculations for dimensions 729–736
calling stored procedures 200
CALs 57–63
capacity planning 452, 981–983
CASCADE option 138
cascading referential integrity constraints 15
CASE 172, 181
CAST 173
CD 1103–1108
cells 26, 31, 706
Cellset object 784
certificates 260
chained transactions 92
challenge defense protocol 347
change control 403, 458
change processing
contingencies 230
development environment 215–225
goals 214
levels of control 214
production implementations 227–231
QA environment 225–227
release readiness review 229
Change SQLServers button 1023
changing
See also database changes
attributes for data mining 752
default locking behavior 153
dimensions 28, 490–494, 683
English Query model 949
entity properties 940
permissions 286
recovery models 247
relationships 941
sp_rk_audit_configure 1002
sp_rk_audit_dboptions_check_1_db 1012
sp_rk_blocker_blockee 1026
subsystem 987
TCP/IP addresses 370
channels 987
character functions 169
character mapping 293
CHECK commands 250
CHECK constraints 140, 142, 1080
checking
database configuration 1005
permissions 285
server configuration options 999
version of SQL Server 1004
checkpoints 245, 645
classification in data mining 755–758
cleaner threads 807
cleaning data for data mining 750
clickstream star schema 484
Client Access Licenses 57–63
Client Cache Size property 780
client configuration 88
client connectivity enhancements 32
Client Network Utility 114
client/server applications 64, 80
Client-Requested Encryption option 261
client-side cache 698
CLOB 303
cloning partitions 524
CLOSE CURSOR 188
closed-loop data mining 744, 770
closing cursors 188
ClosingPeriod function 732
Cluster Group 344
cluster nodes 342
cluster servers and replication 879
clustered indexes
See also indexed views
Oracle migration 126
system performance 611
clustered keys 1005
clustering
See also failover clustering
administrator account 343
enhancements 339
grouping 343
instances of SQL Server 345
IP addresses 343, 357
Microsoft Cluster Service components 342-348
multiple-instance clusters 346
names 343, 357
Network Load Balancing clusters 342
resource types 343
server clusters 342
single-instance clusters 346
system availability 422–428, 438
virtual servers 344
vs. replication for failover 879
Windows Clustering 341–348
clustering in data mining 755–760
CoalesceEmpty function 739
Cobol Programmatic Interface 207
code comments 217
code samples for ETL systems 544–574
collations 15
collocating data 1087
CollSelect Web Part 859
Collselect.asp 864
colon operator 733
Column object 1035
ColumnID parameter 884, 895
COLUMNPROPERTY function 1060
columns
BLOBs 325
Oracle migration 118
statistics 652
Columns collection 1036
COM
See also task management through COM interface
multiphase data pump 655
new interfaces with release 35–40
COM+ events 663
command line scripts for implementation code 220
CommandID constants 1046
comment text field 945
comments in database code 217
COMMIT TRANSACTION 95, 150, 201
communication rates 398
CompareCaseNonSensitiveStringFlags property 780
CompareCaseSensitiveStringFlags property 780
comparing
outcomes for data mining model 764
system performance 406
time periods in MDX 738
comparison operators 176
compatibility issues
log shipping 387
replication 877
Sybase clients and servers 88
COMPLEXITY_PENALTY 760
Component Services 62
COMPUTE clause 165
ComputeAdditiveValues method 916
computed columns
indexes on 615
Oracle migration 130
updatable views with triggers 1076
ComputeMinimumValue method 917
CONCAT_NULL_YIELDS_NULL 1059
concurrency
scaling out 1087
storage engine 240
Sybase migration 95
conditional processing 181
conditional tests 172
configuration options
database 1005
server 999
impacting performance 577–584
confirming success or failure for ETL processes 539
conflict resolution
Additive Resolver 915
ComputeAdditiveValues method 916
ComputeMinimumValue method 917
constants 904
enumerated data types 904
GetHandledStates method 882
IConnectionInfo interface 884, 895
IConnectionInfo interface methods 895
IRepIRowChange interface 884
IRepIRowChange interface methods 884
IVBCustomResolver interface 882, 915
MinimumResolver 916
overview 879
Reconcile method 883, 915, 917
registering custom conflict resolvers 914
REPLGUID constants 904
REPOLE_BOOL constants 904
REPOLE_CHANGE_TYPE constants 904
REPOLE_COLSTATUS_TYPE constants 908
REPOLE_COLUMN_ATTRIBUTES constants 909
REPOLE_CONFLICT_TYPE constants 909
REPOLE_DATASOURCE_TYPE constants 910
REPOLE_DBADDOPTION constants 910
REPOLE_EXTENDEDERROR_TYPE constants 911
REPOLE_PRIORITY_TYPE constants 911
REPOLE_SECURITY_TYPE constants 912
REPOLE_SERVER_MINOR_VERSION constants 913
REPOLE_SERVER_VERSION constants 912
REPOLE_SQL_DATATYPE constants 913
samples 915–918
SQL Replication Conflict Resolver Library 881
conforming dimension 486
confusion matrix 765
Connect property 1025
Connect Timeout property 780
connection handle 197
Connection object 784, 791, 828
ConnectionString property 783
connectivity
Analysis Services 32
failover clustering 358
log shipping 381
Oracle migration 114
consistency
ETL design considerations 534
log shipping 387
constants in SQL Replication Conflict Resolver Library 904
constraints
adding 137
CHECK 140, 142
DEFAULT 140, 142
domain integrity 140
entity integrity 136–140
FOREIGN KEY 142
Oracle migration 136–140
PRIMARY KEY 136, 142
referential integrity 142
removing 137
UNIQUE 136, 142
vs. triggers 1080
contention 1091
Context Switches/sec counter 641, 837
context-sensitive commands 783, 785
CONTINUE 183
continuity 401
continuous attributes 752
continuous computing See high availability
controller configuration 352
control-of-flow language 178–184
controls over development database 216–219
conventions 12
conversion functions 171, 173
CONVERT 173
converting
See also migrating Access databases; migrating Oracle databases; migrating Sybase databases
code 80
data types 532
queries 68
Copy Database Wizard 20
CopyColumnFromSource method 884
copying
See also ETL
BLOBs and 316
production database 219
CopyRowFromSource method 884
cost threshold for parallelism option 582
Count function 714
counters
% Disk Time counter 643
% Privileged Time counter 836
% Processor Time counter 641, 836
% User Time counter 836
Analysis Services 826–834
Average Disk Queue Length counter 644
Avg time/query counter 832
Avg. Disk Bytes/Read counter 835
Buffer Cache Hit Ratio counter 639
Bytes Total/sec counter 835
Context Switches/sec counter 641, 837
Current bytes counter 827
Current connections counter 828
current counters 826
Current Disk Queue Length counter 643–645, 835
Current process thread pool counter 832
Current process thread queue length counter 832
Current process threads active counter 832
Current threads counter 832
Current worker thread pool counter 832
Current worker threads active counter 832
Data bytes counter 828
Data reads counter 828
database counters 407
Direct hits/sec counter 827
Disk Bytes/sec counter 836
Disk Length Queue counter 642
Disk Reads/sec counter 640, 642, 836
Disk Transfers/sec counter 836
disk usage counters 406
Disk Write Bytes/sec counter 642
Disk Writes/sec counter 640, 642, 836
DSN requested counter 828
DSN used counter 828
Failures/sec counter 828
File bytes written/sec counter 830
File Control Operations/sec counter 837
File rows written/sec counter 830
Filter hits/sec counter 827
Free Pages counter 645
hard paging 639
I/O counters 643
Index bytes counter 828
Index reads counter 828
IO Data Bytes/sec counter 836
IO Data Operations/sec counter 836
IO Other Bytes/sec counter 836
IO Other Operations/sec counter 836
IO Read Bytes/sec counter 836
IO Read Operations/sec counter 836
IO Write Bytes/sec counter 836
IO Write Operations/sec counter 836
lazy writer 645
Lazy Writes/sec counter 645
logging 960
Lookups/sec counter 827
Map bytes counter 828
Map reads counter 828
memory counters 406, 639
Memory size bytes counter 830
Memory size rows counter 830
Misses/sec counter 827
Page Faults/sec counter 640
Page Reads/sec counter 640
Pages Input/sec counter 640
Pages/sec counter 639, 834
percent disk time 643
performance 33
Processor Queue Length counter 641, 837
processors 407, 641
RAID 593
ratio counters 826
Requests/sec counter 828, 833
Rows created counter 828
Rows created/sec counter 830
Rows merged/sec counter 830
Rows read counter 828
Rows read/sec counter 830
Server uptime counter 834
soft paging 640
SQL Server counters 407
Sybase migration 103
System Calls/sec counter 837
System Monitor 33
system performance counters 834–837
Temp file bytes written/sec counter 831
Thread Count counter 836
Time counter 828
total counters 826
Total Server Memory counter 639
Virtual Bytes counter 836
VLDM requests/sec counter 833
Working Set counter 689, 836
covering indexes 619
CPUs
aggregations 701
monitoring 641
System Monitor 636
system performance 583, 973, 982
Create Database Backup Wizard 113
CREATE INDEX 128, 243, 623
CREATE MINING MODEL 761
CREATE PROCEDURE 144
CREATE TABLE 122, 131
CREATE TRIGGER 147, 1080
CREATE VIEW 124
creating
dashboards 839–853
data mining models 761
dimension record 491
disaster recovery plan 458–464
event class DLL 664
fields in dimension record 492
indexed views 1058–1062
indexes 609
partitioned views 1093
partitions 512, 520
tables 122, 319, 1033
updatable views 1074
views 124
CROSS JOIN 165
Crossjoin function 726
crosstab queries 70, 72
cryptography 260
CT-Library 88
cubes
actions 705
aggregations 701
caching 698
calculated members 704
cell security 706
characteristics 699
data density 676
data explosion 675
data warehouses 506, 511
designing 675, 694
distributed partitioned cubes 25, 697
growth 675
hidden elements 27
linked cubes 26, 696
managing local 32
measures 702
named sets 706
offline cubes 698
partitioned cubes 514, 519–523
partitions 699
processing 26, 707, 811
real-time cubes 697
regular cubes 695
results from different cubes 712
sparsity 676
storage modes 707
types 695
virtual cubes 27, 696
what’s new 25–27
cumulative sums as measures 717
Current bytes counter 827
Current connections counter 828
current counters 826
Current Disk Queue Length counter 643–645, 835
CURRENT OF clause 188
Current process thread pool counter 832
Current process thread queue length counter 832
Current process threads active counter 832
Current threads counter 832
Current worker thread pool counter 832
Current worker threads active counter 832
CurrentMember function 716, 729, 735
cursors
BLOBs 325
closing 188
CURRENT OF clause 188
declaring 186
default result sets 196
fetching 187, 193–197
forward-only cursors 193
opening 187
Oracle migration 155, 185–189, 193–197
scrollable cursors 195
server cursors 194
Sybase migration 94
custom conflict resolvers
Additive Resolver 915
ComputeAdditiveValues method 916
ComputeMinimumValue method 917
constants 904
enumerated data types 904
GetHandledStates method 882
IConnectionInfo interface 884, 895
IConnectionInfo interface methods 895
IRepIRowChange interface 884
IRepIRowChange interface methods 884
IVBCustomResolver interface 882, 915
MinimumResolver 916
overview 879
Reconcile method 883, 915, 917
registering 914
REPLGUID constants 904
REPOLE_BOOL constants 904
REPOLE_CHANGE_TYPE constants 904
REPOLE_COLSTATUS_TYPE constants 908
REPOLE_COLUMN_ATTRIBUTES constants 909
REPOLE_CONFLICT_TYPE constants 909
REPOLE_DATASOURCE_TYPE constants 910
REPOLE_DBADDOPTION constants 910
REPOLE_EXTENDEDERROR_TYPE constants 911
REPOLE_PRIORITY_TYPE constants 911
REPOLE_SECURITY_TYPE constants 912
REPOLE_SERVER_MINOR_VERSION constants 913
REPOLE_SERVER_VERSION constants 912
REPOLE_SQL_DATATYPE constants 913
samples 915–918
SQL Replication Conflict Resolver 881
custom member properties in MDX 721
custom rollup and member formulas 29
custom tasks 24
customer feedback 12
Customer List Web Part 847, 849
customer support staffing 402
Customerlist.htc 851
Customerlist.htm 849
Customerlist.xml 850
Customerlist.xsl 850
customizing error messages 1071
D
DAO 80
dashboards See digital dashboards
data access considerations and system availability 445
Data Access Objects 80
data access with Analysis Services
ADO MD 782, 791
ADO 784, 793
DSO 786–790
meta data 786–795
OLE DB 790
overview 777
PivotTable Service 779–782, 790
data accuracy 532
data analysis applications for data warehouses 501
Data bytes counter 828
data centers
Application Service Providers 410
Application Specialist 400
backups 409
change control 403
communication 398
Deployment Specialist 399
Disaster Recovery Specialist 399
emergency preparedness 404
facility requirements 395
general operations 403
hardware 397
Hardware Specialist 402
high availability 465
maintenance 409
Monitoring Specialist 401
monitoring SQL Server 406
overview 393
quality assurance 403
Quality Assurance Specialist 402
recovery 409
security 405
Security Specialist 399
SQL Developer 400
SQL Profiler 408
SQL Server operations 405
staffing recommendations 398
Support Specialist 402
System Continuity Specialist 401
System Monitor 406
Trend Analysis Specialist 401
types 393
data cleaning 750
data communication 398
data connectivity 395
data density 676
data dictionary 110
data enrichment 751
data explosion 675
data extraction 531, 538
data extraction/transformation/loading process See ETL
Data File(s) Size (KB) counter 407
data integrity
database changes 213
domain integrity 140
entity integrity 136–140
Oracle migration 135–150
partitions in Analysis Services 521
referential integrity 142
user-defined integrity 144–149
data interaction with storage and relational engines 238–241
data load speed 505, 513, 515
data loading 533, 539, 602–607
data maintenance applications for data warehouses 501
data maps 537
data marts 747
data mining
See also data mining model; meta data
association 755, 760
classification 755, 758
closed-loop data mining 744
clustering 755, 760
data cleaning 750
data enrichment 751
data mart 747
data selection 747
data transformation 752
data with known outcome 756
data with unknown outcome 756
database types 747
data-driven data mining 756
enhancements 29–31
estimation 755, 758
identifying data 749
locating data 747
Microsoft Clustering algorithm 760
Microsoft Decision Trees algorithm 758
model-driven data mining 756
operational data mining 745
overview 478, 741
process overview 746
purposes 755
tasks 755
training set case 752
data mining model
algorithm provider 754, 757–761
calculating effectiveness 765
closed-loop data mining 770
comparing outcomes 764
constructing 754
creating 761
data-driven data mining 756
evaluating 764
evaluation case set 764
feedback 770
initial rules and patterns 752
Microsoft Clustering algorithm 760
Microsoft Decision Trees algorithm 758
model-driven data mining 756
model-driven vs. data-driven 757
no predictive value 758
operational data mining 770
overfitting 758
oversampling 753, 766
overview 743
predictions 770
pruning 759
refinement 765
relational data mining models 761
relationships 768
sampling 752
structure evaluation 768
tasks 755
test case set 765
training 762
training set case 752, 762
tuning 768
visualizing 768
Data Mining Model Browser 768
data nibbling 504
data on demand 785
data optimization and system performance 576
data partitioning
See also partitioned views; scaling out
backups 1099
data-dependent routing 1096
high availability 1100
INSTEAD OF trigger 1098
maintenance 1098
overview 1085, 1089
partitioned queries 1094
repartitioning 1098
replication 1097
restoring 1099
startup filters 1094
unique columns 1098
data patterns and rules See data mining
data pruning 504, 515, 523, 759
data pump
See also multiphase data pump
programming interfaces 655
transformation status 658
troubleshooting 657
Data reads counter 828
data schema and structure See meta data
data security See security
data selection for data mining 747
data shipping 1092
data size and scaling out 1088
Data Source parameter 795
data storage
components 253
optimizing your system 970, 977
scaling out 1088
data striping 111
data synchronization 379
data tier
80/20 Rule 953
analyzing results 966–974
baseline 963
blocking 967
cache 987
capacity planning 981–983
change management 974
channels 987
code components 975
costs 980
CPUs 982
current equipment 983–988
data components 974
data storage issues 970-977
database design 967
disk controllers 987
disk layout 970
disk planning 983
DSS system layout 991
error handling 962
evaluating your system 956–961
files 977
functions 958
hardware 970, 980–993
identifying areas for improvement 963
indexing issues 969
information gathering 956–961
large data center failover cluster 992
logs 978
memory 971, 982
monitoring system 957–966
narrowing areas for improvement 964–966
OLTP system layout 990
optimization cycle 954
overview 953
Pareto Principle 953
post-change evaluation 956
query run times 967
RAID 979, 985
reporting system 973
response time 961, 966
sample configurations 988–993
scenarios 962–980
server configuration 979
small system layout 988
SQL Profiler 957
standardizing your system 984
subsystem design 984–988
System Monitor 959
system stored procedures 958
tempdb database 979
testing system 956, 960
data transformations 532, 538, 752
Data Transformation Services
See also DTS packages
Access migration 66
data mining 30
ETL system 540
Oracle migration 204
what’s new 24
data types
bigint 243
BLOBs 305, 313
conversion 532
mappings 197
new with this release 14
Oracle migration 131–134, 173, 197
SQL Server vs. Access 79
SQL Server vs. Oracle 197
sql_variant 243
Sybase migration 90
table 243
Unicode 132
user-defined 133
data validation 532
data warehouses
See also Analysis Services; designing data warehouses; ETL
architecture 478
common characteristics 479
cubes 506
data mining 478, 742
defined 475
enterprise 747
meta data 512, 516, 518
OLAP and 477
OLTP system support 477
partitions in Analysis Services 514–523
partitions in relational data warehouse 504–513
partitions overview 503
surrogate keys 544
users 479–481
vs. OLTP databases 475
data with known outcome 756
data with unknown outcome 756, 770
database administrators
goals 214
ownership of database changes 228
roles 415
database changes
See also databases
contigencies 230
development environment 215–225
goals 214
levels of control 214
production implementations 227–231
QA environment 225–227
release readiness review 229
database console commands 235, 250
database counters 407
Database interface 788
Database Logs collection 1043
Database Maintenance Plan Wizard 383
Database object 1041
databases
See also data tier; database changes; ETL; migrating Access databases; migrating Oracle databases; migrating Sybase databases; storage engine; tuning databases
attaching database files 301
checking database configuration 1005
code comments 217
consistency checking 387
detaching database files 301
failed implementation 230
for data mining 747
for English Query 939
heterogeneous 876
logins 219
maintenance plan 378, 383
objects 118
options 235
recovery 245–249
size 90
states 251
system availability 430
verification 250
Databases collection 1029
data-dependent routing 1096, 1099
data-driven data mining 756
Datatype property 1036
date dimensions 488
date functions 170
date partitioning 504, 510, 515, 522
date ranges in MDX 733–734
date values 200
date-dimensioned cubes 522
DB Wizards button 1024
db_accessadmin fixed database role 116, 266
db_backupoperator fixed database role 267
db_datareader fixed database role 266
DB_DATAWRITER 1021
db_datawriter fixed database role 267
DB_DDLADMIN 1021
db_ddladmin fixed database role 217, 267
db_denydatareader fixed database role 267
db_denydatawriter fixed database role 267
db_owner fixed database role 116, 118, 217, 266
db_securityadmin fixed database role 267
DB2 data source for replication 876
DBAs
goals 214
ownership of database changes 228
roles 415
DBCC DBREINDEX 623
DBCC INDEXDEFRAG 244, 622
DBCC SHOWCONTIG 244, 622
DBCCs 235, 250
dbcreator fixed server role 267
dbid column 283
DB-Library 88
dbo objects 217
dbo use only option 1005
DBSelect Web Part 859
Dbselect.asp 864
DDRK 843, 855
DDSC 839, 856
deadlocks 156, 240
DEALLOCATE 94
DEALLOCATE CURSOR 188
Decision Support Objects 762, 786–790
decision support process and data mining 741
Decision Support Systems 973
decision trees 758, 768
DECLARE CURSOR 186
declaring cursors 186
declaring variables 180
DECODE 172
DEFAULT constraints 140, 142
default
database errors 220
extents 111
filegroups 599
locking 153
measures 27
members 29
result sets 196
Default GUID Dialect property 781
defragmentation 244, 622
delaying stored procedure execution 146
delegation 257
DELETE 1163, 1069
delete queries 70
deleted tables 1071
DeleteRow method 884
deleting transaction logs 381
DENY 134, 278
denying permissions 134, 277
denylogin status 284
dependencies 352
Dependency Network Browser 768
dependent dimensions 28
Deployment Specialist 399
Descendants function 714, 717, 723, 725, 728, 731
descending order key columns 235
description text field 945
designing data warehouses
See also dimensional model
architecture 478, 498
common characteristics 479
data analysis applications 501
data maintenance applications 501
data mining 478
data warehouse database vs. OLTP database 475
deploying 502
dimension tables 486–495
fact tables 495–497
indexes 499
OLAP 477, 501
OLTP system support 477
Operational Data Store 501
prerequisites 478–480
relational database 499
relationships 499
requirements 480
schemas 483–485, 499
testing 502
user categories 479
user needs 481
user queries 480
views 500
Desktop Engine 53, 62, 257, 877
detaching database files 301
deterministic expressions 1060
deterministic functions 615, 1060
Developer Edition 51, 54
developers 400
development environment
controls 216–219
duplicating production database 219
implementation scripts 220–224
logins 219
overview 215
permissions 217, 220
processes 215
risk analysis 224
rollback strategies 224
development goals 214
differential backups 113, 235
digital dashboard browser
See also digital dashboards
ASP files 863
CollSelect Web Part 859
connecting to registry 865
copying files 856
creating digital dashboard 858–861
DBSelect Web Part 859
Digital Dashboard Resource Kit 855
Digital Dashboard Services Component 856
IIS virtual directory 857
log in 861
MemberSelect Web Part 860
MetaData Web Part 860
overview 853
permissions 857
requirements 855
sample files 861–865
ServerConnect Web Part 858
setup 856
sizing of Web Parts 866
testing digital dashboard 861
text files 862
Digital Dashboard Resource Kit 843, 855
Digital Dashboard Services Component 839, 856
digital dashboards
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
.htm 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
.xml file 845
DIMENSION PROPERTIES 721
dimension tables
attributes 486, 789
changing 490–494
date dimensions 488
hierarchies 487
indexes 499
keys 488, 490
multi-use dimensions 495
overview 486
relationships 499
surrogate keys 487
time dimensions 488
dimensional data warehouses 504, 506
dimensional model
data warehouse architecture 498
dimension tables 486–495
fact tables 495–497
overview 481
schemas 483–485
dimensions
balanced hierarchies 697
calculated members 704
calculations for different dimensions 731
calculations for different levels 728
changing 490–494, 683
characteristics 686
date dimensions 488
designing 678
drilling down 723
empty members 676
enhancements 28–29
filters 29
hierarchies 487, 686
levels 689
members 689
memory 801
multiple hierarchies 687
multi-use dimensions 495
new dimension record 491
new fields in dimension record 492
overview 486
overwriting dimension record 490
parent-child dimensions 685
private dimensions 681
processing 693, 811
ragged hierarchies 687
ranking or reordering members 727
real-time OLAP 691
samples for ETL system 548–562
schemas 679
security 31, 692
shared dimensions 681
sparsity 676
storage modes 692
time dimensions 488
types 683
unbalanced hierarchies 687
virtual dimensions 684
Direct hits/sec counter 827
directory storage method 222
dirty buffers 645
dirty logs and pages 645
disaster recovery plans 399, 404, 458–464, 878
Disaster Recovery Specialist 399
disconnected users and high availability 418
discrete attributes 752
disk arms 585
disk backups for failover clusters 369
Disk Bytes/sec counter 836
disk contention 1091
disk controllers 987
disk drives and system availability 419
disk I/O parallelism 595
disk layout and data tier optimization 971
Disk Length Queue counter 642
disk mirroring 455, 590
disk planning 983
disk queuing
flushing buffers 645
flushing dirty logs and pages 646
monitoring 643
system performance 596, 972
Disk Reads/sec counter 640, 642, 836
disk storage 254, 455, 701, 815, 823, 835
disk striping 455, 589
Disk Transfers/sec counter 836
disk usage counters 406
Disk Write Bytes/sec counter 642
Disk Writes/sec counter 640, 642, 836
diskadmin fixed server role 266
distance mirroring 431
DISTINCT COUNT 702
distinct count measures 702
DistinctCount aggregate function 27
distributed marks 246
distributed partitioned cubes 25, 697
distributed partitioned views
backups 1099
defined 1090
federation 1088
options 1091
restoring 1099
distributed queries 17
distributed transactions 157, 431
distributing load 814
distribution servers 203
Distributors
data updates 872
local vs. remote 873
replication types 871
SQL Server Desktop Engine 877
subscriptions 874
system availability 448
document storage 223
documentation 3–13, 42, 1103–1108
documents containing recovery plans 462
DoDummyUpdate method 884
domain integrity 140
domain user account 296
domains 292, 747
downtime 414, 443
DrillDownMember function 723
drilling up and down 723
drillthrough 27
DrillThrough command 784
drive letters 984
drive platters 585
drive pool method 595
DROP INDEX 623
DROP_EXISTING 623
DSN requested counter 828
DSN used counter 828
DSO 762, 786–790
DSS 973
DTS
See also DTS packages
Access migration 66
data mining 30
ETL system 540
Oracle migration 204
what’s new 24
DTS Import/Export Wizard 67, 91, 131, 204
DTS packages
encryption 262
log shipping 379, 386
multiphase data pump 657, 669–673
DTSTransformPhaseInfo 663
DUMP 101
duplexing 455
duplicate values 130
duplicating production database 219
Dwdesign.lit 1104
dynamic filters 871, 1094
dynamic forecast 736
dynamic locking 153
dynamic memory 639
dynamic tuning 235, 252
Next
Last Updated: Friday, July 6, 2001 |
