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