|
|
 |
|
|
|
Microsoft® SQL Server™ 2000 High Availability
|
|
|
Author
|
|
Allan Hirt with Cathan Cook, Kimberly L. Tripp, Frank McBath
|
|
|
Pages
|
784
|
|
Disk
|
1 Companion CD(s); 1 Evaluation CD(s)
|
|
Level
|
Intermediate
|
|
Published
|
07/09/2003
|
|
ISBN
|
9780735619203
|
|
Price
|
$49.99
To see this book's discounted price, select a reseller below.
|
|
|
|
|
|
|
|
|
Index
Symbols and Numbers
/3GB boot.ini switch, 664, 667, 678
32-bit memory
physical memory, 658
SQL Server instances, 677
Windows versions, 666
64-bit memory
physical memory, 658
Windows versions, 674
A
access control lists (ACLs), 243
account passwords, 640-41
ACLs (access control lists), 243
active/active clustering, 62-63
active/passive clustering, 62-63
addresses, failover clusters, 248-49
Address Windowing Extensions (AWE), 668-70, 678
administration, 639-90
application security, 646-49
database configuration, 657
database corruption, 656
defragmenting indexes, 653-54
defragmenting VLDBs, 655-56
DTS package transfers, 689-90
failover clusters. see failover clusters, administering
files and filegroups, 126
logical vs. physical fragmentation, 654-55
login transfers between instances, 682-89
login transfers to standby, 681
maintenance, 649-53
memory. see memory
object transfers between instances, 682-89
object transfers to standby, 681
resource management, 681
server clusters. see server clusters, administering
server security, 640-46
SQL Server 2000 upgrade and, 602-3
user transfers to standby, 681
user transfers between instances, 682-89
affinity mask option, 597-98
alerts
automating data collection, 713
configuring for file size, 129
setting up, 704
SQL Server, 706-7
transaction log backups, 469-71
ALTER DATABASE Transact-SQL command, 128
Analysis Services, SQL Server 2000
administrative information in run books, 537
cluster awareness and, 224-25
antivirus software
failover clusters and, 229
server clusters and, 166
API (application programming interface), 54
application programming interface (API), 54
applications
compatibility with database instances, 595
database, 88-93
dependencies, not configuring as, 220-21
deploying, 612
failover capability, 219-20
failover clustering and, 66, 244
log shipping and, 69, 288-90
replication, 71
run books and, 537
security, 646-49
upgrades, 618
arbitration process, 105
architecture
disk subsystem, 595
hardware, 111
high availability, 518-19
replication, 352
security, 537
ASR (Authoritative System Restore), 501
attaching/detaching databases, 622-24
attacks, 531
auditing
data center security, 25
events with SQL Profiler, 645
production change plan, 42
authentication
application security and, 646-47
log shipping and, 293
types of, 641
Authoritative System Restore (ASR), 501
authorization, 24-25
autogrow settings, 128-29
Automatic Recovery, 388-89
Automatic switch to standby, 78-79
automating backups, 466-72
alerts, 469-71
preventing human intervention errors, 501
schedule for, 466-69
time interval criteria, 467
transaction log backups, 467-69
automating collection of data, 712-14
AUTO_SHRINK property, 130
availability, 513-32
assessing, 15-16
architecture, 518-19
barriers to, 18
calculating, 10-11
conditions and constraints, 514-15
cost of, 16-17
customers and, 16-17
defined, 4, 8
documentation, 49
guiding principles of, 7-8
nines, 10-12
reporting server, 522
AWE (Address Windowing Extensions), 668-70, 678
B
back-end deployment, 612
backup and restore
attach/detach database and, 624
backup process, 384-85, 416-17
barrier categories, 381-82
database log behavior and, 400-404
database structures and, 385
differential database backups, 424-26
differential file/filegroup backups, 427
differential rotation, 438-39
file-based backup strategy, 440-42
full database backups, 418-22
full database-based backup strategy, 435-38
full file/filegroup backups, 426-27
overview of, 379-80
recovery models. see recovery models
restore process, 71-72
retention period, 433-34
SQL Server 7.0 upgrades and, 620
strategy, 429-33, 443-44
symptoms and recovery, 384
technology for, 380
third-party software, 509
user error and, 382-84
write-ahead log, 386-90
backup and restore, implementing, 444-79
automating and simplifying, 466-72
backup devices, 445-46
backup job completion, 474-75
backup options, 444-45, 452-54
clustered environments and, 507-8
database recovery. see database recovery
file-based backup, 462-65
full database-based backup, 460-62
multifile backups, 450-52
NUL device, 447-48
operating systems, 501-3
parallel striped backup device, 448-50
split-mirrors, 455-59
SQL Server Agent backup jobs, 472-74
storage assisted backups, 455
third-party hardware and tools, 454-55
tips and best practices for, 499-501
verifying backups, 475-78
Volume Shadow Copy Service (VSS), 459-60
Windows Backup, 502-7
backup and restore, transaction logs
backups, 422-24
continuity, 392-94, 397
full, 394-97
management, 398-99
performance, 390-91
BACKUP DATABASE command, 460, 462
backup devices
creating, 445-46
multifile, 450-52
NUL, 447-48
parallel striped, 448-50
reading, 485
removing, 446
BACKUP LOG command
WITH NO_LOG, 394, 397
WITH TRUNCATE_ONLY, 394, 397
backup logs
@backup_log, 312
reviewing, 501
backup media
accessing media sets, 485
LTO (Linear Tape-Open), 438-39
manipulating, 453
reading, 485
rotating, 433
storing, 434
tape, 499, 501
tape devices, 453-54
backups. See also backup and restore; recovery models
backup share, 292
change implementation and, 46
defining individual, 452
disaster recovery without, 271-72
documenting, 541
file/filegroup protection, 125-26
hardware-assisted, 221
hotfixes and, 625
log shipping and, 293-94
master database and, 571-73
naming conventions, 417
Network-Attached Storage and, 98-99
options, 444-45, 452-54
overview of, 71-72
planning, 44
recovery models and, 415
replicated databases and, 364-65
run books and, 536
service packs and, 625
storage location, 434
strategy for, 384-85
types of, 416-17
upgrades and, 583
Backup tool. See Windows Backup
backup windows, 385
bandwidth
log shipping and, 75, 285-86
Network-Attached Storage and, 99, 100
system, 111
transactional replication and, 75
baselines, 702
Baseline Security Analyzer, 146
basic disks
creating, 177-78
failover clusters and, 218
overview of, 121
batch processes, 382, 384
BCP, 619-20
BCV (business-continuance volume), 457
binary storage, 500
bits of physical memory
32-bit, 658, 666, 677
64-bit, 658, 674
BizTalk servers, 548-49
blocking, extended, 721
block size, disk formats, 122
boot.ini
/3GB boot.ini switch, 664
/MAXMEM switch, 673-74
/USERVA boot.ini switch, 665
budgets
availability and, 16-17
constraints, 522-25
cost as barrier, 18
dedicated environments, 37
disk subsystems, 520-22
geographically dispersed clusters, 60
log shipping vs.transactional replication, 75
technology selection and, 73
tradeoffs, 526
BULK INSERT, 619-20
Bulk-Logged recovery model
comparisons with other recovery models, 405-6
log shipping and, 283-84
overview of, 409-11
when to use, 412
business-continuance volume (BCV), 457
C
C2 security, 645-46
cabling
allowing room for, 23
data center best practices, 26-27
caching
disk, 113-14
SANs and, 102
capacity planning, 86-97
application database, 88-93
deletes, 91-93
disk space needed, 87-88
high availability solutions, 514
indexes, 90-91
inserts, 91-93
monitoring, 724
overview of, 86-87
physical disk performance, 93-96
selects, 91-93
SQL Server assisting with, 96-97
updates, 91-93
case sensitivity, database schema, 352
chain of command, disaster recovery, 554
challenge/defense protocol, 105
change management, 35-48
development and, 38-41
environments and, 36-38
implementing, 46-48
overview of, 35-36
production and, 41-45
change plan, 42
change request forms, 37-38, 45
chargeback, upgrade costs, 584, 603
CHECKALLOC, 649
CHECKCONSTRAINTS, 649
CHECKDB, 649-50
CHECKFILEGROUP, 649
checkpoint files
corrupt or missing, 268, 272
failover clusters, 507-8
checkpoints, 387, 389
CHECKTABLE, 649
client transparency, 78, 80
clients
failover clustering and, 66
log shipping and, 69
network connectivity and, 176
replication and, 71
climate control
data center location and, 21, 23
disk drives and, 109
clones, 457
CLUSDIAG, 508
Clusrest.exe, 266
Cluster Administrator
failback settings, 237-38
failover threshold settings, 238-41
failover validation, 189, 242-43
IP address changes, 190-91
Kerberos, enabling, 182
MNS cluster, forcing quorum, 198-99
MS DTC on Windows 2000 Server, 184-87
MS DTC on Windows 2003 Server, 187-89
name changes, 190-91
network priority configuration, 181-82
overview of, 244
preferred owner settings, 236-37
server clusters, adding disks to, 194-95
cluster command-line utility, 244
cluster database
corruption on node, 267
log files, 507
snapshot files, 507
cluster disk, corrupt or nonfunctional, 271
clustered indexes, 91
clustered servers. See server clusters
Cluster.exe, 191, 198
cluster-fixquorum command, 266-67
cluster groups
configuration, 226
thresholds, 240-41
virtual servers and, 58
cluster nodes
adding/removing, 245-47
available for failover clusters, 214-16
database corruption and, 267
disaster recovery and, 271-72
failover verification and, 243
failure, 268-70
Network Load Balancing, 58
overview of, 54-55
service packs and, 630
SQL Server implementations and, 62
upgrades and, 616
virtual servers and, 54-55
Cluster Service account
changing server cluster domain, 190
forcing quorum for MNS cluster, 199
overview of, 159-60
Clustering API (application programming interface), 54
Cluster.log, 263
ClusterLogSize, 182-83
clusters. See failover clusters; server clusters
CLUSTOOL, 508
Clustool.exe, 266
code reviews, 648
collations, SQL Server, 590, 604-5
columns, database schema, 349
Comclust.exe, 184
Commerce Server, 549
communications
data center best practices, 25-27
disaster recovery and, 554
DBA team, 31-32
compatibility
availability technologies and, 73
failover clusters and, 224
log shipping vs. transactional replication, 76
server clusters and, 53-54
completion states
NORECOVERY, 484
RECOVERY, 483-84
STANDBY, 484
compression, 122
computer management, 122
configuration settings
documenting, 539
SQL Server 2000 upgrade and, 591
connectivity
domain connectivity, 278
encryption and, 647-48
failover clusters and, 241-42
troubleshooting, 264-65
verifying network, 189, 647-48
consolidation planning phase, SQL Server 2000 upgrade, 606-11
migrating applications, users, and data, 606-7
production server design, 606
risk assessment, 610-11
testing processes, 607-10
contact information, documenting, 537, 540
containers, 86
Content Management Server, 549-50
contingency plans, 42-43
contracts. See support agreements
convergence, Network Load Balancing, 58
costs. See budgets
counters
Sysperfinfo, 714-18
System Monitor, 709-12
CRM (customer relationship management), 92-93
CustomDB1 server, 551-52
CustomDB2 server, 551-52
CustomDB3 server, 551-52
customer relationship management (CRM), 92-93
custom log shipping, 296-97, 339-40
D
damage assessment, disaster recovery, 562-63
dark fiber networks, 60
DAS (Direct-Attached Storage), 97-98, 103
data, 276-77
data centers, 19-30
cabling, 25-27
communications systems, 25-27
location, 21-24
networks, 25-27
overview of, 19-20
power supply and, 25-27
security, 24-25
support agreements, 28-29
third-party hosting, 27-28
"under the desk" syndrome, 29-30
data definition language (DDL), 349
data interfaces, 536
Data Source Names (DSNs), 290, 539
Data Transformation Services. See DTS (Data Transformation Services) packages
data warehouses, 113, 122
database administrators. See DBAs (database administrators)
database identifier (DBID), 281
Database Maintenance Plan
backup options, 444
initial backups, 303
log shipping configuration, 304-8, 324-26
primary server and, 323
removing log shipping, 319-20
secondary servers, adding, 333-35
database recovery, 479-99
completion states, 483
file-based backup, 490-95
file creation phase, 481-82
full database-based backup, 487-89
hardware failure, 487
media copy phase, 482
moving data to new location during, 498-99
NORECOVERY completion state, 484
overview of, 479-81
phases of, 481
point-in-time recovery, 496-97
RECOVERY completion state, 483-84
redo and undo phases, 483
restore options, 485-86
STANDBY completion state, 484
types of failure and, 486-87
database system engineers (DSEs), 41-45
database-level login, 287
databases
access methods, 646
attaching/detaching vs. backup and restore, 622-24
backing up, 293-94
backing up replicated databases, 364-65
configuration options, 657
corruption, 656
@database, 316
dependencies, 545-49
instances, 593-94
list of, 544-45
log shipping and, 280
log space monitoring, 722
placement of, 124-26
read-only access, 274-75
replication, 345-46, 348-52
risk assessment, 544-45
run books and, 538
size of, 88, 127-30
structures, 385
synchronization methods, 517
transaction logs, 401-4
VLDBs (very large database), 430
DBAs (database administrators)
change management, 35, 41-45
documentation and, 31
focusing on requirements before technology, 515
hardware procurement, 131
Microsoft.com case study, 527
recovery model and, 407-8
rollout implementation, 46-47
SQL Server tools and, 64
team creation, 30-32
upgrading SQL Servers and, 617
DBCC CHECKDB, 500, 651-52
DBCC DBREINDEX, 652, 654-56
DBCC INDEXDEFRAG, 126, 649-51, 653, 656
DBCC INPUTBUFFER, 721
DBCC OUTPUTBUFFER, 474-75
DBCC SHOWCONTIG, 651
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES, 652-53
DBCC SHRINKDATABASE, 130, 654
DBCC SHRINKFILE, 130
DBCC SQLP-ERF(LOGSPACE), 467
DBID (database identifier), 281
DBREINDEX, 649
DDL (data definition language), 349
deadlocks, monitoring, 721
Debug directory, Windows Server 2003, 152
debugging code, 648
decision process, availability technology, 72-74
declarative referential integrity (DRI), 383
default instances, databases, 62, 593-94
defragmenting
indexes, 653-54
VLDBs, 655-56
deletes, capacity planning, 91-93
demilitarized zone (DMZ), 167, 516
dependencies
application, 540
failover clusters and, 220-21
risk assessment and, 545-48
deployment
availability technology selection, 73
Network-Attached Storage, 99
rollout implementation, 46-47
tasks following, 47-48
deployment phase, SQL Server 2000 upgrade, 612
detach/attach databases, 622-24
developers, Microsoft.com case study, 530-31
developing phase, SQL Server 2000 upgrade, 611
development
change management and, 38-41
environment, 36-38
Microsoft.com case study, 528-29
monitoring, 692
overview of, 38-41
device drivers, 107, 562
differential database backups
costs of, 439
executing with Transact-SQL, 462
full database-based backup and, 435
how they work, 425-26
log shipping and, 293-94
overview of, 424-25
differential file/filegroup backups, 427
differential rotation, 438-39
Direct-Attached Storage (DAS), 97-98, 103
direct code, 515
disaster recovery, 533-77
availability costs and, 16-17
backup and restore. see backup and restore
chain of command, 554
communication plan, 54
damage assessment, 562-63
defined, 4
executing, 557-60
failover clusters. see failover clusters, disaster recovery
full-text indexes, 575-76
implementing, 553-56
master databases, rebuilding, 566-70
master databases, restoring, 571-73
model database, 574-75
msdb database, 573-74
MTTR and, 11
overview of, 5-6
planning, 534
reconstruction, 563-65
replication. see replication, disaster recovery
risks. see risk assessment
run book, 534-41
SLAs and, 541-42
synchronizing logins, 576
techniques, 560-61
tempdb, 566
testing, 556-57
upgrades and, 582
user databases, 575-76
disk arbitration process, 105
disk cache, 113-14
disk configuration, 85-138
capacity planning. see capacity planning
database file size, 127-30
disk cache, 113-14
disk drives, 109-10
disk subsystem types, 97-103
disk types, 121-22
example of, 130-37
failover clustering, 103-7
file placement and protection, 123-27
file system types, 121-22
formats, 122-23
hardware, 110-11
RAID and, 114-19
remote mirroring, 119-20
server clusters, 103-7
spindles, number of, 108-9
SQL Server interaction with disks, 112-13
storage composition, 120
terminology, 85-86
Windows requirements, 144-46
disk drives
adding to failover clusters, 229-31
adding to server clusters, 194-95
configuration, 232
disk space and, 87-88, 625
DISKPART and, 195-97
documenting, 538
log shipping requirements, 280-81
monitoring, 722
overview of, 109-10
performance counters, 93-96
planning disk subsystem, 518-19
redundancy of, 381
replication requirements, 360-63
shared, 176-79
Windows, 121
disk drives, failover clusters
configuration, 216-18
corrupt or nonfunctional, 271
disaster recovery when there is no backup, 271-72
quorum disk corruption, 267-68
quorum disk failure, 266-67
Disk Management, Terminal Services, 147
disk mirrors. See split-mirrors
disk space
capacity planning and, 87-88
upgrades and, 625
disk stripes, 457
disk subsystem
architecture, 518-19, 595
budgets, 520-22
SQL Server 2000 upgrade and, 595
types of, 97-103
Windows Server 2003, 159
DISKPART, 195-97
LIST DISK command, 197
LIST VOLUME command, 196
scripts, 197
Distance limitations, availability technology, 78, 80
distributed databases, 538
Distributed Transaction Coordinator (DTC), 188, 605
Distribution Agent, 353, 355
distribution database, 124-25, 366
Distributors
backing up, 366
disaster recovery scenarios, 371-72
separating from Publishers, 356-58
server cluster design and, 520
DMI tools, 695, 703
DMZ (demilitarized zone), 167, 516
DNS (Domain Name Service) server, 200, 206
documentation. See also run book
application tests, 40-41
data interfaces, 536
DBAs skill in, 31
overview of, 49
patches, 581-82
risk exposure and, 525
rollout implementation and, 46-47
run book, 534-41
system, 589
upgrades and, 581-82, 625
version control, 40
domain accounts, 291, 599
domain connectivity, 278
Domain Name Service (DNS) server, 200, 206
domains
failover clusters, 259-60
log shipping across, 292
server clusters, 167-70, 190
downtime
acceptable, 276
adding disk to cluster, 107
availability and, 10-11, 16-17
business impact of, 516
log shipping and, 75, 274
risk assessment, 9-10
transactional replication and, 75
DRI (declarative referential integrity), 383
drives. See disk drives
DSEs (database system engineers), 41-45
DSNs (Data Source Names), 290, 539
DTC (Distributed Transaction Coordinator), 188, 605
DTS (Data Transformation Services) packages
database synchronization, 517
moving, 287, 689-90
storing as files, 536
transactional replication and, 369
transferring logins, 309-10
transferring objects, 686-89
Dumpcfg.exe, 266
dynamic disks, 121, 218
dynamic DNS, 200
E
e-commerce environment, 43-44
e-mail, 47
encryption
file system, 645
SSL (Secure Sockets Layer), 235, 641
types of, 647-48
Windows Server 2003 and, 122, 146
Enterprise Manager
backup devices, 446
database growth properties, 128
job setup, 467
Replication folder, 364
service accounts, 642-43
shrinking database, 129-30
SQL Server alerts, 706-7
Enterprise Qualification Process, 161
environments
availability assessment, 15-16
change management and, 36-38
clusters and, 507-8
consolidating, 605
e-commerce, 43-44
inventorying for SQL Server 2000 upgrade, 592
production, 529
risk assessment, 542-43
envisioning phase, SQL Server 2000 upgrade, 586-93
profiling target systems, 586-93
SQL Server information, gathering, 590-93
system performance metrics, gathering, 590
technical planning questions, 586-93
Event Viewer
log shipping status, 322-23
overview of, 704
Windows Server 2003 shutdown and, 152-54
F
failover clusters, 211-72
advantages of, 81
Analysis Services and, 224-25
applications and, 219-20
availability technologies, comparing, 78-80
backup and restore, 507-8
clients, 66
Cluster Group configuration, 226
cluster nodes, 214-16
compatibility issues, 224
components, 520-21
database placement and, 123, 125-26
defining, 53
dependencies, 220-21
disaster recovery. See disaster recovery, failover clusters
disk array design, 131-37
disk configuration, 103-7, 216-18
hardware-assisted backups and, 221
instances per cluster, 212-13
IP addresses dedicated to, 218-19
log shipping compared with, 77-78
memory configuration, 223-24
Microsoft.com case study, 528
Microsoft Exchange Server and, 225-26
naming, 213-14
network cards, 219
Network-Attached Storage and, 99
networking and, 599
planning for, 211-12
ports, 219
previous implementations, 62-64
replication compared with, 77-78
server clusters configured as, 156-57
service accounts and, 222-23
SQL Mail and, 225
SQL Server and, 64-66
third-party backup software, 509
validating, 189
Windows versions supported, 212
failover clusters, administering, 244-60
cluster nodes, adding/removing, 245-47
domains, changing, 259-60
manually removing, 251-56
renaming, 249
service failures and, 245
SQL Server Agent service accounts, changing, 258
SQL Server service accounts, changing, 256-57
TCP/IP addresses, adding/changing/updating, 248-49
tools for, 244-45
uninstalling, 249-51
failover clusters, disaster recovery, 265-72
checkpoint files, lost or corrupt, 268
cluster database corruption on node, 267
cluster disk corrupt or nonfunctional, 271
cluster node failure, 268-70
overview of, 265-66
quorum corruption, 267-68
quorum disk failure, 266-67
when there is no backup, 271-72
failover clusters, implementing
antivirus software, 229
Cluster Group thresholds, 240-41
disabling unnecessary services, 233
disk drives, adding, 229-32
installation order, 227-28
installing virtual servers, 229
IPSec, 234
Kerberos, 234-35
prerequisites, 227
resource failover preferences, 237-40
resource ownership preferences, 236-37
SSL certificates, 235
static port numbers, assigning, 233-34
failover clusters, testing
applications and, 244
cluster node participation, 243
connectivity and name resolution, 241-42
failover validation, 242-43
service accounts and, 243
failover clusters, troubleshooting, 260-65
cluster support and, 261-62
connectivity problems, 264-65
Full-Text Search resource types and, 265
installation problems, 264
log files, 263
MPS Reporting Tool, 263
problems, list of, 260-61
process sequence, 262
Failure detection, 78-79
failures. See damage assessment
FAT (file allocation table), 121
FC-AL (Fibre Channel-Arbitrated Loop), 145
Fibre Channel-Arbitrated Loop (FC-AL), 145
Fibre Channels
large cluster configurations and, 103
overview of, 104-5
Windows servers using, 159
file allocation table (FAT), 121
file creation phase, database recovery, 481-82
file-based backups
automating, 440
executing with Transact-SQL, 462-65
filegroup organization and, 440-41
frequency of, 440
pros/cons, 443-44
recovering in place and up to the minute, 490-95
filegroups
implementing, 126-27
log shipping and, 295-96
files
encryption, 645
@filename, 315
implementing, 126-27
log shipping and, 295-96
NTFS file system, 121-22
permissions, 645
placement and protection of, 123-27
replication, 354
shares, 220-21, 539
size of, 127-30
types of Windows' system, 121-22
filters, trace data, 721
fire suppression system, data centers, 23
firewalls, 165
firmware, 107, 538
::fn_trace_gettable, 719
fn_virtualfilestats function, 96-97
ForceQuorum key, 198-99
/forcequorum switch, 198
formats, disk, 122-23
fragmentation
logical vs. physical, 654-55
monitoring, 722-23
full database backups, 418-22
conflicts with, 420-22
costs of, 438
full database-based backup and, 435
log shipping and, 293-94
overview of, 418
steps in process of, 419-20
full database-based backup, 435-38
automating backups, 435
example of, 436-38
executing with Transact-SQL, 460-61
frequency of backups, 435
pros/cons, 443
recovering in place and up to the minute, 487-89
full file/filegroup backups, 426-27
Full recovery model, 283-84
comparisons with other recovery models, 405-6
overview of, 408
when to use, 409
full-text indexes
log shipping and, 69
placement of, 125
replication and, 71
restoring, 575-76
full-text searches
log shipping and, 281-83
resource types, 265
Full-text support, 79-80
G
"go/no-go" meeting, 45
graphical user interface (GUI), 670-71
GUI (graphical user interface), 670-71
H
hackers, 531
hardware
architecture, 518-19
availability technology, 74
backup and restore barriers, 381
DBA role in procurement, 131
disk configuration, 110-11
disk drives, 109-10
duplicates in case of failure, 553
failures, 562
monitoring, 702-3
RAID, 119
recovering from failures, 487
requirements, 78, 80
selection and costs, 519
vendor information, 536
Windows Server 2003, 155
hardware-assisted backups, 221
Hardware Compatibility List. See HCL (Hardware Compatibility List)
HBAs (host bus adapters)
booting Windows from SANs, 145
server clusters and, 105, 521
support for, 162
HCL (Hardware Compatibility List), 615
geographically dispersed clusters, 60
SANs and, 106-7
server cluster configuration, 53
server clusters and, 160-64
health checks, SQL Server 2000 upgrade, 592
heartbeat. See private networks (heartbeat)
help desk personnel, 43-44
high availability, preparation, 2-18
barriers, 18
calculating availability, 10-11
costs, 16-18
defining, 4
disaster recovery, 5-6
environment assessment, 15-16
guiding principles, 7-8
negotiations, 13-14
nines, 11-13
overview of, 3-4
prevention, 4-5
project team, 6-7
risk assessment, 9-10
trade-offs, 8-9
types of unavailability, 14
host bus adapters. See HBAs (host bus adapters)
hosting, third-party, 27-28
hotfixes. See also service packs
adding disk to Windows 2000 Server, 194
critical, 624
emergency, 626-27
Microsoft.com case study, 530
rules of thumb, 625-26
server clusters, 181
testing prior to rollout, 581
when to use, 635-36
hot-swappable drives, 110
HTML, 712
hub, server cluster design, 521
I
IDE (Integrated Device Electronics) disks, 158
identity theft, 640
IGMP (Internet Group Membership Protocol), 200
IIS (Internet Information Server)
dependencies, 549
IIS Common Files, 180
image data, filegroup backups, 442
image fields, database schema, 349-50
indexes. See also full-text indexes
defragmenting, 653-54
monitoring, 722-23
overview of, 90-91
initial database, log behavior, 400-404
input/output. See I/O (input/output)
inserts, capacity planning and, 91-93
installation order, failover clusters, 227-28
instances, database
application compatibility and, 595
memory management and, 678-81
single vs. multiple, 593-94
SQL Server 2000 technology, 62
transferring users, logins, and objects between, 682-89
integer columns, 350-51
Integrated Device Electronics (IDE) disks, 158
Integrated Security, 641
InterchangeDTA, 549
Internet Group Membership Protocol (IGMP), 200
Internet Information Server (IIS)
dependencies, 549
IIS Common Files, 180
I/O (input/output)
MPIO (multipath I/O) stack, 106, 162
random I/O, 112
readahead I/O, 112
sequential I/O, 112-13
SQL Server requirements, 595
IP (Internet Protocol) addresses
failover clusters and, 218-19
MS DTC on Windows Server 2003, 187
network connectivity, 176, 189
Network Load Balancing and, 201
server clusters and, 167, 190-91
static port assignments, 233
virtual servers, 54
IPSec, 234
IsAlive process, 56, 64-65
J
jobs
backups, 472-75
changing primary, 316
demoting primary, 311-13
history, 322
log shipping, 301-2
login resolution, 315-16
promoting secondary, 313-15
role changes, 311-16
setting up, 467-69
syslogin backup, 310-11
K
Kerberos
enabling, 182
failover clusters and, 234-35
Windows Server 2003 encryption and, 122
KillPwd, 645
L
Lamport, Leslie, 105
large tables, 442
latency, log shipping, 279
layers, monitoring in, 695
leadership, DBA team, 32
Linear Tape-Open (LTO), 438-39, 500
LIST DISK command, 197
LIST VOLUME command, 196
load balancing, 58-60, 67
load tests, 44-45, 516
Local Backups, 501
Local Distributor, 356
local network accounts, 291
local system accounts, 291
locks
data center security, 25
locking hints, 92-93
logical disk performance counters, 93
logical disks, 86
logical failures, 562
logical fragmentation, 654-55
logical unit, LUNs, 85-86
logins
DTS packages for transferring, 309-10
levels of, 287
multiple databases and, 280
replication and, 348
secure transfer of, 292
SQL Server 2000 upgrade and, 590-91, 600-601
synchronizing following recovery, 576
transferring between instances, 682-89
transferring to standby, 681
Log Reader Agent, 353, 355
logs
cluster size and, 182-83
failover clusters, 263
monitoring, 704, 722
Network Load Balancing Manager and, 206-7
OLTP systems and, 113
tempdb system database and, 124
UNC paths and, 713
log sequence number (LSN), 283
Log Settings dialog box, 206-7
log shipping, 273-343
advantages of, 81
applications and, 288-90
authentication, 293
availability technology and, 78-80
built-in, 296-97
business considerations, 276-77
clients, 69
custom, 296-97, 339-40
database backups and, 293-94
database synchronization, 517
data captured by, 286
defragmenting VLDBs, 655-56
disk capacity and, 280-81
DTS packages, 287
failover clusters compared with, 77-78
files and filegroups, 295-96
full-text searches, 281-83
latency, 279
logins, 287
merge replication, 374-75
multiple databases, 280
network bandwidth, 285-86
overview of, 67-68, 273-75, 372-73
recovery models for, 283-85
role changes, 290, 375-77
secondary, 522
security, 291-93
server capacity and configuration, 279-80
service packs and, 295
split-mirror backups and, 456
SQL Server 7.0 to SQL Server 2000, 340-43
SQL Server 7.0 upgrade and, 619
SQL service packs and, 632-33
stored procedures, 286
technical issues, 277-79
transactional replication and, 74-77, 373-74
transactions, 68-69
uses of, 273-275
log shipping, administering, 319-39
Log Shipping Monitor, 329-33
monitoring, 321-23
parameters, changing, 324-28
primary, switching back to original, 339
removing, 319-20
role changes, 335-38
secondary, adding, 333-35
secondary, deleting, 320
log shipping, configuring, 297-319
components, 298-99
Database Maintenance Plan Wizard, 304
DTS packages for transferring logins, 309-10
jobs, 301-2
log shipping monitor, 302
overview of, 297-98
parameters, 305-8
post-configuration tasks, 309
restoring initial backup on secondary, 303-4
role changes, 311-16
stored procedures, 299-301
syslogin backup and, 310-11
troubleshooting, 318-19
verifying configuration, 317
Log Shipping Monitor, 67-68
monitoring log shipping with, 321-22
moving to another SQL Server, 329-33
overview of, 302
tweaking log shipping configuration, 327-28
verifying log shipping configuration, 317
log space, 722
log_shipping_monitor table, 331-32
LooksAlive process, 56, 64-65
low-level failures, 562
LRQ_Trace_Start, 718-21
LSN (log sequence number), 283
restoring transaction logs and, 494
LTO (Linear Tape-Open), 438-39, 500
LUNs, 85-86
M
maintenance. See also Database Maintenance Plan
Analysis Services documentation and, 537
costs, 650
files and filegroups, 126
intrusive, 294, 652-53
log shipping and, 278
medium impact, 651
plans in run book, 535
routine, 649-50
Windows Server 2003 enhancements, 156
majority node set (MNS) cluster, 157-59, 198-99
Manage Your Server, 148
MAPI, 225
MapSids utility, 682-83
masking, 106-7, 145
Masks disk failure, 78-79
Masks SQL process failure, 78-79
master database
failover clusters and, 65
file protection and, 125
upgrades and, 603-4
master databases
backing up, 370
log shipping multiple databases, 280
rebuilding, 561, 566-70
restoring, 571-73
stored procedures, 299-300
/MAXMEM swith, 673-74
MCS (Microsoft Cluster Service), 52
MDAC 2.6, 595
mean time between failures (MTBF), 11, 110
mean time to recovery (MTTR), 11
media banding, 110
media copy phase, 482
Media Sense, 170
media sets, 452, 485
memory, 657-81
32-bit, 141-42, 677
/3GB boot.ini switch, 664
64-bit, 141-42, 674
accessing, 663
AWE (Address Windowing Extensions), 668-70
clusters and, 678-81
configuration for failover clusters, 223-24
memory split in Windows, 658-60
multiple instances and, 678-81
overview of, 657-58
PAE (Physical Address Extensions), 665-67
paging files and, 660-61, 670-74
physical, 658
recommendations, 674-81
requirements, 519
tuning options, 664
upgrades and, 596-98
/USERVA boot.ini switch, 665
virtual, 661-62
Windows servers and, 140-41
Merge Agent, 353, 355
merge replication
disaster recovery scenarios, 371-72
distribution databases, 366
log shipping and, 374-75
overview of, 69
primary keys and, 348
publication databases, 366
subscription database, 367
table rows and columns, 349
when to use, 346
Message Queueing (MSMQ), 549
Metadata support, 78-79
Microsoft Baseline Security Analyzer, 146, 644-45
Microsoft Cluster Service (MCS), 52
Microsoft.com case study, 527-31
background, 527-28
barriers to availability, 530-31
failover clusters, 528
high availability and, 527-31
planning and development, 528-29
production environment, 529
Microsoft Developer Network (MSDN), 45
Microsoft Distributed Transaction Coordinator. See MS DTC (Microsoft Distributed Transaction Coordinator)
Microsoft Exchange Server, 225-26
Microsoft Operations Framework (MOF), 35-36
Microsoft Operations Manager (MOM), 681, 703, 705-6
Microsoft Product Support Services (PSS)
failover clusters and, 225-26
list of failover problems, 260-61
MPS (Microsoft Product Support) Reporting Tool, 263
production change plan and, 42
Microsoft Search Service, 265, 605
Microsoft Solutions Framework (MSF), 35-36
Microsoft Support Life Cycle, 29
Microsoft Visio, 539
Microsoft Visual Source Safe, 537
Microsoft Web site information
antivirus programs, 166
change management, 35
disk arbitration process, 105
firewalls, 165
geographically dispersed clusters, 60
HCL (Hardware Compatibility List), 160
index tuning, 722
Microsoft Support Life Cycle, 29
MOM (Microsoft Operations Manager), 706
monitoring, 702
Network Attached Storage, 101
Network Load Balancing, 199, 201
ports, 165
RPCs (remote procedure calls), 165
server clusters, 53, 164, 181
testing implementations, 45
troubleshooting Sysperfinfo, 718
Windows Catalog, 160-61
Windows Server 2003, 64 bit, 142
Windows server security, 146
WMI (Windows Management Instrumentation), 705
Microsoft Windows Authentication, 641, 646-47
migration. See also upgrades
applications and data, 606-7
objects, 602
between SQL Servers, 274
users, 606-7
mirrored stripes, 116-17
mirroring (RAID 1)
overview of, 115
sample drive configurations, 133-37
MNS (majority node set) cluster, 157-59, 198-99
model database, 574-75, 603-4
MOF (Microsoft Operations Framework), 35-36
MOM (Microsoft Operations Manager), 681, 703, 705-6
monitoring, 691-726. See also Log Shipping Monitor
automating data collection, 712-14
availability and, 699-701
capacity planning and, 724
critical services, 723-24
database log space, 722
deadlocks, 721
Event Viewer and, 704
extended blocks, 721
ground rules, 696-99
hardware layer, 702-3
index fragmentation, 722-23
log files, 704
log shipping, 321-23
Microsoft.com case study, 528
Microsoft Operations Manager (MOM), 705-6
overview of, 692-95
Profiler/Trace Core, 718-21
read/write statistics per file, 723
sqldiag.exe, 704
SQL Server alerts, 706-7
SQL Server Notification Services, 708
SQL Server Profiler, 704
Sysperfinfo and, 714-18
System Monitor, 704, 709-14
system uptime, 708
transaction log space, 722
WMI, 704-5
mountpoints
failover clusters, 218
overview of, 121
volumes, 178-79
MPIO (multipath I/O) stack, 106, 162
MPS (Microsoft Product Support) Reporting Tool, 263
msdb database
backing up, 500
file protection and, 125
log shipping more than one database, 280
log shipping tables, 300-301
replication and, 369-70
restoring, 573-74
sample drive configurations, 133-37
size of, 88, 603
SQL Server 2000 upgrade and, 603-4
stored procedures in, 299-300
MSDN (Microsoft Developer Network), 45
MS DTC (Microsoft Distributed Transaction Coordinator)
configuring, 183
creating on Windows 2000 Server, 184-87
creating on Windows 2003 Server, 187-89
server cluster design and, 520
MSF (Microsoft Solutions Framework), 35-36
MSMQ (Message Queueing), 549
MTBF (mean time between failures), 11, 110
MTTR (mean time for recovery), 11
multifile backups, 450-52
multipath I/O (MPIO) stack, 106, 162
multiple-instance clusters, 63
Next
Last Updated: June 19, 2003
|
