D

DAC (Dedicated Administrator Connection)

configuring, 80–81

default settings for, 75

data cache, clearing, 467

data column, for tracing, 418

data integration and transformation. See SSIS (SQL Server Integration Services)

data locality, 396

data modification plans, 477–478

data pages, maximum size of, 5

data source view (DSV), 182

data transfer rate, 364

data types

changing, 9

custom, creating, 9

displaying for columns in a table, 9–10

list of, 10–12

data-access method, SQL Native Client as, 5

database. See also backup and recovery

for database mirroring

mirror database, 548, 569–570

principal database, 548, 567–568

setting up, 555–556

witness database, 569–570

default location for, setting, 42, 89

features for, default settings for, 75

master database

backing up, 7, 601–602

definition of, 6–7

objects in, 7

specifying at startup, 83

model database

backing up, 602

changes to, 6

definition of, 7

msdb database

backing up, 7, 602

definition of, 7

Northwind database, 37, 64

performance considerations for, 343–344

Pubs database, 37, 64

ReportServer database, dropping, 45–46

ReportServerTempDB database, dropping, 45–46

Resource database, 6

sample databases, installing, 34–37

space required for, 66

for SSAS

backing up and restoring, 194–197

deploying, 188–191

processing, 191–194

synchronizing, 197–198

tempdb database

backing up, 602

capacity planning for, 381–382

configuring, 43–44

definition of, 7

features using, 380

performance considerations for, 370

placement of, 380

space required for, 66

uses of, 42–43

white paper regarding, 453

database administrator (DBA)

BI (Business Intelligence) DBA, 3

Development DBA, 2

features for, by edition, 15

Hybrid DBA, 3

Production DBA, 1–2

role of, changes in, 1, 4

database files

architecture of, 5

placement of, 379

database I/O, 453–454

Database Mail

account for, 130, 131

architecture of, 127–128

archiving, 133

configuring, 81, 129–133

default settings for, 75

definition of, 127

enabling, 129, 130

security of, 129

troubleshooting, 142

upgrading to, 66

database mirroring

alerts for, 138

as alternative to clustering, 657

automatic failover, 549, 558–559, 563–565

backup and recovery for, 606

catalog views of, 560–563, 574, 578

compared to clustering, 584

compared to log shipping, 585

compared to transactional replication, 585

databases for

mirror database, 548, 569–570

principal database, 548, 567–568

setting up, 555–556

witness database, 569–570

definition of, 547–548

disk configuration for, 21

endpoints for, setting up, 551–555

forced failover, 549, 566–567

log shipping integrated with, 648

manual failover, 549, 565–566

mirror server for

definition of, 548

preparing for failover, 579–583

mirroring session, establishing, 556–558

monitoring

using Database Mirroring Monitor, 572–577

using SQL Profiler, 577

using System Monitor, 570–572

for multiple databases, 583

operating modes of, 549–550, 558–560

parallel redo, 560

planned downtime, database availability during, 581–582

principal server for, 548

quorum for, 559

redo queue for, 550

role change for, 563–567

send queue for, 550

snapshots on mirror database, 589–590

SQL Native Client used by, 5

state change events, handling, 585–589

support for, by SQL Server edition, 15, 560

synchronizing principal and mirror databases, 556

troubleshooting, 577–579

witness server for, 552, 558–559

Database Mirroring Monitor, 572–577

database reports, in Management Studio, 86–87

database triggers, 324–329

Database Tuning Advisor (DTA)

creating workload to use in, 418

definition of, 500

indexes, analyzing number of, 523–525

new features for, 483–484

partitioning recommendations by, 383, 496

tuning individual queries

inserts, indexes for, 500–509

reassessing update indexes, 520–523

updates, indexes for, 509–520

workload, tuning, 526–528

Database:Database Mirroring State Change event , 577

DatabaseMail90.exe program, 127, 129

database_mirroring view, 561–562

database_mirroring_endpoints view, 562–563

database_mirroring_witnesses view, 562

database-scoped dynamic management views and functions, 433

data-drive subscriptions, support for, 15

date and time data types, list of, 11–12

(DATE) token, 134

datetime data type, 11–12

DBA (database administrator)

BI (Business Intelligence) DBA, 3

Development DBA, 2

features for, by edition, 15

Hybrid DBA, 3

Production DBA, 1–2

role of, changes in, 1, 4

db_accessadmin role, 298

db_backupoperator role, 298

db_broker_forwarded_messages DMV, 266

DBCC FLUSHPROCINDB command, 446

DBCC FREEPROCCACHE command, 446

DBCC INPUTBUFFER command, 96

DBCC TRACEOFF command, 97

DBCC TRACEON command, 97

DBCC TRACESTATUS command, 97–98

dbcreator role, 297

db_datareader role, 298, 304

db_datawriter role, 298, 304

db_ddladmin role, 298

db_denydatareader role, 298

db_denydatawriter role, 298

DBMailArchive_ tables, 133

DBMail_Attachments_ tables, 133

DBMail_Log_ tables, 133

db_owner role, 298, 303

db_version table, 337–338

"DDL Event Groups for Use with Event Notifications" (Books Online topic), 433

DDL triggers

creating, 324, 327–328

database triggers, 324–329

disabling, 328–329

enabling, 329

events monitored, list of, 325, 329

server triggers, 329–330

views of, 330

DDL_ASSEMBLY_EVENTS events, 325

DDL_DATABASE_EVENTS events, 329

DDL_DATABASE_SECURITY_EVENTS events, 325

DDL_ENDPOINT_EVENTS events, 329

DDL_EVENT_NOTIFICATION_EVENTS events, 325

DDL_FUNCTION_EVENTS events, 325

DDL_PARTITION_EVENTS events, 325

DDL_PROCEDURE_EVENTS events, 325

DDL_SERVER_SECURITY_EVENTS events, 329

DDL_SSB_EVENTS events, 325

DDL_SYNONYM_EVENTS events, 325

DDL_TABLE_VIEW_EVENTS events, 325

DDL_TRIGGER_EVENTS events, 325

DDL_TYPE_EVENTS events, 325

DDL_XML_SCHEMA_COLLECTION_EVENTS events, 325

deadlocks

identifying causes of, 417–418

traces, 98–100

decimal data type, 10, 11

decision support (DSS), workload for, 377–378

Dedicated Administrator Connection (DAC)

configuring, 80–81

default settings for, 75

denormalization, 146

DENY SELECT statement, 318–319

Deployment Wizard, for SSAS databases, 188–191

Developer Edition, 12–13

developer features, 14

Development DBA, 2

diagnostic information, in DMVs, 8

dialog, in Service Broker, 214

dialog security, for Service Broker, 242–243, 250–254

differential backup, 594

DISABLE TRIGGER statement, 328–329

disaster recovery (DR) planning, 624–625, 628–629

disasters, causing database failure, 593

disk activity counters, Performance Monitor, 408–414

disk adapters, 372–373

disk controllers, planning requirements for, 23–24

disk drives

bottlenecks, resolving, 413–414

configuration of, 21, 22–23, 27, 366–372

cost of, 367

isolation of, 369–370

monitoring activity of, 408–414

number of, 365–366, 367

planning requirements for, 21–24

RAID, 21, 23, 27, 368–369

speed of, 366

throughput of, 21–23

disk I/O, 363–365

disk virtualization, 370–371

diskadmin role, 297

Distributed Transaction Coordinator (DTC), 679–680

Distribution Agent, replication using, 531

DLL, assembly packaged as, 280

dm_broker_activated_tasks DMV, 266

dm_broker_connections DMV, 266

dm_broker_queue_monitors DMV, 266

dm_db_index_physical_stats DMF, 497

dm_exec_cached_plans DMV, 439, 441

dm_exec_connections DMV, 96

dm_exec_query_stats DMV, 97

dm_exec_sql_text DMV, 96–97, 442

DMF (dynamic management function)

categories of, 434

monitoring server and database using, 433–438

dm_io_virtual_file_stats DMF, 453

DMV (dynamic management view)

blocking information, monitoring, 435

categories of, 434

CLR, monitoring, 291–292

database, monitoring, 433–438

definition of, 8–9

index usage, monitoring, 435–437

locking information, viewing, 434

memory usage, monitoring, 438

monitoring using, 404

queries, monitoring, 437–438

server, monitoring, 433–438

for Service Broker, 266–267

user connections, monitoring, 437

dollar sign ($), preceding variables in sqlcmd initialization files, 332

domain accounts, 28

downtime. See also backup and recovery

minimizing

with clustering, 655

with database mirroring, 547–548

warm backup server for, 656

warm standby server for, 628

DR (disaster recovery) planning, 624–625, 628–629

drill-down, infinite, support for, 15

DROP ASSEMBLY statement, 285–286

DROP MESSAGE TYPE statement, 216

DROP_DATABASE events, 329

DSS (decision support), workload for, 377–378

DSV (data source view), 182

DTA (Database Tuning Advisor)

creating workload to use in, 418

definition of, 500

indexes, analyzing number of, 523–525

new features for, 483–484

partitioning recommendations by, 383, 496

tuning individual queries

inserts, indexes for, 500–509

reassessing update indexes, 520–523

updates, indexes for, 509–520

workload, tuning, 526–528

DTC (Distributed Transaction Coordinator), 679–680

DTExec utility, executing packages with, 171–172

DTExecUI utility, executing packages with, 172–173

DTS packages

jobs executing, 107

migration of, 64

replaced by SSIS, 145–146

upgrading to SSIS, 67–71

DTUtil Package Management Utility, 164–165, 170

Dump Database statement, 64

Dump Transaction statement, 64

Duncan, Bart (PSS), 47

dynamic AWE, support for, 15

dynamic management function (DMF)

categories of, 434

monitoring server and database using, 433–438

dynamic management view (DMV)

blocking information, monitoring, 435

categories of, 434

CLR, monitoring, 291–292

database, monitoring, 433–438

definition of, 8–9

index usage, monitoring, 435–437

locking information, viewing, 434

memory usage, monitoring, 438

monitoring using, 404

queries, monitoring, 437–438

server, monitoring, 433–438

for Service Broker, 266–267

user connections, monitoring, 437

"Dynamic Management Views and Functions" (Books Online topic), 434

dynamic SQL in stored procedures, security for, 310–314