Index
Symbols
64-bit SQL Server, 32-bit compared to, 37
% Disk Time counter, 471
% Privilege Time counter, 457
% Processor Time counter, 457
A
access
denying
to database securables, 222
to server securables, 221
granting
to database securables, 222
to server securables, 221
to SQL Server by Windows users, 213
Account Provisioning tab (Database Engine Configuration screen), 84
accountable, being, as DBA, 529
actions, specifying when running command-line installation, 99
active node, 45
Activity Monitor, starting, 3
Address Windows Extensions (AWE), enabling, 163
Advanced category (SQL Server Installation Center), 77
Advanced tab (SQL Server Properties dialog), 147
AFTER trigger, 290
alerts, SQL Server Agent, 429–432
ALLOW_PAGE_LOCKS relational index option, 307
ALLOW_ROW_LOCKS relational index option, 307
ALTER DATABASE command, for changing compatibility level, 139
ALTER RESOURCE GOVERNOR RECONFIGURE statement, 513
ALTER TABLE statement
CASCADE option, 262
default constraints on columns and, 257
DROP CONSTRAINT clause, 259
DROP CONSTRAINT keywords, 260–262
LOCK_ESCALATION option, 5
NOCHECK CONSTRAINT keywords, 263
NO CHECK option, 262
primary key constraints and, 260
SPARSE option, 267
unique constraints, creating with, 261
WITH NOCHECK option, 265
WITH VALUES clause, 258
altering
functions, 290
views, 274
Analysis Wizard (Upgrade Advisor)
affected objects, 117
analysis report, 116
authentication page, 113
confirmation screen, 114
database selection, 113
rule analysis, 115
server and components selection, 112
warning screen, 115
Welcome screen, 111
application roles, creating, 220
Apress book resources for DBA, 542–543
arguments, searchable, 303
Assessment and Planning toolkit (MAP, Microsoft), 40
assigning
server principals to database principals, 217
users to schemas, 223
asymmetric keys
associating logins with, 213–214
Audit feature, 6
audit objects, 494
auditing
audit objects, 494
database audit specification
database-level audit action groups, 485–486
database-level audit actions, 486–487
overview of, 479
reviewing files with SSMS, 492
server audit
server-level action groups, 481–482
creating with T-SQL, 481
shutdown problems, 494
startup problems, 493
authentication
SQL Server, 211
Authorization command, 219
autogrowth options for data and log files, enabling, 171
automatic recovery from corrupted page, 8
automating maintenance tasks
Database Mail
configuring using T-SQL, 422–424
overview of, 413
SQL Server Agent
enabling notifications, 427–429
automating monitoring, 477–478
autoshrink option for data and log files, 172
availability. See high availability
availability enhancements, 8–9
Available MBytes counter, 465
Avg Disk Queue Length counter, 471
Avg Disk Reads/Sec or Writes/Sec counter, 471
AWE (Address Windows Extensions), enabling, 163
B
backing up
See also backups; restoring
database after reverting, 257
master and database keys, 249
backup compression, 6, 353–355
Backup Compression Default option, 163–164
Back Up Database dialog box
Backup Device dialog box
General section, 355
Media Contents section, 356
backup drive, determining size of, 33
BackupGroup workload group, creating, 514
backup history tables, 369
Back Up interface, 358, 362–363
Backup resource pool (Resource Governor), 513
BACKUP statement
COPY_ONLY option, 352
FORMAT option, 345
READ_WRITE_FILEGROUPS option, 351
WITH COMPRESSION and WITH NO_COMPRESSION options, 353
backups
See also backing up; full database backups
database recovery model, 343–344
logical backup devices, 355–358
overview of, 343
Resource Governor and, 354
of system databases, 367
transaction log type of, 349
types of, 346
baseline metrics on server, collecting, 451–452
Batch Requests/Sec counter, 458
Best Practices Analyzer, 170
blocked process threshold option, 168
book resources for DBA, 542–543
B-tree data structure, parts of, 298
built-in system accounts, using, 70
bulk-logged recovery model, 343
C
CAL (client access license), 22
cardinality of data, 303
catalog views, Resource Governor, 522
central management servers
configuring multi-server query options, 194–195
evaluating policies using, 196–199
running multi-server queries, 193–194
certificate encryption, 242–244
certificates
associating logins with, 213–214
backing up, 367
certification authorities, 242
certification exams, 537
Change Data Capture feature, 6, 64
Change Tracking feature, 6
check condition, 7
check constraints
disabling, 266
dropping, 266
classifier function (Resource Governor), 511, 516–519
cleanup procedures, Database Mail, 425–426
client access license (CAL), 22
Client Protocols Properties dialog box, 155
Client Protocols subsection (SQL Native Client Configuration), 154–156
CLR (common language runtime)
enabling, 166
clr enabled option, 166
clustered indexes
creating, and retrieving data from newly created tables, 331–333
structure of, 299
clustered index keys, 300
clustered index scan, query to create, 504
clustered index seek, query to create, 505
clustering, 9, 64. See also failover clustering
columns
defining default constraints on, 257–259
include, 303
command-line installation
action and components, specifying, 99
data directories, specifying, 100
level of silence, choosing, 98
overview of, 95
parameters passed to executable, 96–98
parameters passed to installer, 98
command-line interface, executing configuration file installation, 108
commands
ALTER DATABASE, for changing compatibility level, 139
Authorization, 219
CREATE DATABASE, AS SNAPSHOT OF clause, 254
Database Consistency Checks for monitoring memory, 467–469
DBCC CHECKDB, for testing object integrity, 139
DBCC memorystatus, 469
DBCC UPDATEUSAGE, for correcting row and page counts, 140
DROP DATABASE, 256
DROP SYNONYM, 280
RESTORE DATABASE, FROM DATABASE_SNAPSHOT clause, 256
common language runtime (CLR)
enabling, 166
communication issues for DBA
listening, 531
providing too much information, 531
target audience, talking to, 530–531
compatibility level, changing after upgrade, 139
Complete screen
configuration file installation, 109
for in-place upgrade, 127
when installing first instance, 89
components, specifying when running command-line installation, 99
composite indexes, 302
composite keys, 259
compressed and encrypted database restore
certificates, restoring, 403
transaction logs, restoring, 403–409
compressed indexes, 302
compression, tables and, 267–269
compromise, accepting, 528
condition to use in policy, creating, 178–179
configuration file installation, 104, 108–110
Configuration Manager
SQL Native Client Configuration section, 154–156
SQL Server Network Configuration section, 148–154
SQL Server Services section, 144–147
configuring
Database Mail
using Configuration Wizard, 413–422
disk subsystem, 36
instance
Backup Compression Default option, 163–164
CLR option, 166
dedicated administrator connections option, 166
default trace option, 166
enabling Address Windows Extensions, 163
for in-place upgrade, 125
lightweight pooling option, 167
login failure auditing option, 165
query governor cost limit option, 167–168
specifying maximum and minimum server memory, 161–162
viewing advanced options, 157
viewing configuration settings, 157–161
when running command-line installa-tion, 99–100
xp_cmdshell option, 168
multi-server query options, 194–195
consolidation
with virtual servers, 40
constraints, indexes created by, 301
Context Switches/Sec counter, 457
Copy Database Wizard
Complete the Wizard screen, 137
database selection, 133
destination database selection, 134
destination server selection, 132
for migrating database to new instance, 130
package configuration, 136
Performing Operation screen, 138
scheduling SSIS package, 136
server objects selection, 135
source server selection, 131
starting, 131
transfer method selection, 133
Welcome screen, 131
copying data with log shipping. See log shipping
copying databases, methods for, 130
correlating Profiler and Performance Monitor, 495–496
corrupted page, automatic recovery from, 8
cost threshold for parallelism option, 169
counters
for general monitoring, 472
for monitoring disk IO, 469–470
for monitoring memory
covering queries, 303, 333–337
CPU
hot-adding, 9
monitoring
SQL Server counters and, 458–459
CPU needs
deriving baseline specification, 29
example using questions, 30–31
future utilization, considering, 30
CREATE ASSEMBLY statement, 294
Create Audit dialog box, 489
CREATE CERTIFICATE statement, 243
Create Database Audit Specification dialog box, 490
CREATE DATABASE command, AS SNAP-SHOT OF clause, 254
Create New Condition dialog box (Policy-Based Management), 178–179
Create New Policy dialog box (Policy-Based Management)
accessing, 179
completed, 182
Description page, 181
entering information into, 180
CREATE VIEW statement, WITH SCHEMA-BINDING clause, 276
credentials, linking to logins, 214–215
criticism, accepting and learning from, 528
CrossServerSelect stored procedure, 200
Current Disk Queue counter, 471
current state, exporting as policy, 185–187
cursor threshold option, 169
D
Dam, Sajal, SQL Server 2008 Query Perfor-mance Tuning Distilled, 495
data
See also log shipping
cardinality of, 303
Data and Log File Location Policy
Description tab (Policy-Based Manage-ment), 189
General tab (Policy-Based Management), 188
Data Collector, 7
DATA_COMPRESSION relational index option, 307
data directories, specifying when running command-line installation, 100
Data Directories tab (Database Engine Configuration screen), 85
data files
autogrowth options for, 171
autoshrink option, 172
size of, 171
data locators, 300
DATA_PURITY option (DBCC CHECKDB command), 139
Data Transformation Services (DTS), 119–120
database administrator. See DBA
database audit, choosing, 480
database audit specification
creating
Database Consistency Checks (DBCC) commands for monitoring memory, 467–469
Database Engine Configuration screen
installing multiple instances and, 94
when installing first instance, 84–86
database-level audit action groups, 485–486
database-level audit actions, 486–487
Database Mail
Configuration Wizard
Complete the Wizard screen, 421
Configure System Parameters screen, 419–422
Manage Profile Security screen, 418–419
New Database Mail Account dialog box, 416–418
New Profile screen, 415
Select Configuration Task screen, 414
Welcome screen, 413
configuring using T-SQL, 422–424
database master key, backing up, 366–367
database mirroring
implementation of, 49
key terms, 48
log shipping compared to, 53
transparent client redirect feature, 50
database preproduction tasks, 170–172
DATABASEPROPERTYEX system function, 344
database recovery model, 343–344
Database Role – New dialog box (SQL Server Management Studio)
database scope securables, 211
database security features, principals
application roles, 220
database users, creating, 217–218
overview of, 216
Database Settings section (Server Properties dialog), 163
database snapshots
reverting database to, 256–257
Database Snapshots folder (Object Explorer), 253
database users, creating, 217–218
databases
See also recovery examples; restoring databases
copying, methods for, 130
moving to new instance
backup/restore migration method for, 130
Copy Database Wizard migration method for, 130–138
detach/attach migration method for, 129–130
methods for, 128
physical files of, 251
Resource, 253
reverting to database snapshot, 256–267
system, backing up, 367
tempdb, 253
db_datareader role
creating login and user for, and adding user to, 223
creating schema and table for, then removing user from, 224
DBA (database administrator)
accepting and learning from criticism, 528
accepting things won't always go your way, 528
accepting you are not best, 527
being good at what you do, 527
building reputation as, 528–529
communication issues for, 530–531
developing non-technical skills as, 527
free training events for, 538
going above and beyond as, 529–530
leadership issues for, 534–536
ongoing experimentation and learning for, 537–538
podcasts for, 538
pressure situations and, 532–534
updating skill set as, 537
web site resources for, 542
DBCC (Database Consistency Checks) commands, for monitoring memory, 467–469
DBCC CHECKDB command, for testing object integrity, 139
DBCC memorystatus command, 469
DBCC UPDATEUSAGE command, for correcting row and page counts, 140
decision-making, as DBA, 535
DECRYPTBYASYMKEY function, 245
DECRYPTBYCERT function, 243
DECRYPTBYKEY function, 246
dedicated administrator connections, enabling, 166
default constraints, defining on columns in table, 257–259
default resource pools (Resource Governor), 512
defaulting user schemas, 223–224
default trace, disabling, 166
delete statement, post-index creation, 325–326
denying access
to database securables, 222
to server securables, 221
dependable, being, as DBA, 529
design-time support for DTS add-on, installing, 119
Developer Edition, features of, 21, 24
Device Contents dialog box, 361
dictionary compression, 268
differential restore
description of, 371
disabling
See also enabling
check constraints, 266
default trace, 166
foreign key constraints, 264
lightweight pooling, 294
Resource Governor, 513
disaster recovery
See also recovery examples; restoring databases
defining amount of time allowed for, 44
high availability compared to, 43
disk IO, monitoring
SQL Server counters and, 471–472
Windows counters and, 471
Disk Space Requirements screen, 82
disk storage configuration, 36
disk subsystems, determining design, size, and utilization of, 31–32
DMVs (Dynamic Management Views)
capturing performance monitor counters using, 472
monitoring memory and, 467–469
querying, 326
reports compared to, 501
Resource Governor and, 521–523
storing results of, 478
SYS.DM_DB_INDEX_USAGE_STATS, 327
SYS.DM_DB_MISSING_INDEX_DETAILS, 328–329
sys.dm_db_persisted_sku_features DMV, 40
sys.dm_exec_query_memory_grants DMV, 468
sys.dm_exec_query_plan, 507
SYS.DM_EXEC_QUERY_STATS, 326–327, 497
sys.dm_exec_query_optimizer_info DMV, 462
sys.dm_os_performance_counters DMV, 472
sys.dm_os_process_memory DMV, 468
sysdm_os_schedulers DMV, 460
sys.dm_os_sys_memory DMV, 467
sys.dm_os_tasks DMV, 461
sys.dm_os_threads DMV, 459
sys.dm_os_workers DMV, 459
sys.dm_server_audit_status DMV, 494
documentation for recovery from scratch, 385
documenting process for spec'ing out servers, 38
domain accounts, using, 70
downloading Upgrade Advisor, 111
DROP ASSEMBLY statement, 295
DROP DATABASE command, 256
DROP_EXISTING relational index option, 307
DROP SYNONYM command, 280
dropping
assembly, 295
check constraints, 266
database snapshots, 256
default constraints, 259
foreign key constraints, 264
functions, 290
primary key constraints, 260
stored procedure, 285
unique constraints, 262
DTS (Data Transformation Services), 119–120
DTS xChange tool, 120
Dynamic Management Views. See DMVs
E
Edit Filter dialog box (Profiler), 474–475
editions of SQL Server
choosing, 21
Developer, 24
Standard Edition for Small Businesses, 24
Web, 25
Workgroup, 25
EKM (Extensible Key Management), 20, 247–248
enabled protocols, viewing, 149
enabling
See also disabling
Address Windows Extensions, 163
autogrowth options for data and log files, 171
backup compression, 353
CLR integration functionality, 293
dedicated administrator connections, 166
foreign key constraints, 264
lightweight pooling, 167
Resource Governor, 513
Service Broker for msdb database, 422
SQL Server Agent notifications, 427–429
ENCRYPTBYASYMKEY function, 245
ENCRYPTBYCERT function, 243
ENCRYPTBYKEY function, 246
encrypted views, creating, 273
encryption
backing up master and database keys, 249
Extensible Key Management and, 247–248
overview of, 242
Transparent Data Encryption and, 248–249
using asymmetric keys, 244–246
ENCRYPTION BY keyword, 247
ENCRYPTION BY PASSWORD option, 243
Enterprise Edition, features of, 22–23
Error and Usage Reporting screen, 86
error message
in command-line window during installation, 102
when accessing data from offline filegroup, 394
when adding data to table that has nonclustered indexes in offline state, 395
when attempting to restore encrypted database without encryption keys in place, 403
Evaluate Policies dialog box
defined targets and, 183
results page, 198
with group name displayed, 196
with selected policies, 198
Evaluate Policies option (context menu), 196
evaluating policies against multiple servers, 196–199
example table, creating via GUI, 316–317
execution plans
joining multiple tables with all filegroups restored, 398
OPTIMIZE FOR query hint to optimize
for unknown value, 503
for value of one, 502
query to generate execution plan that uses hash joins, 502
query to generate execution plan that uses merge joins, 501
when primary filegroup is only available filegroup online, 393
Export as Policy dialog box (Policy-Based Management), 186
Export Integration Services maintenance plan package, 445
Export Package dialog box, 446
exporting
current state as policy, 185–187
Express Edition, features of, 26–27
Extensible Key Management (EKM), 20, 247–248
F
facet, current state of, 185–187
Failed status of rule, 79
failover clustering
database mirroring compared to, 49
enhancements to, 9
pros and cons of, 48
fast recovery feature, 45
feature selection for in-place upgrade, 124
Feature Selection screen, 81–82, 92
filegroups
description of, 251
NONCLUSTEREDINDEX, status of
after restoring all applicable files, 397
after restoring with NORECOVERY, 396
state description of, after primary is no longer read-only, 395
utilizing for faster restores, 383–384
FILESTREAM tab
Database Engine Configuration screen, 85
SQL Server Properties dialog, 146
FILLFACTOR relational index option, 307–308
filter condition, 7
filtered indexes
filtered statistics, 4
finding similar queries, 497–499
first instance, installing
Complete screen, 89
Database Engine Configuration screen, 84–86
Disk Space Requirements screen, 82
Error and Usage Reporting screen, 86
Feature Selection screen, 81–82
Installation Progress screen, 88
Installation Rules validation process, 87
Instance Configuration screen, 82
Ready to Install screen, 88
Server Configuration screen, 83–84
stand-alone installation, 80
System Configuration Checker and, 79–81
flexible database roles, 218
fn_get_audit_file function, 484–494
FORCESEEK table hint, 5, 504–505
forcing
foreign key constraints
disabling, 263
dropping, 264
free training events, 538
Fritchey, Grant, SQL Server, 2008 Query Performance Tuning Distilled, 495
FROM SOURCE clause, 212
full database backup
differential backup compared to, 348
restoring file and filegroup of, 378
restoring page of, 380
restoring, syntax for, 374
full database restore, 371, 376
full recovery model, 343
Full Scans/Sec counter, 471
functions
classifier function (Resource Governor), 516
DATABASEPROPERTYEX system, 344
DECRYPTBYASYMKEY, 245
DECRYPTBYCERT, 243
DECRYPTBYKEY, 246
description of, 286
ENCRYPTBYASYMKEY, 245
ENCRYPTBYCERT, 243
ENCRYPTBYKEY, 246
sys.dm_sql_referenced_entities dynamic management, 201
sys.dm_sql_referencing_entities dynamic management, 203
sys.fn_validate_plan_guide, 509
trace, 475
G
geographically disbursed clustering, 64
granting access
to database securables, 222
to server securables, 221
GROUPING_ID function syntax, 18–20
grouping permissions, 226
GROUPING SETS operator, 10, 17
GUI
See also SQL Server Management Studio
Back Up interface, 358, 362–363
creating indexes via
database audit specification, creating with, 490–492
detaching and attaching databases using, 130
server audit, creating with, 489–490
H
hardware failures, preparing for, 384
hardware requirements
CPU needs
deriving baseline specification, 29
example using questions, 30–31
future utilization, considering, 30
RAID levels
choosing, and laying out files, 35–36
disk storage configuration, 36
hash joins, query to generate execution plan that uses, 502
heap index structure, 301
heaps, 297
high availability
database mirroring
implementation of, 49
key terms, 48
transparent client redirect feature, 50
pros and cons of, 48
feature comparison, 64
log shipping
key terms, 53
pros and cons of, 56
restoring database to secondary server, 55–56
other techniques for, 64
replication
key terms, 57
pros and cons of, 63
snapshot type, 58
types of, 57
high-performance mode of database mirroring, 49, 51–52
high-safety mode of database mirroring, 49–51
hints
adding through plan guides
validating plan guides, 509–511
types of, 5
hot-adding CPUs, 9
I
IGNORE_DUP_KEY relational index option, 301, 307
immediate updating, 60
implementation
of database mirroring, 49
Import dialog box (Policy-Based Management), 187
Import Package dialog box, 447
importing
include columns, 303
Include the Actual Execution Plan option, 313
indexes
cardinality of data, 303
clustered
creating via T-SQL, 304–305 description of, 297–298
composite, 302
compressed, 302
covering queries, 303
created by constraints, creating via GUI, 316–317
creating via T-SQL
overview of, 304 syntax for, 306–308
demonstrations
clustered index seeks, 331–333
filtered index, creating, 339–341
JOIN criteria, indexing, 337–339
scenario for, 329 table scans, 330–331
filtered
heaps, 297
include columns, 303
nonclustered
creating via T-SQL, 305–306 description of, 298
post-index creation processes
Dynamic Management Views, 326–329
insert, update, and delete statements, 325–326
overview of, 324
primary, creating via T-SQL, 309–310
primary XML, creating via T-SQL, 315
searchable arguments, 303
secondary XML, creating via T-SQL, 316
structure of
clustered, 299
heap, 301
overview, 298
unique, creating via T-SQL, 309–310
XML
description of, 302
inline table-valued function, creating, 288
INSERT statement
installation
See also pre-installation considerations
command-line
action and components, specifying, 99
data directories, specifying, 100
level of silence, choosing, 98
overview of, 95
parameters passed to executable, 96–98
parameters passed to installer, 98
configuration file, 104, 108–110
of first instance
Complete screen, 89
Database Engine Configuration screen, 84–86
Disk Space Requirements screen, 82
Error and Usage Reporting screen, 86
Feature Selection screen, 81–82
Installation Progress screen, 88
Installation Rules validation process, 87
Instance Configuration screen, 82
Ready to Install screen, 88
Server Configuration screen, 83–84
stand-alone installation and, 80
System Configuration Checker and, 79–81
of multiple instances
Database Engine Configuration screen, 94
Instance Configuration screen, 93–94
preparation for, 90
Ready to Install screen, 94
System Configuration Checker, 90–92
prerequisites for, 71
SQL Server Installation Center
Advanced category, 77
Installation category, 73
Maintenance category, 74
Resources category, 76
Upgrade option, 121
starting, 70
Installation category (SQL Server Installation Center), 73
installation methods, 69
Installation Progress screen
configuration file installation, 108
during command-line installation, 102
when installing first instance, 88
Installation Rules validation process, 87
Installation Type screen, 91
Installed SQL Server Features Discovery Report option (Tools category), 75
Installed SQL Server Features Setup Dis-covery Report, installing multiple instances and, 91
installing
See also installation
support for DTS add-on, 119
Upgrade Advisor, 111
instance
See also first instance, installing; multiple instances, installing
configuring
Backup Compression Default option, 163–164
CLR option, 166
dedicated administrator connections option, 166
default trace option, 166
enabling Address Windows Extensions, 163
for in-place upgrade, 125
lightweight pooling option, 167
login failure auditing option, 165
query governor cost limit option, 167–168
specifying maximum and minimum server memory, 161–162
viewing advanced options, 157
viewing configuration settings, 157–161
when running command-line installa-tion, 99–100
xp_cmdshell option, 168
configuring as central management server, 190–193
moving database to new
backup/restore migration method for, 130
Copy Database Wizard migration method for, 130–138
detach/attach migration method for, 129–130
methods for, 128
Instance Configuration screen, 82, 93–94
instance ID, installing multiple instances on same server and, 90
instance selection for in-place upgrade, 124
INSTEAD OF trigger, 290
internal resource pools (Resource Governor), 512
IO subsystem, testing, 170
IP Addresses tab (TCP/IP Properties dialog), 152
IsAlive message, 47
J
jobs, SQL Server Agent, 432–439
JOIN criteria, indexing, 337–339
joining multiple tables without indexes, 331
Jumpstart TV SQL Channel, 538
K
Keep Alive selection (TCP/IP Properties dialog), 152
L
launching
Activity Monitor, 3
Performance Monitor, 452
Profiler, 472
leadership issues for DBA
making tough decisions, 535
overview of, 534
level of silence, choosing when running command-line installation, 98
license agreement screen for in-place upgrade, 123
lightweight pooling
disabling, 294
enabling, 167
links in Resources category, 76
listening skills, 531
listings
alerts for severity 19 messages, creating, 431
assembly
creating stored procedure that references, 295
dropping, 295
registering, 294
signing with strong key, 294
attaching database, 129
Backup and Reporting resource pools, creating, and enabling Resource
Governor, 513
backup compression, enabling, 353
Backup, Reporting, and Testing workload groups, creating, 514
backups
creating with and without compression, 353
determining compression percentage per, 354
bringing database online and reviewing status of filegroups, 394
certificate, backing up, 367
certificate encryption, 243
check constraint
creating when creating table, 264
creating with ALTER TABLE statement, 265
dropping, 266
classifier function
creating and enabling, 517–518
removing from Resource Governor, 518
CLR integration functionality, enabling, 293
clustered indexes, creating and retrieving data from tables, 331
composite key, creating using ALTER TABLE statement, 260
compression, adding or changing on existing table, 269
configuration file sample, 104–108
copy-only backup, creating, 352
CrossServerSelect stored procedure, 200
database audit specification
creating, 484
database master key, backing up, 366–367
database recovery model, viewing and changing, 344
database snapshot
creating, 254
creating using multiple filegroups, 254
query to view snapshot metadata, 255
reverting database to, 256
db_datareader role
creating login and user for, and adding user to, 223
creating schema and table for, then removing user from, 224
DDL trigger, creating, 291–292
default, creating
on column using ADD VALUES clause, 258
on column when creating table, 257
on preexisting column, 258
detaching database, 129
differential database backup, creating, 348
distributed partition view, creating, 274
DML trigger, creating, 291
encrypted view, creating, 273
encryption
using asymmetric keys, 245
using symmetric keys, 246
execution plans, specifying by supplying USE PLAN query hint, 506
file backup, creating, 351
filtered index, creating, 339
foreign key constraint
creating between two tables, 262
disabling and enabling, 264
dropping, 264
full database backup, creating, 347
GROUPING SETS operator, script demon-strating, 17
identifying amount of time worker has
been running compared to being
suspended, 460
indexed view, creating, 276
inline table-valued function, creating, 288
lightweight pooling, disabling, 294
log send buffers, enhancements to, 9
log sequence number, 349
log shipping
key terms, 53
pros and cons of, 56
restoring database to secondary server, 55–56
side-by-side upgrade and, 128
logical backup device, adding or remov-ing, 355
logins
SQL Server, syntax for, 211
logon trigger, creating, 293
media set with multiple devices, creating, 345
MERGE statement, three uses of, 14–16
multi-statement table-valued function, creating, 289
nonclustered indexes
creating, and querying data for given date range, 334
creating, and querying multiple tables, 338
creating, and using different indexes
existing on table, 336
forcing SQL Server to use for date range scan, 335
OPTIMIZE FOR query hint, using
for unknown value, 503
for value of one, 502
partial backup, creating, 350
partitioned table, creating, 269–270
performance counters for default work-load group and resource pool, 519
piecemeal restore, 381
piecemeal restore example, 386–388
plan guide, creating, 509
primary key constraint, 260
private key, backing up, 367
queries
to aggregate execution count and total worker time using hash values, 498
to analyze hash values, 497
by average CPU time, top ten, 500
to cause performance spike, 495
to create clustered index scan, 504
to create clustered index seek, 505
to create similar hash values, 497
to generate execution plan that uses hash join, 502
to generate execution plan that uses merge join, 501
to retrieve information from invoice table with isPaid = 1, 312
querying multiple tables and inserting data into table, 398
removing backup history, 369–370
Resource Governor
catalog views, 522
Dynamic Management Views, 521
RESTORE HEADERONLY and transaction logs query for restore header information, 391–392
restoring
database and retrieving data from new tables, 389
from database snapshot, 381
file and filegroup, and applying transac-tion log to that filegroup, 379
file and filegroup of full database backup, 378
NONCLUSTEREDINDEXES filegroup, 396
page of full database backup, 380
transaction log to mark, 407
role, creating, and granting execute permissions, 219
ROLLUP, CUBE, and GROUPING_ID syntax, 18
row constructors compared to prior meth-ods of inserting multiple rows, 11
scalar-valued function, 286–287
schedulers, gathering statistics on, 461
server audit, 480
server audit and server audit specification, creating and firing, 483–484
server audit specification
creating, 481
with server-level audit action, creating, 482
server-side trace, creating, 476–477
service master key, backing up, 366
sp_help_job stored procedure, 204
statement retrieving invoices from invoice table where isPaid = 0, 313 stored procedure
altering, 282
altering to use RETURN clause, 284
creating and executing, 281
dropping from database, 285
enabling execution of, on startup, 286
forcing to generate new execution plan, 285
sys.dm_sql_referenced_entities dynamic management function, 201
sys.dm_sql_referencing_entities dynamic management function, 203
sysjobshistory table, 202
sys.sql_expression_dependencies catalog view, 200
table, creating
enabling page compression when, 269
using sparse columns, 267
table-valued parameters, script demon-strating use of, 13–14
tasks in task_state, showing number of, 461
TDE example, setting up, 399–402
top ten queries by average CPU time, 500
transaction log backup, creating, 349
transaction logs, restoring, 377
unique constraints
creating using ALTER TABLE statement, 261
creating when creating table, 261
dropping, 262
updateable view, creating using WITH CHECK option, 275
user-defined function, dropping from database, 290
views, 274
Loading Policies dialog box, 198
local server groups, running multi-server queries using, 194
local user accounts, using, 70
LOCK_ESCALATION option (ALTER TABLE statement), 5
log drive, determining size of, 33
log files
autogrowth options for, 171
autoshrink option, 172
size of, 171
logical backup devices, 355–358
logical sequence number, 372
login failure auditing option, 165
Login properties (General page) dialog box (SQL Server Management Studio), 228–229
Login properties (Securables page) dialog box (SQL Server Management Studio), 231
Login properties (Server Roles page) dialog box (SQL Server Management Studio), 229
Login properties (Status page) dialog box (SQL Server Management Studio), 232
Login properties (User Mapping page) dialog box (SQL Server Management Studio), 230
logins
adding to server-level roles, 216
associating with certificates and asym-metric keys, 213–214
creating
SQL Server-authenticated, 213
for Windows principals, 212–213
linking credentials to, 214–215
removing from server-level roles, 216
Logins folder (SQL Server Management Studio), 228
Logins/Sec counter, 472
Log On tab (SQL Server Properties dialog), 144
LooksAlive message, 47
M
Maintenance category (SQL Server Installation Center), 74
Maintenance Plan Wizard
Select Maintenance Tasks page, 442
Select Plan Properties page, 442
maintenance plans
importing and exporting, 445–447
overview of, 441
maintenance preproduction tasks, 172–173
maintenance tasks, automating
Database Mail
configuring using T-SQL, 422–424
overview of, 413
SQL Server Agent
enabling notifications, 427–429
maintenance window, defining, 44
Manage Policy Categories dialog box (Policy-Based Management), 184
manageability enhancements
Audit feature, 6
backup compression, 6
central management servers, 7
Change Data Capture feature, 6
Change Tracking feature, 6
Data Collector, 7
overview of, 5
policy-based management, 7
PowerShell scripting tool, 8
Resource Governor feature, 8
management data warehouse, 7
Management Studio Object Explorer, SQL Server 2005 compared to SQL Server 2008, 3
managing
See also managing query performance
permissions, 226
server securables, 220
managing query performance
correlating Profiler and Performance Monitor, 495–496
finding and tuning similar queries, 497–499
forcing execution plans, 506–507
optimizing for specific parameter values, 501–504
overview of, 495
plan guides
Resource Governor
overview of, 511
running standard reports, 499–501
master database
description of, 253
building using REBUILDDATABASE, 410–411
restoring from single user mode, 409
MAXDOP relational index option, 307
maximum degree of parallelism option, 169
MDAC (Microsoft Data Access Components), 50
media family, 345
memory
monitoring
DMVs and DBCC commands and, 467–469
overview of, 462
SQL Server counters and, 466–467
usage of memory in SQL Server, 462–463
Memory section (Server Properties dialog), 161
merge joins, query to generate execution plan that uses, 501
Microsoft
Advisory Services, 541
Assessment and Planning (MAP) toolkit, 40
certification exams, 537
Data Access Components (MDAC), 50
Distributed Transaction Coordinator (MSDTC), 60
Help and Support web site, 541
learning web site, 537
SQL Server Connect web site, 542
SQL Server Training Portal, 537
Technical Communities web site, 540–541
minimum hardware requirements, 28–29
mirrored and striped disks (RAID 10), 35
mirrored disks (RAID 1), 34
Mixed Mode authentication, 108, 211
model database, 253
monitoring
CPU
SQL Server counters and, 458–459
disk IO
SQL Server counters and, 471–472
Windows counters and, 471
memory
DMVs and DBCC commands and, 467–469
overview of, 462
SQL Server counters and, 466–467
usage of memory in SQL Server, 462–463
Resource Governor
server
baseline metrics, collecting, 451–452
setting up processes for, as preproduction task, 172–173
msdb database
description of, 253
enabling Service Broker for, 422
policies stored in, 185
MSDTC (Microsoft Distributed Transaction Coordinator), 60
multi-node cluster configuration, 46
multi-node cluster configuration after failure, 47
multiple instances, installing
Database Engine Configuration screen, 94
Instance Configuration screen, 93–94
preparation for, 90
Ready to Install screen, 94
System Configuration Checker, 90–92
multi-server administration. See central management servers; Policy-Based Management
Multiserver Results options (SQL Server Management Studio), 195
multi-statement table-valued function, creating, 289
N
named pipes
description of, 150
TCP/IP compared to, 153
network load balancing, log shipping and, 54
New Index dialog box
Filter section, 323
Included Columns section, 322
Index Key Columns area, 319
Options section, 320
Storage section, 320
New Plan Guide dialog box, 507–508
New Query option (context menu), 193
New Server Group Properties dialog box (SQL Server Management Studio), 191
New Server Registration dialog box (SQL Server Management Studio), 190–191
New Synonym dialog box, 278
nodes, 44
nonclustered indexes
creating
description of, 298
norms, challenging, 530
notifications, SQL Server Agent, 427–429
O
object dependencies, determining, 199–207
Object Explorer (SQL Server Management Studio)
Databases folder, 252
Database Snapshots folder, 253
Programmability folder, 280
System Databases folder, 252–253
object integrity after upgrade, 139–140
objects
See also functions; tables; triggers; views
offline restore, 372
online recovery, 383
ONLINE relational index option, 307
online restore, 372
OPTIMIZE FOR query hint, 5, 501–504
optimizing for specific parameter values, 501–504
Options category (SQL Server Installation Center), 77–78
P
packages, DTS, converting to SSIS, 120
PAD_INDEX relational index option, 308
page counts, correcting after upgrade, 140
Page Faults/Sec counter, 466
page-level compression, 268
Page Reads/Sec counter, 466
Page Splits/Sec counter, 472
page verification method, setting after upgrade, 140–141
Pages/Sec counter, 466
Pages Input/Sec counter, 466
parameter values, optimizing for, 501–504
parameters
passed to executable for command-line installation, 96–98
passed to installer for command-line installation, 98
partitioned views, 274
partitions, tables and, 269–271
Passed status of rule, 79
passive node, 45
password options, 212
passwords, strong, 215
patches, applying, 170
pecking order, remembering, 528
peer-to-peer replication, 9, 61–62
% Disk Time counter, 471
% Privilege Time counter, 457
% Processor Time counter, 457
perfmon. See Performance Monitor
performance. See managing query performance
performance counters (Resource Governor), 519–520
Performance Monitor (perfmon)
Add Counters dialog box, 453
capturing counters using DMVs, 472
correlating with SQL Server Profiler, 495–496
CPU and, 456
default counters displayed within, 453
highlighted counters in, 453
monitoring memory and, 464
overview of, 452
Resource Governor and, 519–520
Trace section of, 478
Performance - Top Queries by Average CPU Time Standard report, 500
permissions
groups of, 226
managing, 226
registering assemblies and, 294
synonyms and, 280
Physical Disk object Windows counters, 471
physical files of databases, 251
piecemeal restore
plan guides
planned role changes, 55
Planning category (SQL Server Installation Center), 72–73
podcasts, 538
point-in-time recovery, 371
policies
evaluating against multiple servers, 196–199
exporting current state as, 185–187
Policy-Based Management
creating policy manually, 178–185
exporting current state as policy, 185–187
importing predefined policy, 187–189
terminology, 177
Policy Evaluation Warning message box (Policy-Based Management), 184
post-index creation process
Dynamic Management Views, 326–329
insert, update, and delete statements, 325–326
overview of, 324
post-installation. See Configuration Manager; configuring instance
post-upgrade procedures
compatibility level, changing, 139
object integrity, checking, 139–140
overview of, 138
page verification method, setting, 140–141
row and page counts, correcting, 140
PowerShell scripting tool, 8
practicing recovery strategy, 373–374
predefined policies, importing, 187–189
prefix compression, 268
pre-installation considerations
editions of SQL Server
choosing, 21
Developer, 24
Standard Edition for Small Businesses, 24
Web, 25
Workgroup, 25
hardware requirements
disk storage configuration, 36
preparation and prerequisites, 70
user accounts, 70
preparation
for hardware failures, 384
for installation, 70
for installation of multiple instances, 90
preproduction tasks
maintenance and monitoring, 172–173
server, 170
pressure situations for DBAs, dealing with, 532–534
primary key constraints, 259–260
primary XML indexes, creating via T-SQL, 315
principals
database
application roles, 220
overview of, 216
logins
associating with certificates and asymmetric keys, 213–214
linking credentials to, 214–215
logins, creating
SQL Server-authenticated, 213
backing up, 367
description of, 244
Processor Queue Length counter, 458
Product Key screen for in-place upgrade, 123
product solution center for SQL Server, 539
Profiler
correlating with Performance Monitor, 495–496
Edit Filter dialog box, 474–475
launching, 472
plan guide events in, 510
Trace Properties dialog box, 473
programmability enhancements
GROUPING SETS operator, 17
overview of, 10
ROLLUP, CUBE and GROUPING_ID syntax, 18–20
table-valued parameters, 12–14
Programmability folder (Object Explorer), 280
properties
Resource Governor, 518
workload groups (Resource Governor), 514
Properties dialog box
of Named Pipes protocol, 150
of Shared Memories protocol, 149
pros and cons
of failover clustering, 48
of log shipping, 56
of replication, 63
Protocol tab (TCP/IP Properties dialog), 151
protocols
Named Pipes, 150
Shared Memory, 149
TCP/IP, 153
testing to determine performance, 154
proxies, SQL Server Agent, 439–441
public keys, 244
pull subscription, 57
push subscription, 57
Q
queries
See also querying
enhancement to performance and processing of, 5
finding and tuning similar, 497–499
multi-server
query governor cost limit, enabling, 167–168
query hash, 497
query hints
query plan hash, 497
query wait option, 169
querying
See also queries
synonyms, 279
sys.backup_devices catalog view, 357
SYS.DM_DB_INDEX_USAGE_STATS, 326–327
SYS.DM_DB_MISSING_INDEX_DETAILS, 328–329
SYS.DM_EXEC_QUERY_STATS, 326–327
sys.messages catalog view, 432
three tables, 330
queued updating, 60
R
RAID 0 (striped disks), 34
RAID 5 (striped disks with distributed parity), 34
RAID 10 (mirrored and striped disks), 35
RAID levels
choosing, and laying out files, 35–36
Reads/Sec or Writes/Sec counter, 471
Ready to Install screen
installing multiple instances and, 94
when installing first instance, 88
Ready to Upgrade screen for in-place upgrade, 126
rebuilding master database, 410–411
recovery
See also recovery examples
automatic, from corrupted page, 8
fast recovery feature, 45
from scratch, documentation for, 385
recovery examples
compressed and encrypted database restore
certificates, restoring, 403
transaction logs, restoring, 403–409
piecemeal restore
description of, 372
system database restore
master database, rebuilding using
master database, restoring from single user mode, 409
transaction log restore
attempting to bring database online, 394–396
reviewing execution plans without non-clustered indexes, 392–394
verifying correct execution plans, 398
RECOVERY option, 372
recovery strategy, practicing, 373–374
Registered Servers window (SQL Server Management Studio), 189
registering assembly, 294
removing
See also dropping
classifier function from Resource Governor, 518
logical backup device, 355
views from database, 274
replication
key terms, 57
pros and cons of, 63
snapshot type, 58
transactional type
overview of, 59
updateable subscriptions, 60
types of, 57
replication agents, 57
ReportingGroup workload group, creating, 514
Reporting resource pool (Resource Governor), 513
reports
DMVs compared to, 501
standard performance, running, 499–501
reputation as DBA, building, 528–529
Resource database, 253
Resource Governor
backups and, 354
features of, 8
monitoring
overview of, 511
properties, 518
in SSMS, 515
resource pools (Resource Governor), 511–513
Resources category (SQL Server Installation Center), 76
RESTORE DATABASE command, FROM DATABASE_SNAPSHOT clause, 256
Restore Database dialog box, General tab, 402
RESTORE statement
FROM DISK clause, 378
PARTIAL option, 381
syntax for, 376
VERIFYONLY option, 382
Restore Transaction Log dialog box, General page, 404
Restore Transaction Log selection, 403
restoring databases
See also backing up; recovery examples
filegroups, utilizing for faster restores, 383–384
full backups, 376
overview of, 371
pages, 380
piecemeal restores, 372, 380–381, 386–389
preparing for hardware failures, 384
transaction logs, 371, 376–377, 389–398
result set after multi-server query, 194
Results Detailed View dialog box (Policy-Based Management), 183
RETURN clause, 284
RETURNS clause, 286
reverting database to database snapshot, 256–257
reviewing
audit files with SSMS, 492
summary.txt file, 103
role changes and log shipping, 54–55
roles
application, 220
creating in SQL Server Management Studio, 234–239
Roles folder (SQL Server Management Studio), 234
row counts, correcting after upgrade, 140
row-level compression, 268
running
command-line installation, 101–102
standard performance reports, 499–501
running balance, function for returning, 286–287
runtime support for DTS add-on, installing, 119
S
scalar-valued functions, 286–287
Schema – New dialog box (SQL Server Management Studio), 240
schema scope securables, 211
schema securables
user schemas, defaulting, 223–224
searchable arguments (SARGs), 303
secondary server, restoring database to, 55
secondary XML index, creating via T-SQL, 316
securables
description of, 211
schema
user schemas, defaulting, 223–224
server
access to, 221
managing, 220
security enhancements, 20
security features
See also encryption
principals
associating logins with certificates and asymmetric keys, 213–214
linking credentials to logins, 214–215
logins for SQL Server, creating, 211–212
logins for Windows, creating, 212–213
SQL Server-authenticated logins, creating, 213
schemas, 211
securables, 211
terminology for, 209
Security folder (SQL Server Management Studio), 227
security options within SQL Server Management Studio
Security section (Server Properties dialog), 165
Select Columns dialog box, 318
Select Marked Transaction dialog box, 405
Select Object Types dialog box (SQL Server Management Studio), 240
Select Policy dialog box, 197
Select Source dialog box, 196
sending Database Mail, 424–425
server audit
choosing, 479
creating
server-level action groups, 481–482
server audit specification
creating with T-SQL, 481
Server Configuration screen, 83–84
server memory, specifying maximum and minimum, 161–162
server preproduction tasks, 170
Server Properties dialog box
Database Settings section, 163
Memory section, 161
Security section, 165
Server Properties window, 159
server scope securables, 211
server securables
access to, 221
managing, 220
servers
See also central management servers
monitoring
baseline metrics, collecting, 451–452
secondary, restoring database to, 55
virtual, consolidation with, 40
witness, database mirroring high-safety mode with, 50–51
Service Broker, enabling for msdb database, 422
Service Level Agreement (SLA), high availability goal and, 43
service master key, backing up, 366
Service tab (SQL Server Properties dialog), 145
during command-line installation, 101
when installing first instance, 80
Setup Support Rules screen for in-place upgrade, 122
Setup Support Rules, summary of, 79
Shared Memory protocol, 149
SHOWPLAN_XML_SET statement, 506
shutdown problems, audit-related, 494
side-by-side upgrade
backup/restore migration method, 130
Copy Database Wizard migration method, 130–138
detach/attach migration method, 129–130
signing assembly with strong key, 294
simple recovery model, 344
single-node cluster configuration, 45
64-bit SQL Server, 32-bit compared to, 37
SLA (Service Level Agreement), high availability goal and, 43
SMO (SQL Management Objects), copying databases using, 130
snapshot feature of database mirroring, 49–50
snapshot replication, 58
snapshot restore, 372, 381–382
SORT_IN_TEMPDB relational index option, 308
source databases, database snapshots and, 253
sp_add_operator stored procedure, 427
sp_addrolemember stored procedure, 219
sp_addsrvrolemember stored procedure, 216
sp_addumpdevice stored procedure, 355
sp_attach_db stored procedure, 129
sp_configure stored procedure
‘backup compression default’ parameter, 353
overview of, 422
results of executing, 157
sp_delete_database_backuphistory stored procedure, 370
sp_detach_db stored procedure, 129
sp_dropsrvrolemember stored procedure, 216
sp_estimate_data_compression_savings stored procedure, 268
sp_get_composite_job_info stored procedure, 205
sp_help_jobserver stored procedure, 206
sp_help_job stored procedure, 204
sp_ prefix stored procedure, 281
sp_procoption stored procedure, 286
sp_recompile stored procedure, 285
sp_send_dbmail stored procedure, 424
sp_settriggerorder stored procedure, 290
sp_spaceused stored procedure, 346
sp_start_job stored procedure, 432
sp_trace_create stored procedure, 475
sp_trace_event stored procedure, 475
sp_trace_setFilter stored procedure, 475
sparse columns feature, 266–267
sparse files, 253
spec'ing out server, 27
SQL Compilations/Sec counter, 458
SQL Management Objects (SMO), copying databases using, 130
SQL Native Client Configuration section (Configuration Manager), 154–156
SQL Recompilations/Sec counter, 458
SQL Server Agent
enabling notifications, 427–429
New Alert dialog box
General page, 429
Options page, 431
Response page, 430
New Job dialog box
General page, 433
Notifications page, 438
Steps page, 433
New Job Schedule dialog box, 437
New Job Step dialog box
Advanced page, 436
General page, 434
New Operator dialog box, 426
New Proxy Account dialog box
General page, 440
Principals page, 440
Properties dialog box, Alert System page, 428
SQL Server-authenticated logins, creating, 213
SQL Server authentication, 211
SQL Server counters
for monitoring CPU usage, 458–459
for monitoring disk IO, 471–472
for monitoring memory, 466–467
SQL Server Installation Center
Advanced category, 77
Installation category, 73
Maintenance category, 74
Resources category, 76
Upgrade option, 121
SQL Server Integration Services (SSIS), 119–120
SQL Server Management Studio (SSMS)
See also GUI; Object Explorer
adding logical backup device using, 355
description of, 316
Multiserver Results options, 195
New Credential dialog box, 439
New Server Group Properties dialog box, 191
New Server Registration dialog box, 190–191
Policy Management node folder structure, 177
Registered Servers window, 189
Resource Governor in, 515
restoring log files using, 407
reviewing audit files with, 492
security options within, 227
standard reports list in, 499
SQL Server Network Configuration section (Configuration Manager), 148–154
SQL Server Profiler
correlating with Performance Monitor, 495–496
Edit Filter dialog box, 474–475
launching, 472
plan guide events in, 510
Trace Properties dialog box, 473
SQL Server Properties dialog
Advanced tab, 147
FILESTREAM tab, 146
Log On tab, 144
Service tab, 145
SQL Server Services section (Configuration Manager), 144–147
SQL Server 2008 Query Performance Tuning Distilled (Fritchey and Dam), 495
SSIS (SQL Server Integration Services), 119–120
SSMS. See SQL Server Management Studio
stand-alone installation, selecting, 80
Standard Edition, features of, 23–24
Standard Edition for Small Businesses, features of, 24
STANDBY option, 55
starting. See launching
startup problems, audit-related, 493
statements
See also ALTER TABLE statement; BACKUP statement; RESTORE statement
CREATE ASSEMBLY, 294
CREATE CERTIFICATE, 243
CREATE VIEW, 276
delete, post-index creation, 325–326
DROP ASSEMBLY, 295
SHOWPLAN_XML_SET, 506
STATISTICS XML SET, 506
update, post-index creation, 325–326
statistics, filtered, 4
STATISTICS_NORECOMPUTE relational index option, 308
STATISTICS XML SET statement, 506
stored procedures
CrossServerSelect, 200
to reference assembly, creating, 295
sp_add_operator, 427
sp_addrolemember, 219
sp_addsrvrolemember, 216
sp_addumpdevice, 355
sp_attach_db, 129
sp_configure
‘backup compression default’ parameter, 353
description of, 422
results of executing, 157
sp_delete_database_backuphistory, 370
sp_detach_db, 129
sp_dropssrvrolemember, 216
sp_estimate_data_compression_savings, 268
sp_get_composite_job_info, 205
sp_help_job, 204
sp_help_jobserver, 206
sp_prefix, 281
sp_procoption, 286
sp_recompile, 285
sp_send_dbmail, 424
sp_settriggerorder, 290
sp_spaceused, 346
sp_start_job, 432
sp_trace_create, 475
sp_trace_event, 475
sp_trace_setFilter, 475
sysmail_add_account_sp, 422
sysmail_add_profile_sp, 422
sysmail_add_profileaccount_sp, 422
sysmail_delete_log_sp, 426
sysmail_delete_mailitems_sp, 426
xp_cmdshell, 168
xp_instance_regwrite, 165
storing tables, heaps and, 297
stream compression, 8
striped disks (RAID 0), 34
striped disks with distributed parity (RAID 5), 34
strong passwords, creating, 215
clustered, 299
heap, 301
overview of, 298
summary.txt file, reviewing, 103
support options
Microsoft Advisory Services, 541
paid, 541
Surface Area Configuration policy, exporting using current state, 185–187
symmetric keys, encryption using, 246–247
Synonyms folder, 278
synonyms, working with, 277–280
syntax
See also listings
for creating index via T-SQL, 306–308
restoring differential backups, 378
restoring full database backups, 374–376
sys.backup_devices catalog view, querying, 357
sys.configuration system view, results of querying, 158
sys.database_files, querying from snapshot, 255
SYS.DM_DB_INDEX_USAGE_STATS, querying, 326–327
SYS.DM_DB_MISSING_INDEX_DETAILS, querying, 328–329
sys.dm_db_persisted_sku_features DMV, 40
sys.dm_exec_query_memory_grants DMV, 468
sys.dm_exec_query_plan DMV, 507
SYS.DM_EXEC_QUERY_STATS, querying, 326–327, 497
sys.dm_exec_query_optimizer_info DMV, 462
sys.dm_os_performance_counters DMV, 472
sys.dm_os_process_memory DMV, 468
sys.dm_os_schedulers DMV, 460
sys.dm_os_sys_memory DMV, 467
sys.dm_os_tasks DMV, 461
sys.dm_os_threads DMV, 459
sys.dm_os_workers DMV, 459
sys.dm_server_audit_status DMV, 494
sys.dm_sql_referenced_entities dynamic management function, 201
sys.dm_sql_referencing_entities dynamic management function, 203
sys.fn_validate_plan_guide function, 509
sysjobshistory table, 202
sysmail_add_account_sp stored procedure, 422
sysmail_add_profileaccount_sp stored procedure, 422
sysmail_add_profile_sp stored procedure, 422
sysmail_delete_log_sp stored procedure, 426
sysmail_delete_mailitems_sp stored procedure, 426
sys.messages catalog view, querying, 432
sys.sql_expression_dependencies catalog view, 200
System Configuration Checker
installing first instance and, 79–81
installing multiple instances and, 90–92
Planning category, 72
Tools category, 75
system database restore, 409–411
system databases, backing up, 367
System Databases folder (Object Explorer), 252–253
T
table hints, FORCESEEK, 5, 504–505
table scan
clustered index seek compared to, 332
description of, 297
table-valued functions, 10, 287–290
table-valued parameters, 12–14
tables
See also table scan
backup history, 369
defining default constraints on columns in, 257–259
example, creating via GUI, 316–317
foreign key constraints, 262–264
joining multiple without indexes, 331
overview of, 257
primary key constraints, 259–260
sparse columns feature, 266–267
storing, heaps and, 297
sysjobshistory, 202
tblReader, data within after restoration of transaction logs, 408
Tables folder, 257
target audience, talking to, 530–531
tblReader table, data within after restoration of transaction logs, 408
TCP/IP, named pipes compared to, 153
TCP/IP Properties dialog box
Client Configuration section, 156
IP Addresses tab, 152
Keep Alive selection, 152
Protocol tab, 151
warning messages after changes made in, 153
TDE (Transparent Data Encryption), 20, 248–249, 399
tempdb database, 253
tempdb drive, determining size of, 33
terminology
database mirroring, 48
encryption, 242
log shipping, 53
network configuration, 148
overview of, 31
Policy-Based Management, 177
replication, 57
security, 209
testing IO subsystem, 170
TestingGroup workload group, creating, 514
testing protocols to determine performance, 154
tools
See also Configuration Manager; Performance Monitor; Profiler
DTS xChange, 120
PowerShell scripting, 8
Upgrade Assistant, 118
Tools category (SQL Server Installation Center), 75–76
trace events (Resource Governor), 520–521
Trace Properties dialog box (Profiler)
Events Selection tab, 473
General tab, 473
transaction log bac
Transact-SQL. See T-SQL kup, 349
transaction log restore
attempting to bring database online, 394–396
reviewing execution plans without nonclustered indexes, 392–394
verifying correct execution plans, 398
transactional replication
overview of, 59
updateable subscriptions, 60
transparent client redirect feature of database mirroring, 50
Transparent Data Encryption (TDE), 20, 248–249, 399
triggers
INSTEAD OF, 290
T-SQL (Transact-SQL)
configuring Database Mail using, 422–424
creating indexes via
overview of, 304
primary XML, 315
secondary XML, 316
database audit specification
database-level audit action groups, 485–486
database-level audit actions, 486–487
logical backup device, adding or removing with, 355
plan guide, creating, 509
server audit, creating with, 480–481
server audit specification
creating with, 481
server-level action groups, 481–482
server-side trace, creating, 476–477
tuning similar queries, 497–499
U
unplanned role changes, 55
updateable subscriptions, in transactional replication, 60
update statement, post-index creation, 325–326
updating skill set as DBA
free training events for, 538
ongoing experimentation and learning for, 537–538
podcasts for, 538
web site resources for, 542
Analysis Wizard
affected objects, 117
analysis report, 116
authentication page, 113
confirmation screen, 114
database selection, 113
rule analysis, 115
server and components selection, 112
warning screen, 115
Welcome screen, 111
installing, 111
Planning category, 73
Upgrade Assistant, 118
Upgrade Integration Services Packages option (Tools category), 76
Upgrade Progress screen for in-place upgrade, 126
upgrade rules for in-place upgrade, 125
upgrading
post-upgrade procedures
compatibility level, changing, 139
object integrity, checking, 139–140
overview of, 138
page verification method, setting, 140–141
row and page counts, correcting, 140
side-by-side upgrade
backup/restore migration method, 130
Copy Database Wizard migration method, 130–138
description of, 128
detach/attach migration method, 129–130
SQL Server Integration Services and, 119–120
strategies for, 120
user accounts, pre-installation decisions about, 70
User Connections counter, 472
user connections option, 169
user-defined table type, 10
user schemas, defaulting, 223–224
users
creating in SQL Server Management Studio, 227–234
USING XML INDEX option, 315
V
validating plan guides, 509–511
variables, enhancements to, 10–11
View Facets dialog box (Policy-Based Management), 185
viewing
enabled protocols, 149
views
altering existing, 274
creating, 273
description of, 272
partitioned, 274
removing from database, 274
virtual servers, consolidation with, 40
W
warning messages (TCP/IP Properties dialog), 153
Web Edition, features of, 25
web sites
dedicated to SQL Server, 542
free training events, 538
Jumpstart TV SQL Channel, 538
Microsoft
Advisory Services, 541
Help and Support, 541
learning, 537
SQL Server Connect, 542
SQL Server Training Portal, 537
Technical Communities, 540–541
podcasts, 538
product solution center for SQL Server, 539
Windows authentication, 190, 211–212, 215
Windows counters
for monitoring CPU usage, 457–458
for monitoring disk IO, 471
for monitoring memory, 465–466
Windows principals, creating logins for, 212–213
WITH CHECK option, 275
WITH ENCRYPTION option, 273
witness server, database mirroring high-safety mode with, 50–51
Workgroup Edition, features of, 25
workload groups (Resource Governor), 511, 514–516
write-ahead events, enhancements to, 9
X
xp_cmdshell stored procedure, 168
xp_instance_regwrite stored procedure, 165