Deploying Microsoft SQL Server 7.0 Book

Symbols and Numbers
# (hash sign) or ## (hash signs), 231
16-bit clients, upgrading, 543
32-bit clients, upgrading, 542–43
1252 character set, 462

A
A&G Phone, 179, 193–94
access concept, 160
access groups, 148–49
access patterns, 160–62, 212–13
access rights, 168–69, 213
activating tasks of project management, 52
ActiveX scripts, 29, 133
ActiveX Template Library (ATL), 424
activity flow, determining for an upgrade, 520–21
ad hoc query tools, 84
ad hoc reports in MS Sales, 366
administration
    developing and testing jobs in separate environments, 172
    filegroups and, 223
    of SQL Server 7.0, 24–25
agent profiles, 476–77
agents, monitoring the health of, 476
aggregate table sample for a data warehouse, 378
Aggregation Design Wizard, 30
aggregation selection, 29
aggregations in MS Sales, 363
aggregation storage method, 443
aggregation strategy, 102
alerts, 475–76
Alfred database, 432
ALTER DATABASE command, 390
anonymous Subscribers, 455
anonymous subscriptions, 471
ANSI mode, switching on and off, 255–56
anti-fault-tolerant RAID, 33
application architecture, 71, 81–84
application databases, 212–13
application design, metadata specifying, 394, 395
applications
    competency in designing and building using replication, 17–19
    competency in designing the hardware infrastructure to support, 15
    competency in developing for performance, 16–17
    competency in migrating to SQL Server from competitors’ systems, 9
    converting for an upgrade, 526–34
    designing for enhanced recoverability, 388
    ensuring high quality of, 170
    keyword conflicts in, 531
    minimizing upgrade risks to, 533–34
    testing, 34, 43, 534–35
    verifying the compatibility of, 42–44
approval
    of the data warehouse architecture, 87
    for the first iteration of a data warehouse project, 70
Approved Company Only restriction, 292
architecture. See also technical architecture
    of MS Sales, 368–69
    review and design stage, 70–71
ArchiveDate column, 158
archive plan. See data archive plan
archive tables in HRDataMart, 317
archiving
    implementing and testing, 112
    objects, 381
articles, 357–58, 451, 475
associative entity type, 144
asynchronous I/O requests, 189–90
ATL (ActiveX Template Library), 424
attended installation of SQL Server 7.0, 35–36
Attic database in HRDW, 309–10
attribute inheritance, 353–55
attributes
    of entities, 151, 209
    selecting for the HRDW, 289
    sharing in objects’ names, 379–80
Audit Genie, 319, 328
auditing, adding columns for, 158
audit reporting, 293
autogrowth, filegroups with more than one data file and, 221–22
automated "black box" testing for Y2K compliance, 304
automated monitoring procedure, 392
automatic expansion, preventing for the transaction log file, 219
automatic file growth
    imposing a uniform chunk size, 217
    preventing for data files, 222
automatic shrinkage, avoiding, 217
automatic synchronization, 481–83
AUTORUN.EXE file, 35
auto-tuning, 185
availability, increasing for production databases, 279

B
BackOffice, competency in integrating with, 14
back-out strategy, selecting for an upgrade, 517–19
backup and recovery, 130
    for data warehouses, 386–94
    goals for, 386–87
    implementing and testing, 112
    planning for, 105, 493–96
    strategies for, 387–93, 515–16
    summary of tips, 393–94
backup and restore method for synchronizing data at a Subscriber, 491–92
Backup manager, 425
backup method, developing a physical, 388–89
backups
    of databases, 265
    to disk, 267–68
    strategy for SAP at Microsoft, 205
    to tape, 269
backup scripts, 392
backup site controller (BSC), 184
BACKUP statement, 25
backup window for filegroups, 223
bad data, insulating data warehouses from, 331–32
bandwidth requirements for VLDBs, 183
Bann applications, competency in using OLAP Services with, 13
batches, 190–91
batch files, running, 294–95
batch jobs in a data warehouse environment, 395–96
bcp files, using native format, 482
bcp utility, 134, 466, 489
BCVs (business continuous volumes), 266–67
bidirectional replication, 454, 456, 457, 458–59
bidirectional transactional replication, validating, 480
binary sort order, 463, 522
bitmap indexing, 384
Black Dinosaur Oil, 195
blocking locks, avoiding, 259–60
Boolean values for comparisons, 255
Bound Galley Book Cellars, 125, 129, 175, 179, 183–85
BPR (business process reengineering), 54
BQA (business question assessment), 61–70
breaking the mirror, 266
Broadband Cable Communications (BCC), 125
    documentation guidelines for, 136
    storing addresses in a table, 226–27
    system map from, 139–41
BSC (backup site controller), 184
B-trees, 234
    compared to bitmap indexing, 384
    rebalancing, 231–32
Budget WorkBench (BWB), 433
buffer pool size, 201
build breakers, 293
build process for HRDW, 315
builds, 329, 341. See also nightly processing
build server, 318, 320–21
bulk copy program. See bcp utility
BULK INSERT command, 134, 489
bulk load operations, 385
bullpen environment, impact on documentation, 288, 289
bundled products, unbundling in MS Sales, 355
bundling, 345
Burkett, Howard, 122
"burn box" environment, 175
Business Analyst, Technical Staff role, 7
business analyst team for MS Sales, 370
business and technical requirements, refining, 97–98
business case stage of a data warehouse project, 54–61
business continuous volumes (BCVs), 266–67
business design walkthrough, 103
business driver, 49
business intelligence, 27–30
business metadata, 73, 74, 75–76. See also semantic metadata
    defining, 103
    loading, 110
business problems, 127
business process reengineering (BPR), 54
business question assessment (BQA), 61–70
business rules
    application of, 289
    capturing and documenting, 337
    documentation of, 334
    enforcing on extracted SAP R/3 data, 287–88
business services, 129
bus systems for VLDBs, 181
BWB (Budget WorkBench), 433

C
cardinality relationship between entities, 144
case-insensitive sort order, 522
CDE model, 409
central distribution server, 471
Central Figure organization in datamart sales tables, 361
centralized warehouse administration, 399
central Subscriber, 451
central system scenario for small SAP installations, 202
change management board, 115
change management program, 63
change requests, managing for VLDB projects, 135
Channel Measurement Operational Data System (CMODS), 349
channel sales team for MS Sales, 370
channel virtual cube, 441
characteristic entity type, 144
character sets, 35, 462
chargeback plan, 106
CHECKCATALOG command. See DBCC CHECKCATALOG command
CHECKDB command. See DBCC CHECKDB command
checkpoints, 391, 392
child, 147
CHKUPG65.EXE utility, 527
chronology, cases of, 157
chunk size, setting a uniform, 219
cleansing programs, creating and testing, 109–10
cleansing tools, 82
clients, upgrading, 541–44
client-server architecture approaches, considering for a data warehouse project, 80
client/server software architecture, logical components of, 129
clustered indexes, 234, 235–36
    compared to nonclustered, 236–37
    displaying a query execution plan for, 251
    impact on I/Os, 238
    impact on secondary indexes, 237
    recommendations on the use of, 238–39
    reorganizing tables with, 271–72
clustering, 183–85
CMODS database in MS Sales, 349
code
    converting to SQL Server 7.0, 306–7
    increasing the maintainability of, 333
code inspection testing method for Y2K compliance, 303–4
code pages. See character sets
code review of Transact-SQL commands, 41
COGS (cost of goods sold), 358
column-level security, 291, 333
columns
    adding standard to tables, 155–58
    listing in table documentation, 162–63
    selectivity of values in, 229–30
command script for reorganizing tables with clustered indexes, 272, 275–79
COM model, 410
communication in large projects, 136
communications plan, creating for an upgrade, 505
comparison operators, 255
compatibility mode. See SQL Server 6.5, compatibility mode
competency
    applying data modeling techniques, 11
    in building schemas and migrating applications, 9
    designing a database schema, 15–16
    designing a mobile disconnected strategy, 18–19
    designing for high availability using MSCS or hot backups, 14–15
    designing the hardware infrastructure to support applications, 15
    developing applications for performance, 16–17
    developing applications using OLAP, 10
    developing a replication strategy, 17–18
    developing management systems, 9
    developing with Microsoft Office and Visual Studio tools, 12–13
    with DTS (Data Transformation Services), 11
    integrating OLAP Services with other applications, 13
    integrating with BackOffice, 14
    integrating with English Query, 10
    with Microsoft Repository, 11
    replicating data from legacy systems, 18
    in SQL Server architecture, 12
    using third-party tools, 14
complex queries, achieving optional response times on, 214
Component Object model, 410
computers, selecting for VLDBs, 179–81
computer-to-computer upgrade, 37, 38
conceptual data model (CDM), 128, 137, 141, 209
conceptual design, creating a high-level, 500–501
configuration management (CM), 106, 133, 296
configuration options, 185–88
configuration settings for VLDB projects, 131
conflict resolution, 460
conflict resolution logic for bidirectional replication, 459
connections to distributed metadata, 401
consistency checks, planning for VLDB projects, 130
consolidated virtual cube, 441
consolidation of legacy databases, 309–10
context switching, 190, 191, 192
controlling tasks of project management, 52–53
conversion times for databases, 37
CO (constraint) object type, 153
corruption, due to hardware or software failures, 265
cost
    categories for a data warehouse project, 57
    planning for a data warehouse project, 51
cost/benefit analysis, 56–59
cost of goods sold (COGS), 358
country restrictions on accessing HR data, 292
covered indexes, 241–42, 251, 252
CPIT (current point-in-time), 317, 340
CPU-bound queries, 246
CPU-bound work, estimating for VLDB projects, 131
CPUs
    checking overall utilization, 192
    insufficient power, 180
    switching threads, 190
Create Publication Wizard, 482
CREATE STATISTICS statement, 196
criteria matrix for tool selection, 90–93
Critical Success Inhibitors (CSIs), 60
CS (contingent staff), 341
CSFs (critical success factors), 59–60
CS (contingent staff) Only data view restriction, 325
cube roles, 441
cubes, 84. See also virtual cubes
Cube Wizard, 440–41
cubing, 72
customer functions, 85–86
cycle processing, 391

D
DAO objects, checking data sources for, 43
data, 28
    combining from multiple sites, 451
    correcting anomalies in HRDW, 293
    distributing to closer sites, 451
    guidelines for storing physically, 166
    implementing a logical layer around, 160
    partitioning between sites, 456
    physically partitioning into multiple tables, 469–70
    publishing, 331–32
    transforming from previous systems, 133
    transforming from SAP R/3 to the warehouse, 334
data access
    procedures, 104–5
    process, 83–84
data architecture, 70, 75–79
data archive plan, 105–6
data archiving, adding a column for, 158
Database Access Objects (DAO), 43
database administration of MARS, 438–39
database administrator (DBA), 98. See also development DBA; production DBA
Database Administrator role
    on the data warehouse project team, 7
    on the Upgrade SQL Server project team, 6
    on the VLDB project team, 8
Database Backup Plan dialog, 267
database checking method for Y2K compliance, 304
database consistency checker. See DBCC
database development process, 137–39
database environment, documenting for an upgrade, 507–10
database information model, 412
Database Maintenance Plan Wizard, 24, 130, 267
Database model, 410
database object names, 165–66
database objects, 380
database object types, 153
Database Operations role on the Upgrade SQL Server project team, 6
database population strategy in SQL Server 6.5, 519
databases
    access layers, 544
    access mechanisms in, 42–43
    access patterns, 160–62
    adding files or filegroups to, 222
    arguments for integration, 31
    backup of, 265
    checking consistency for upgrades, 39
    competency in designing schema, 15–16
    converting for an upgrade, 526–34
    converting from 6.x, 37
    creating files on raw partitions, 216
    design for VLDB projects, 128
    designing for enhanced recoverability, 387–88
    error checking before upgrading, 38–39
    exporting contents for logical backups, 269
    failures, 265
    generating applications, 24
    guidelines for project documentation, 136
    implementing and testing security, 112
    implementing automated monitoring of problems, 392
    increasing availability of production, 279
    keyword conflict resolution for, 526–30
    partitioning, 426–27
    planning, 212–15
    types of, 212–15
    upgrading to SQL Server 7.0, 305–7
database server, 179–80, 201–2
database snapshot, transferring to a replica, 266
data characteristics of HR data, 286–87
data corruption, automated monitoring for, 392
data delivery, performed by the metadata management tool from MetaEdge, 407
data dictionary, reviewing for source systems, 66
data elements
    assessing the value of, 68–69
    data standards defining, 78
    determining the availability of, 65–66
    dimensions of, 69
    drill paths for, 101
    extraction of, 67
    grouping into broad dimensions, 68
    selecting sources for, 67
data explosion, 442–43
data extraction, cleansing, transformation, and load plan, 104
data extraction from source systems, 82
data extraction/transformation/cleansing/loading architecture, 83
data factory, 345. See also factory process
data files, 216–18, 379. See also filegroups; files, adding to a database; files and filegroups
    configuring to grow or shrink automatically, 217
    designing for quick recovery, 391
    impact of several in one filegroup, 221–22
    locating, 217
    naming, 217
    placing in SAP, 197–98
    preventing automatic growth, 222
    processing in parallel, 334
    proportional fill of, 198
Data Genie, 283, 294, 315, 319, 328
data granularity, 78–79
data grouping, 442
data growth, documenting, 163–64
data historization. See chronology, cases of
data integrity, 159–60. See also data quality
    guidelines for the BCC project, 159–60
    means for ensuring, 228
data items, 142, 149–50
data lineage information, saving in DTS, 418
data loading for VLDB projects, 134
data manipulation, 245. See also transactions
data-manipulation layer, defining for applications, 228
DATAMART database, 340
datamarts, 73–74, 345
    in MS Sales, 360
    no null values in, 367
    query interface for, 299
    separating from data warehouses, 331
datamart servers in MS Sales, 369
data modeling, 209–11
data modeling tools, 68
data models
    refining, 98
    in the VLDB design process, 128–29
data owner, 66
Data Owner role, 7
data ownership for HR data, 289
data ownership program, 79
data population standards, 78–79
data pump, 368, 369
data quality, 77–78. See also data integrity
data redundancy, 451
data refresh plan, 104
data refresh process, 83, 316–17
data reorganization. See reorganization
data row count, 38
data services, 129
Data Source Office 2000 Web Component, 445
data source systems, 66
data steward, 66
Data Steward role, 7
data subject areas (DSAs), 291, 323–24
data subset for HR data, 296
data transformation in MS Sales, 352–57
Data Transformation Services. See DTS (Data Transformation Services)
data transformation tools, 82
Datatype model, 410
data types, 224–25, 350
data view restrictions, 325
data warehouse project
    activating, 52
    building the case for, 55–61
    categorizing tools for, 88–89
    controlling, 52–53
    critical success factors (CSFs), 59–60
    Critical Success Inhibitors (CSIs), 60
    development process, 49
    ending, 53, 116
    lessons learned, 119–21
    planning, 51–52
    project assumptions, 60–61
    refining requirements in terms of business needs, 64–65
    reusing objects from, 118
    rules for success, 121–22
data warehouse project team, 7
data warehouses, 27
    applying parallel processing, 424–25
    architecture, 408
    backup and recovery for, 386–94
    benefits at Microsoft, 327–28
    charging back for the use of, 106
    competencies for setting up a deployment team, 10–14
    design plan for building, 49, 50
    detailed design for, 99–108
    documentation by development staff, 113
    estimating the costs for developing and implementing, 57
    implementing (populating), 96
    information sources for, 66
    insulating from bad input data, 331–32
    integrated support for, 44
    limiting external interfaces to, 335
    purchasing and installing components, 108–9
    rolling out, 113–14
    separating data into different databases, 213
    separating from datamarts, 331
    total cost of ownership, 58
dates, testing for Y2K compliance, 304
DBA (database administrator), 98. See also Database Administrator role; development DBA; production DBA
DBCC (database consistency checker), 279–80
DBCC CHECKCATALOG command, 38, 39
DBCC CHECKDB command, 38, 39, 280
DBCC DBREINDEX command, 276
DBCC NEWALLOC command, 38, 39, 280
DBCC SHOW_STATISTICS command, 196–97
DBCC SHOWCONTIG command, 199
DBCC SHRINKFILE command, 222
DBCC TEXTALL command, 38, 39
DBCC trace flags, 526
DBCC TRACEON command, 531
DB-Library functions, 42
DBM model, 410, 424
DBMS, evaluating for a data warehouse project, 81
deadlocks, 256–59
DEC Alpha processors, 179, 180
Decision Support System (DSS) Developers role, 7
decision support system/executive information software applications (DSS/EIS tools), 84
declarative referential integrity (DRI), 159, 228
dedicated performance planning environment, 173
default filegroup, 382, 383, 390
deferred name resolution, 306
deferred updates, 463–64
delete trigger, stored procedure for creating, 489
department administrators, using MARS, 432
departmental restrictions on accessing HR data, 292
design and implementation plan, developing a detailed, 96–97
design information, 138–39
design security plan, 104
Desktop Edition of SQL Server, 453. See also SQL Server Desktop version, replication and
detailed design
    compared to architecture review and design, 71
    for a data warehouse project, 99–108
Developer role
    on the data warehouse project team, 7
    on the Upgrade SQL Server project team, 6
    on the VLDB project team, 8
development, speeding up with separate environments, 170
development DBA, 113
development environment
    for MS Sales, 373–74
    separating from the production system, 172
    sizing and configuring, 173
development function on the project team, 335
development team, 5, 370–72, 373
dictionary sort order, 463
differential backups, 389
dimensional modeling, 101, 102
dimensional structure, 102
dimension drill paths, 101
dimensions, 67, 100, 101
directory levels in file systems, 380–81
disaster recovery
    implementing and testing, 112
    planning for, 105, 393
disk controllers for VLDBs, 181
disk I/O, optimizing for VLDB projects, 131
disks
    assigning to filegroups, 220–21
    partitioning, 426
disk space
    managing, 382–86
    monitoring free, 218
disk systems for VLDBs, 181–83
distributed metadata, storing connections to, 401
distributed object-oriented technology, common repository with, 400
distributed queries from heterogeneous sources, 44
Distribution Agent, initializing the Subscriber, 482
distribution channel for MS Sales, 345–46
distribution database, recovering, 495–96
documentation
    of business rules, 334
    guidelines from Broadband Cable Communications, 136
    impact of a bullpen environment on, 288, 289
domain integrity, 159
domains, 142, 150–51
domain tables, 349
drawing conventions
    for Phase 3, 152–53
    for Phases 1 and 2, 143–48
DRI (declarative referential integrity), 159, 228
drill paths, 68, 101
DSAs (data subject areas), 291
DSS/EIS tools, 84
DTM model, 410
DTS (Data Transformation Services), 133, 134
    competency with, 11
    designing and building data warehousing solutions, 27
    manually initializing Subscribers, 491
    package concept, 27
    providing data extraction and transformation capabilities, 72
    using with OLE DB products, 44
DTS Designer, 491
    capturing metadata, 417–18
    saving package metadata and data lineage information to Microsoft Repository, 28–29
DTS Import and Export Wizards, 491
DTS model, 410
DTS packages
    building, 417
    saving, 29
    scheduling for later execution, 29
DTS Query Builder Wizard, 29
DTS repository browser and viewing tools, 28
DTS Wizard, 29, 269
DW. See data