Microsoft SQL Server 2000 High Availability

S

SANs (Storage Area Networks), 101 “2

booting Windows from, 144 “45

caching and, 102

devices for, 454

disk formatting, 122

disk subsystem design, 522

disk technology selection and, 103

server clusters and, 106 “7

sa passwords, 640

schedules, disaster recovery, 554 “55, 559 “60

schema, database, 348 “52

case sensitivity, 352

changing, 349

image fields, 349 “50

integer columns , 350 “51

primary keys, 348

server collations, 352

text fields, 349 “50

timestamp columns, 351 “52

schema design

disk space and, 89 “90

roles and, 648

scope creep, avoiding, 607

scripts

database component information, 538

disaster recovery and, 536

DISKPART, 197

generating SQL scripts, 683 “86

production change plan and, 42

replication, 364

safe storage of, 537

SCSI

Direct-Attached Storage and, 97 “98

overview of, 104 “5

Windows servers using, 159

secondary servers

adding, 333 “35

capacity, 278 “80

deleting, 320

keeping current, 279

log shipping and, 67, 75 “76, 522

log shipping more than one database to, 280

log shipping transactions and, 68 “69

promoting, 313 “15

restoring initial backup on, 303 “4

@secondary_server, 316

transactional replication and, 75 “76

Secure Sockets Layer (SSL)

encryption, 641

failover clusters and, 235

security

application security, 646 “49

Cluster Service account, 159 “60

data center and, 24 “25

documenting architecture of, 537

failover clusters, 234 “35

file system encryption, 146

log shipping, 291 “93

Microsoft.com case study, 531

models, 515

patches and hotfixes, 641

SANs and, 102

server security, 640 “46

trade-offs, 8 “9

under the desk syndrome, 29 “30

upgrades and, 590 “91, 600 “601

Windows servers, 146 “47

SELECT* FROM query, 92

SELECT INTO, 408

recovery models and, 413

selects, capacity planning, 91 “93

semisynchronous mode

merge replication, 375

transactional replication, 374

sequential I/O, 112, 113

server clusters. See also failover clusters; Windows Clustering

antivirus software and, 166

components , 54 “57, 520 “21

compression and, 122

defined, 51 “52

disk arbitration process, 105

disks and, 103 “7

encryption and, 122

groups, 58

memory management and, 678 “81

MNS cluster vs. standard, 157 “59

Network Load Balancing and, 58 “60

not mixing versions of, 616

overview of, 52 “54

resources, 57

service packs and, 628, 630 “31

support or, 261 “62

Terminal Services and, 147

upgrading SQL versions, 621 “22

upgrading Windows versions, 615 “16

virtual servers and, 57

server clusters, administering

Cluster Service account, 159 “60

disk management, 193 “97

domain changes, 190

forcing quorum for MNS cluster, 198 “99

IP addresses, 190 “91

name changes, 190 “91

password changes, 191 “93

service accounts changes, 191 “93

server clusters, geographically dispersed

cost of, 73

networking with, 168

overview of, 60 “61

planning, 166

Windows Catalog and, 163

server clusters, implementing, 170 “89

installing, 179 “80

network cards for private networks, 172 “75

network cards for public networks, 171 “72

network priorities, 175

postconfiguration tasks , 180 “89

shared disks, 176 “79

verifying installation, 189

verifying network connectivity, 176

server clusters, planning, 156 “70

antivirus programs, 166

disk subsystem, 159

domains, 167 “70

firewalls, 165

geographically dispersed clusters, 166

HCL and, 160 “64

network configuration, 167 “70

overview of, 156 “57

ports, 165

remote procedure calls, 165

service accounts, 159 “60

types of, 157 “59

Windows Catalog and, 160 “64

server level login, 287

servers

BizTalk servers, 548 “49

Commerce Server, 549

configuration, 149 “52

configuration information in run books, 538

Content Management Server, 549 “50

CustomDB1, DB2, and DB3 servers, 551 “52

data center security, 25

log shipping and, 279 “80

planning for changes, 43

primary. see primary server

proper location of, 22

risk assessment, 552

roles, 148 “52

secondary. see secondary servers

security, 146, 640 “46

SQL. see SQL Server

under the desk syndrome, 29 “30

Windows. see Windows servers

service accounts

Cluster Service account, 159 “60

domain accounts and, 599

managing, 642 “43

password changes and, 530

privileges for, 641

server clusters, 191 “93

service accounts, failover clusters

changing, 256 “58

configured prior to installation of server cluster, 222 “23

failover cluster verification and, 243

service failures and, 245

service packs, 624 “35. See also hotfixes

failover clusters and, 212, 630 “31

log shipping and, 295, 632 “33

Microsoft.com case study, 530

nonwriteable databases and, 634

nonwriteable filegroup and, 635

replication and, 360, 633 “34

rules of thumb for, 625 “26

server clusters and, 181

SQL, 64, 628 “30

system database, setting to autogrow, 128

testing prior to rollout, 581

Windows, 143 “44, 627 “28

setup information, 537

Setup.log, 263

shared cluster disk array, 55

SHOWCONTIG, 649

Shrink Database command, 129 “30

SHRINKFILE, 391

shutdown, Windows Server 2003, 152 “54

SID (system identifier), 682 “83

Simple recovery model

comparisons with other recovery models, 405 “6

as default, 561

overview of, 412

when to use, 412 “13

single-instance cluster, 63

size , database

application usage and, 88

overview of, 127 “29

shrinking, 129 “30

SLAs (service level agreements)

architectural design and, 519

backup and restore process, 72

costs of, 542

database systems, 32 “34

disaster recovery and, 541 “42, 553

documenting, 541

geographical area covered by, 516

hot-swappable drives , 110

implementation window and, 44

monitoring and, 693

negotiating availability, 14

support agreements and, 28 “29

third-party hosting and, 28

Snapshot Agent, 353 “54

snapshot backups , 98 “99, 457

snapshot replication

backing up distribution databases, 366

columns, 349

disaster recovery scenarios, 371 “72

overview of, 70

rows, 349

when to use, 347

snapshot/ split-mirror backups, 221

software. See also applications

cluster awareness and, 220 “21

RAID, 119

risk assessment, 543

serial numbers of, 536

vendor information in run book, 536

version control, 39 “40

sort order, SQL Servers, 590, 604 “5

sp_addumpdevice stored procedure, 446

sp_configure, 589

SPID (system process ID), 475

spindles

calculating number of, 108 “9

defined, 85

split-brain scenario, 157

split-mirrors

backing up databases, 98 “99, 457

implementation considerations, 458 “59

overview of, 455

pros/cons, 456

remirroring disk stripe and, 457

restoring databases, 458

sp_resolve_logins, 317

sp_spaceused stored procedure, 448

Sqlclstr.log, 263

sqldiag.exe, 500, 536, 589, 704

SQL Mail, 225

SQL Profiler, 645

SQL scripts, 683 “86

SQL Server

Direct-Attached Storage, 97 “98

I/O requirements, 595

log shipping availability, 273 “74

memory recommendations, 674 “81

Network-Attached Storage and, 98 “101

Network Load Balancing and, 202 “3, 205 “6

service accounts, 257, 642

service packs, 628 “30

Setup program, 244

Startup Account, 291

startup procedures, 589

tools for administering failover clusters, 63 “64

tools for administering virtual servers, 244

versions, 677

Virtual Device Interface (VDI), 98 “99

SQL Server 2000

Analysis Services, 224

compatibility with failover clusters, 224

Developer Edition, 297 “98

Enterprise Edition, 297 “98

instances per failover cluster, 212 “13

log shipping SQL Server 7.0 to, 340 “43

recovery models, 405

service packs, 360

Setup program, 63

upgrading between editions, 620

upgrading to. see upgrade to SQL Server 2000

Virtual Device Interface (VDI), 221

SQL Server 6.5, 619, 621 “22

SQL Server 7.0

log shipping to SQL Server 2000, 340 “43

recovery model, 400

upgrading, 619 “22

SQL Server Agent

backup jobs, 472 “74

job history, 322

log shipping jogs, 301 “2

service accounts, 258, 642 “43

SQL Server authentication, 641, 646 “47

SQL Server Enterprise Manager. See Enterprise Manager

SQL Server Notification Services, 708

SQL Server Profiler

monitoring and, 693

overview of, 704

trace core , 718 “21

Sqlspn.log, 263

Sqlstpn.lob, 263

SQLWriter, 459

SSL (Secure Sockets Layer)

encryption, 641

failover clusters and, 235

stabilizing phase, SQL Server 2000 upgrade, 612

staffing, 30 “34

architecture design and, 519

DBA team, 30 “32

help desk personnel, 43 “44

project team, 6 “7

roles and responsibilities for disaster recovery, 18, 558 “59

staging environment

creating, 36 “37

maintaining, 37 “38

stand-alone servers, Windows, 615

standardization, system and process, 48

standard server cluster. See server clusters

standby

automatic switch to, 78 “79

read-only access, 79 “80

transferring users, logins, and objects to, 681

warm standby, 348, 516

STANDBY recovery completion state, 484

startup procedures

security of, 645

SQL Servers, 589

static port assignments, 233 “34

statistics, indexes, 91

STATS options, 486

storage

backup media, 500

configuration information in run books, 539 “40

disk configuration and, 120

virtualization, 110

Storage Area Networks. See SANs (Storage Area Networks)

storage assisted backups, 455

storage controllers, 162

stored procedures

database logic, 515

log shipping, 286, 299 “301

SQL Server configuration, 589

Storport driver, 145

striped mirrors, 115 “16

striped sets, 448 “50

striping, (RAID O), 114, 449

striping with parity (RAID 5), 117 “19, 133 “37, 523

Subscribers

backing up, 367 “69

disaster recovery scenarios, 371 “72

subscription database, 367 “69

support agreements

costs of availability and, 16 “17

overview of, 28 “29

third-party hosting, 28

switches, documenting, 539

sync with backup option, 366 “67

synchronization partners , 375

synchronizing logins, 576

synchronous mode, transactional replication, 373 “74

syslogins table, backing up, 310 “11

Sysperfinfo, 714 “18

system

hangs , 625

information documentation, 589

performance metrics, 590

reconstruction, 565

standardization, 48

system databases, 369 “70

backing up prior to upgrades, 583

characteristics of, 561

consolidating, 590

masters, 370

msdb, 369 “70

server cluster design and, 520

upgrades and, 603 “4

system identifier (SID), 682 “83

System Monitor (Perfmon), 704, 709 “14

system process ID (SPID), 475

System State data, 501, 503

system tables, 605