S

salvaging data, 206–209

SAMPLE option, 113

SaveCheckPoint property, 381

scalability in replication, 480

scale, data types, 441

Scan Density statistic, 312–313

SCC (Source Code Control) systems, 538–539

Schedule tab, 14

Scheduler Health report, 25, 26

schedules

SQL Server Agent jobs, 243

SSRS strategy, 345, 346

Schema Changes History reports, 25, 50

schemas, 585

Script Component transforms, 377

Script task, 374

scripts

breakpoints, 385

migration, 545–546

server and database recovery, 205–206, 206

secure data, 402

Secure Sockets Layer (SSL), 491

security

databases, 555–556

authentication, 575–578, 576

encryption, 570–574, 570

exam essentials, 590

implementing, 583–586, 583

level of privileges, 561–563

logins. See logins

permissions, 557–559, 578–581, 586–589, 586–588

review questions, 591–596

service access, 559–561

service packs, 564

summary, 589

surface area, 567–570, 568–569

updates, 565–566

replication, 491–492

servers, 523–524

analyzing, 524–527

business and regulatory requirements, 526–527

change control. See change control

control strategies, 537–538

denial-of-service attacks, 529–530

design variations, 527

exam essentials, 547

injection attacks, 528–529

internal attacks, 533–536, 534–536

review questions, 548–553

summary, 546

version control systems, 538–539

worms and viruses, 530–532

SSIS packages, 378–380, 380

SSRS, 342–343

Security Analysis and Configuration tool, 589

security principals, 579

Security Properties dialog box, 582, 582

securityadmin role, 580

self-mapping, 464–465

Send Mail task, 374

Sequence containers, 373

SERIALIZABLE hint, 140

Serializable isolation, 124–126

server administrator auditing, 586–588, 586–588

server and network level replication, 511–512

@server argument, 459

server-based reporting tools, 403

Server Dashboard report, 25, 25

server failures, scripts for, 205–206, 206

server-level DDL triggers, 270, 542

server-level information, 323

server-to-client environment replication, 481

serveradmin role, 580

servers.

See also SQL Server

audit checklist, 574

linked. See linked servers

logins, 578–580

monitoring. See physical server monitoring

performance, 20–24

roles, 580–581

security. See security

SSRS, 341–342

status, 338

Service Accounts account, 558

Service Broker service, 274–276, 275

Service Broker Statistics report, 27

service brokers, 36

service master keys, 572

service packs, 564

services

access to, 559–561

accounts, 557–558

auditing, 562

level of privileges, 561

lockouts, 562–563, 562–563

selecting, 558–559

identities, 338

Service Broker, 275

Services and Connections window, 569

SESSION entity, locks for, 127

SET DEADLOCK_PRIORITY statement, 146–147

SET SHOWPLAN_ALL statement, 81

SET SHOWPLAN_TEXT statement, 81

SET SHOWPLAN_XML statement, 81

SET STATISTICS PROFILE statement, 82

SET STATISTICS XML statement, 82

SET TRANSACTION ISOLATION LEVEL statement, 126

sets, 417

checksums, 424–425

EXCEPT operator, 419–422, 421–422

intersections, 422–424, 424

unions, 417–419, 418–419

setupadmin role, 581

shared locks, 129

SHARED_TRANSACTION_WORKSP ACE entity, locks for, 127

Show All Events option, 148

SHOW_STATISTICS statement, 51, 112

SHOWCONTIG statement, 51, 52, 312–314

SHOWPLAN_ALL statement, 81

SHOWPLAN_TEXT statement, 81

SHOWPLAN_XML statement, 81

SHRINKDATABASE command, 314–315

SHRINKFILE command, 315–316

shrinking

databases, 314–315

files, 315–316

SI (snapshot isolation), 125–126

Simple Mail Transport Protocol (SMTP), 339

SIMPLE recovery model, 182

single copy approach to conflicts, 427

SINGLE_USER option, 138

site autonomy in replication, 480

SKIP option, 193

Slammer worm, 530, 532

Slowly Changing Dimension transforms, 377

smalldatetime data type, 441

smallint data type, 440

smallmoney data type, 441

SMTP (Simple Mail Transport Protocol), 339

Snapshot Agent (snapshot.exe), 175, 478–479

Snapshot Agent page, 484

Snapshot Folder page, 482

snapshot isolation (SI), 125–126

snapshot replication, 176, 476, 477

performance tuning, 513

updating subscribers, 430–431, 431

snapshots

database, 189

multiple copies, single maintenance approach, 428

Report Manager, 342, 342

restoring, 201–204, 203

SSRS for, 349

sort transforms, 377

Sort Warnings event class, 240

sorting operations in query plans, 87

Source Code Control (SCC) systems, 538–539

SourceSafe system, 538

sp_add_operator stored procedure, 245

sp_addlinkedserver stored procedure, 458–459, 464

sp_addlinkedsrvlogin stored procedure, 465–466, 469

sp_addmessage stored procedure, 247

sp_addpublication stored procedure, 430–431

sp_addsrvrolemember stored procedure, 581

sp_addsubscription stored procedure, 430–431

sp_article_validation stored procedure, 504

sp_blocker_pss80 stored procedure, 118

sp_change_agent_profile stored procedure, 511

sp_changereplicationserverpasswords stored procedure, 491

sp_control_plan_guide stored procedure, 93

SP_Counts template, 9

sp_create_plan_guide stored procedure, 93

sp_cycle_errorlog stored procedures, 28

sp_drop_agent_profile stored procedure, 511

sp_dropserver stored procedure, 459

sp_dropsrvrolemember stored procedure, 581

sp_help_agent_profile stored procedure, 511

sp_helplinkedsrvlogin stored procedure, 466

sp_helpsrvrolemember stored procedure, 581

sp_helpuser stored procedure, 588

sp_indexoption stored procedure, 139

sp_lock stored procedures, 31, 134

sp_marksubscriptionvalidation stored procedure, 504

sp_monitor stored procedures, 31, 31

sp_publication_validation stored procedure, 504

sp_setapprole stored procedure, 585

sp_trace_ stored procedures, 239

sp_trace_create stored procedure, 18

sp_trace_generateevent stored procedure, 19

sp_trace_setevent stored procedure, 18

sp_trace_setfilter stored procedure, 19

sp_trace_setstatus stored procedure, 19

sp_updatestats stored procedure, 88

sp_validatemergepublication stored procedure, 504

sp_validatemergesubscription stored procedure, 504

sp_who stored procedure, 31, 134, 135

sp_who2 stored procedure, 134, 135

space usage in SSRS databases, 350

SQL Cache Memory (KB) counter, 234

SQL command injection attacks, 528–529

SQL Compare program, 539

SQL Compilations/sec counter, 40, 234

SQL Mail, 532

SQL operating system (SQLOS)

DMVs, 36–38

monitoring, 227–228

in performance, 3

SQL plan guides, 93

SQL Re-Compilations/sec counter, 40

baselines, 234

thresholds, 238

SQL Server

access to, 559

authentication, 575–578

destinations, 378

in performance, 3

uptime, 323

SQL Server Active Directory Helper subsystem, 561

SQL Server Agent, 478

access to, 559–560

alerts, 242–246, 244–245, 494

startup, 180

SQL Server Agent error log (SQLAGENT.x), 28

SQL Server Agent Mail, 532

SQL Server Browser subsystem, 561

SQL Server error log (ERRORLOG.x), 28

SQL Server Health and History (SQLH2) tool, 322–324

SQL Server Integration Services (SSIS)

data-cleaning tools, 426

vs. DTS, 360–362

installing, 362–363, 363

packages

checkpoints and restarting, 380–382

configuring, 386–387, 387–388

control flow, 373–374

creating, 363–370, 364–369

data flow, 375–376

debugging, 383–385, 384–385

destinations, 378

exam essentials, 388–389

maintenance plan tasks, 375

moving, 382–383

performance monitoring, 386

review questions, 390–396

securing, 378–380, 380

summary, 388

transforms, 376–377

SSMS with, 370–372, 370–372

tools, 363

SQL Server Management Studio (SSMS) environment, 20

agent profiles, 509–510

alerts, 494–495

index fragmentation, 108–111

query plans, 81, 84–86

with SSIS, 370–372, 370–372

SQL Server Mobile destination, 378

SQL Server Profiler, 9–12, 10, 133, 142–143, 143

SQL Server Reporting Services (SSRS), 336

automating operations, 336–338, 337

caching strategy, 344, 345

configuration tools, 338–341

databases, 348–350, 348–349

logs, 346–347

performance, 343

scheduling strategy, 345, 346

security, 342–343

servers, 341–342

subscriptions, 343

SQL Server Surface Area Configuration tool, 568–570, 568–569

SQL Traces, 18

sql_variant data type, 443

SQL Writer subsystem, 561

SQLAGENT.x file (SQL Server Agent error log), 28

SQLH2 (SQL Server Health and History) tool, 322–324

SQLNCLI provider, 462

SQLOS (SQL operating system)

DMVs, 36–38

monitoring, 227–228

in performance, 3

SQLPERF command, 117, 120

@srvproduct argument, 459

SSIS. See SQL Server Integration Services (SSIS)

SSIS Package Instances counter, 386

SSIS Pipeline counter, 386

SSIS Service counter, 386

SSL (Secure Sockets Layer), 491

SSMS. See SQL Server Management Studio (SSMS) environment

SSRS. See SQL Server Reporting Services (SSRS)

Standard template, 9–10

STANDBY mode in log shipping, 173

Starting event class, 241

statement-level recompilation, 89–90

statistics

indexes, 111–114

query performance, 71

STATISTICS PROFILE statement, 82

STATISTICS XML statement, 82

StmtCompleted event class, 242

StmtRecompile event class, 40, 242

StmtStarting event class, 242

STOP_ON_ERROR option, 193

STOPAT option, 201

STOPATMARK option, 201

STOPBEFOREMARK option, 201

storage tools for quality data, 403

stored procedures, 30–31, 31

event classes, 241

for injection attacks, 529

locks, 134

logging, 114–115

traces, 239

striped sets, 166–169, 166–168

strong passwords, 532, 577

Subscribers page, 487, 487

Subscription Validation Options dialog box, 503, 503

subscriptions

push, 486–490, 486–490

replication, 175, 475, 512

SSRS, 343, 344

subscribers, 175, 427, 475, 475

subtree costs in query plans, 84

Summary Report pane, 25–27, 25–26, 48–50, 48–50

Supports ‘Like’ operator option, 462

surface area in security, 567–570, 568–569

surrogate primary keys, 435

symmetric encryption, 570–571

Synchronization Schedule page, 488, 488

sys.database_permissions view, 588

sys.database_principals view, 588

sys.dm_broker_ DMVs, 36

sys.dm_clr_ DMVs, 34

sys.dm_db_ DMVs, 53–54

sys.dm_db_index DMVs, 79

sys.dm_db_index_physical_stats DMV, 103–104, 314

sys.dm_db_missing DMVs, 79

sys.dm_exec_ DMVs, 54–55

sys.dm_exec_query_stats DMV, 80

sys.dm_fts_ DMVs, 34

sys.dm_io_ DMVs, 35

sys.dm_os_ DMVs, 36–38

sys.dm_os_latch_stats DMV, 119–120

sys.dm_os_memory_objects DMV, 19, 19

sys.dm_os_wait_stats DMV, 80, 116–117, 120

sys.dm_qn_subscriptions DMVs, 35

sys.dm_repl_ DMVs, 35–36

sys.dm_tran_locks DMV, 80, 134, 136–137, 137

sys.server_triggers view, 263

sys.syslockinfo table, 137

sys.sysmessages catalog, 246

sys.sysprocesses table, 116, 137

sys.triggers view, 263

sysadmin role, 581

sysreplicationalerts table, 495

system catalogs, checking, 299–300

system functions, 32, 32

System Monitor, 6–8, 7–8, 40

system stored procedures, 30–31, 31

event classes, 241

for injection attacks, 529

locks, 134

logging, 114–115

traces, 239

system table locks, 137

Systems Management Server, 566