Index
A
ABORT_AFTER_WAIT
parameter 566
role-based 223
single sign-on (SSO) 222
actions 585
Active Directory Organizational Unit (OU) 507
Active Expensive Queries section 36
Data File I/O section 36
overview of 33
Processes section 34
Recent Expensive Queries section 36
Resource Waits section 35
actual execution plans 408
ad hoc queries 105
Advanced Encryption Standard (AES) 306, 478
affinity masks 105
performance conditions 620
recommendations for 618
SQL Server events 619
WMI event alert conditions 621
alphanumeric data types 334
ALTER ANY EVENT SESSION permission 262
ALTER ANY USER permission 257
ALTER AUTHORIZATION 266
ALTER TABLE statements 352
ALTER TRACE permission 262
Always Encrypted 310
Always On availability groups 64, 255, 515, 636
AlwaysOn_health event session 555, 586
antivirus software, configuring 159
approximate numeric types 335
articles 497
artificial intelligence 138
AS clause 353
ASYNC_NETWORK_IO 580
AT TIME ZONE function 337
auditing defined 319
SQL Server Audit 319
threat detection feature 318
authentication to SQL Server on Linux 245
Certificate-Based Authentication 244
integrated authentication and ActiveDirectory 68
Kerberos authentication 69
mixed mode 141
two-factor authentication 243
types of 242
authorization, vs. ownership 265
autoclose 184
autocreate statistics 184
automatic checkpoints 188
automatic failovers 524
Automatic Plan Tuning feature 418
administering multiple SQL Servers 638
components of automated administration 607
maintaining SQL Server 623
SQL Server Agent 612
SQL Server Maintenance Plans 625
using PowerShell 648
autoshrink database setting 185, 575, 627
availability groups (AGs) 64
alerting 556
availability group administration 548
backups on secondary replicas in 636
basic availability groups 517
capabilities of 503
checklist 529
configuring 513
creating WSFC for use with 519
database mirroring endpoint 520
distributed availability groups 518
failovers in 524
full database and log backup 528
hybrid availability group topology 537
load balanced read-only routing 536
managing multiserver administration in 641
minimum synchronized required nodes 520
None option (clusterless) 517
ownership of 514
Powershell module and 656
RegisterAllProvidersIP and MultiSubNetFailover 535
secondary replica availability mode 521, 531
seeding options when adding replicas 525
SQL Server Agent automation and 621
Available Memory 599
Average Disk seconds per Read or Write 597
Azure Active Directory (Azure AD)
authentication, integrated 244
authentication, password 244
authentication, universal 243
benefits of 71
hybrid cloud with 121
integrated authentication and 68
Kerberos authentication 69
Linux and 68
security offered by 72
Azure Analysis Services 217
Azure Automation 216
Azure Backup 237
Azure Blob Storage 111, 138, 231, 472
Azure CLI, creating databases using 212
Azure Cloud Shell 199
Azure Data Factory 217
Azure Data Lake 218
Azure ExpressRoute 125
Azure Key Vault 305
creating databases using 210
creating servers using 205
Azure Resource Manager 327, 660
Azure Role-Based Access Control (RBAC) 224
auditing 319
Azure governance 200
Azure management 199
cloud-first approach 202
compared to SQL Server 117
database as a service concept 198
database corruption handling 561
Database Transaction Units (DTUs) 202
disaster recovery preparation 229
elastic database pools 118
firewall protection 318
hybrid availability group topology 537
hybrid cloud with Azure 121
logical SQL Servers 201
managed instances 218
migrating logins from one server to another 289
moving to 239
other SQL Server services 216
pricing tiers and service objectives 213
provisioning, considerations for 197
provisioning databases 209
provisioning elastic pools 214
provisioning logical SQL servers 204
recovery strategies 491
securing 326
security considerations 218
service tiers 118
sharding databases with Split-Merge 121
sign in security 72
Threat Detection 318
using PowerShell module with 660
Azure SQL Database Import Service 239
Azure SQL Data Warehouse, benefits of 117
Azure Stack 124
Azure Storage 114
Azure Virtual Machines (Azure VMs) 111
Azure Virtual Network (VNet) 124
Back Up Database task 632
backup disks 470
backups. See also high availability (HA)
Azure Backup 237
backup creation and verification 478
backup types 472
backup vs. restore strategies 459
DBCC CHECKDB and 558
encrypting 478
fundamentals of data recovery 460
manual (Azure SQL Database) 230
on secondary replicas in availability groups 636
physical backup devices 470
post-installation configuration settings 161
RAID and 55
recovery strategies 487
restore strategies 459
restoring 175
backup sets 471
Backup-SQLDatabase cmdlet 653
base table elimination. See partition elimination
basic availability groups 67, 517
Batch Mode execution 447
Batch Requests 599
bigint data type 336
binary data type 339
binary large objects (BLOBs) 367
blocked_by column 387
blocking 386. See also isolation levels and concurrency
boot page 83
Border Gateway Protocol (BGP) 298
bring-your-own-license (BYOL) VM 135
broadcast 297
broken recovery chains 638
brute-force attacks 294
B-tree structure 437
Buffer Cache Hit Ratio (BCHR) 598
Buffer Manager 46
buffer pool extension 47
BUILTINAdministrators group 254
bulkadmin server role 275
Bulk Changed Map (BCM) 83
bulk-logged recovery model 464, 469
Business Intelligence edition, appropriate use of 132
capital expenditures (CapEx) 198
cascading 346
Central Management Server (CMS) 26
core counts and affinity masks 105
core counts and editions 51
core speed 49
multiple 49
Non-Uniform Memory Access 50
power saving disablement 51
simultaneous multithreading (SMT) 49, 75
virtualizing CPUs 75
Certificate-Based Authentication 244
Certification Authority (CA) 302
change data capture 380
CHANGETABLE function 380
change tracking 378
char column 335
check constraints 347
CHECKDB 558
CHECK_POLICY option 251
checksum verification 84, 174, 187, 480, 557, 632
claims 71
classification 99
cloud-first approach 202
hybrid cloud with Azure 121
key features of 198
networking as foundation of 58
scalability 203
virtualization and 73
case against intentionally designing heaps 433
choosing proper clustered index keys 429
design choices 432
function of 429
clustering 61
Code Snippets Manager 29
colocation constraint 548
Column Encryption Keys (CEK) 312
Column Master Key (CMK) 312
architecture of 447
Batch Mode execution 447
benefits of 446
clustered and nonclustered Columnstore indexes 447
compression delay 449
key improvements to 447
power of 448
reorganizing 571
command-line interface 9
Common Language Runtime (CLR) 339
components. See database infrastructure
Compress Backup 632
compression delay 449
compression information (CI) structure 95
computed columns 352
concurrency, optimistic vs. pessimistic 342, 399. See also isolation levels and concurrency
affinity masks 105
file system 107
memory settings 102
page file (Windows) 99
parallelism 100
post-installation checklist 151
using Resource Governor 98
CONNECT ALL DATABASE permission 264
constraints 346
CONTAINMENT 256
CONTROL SERVER/DATABASE permission 265
COPY_ONLY option 474
detecting 557
recovering transaction log files 560
repairing 560
Cost Threshold for Parallelism (CTFP) 426
crash recovery. See recovery
create custom server roles 277
CREATE SEQUENCE command 348
CREATE TABLE statement 351
CREATE TYPE statement 350
credit card information 310
Cumulative Updates (CUs) 604
CXPACKET 581
CXPACKET wait 426
data analytics 138
database as a service (DBaaS) 116, 198
database availability groups (DAG) 503
database checkpoints 88
Database Encryption Key (DEK) 303
Database Engine 24
Database Engine Tuning Advisor 12
database infrastructure 45–78, 79–126
Azure and the data platform 110
Central Processing Unit (CPU) 49
configuration settings 98
connecting to SQL Server over networks 57
data storage 51
high availability concepts 59
hybrid cloud 121
memory 45
physical database architecture 79–126
server access security 68
virtualization 73
configuration options 609
email history 610
key features 607
set up 608
test email 609
troubleshooting 610
capturing Windows performance metrics 592
detecting database corruption 557
maintaining database file sizes 571
maintaining indexes and statistics 561
monitoring databases by using DMVs 575
Policy-Based Management (PBM) 643
product life cycle model 604
protecting important workloads 600
Database Master Key (DMK) 303, 307
database mirroring 64, 505, 520
database ownership 265
database properties and options
autoclose 184
autocreate statistics 184
collation 181
compatibility level 182
containment type 183
Database-Scoped Configurations 187
indirect checkpoints 188
page verify 187
Query Store 188
read-only 187
recovery model 182
reviewing database settings 181
single-user mode 195
Snapshot Isolation mode 186
Trustworthy setting 187
database roles 278
considerations for migrating existing 169
creating 177
migrating master database 290
moving and removing 189
moving existing 175
physical database architecture 79
properties and options 181
provisioning Microsoft Azure SQL databases 197–240
provisioning Microsoft SQL Server databases 127–196
setting default for logins 250
Database-Scoped Configurations 173, 187
database snapshots 473
Database Transaction Units (DTUs) 117, 202
Datacenter edition, appropriate use of 132
data collectors 592
backup compression 96
dictionary compression 95
leaf-level vs. non-leaf-level pages 94
page compression 94
prefix compression 95
purpose of 93
row compression 93
Unicode compression 96
Data Control Language (DCL) 259, 378
Data Definition Language (DDL) 257, 378
Data Definition Language (DDL) events 555
Data Encryption Standard (DES) 306
backups 477
checkpoint process 89
checksum verification 84
data page types 82
extents, mixed and uniform 81
file unit allocation size 130
locating SQL Server files 190
maintaining database file sizes 571
memory-optimized objects 84
MinLSN and the active log 91
multiple instances of 80
partial recovery and 81
primary filegroup 80
restarting with recovery 91
separating SQL Server files 130
shrinking database files 574
datagrams 297
data in motion, securing 314
Data Manipulation Language (DML) 230, 257
data masking 317
Data Migration Assistant 4, 136
Azure Blob Storage 111
Azure VMs, performance optimization 111
Azure VMs, locating TempDB files on 116
bandwidth considerations 113
drive caching 114
infrastructure as a service (IaaS) 110
platform as a service (PaaS) 116
SQL Server data files 114
virtual hard drives (VHDs) 112
virtual machine sizing 115
Data Profiling Task 43
Data Protection API (DPAPI) 303
Data Quality Client 8
Data Quality Server 8
Data Quality Services 7
backup creation and verification 478
backup types 472
backup vs. restore strategies 459
fundamentals of 460
physical backup devices 470
recovery strategies 487
data storage 51–57. See also data files and filegroups
commonly used terms 51
drives 52
Fibre Channel vs. iSCSI 56
IOPS (input/output operations per second) 52
latency 52
Network-Attached Storage (NAS) 56
nonvolatile storage disks vs. drives 51
overcommitting 75
queue depth 52
SMB 3.0 file share 57
Storage-Area Network (SAN) 56
storage arrays and RAID 54
storage layer configuration 53
Storage Spaces 57
types of 52
volumes 52
date and time data types 336
date data type 337
datetime2 data type 336
datetime data type 336
datetimeoffset data type 337
Daylight Saving Time (DST) 337
day-to-day maintenance 623
db_accessadmin role 280
db_backupoperator role 280
DBCC CHECKDB 481, 558, 624, 626
DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS 560
DBCC SHRINKFILE 575
dbcreator server role 275
db_datareader role 280
db_datawriter permission 280
db_ddladmin role 281
db_denydatareader role 281
db_denydatawriter role 281
db_owner database role 279
db_securityadmin role 281
deadlocks 589
decimal-point numbers 336
Dedicated 99
Dedicated Administrator Connection (DAC) 283
default constraints 347
defined 291
securing your environment with 292
delayed durability 65, 85, 400
deprecated features, identifying 5, 44
Developer edition, appropriate use of 132
dictionary attacks 294
differential bitmap 474
Differential Changed Map (DCM) 83
digital certificates 301
Direct-Attached Storage (DAS) 53
dirty reads 385
disaster recovery (DR). See also data recovery
Azure SQL Database preparations 229
compared to high availability (HA) 60, 494, 506
configuring failover cluster instances for 502
overview of 493
diskadmin server role 275
Disk Usage report 572
distributed availability groups 67
distributed-denial-of-service (DDoS) attacks 331
distributors 497
DML statements 365
DMV (dynamic management views) 548, 575, 592
domain security groups 261
double-byte character sets (DBCS) 335
double-hop issue 70
drives 52. See also data storage
mechanical hard drives 52
solid-state drives 53
types of 52
drive starting offset 131
dynamic data masking 317
dynamic management function (DMF) 563
dynamic quorum management 509
e-commerce 300
Edition Upgrade Wizard 136
benefits of 118
best use case for 119
database consolidation 119
elastic database jobs 120
elastic database query 120
multitenant architecture 119
elastic DTUs (eDTUs) 118
elasticity 198
emojis 335
Always Encrypted 310
backup encryption 478
defined 294
deterministic vs. randomized 311
in SQL Server 302
network security and 58
process of 295
symmetric and asymmetric 300
transparent data encryption (TDE) 308
Enforce Password Policy 250
Enterprise edition, appropriate use of 131
Entity Framework 342
error logs 32
estimated execution plans 408
ESTIMATEONLY parameter 559
ETW (Event Tracing for Windows) 588
event counter 589
event forwarding 642
event-handling, extended events GUI 13
events 585
exact numeric types 335
Clustered Index Scans 421
Columnstore Index Scans 422
Constant Scans 422
displaying individual steps 419
Good Enough Plan Found 420
Index Scans 421
interpreting graphical execution plans 419
Join operators 423
Key Lookups 421
lookup operations 421
Memory Limit Exceeded 420
operator cost share 422
Optimization Level 420
ORDER BY 420
Parallel icons 424
Query Cached Plan Stats 420
Reason For Early Termination 420
Remote Scans 422
RID Lookups 421
rightmost objects 421
Row Lookups 421
scan operation 421
seek operations 421
Table Scans 421
thickness of gray connector lines 422
upper-left operator (basic operation) 420
yellow triangles 420
analyzing cached execution plans in aggregate 405
clearing the Procedure Cache 406
enforcing 413
parameterization and “parameter sniffing” 402
permissions necessary to view execution plans 412
permissions required to access cached plan metadata 406
Procedure Cache 404
purpose of 401
retrieving 408
Export Registered Servers Wizard 25
Express edition, appropriate use of 132
ExpressRoute 125
Express with Advanced Services, appropriate use of 132
AlwaysOn_health session 555
autogrowth event detection 590
benefits of 584
deadlock detection 589
page_split event identification 563, 591
securing 591
targets 587
terminology used 585
viewing event data 586
XEvent Profiler tool 584
Extended Events GUI 13
Extensible Key Management (EKM) 303
Extract, Transform, and Load (ETL) 378
FacetDomainControllerCheck 3
FacetWOW64PlatformCheck 3
Failover Cluster Instance (FCI) 57, 61, 500, 505, 507
failover groups 235
feature parity, identifying 5
Machine Learning Services 139
Oracle SE Java Runtime Environment (JRE) 138
PolyBase Query Service 138
federation 72
Fibre Channel (FC) 56
File Allocation Table (FAT) 107
file backups 477
filegroups. See data files and filegroups
file header page 83
FILEPROPERTY function 572
files. See data files and filegroups
file sharing protocols 57. See also data storage
file system, configuration settings 107
FileTable 369
File Transfer Protocol 299
Fill Factor property 561
filter drivers 481
filtered unique index 347
flash memory 53
float data type 336
fn_hadr_backup_is_preferred_replica function 517
forced failovers 525
foreign keys 345
full database backups 473, 483
full recovery model 464, 468, 487
full-text indexes 452
Full-Text Search feature 452
function permissions 267
General Availability (GA) release 604
General Data Protection Regulation (GDPR) 291
General Distribution Releases (GDRs) 604
generic data types 333
geography data type 339
geometry data type 339
geo-replication 232
Get-ChildItem cmdlet 654
Global Allocation Map (GAM) 83
globally unique identifier (GUID) 343
GO statement 351
Grant Perform Volume Maintenance Task Privileges 139
graphical execution plans 419
graph tables 362
GUID Partition Table (GPT) 130
Hadoop nonrelational data 138
hard drives 52. See also data storage
HardMemoryLimit 154
Hardware Security Module (HSM) 304
hash indexes 450
hashing 294
headers 297
heap structures 433
hierarchical data 363
hierarchyid data type 339, 344
availability group administration 548
availability group alerting 556
availability group checklist 529
availability group configuration 513
availability group endpoints 520
availability groups and failovers 524
availability groups and WSFC 519
availability group seeding options 525
availability groups (AGs) 64, 503
clustering 61
defined 59
disaster recovery (DR) and 60, 494
effort and investment required for 493
failover clustering 500
full database and log backup 528
hybrid availability group topology 537
importance of redundancy 60
Linux failover clustering with Pacemaker 62
load balanced read-only routing 536
log shipping feature 494
NIC teaming 67
overview of 493
potential failure points 59
reading secondary database copies 531
Red Hat Enterprise Linux (RHEL) configuration 538
replication 497
secondary replica availability mode 521, 636
SQL Server Transaction Log Shipping 63
Windows Server Failover Clustering 61, 507
High Performance settings 51
historic data and values 122, 354
HISTORY_RETENTION_PERIOD option 357
horizontal partitioning 92, 371
HTTP over Transport Layer Security (TLS) 300, 314
HTTPS (HTTP Secure/HTTP over Secure Sockets Layer [SSL]) 300
automated backups 123
benefits of 121
keeping cold data online and queryable 122
private cloud 124
private networking 124
recovery strategies 490
Hypertext Markup Language (HTML) 299
Hypertext Transport Protocol (HTTP) 299
Hyper-Threading. See simultaneous multithreading (SMT)
IMPERSONATE permission 264
Import Registered Servers Wizard 25
INCLUDE list 437
Index Allocation Map (IAM) 83
clustered index design 429
Columnstore indexes 446
filtered unique index 347
full-text indexes 452
hash indexes 450
hierarchyid data type and 344
index statistics 453
index usage statistics 445
locating hypothetical 13
maintaining indexes and statistics 561, 627
memory-optimized tables 449
Missing Indexes feature 441
monitoring index fragmentation 563
nonclustered index design 434
rebuilding 564
reorganizing 568
reorganizing Columnstore indexes 571
spatial indexes 452
updating index statistics 569
XML indexes 453
indirect checkpoint 188
infrastructure as a service (IaaS) 110, 326
In-Memory OLTP 48
Insert Snippet option 29
adding databases to SQL Servers 169
Installation Tab 6
installing a new instance 134
installing or upgrading SQL Server 6
installing tools and services 7, 164
minimizing footprint 128
moving and removing databases 189
performance and reliability monitoring tools 12
platforms supported 1
post-installation server configuration 151
pre-installation considerations 3, 127, 134
smart setup 146
int data type 336
integrity, guaranteeing 346
IntelliSense 29
interconnected data 362
Internet of Things 298
Internet Protocol (IP) 300
Internet Protocol (IPv4) 297
Internet Protocol (IPv6) 297
internet protocol suite 296
Internet Small Computer Systems Interface (iSCSI) 56
Invoke-Sqlcmd cmdlet 655
IO_COMPLETION 583
IOPS (input/output operations per second) 52
IP addresses 298
IP forwarding 328
isolation levels and concurrency
blocking of concurrent sessions 386
blocking, observing 387
default level 398
experiencing phantom reads 389
isolation levels, changing with table hints 392
isolation levels, changing within transactions 391
isolation levels, choosing 385
nonrepeatable reads 388
nonrepeatable reads, preventing 389
on-disk vs. memory-optimized concurrency 398
preventing phantom reads 390
SNAPSHOT isolation level 393
two requests updating the same rows 387
writes blocks reads 387
JBOD (just a bunch of disks) 54
Join operators 423
JSON-formatted data 341
Kerberos 69
keys, primary and foreign 345
large object (LOB) data types 83, 339, 367
latency 52
LCK_M_* 581
life cycle model 604
link aggregation. See NIC teaming
affinity masks on 107
authentication to SQL Server on 245
availability group configuration 538
Live Data window 586
live execution plan 409
load balanced read-only routing 536
load balancing and failover support (LBFO). See NIC teaming
Local Server Groups 24
local storage. See Direct-Attached Storage (DAS)
Lock pages in memory (LPIM) 47, 100, 105, 160
Maintenance Plan report options 632
setting up 147
transaction log backups 474
viewing Azure SQL Database audit logs 227
logical unit numbers (LUNs) 56
authentication to SQL Server on Linux 245
authentication types 242
BUILTINAdministrators group 254
contained database 256
DBA team logins 252
login types 244
moving SQL Server logins 285
NT AUTHORITYSYSTEM account 255
orphaned SIDs 246
sa login 254
securing logins 249
service accounts 255
terminology 241
Log Sequence Number (LSN) 86
log shipping feature 494
Log Shipping Wizard 64
log truncation 87
LowMemoryLimit 154
Machine Learning Server, limiting memory usage by 156
Machine Learning Services 7, 139
maintenance, day-to-day 623
Back Up Database task 631
backups on secondary replicas in availability groups 636
benefits of 625
Check Database Integrity task 626
covering databases with 633
Execute SQL Server Agent Job task 631
Execute T-SQL Statement task 632
History Cleanup task 630
Maintenance Cleanup task 630
new database detection 633
Rebuild Index task 628
Reorganize Index task 627
report options 632
scheduling options 625
Shrink Database task 627
SQL Server Management Studio and 634
Update Statistics 629
when not to use 635
Maintenance Plan Wizard 478, 623, 625
Maintenance tab (Installation Center) 136
managed instance 218
management data warehouse 15–18
accessing reports 18
data collection set up 17
installing 15
Management/Error Logs node 32
many-to-many relationships 363
Master Boot Record (MBR) 130
Master server (MSX) 638
Master Server Wizard 640
max degree of parallelism (MAXDOP) 101, 425
MAXDOP option 566
MAX_DURATION parameter 566
Maximum Server Memory 152
Max Server Memory 102
Max Worker Threads 104
mechanical hard drives 52
MediaPathLength 3
buffer pool cache 46
Central Processing Unit (CPU) issues 49
competition for among various services 154
configuration settings 102
editions and memory limits 48
Lock pages in memory (LPIM) 47, 100, 105
Non-Uniform Memory Access 50
optimize for ad hoc workloads 105
OS reservation calculation 103
overcommitting 74
post-installation settings 152
procedure cache 47
thread consumption 104
upper limit available 45
working set 46
MEMORYCLERK_XE 584
memory-optimized objects 84, 102
memory-optimized tables 357, 397, 449, 456, 478, 629
Memory Pages 598
MemorySafetyMargin 156
MemoryThreshold 156
merge replication 499
key performance metrics 596
Performance Monitor (perfmon.exe) application 592
querying using Performance Monitor 595
querying with DMVs 592
Microsoft Assessment and Planning (MAP) Toolkit 136
Microsoft Cryptographic Application Programming Interface (MCAPI) 304
Microsoft Data Migration Assistant (DMA) 240
Microsoft Hyper-V 73
Microsoft Management Console 11
Microsoft Power BI 217
migration readiness, assessing 4, 169. See also databases
Minimum Recovery LSN (MinLSN) 89, 91
Minimum Server Memory setting 154
minimum synchronized required nodes 520
Missing Indexes feature 441
mixed extents 81
mixed mode authentication 141, 249
monetary data 336
money data type 336
MSX/TSX feature 638
multicast 297
Multi-Channel Memory Architecture 50
Multi Server Administration options 639
MultiSubNetFailover 535
MUST_CHANGE option 251
Network Address Translation (NAT) 297
Network-Attached Storage (NAS) 56
complexities created by 57
network security 58
protocols and ports 58
Virtual Local-Area Network (VLAN) 58
network interface card (NIC) 343
network packets 297
network routing 298
Network Security Groups (NSG) 327
NEWID() function 343
NEWSEQUENTIALID() function 343
NEXT VALUE FOR 349
NIC teaming 67
node-level fencing 546
NOINDEX parameter 559
NO_INFOMSGS parameter 559
noisy neighbor phenomenon 73
benefits of 434
choosing proper nonclutered index keys 435
creating “missing” nonclustered indexes 441
designing 434
INCLUDE list 437
index usage statistics 445
memory-optimized tables 451
properties of good 434
purpose of 434
redundant indexes 436
non-leaf-level pages 94
Non-Uniform Memory Access (NUMA) 50
Non-Volatile Memory Express (NVMe) 53
NoRebootPackage 3
NORECOVERY option 482
normalization 345
NT AUTHORITYSYSTEM account 255
NT File System (NTFS) 107, 130, 368
NT LAN Manager (NTLM) 69
nullable sparse columns 341, 352
NVARCHAR(100) 350
NVARCHAR(4000) 345
object-relational mappers (ORMs) 342
on-disk concurrency 399
ONLINE keyword 564
Online Transaction Processing (OLTP) 102
Open Database Connectivity (ODBC) 9
Open Geospatial Consortium (OGC) 339
operational expenditures (OpEx) 198
optimistic concurrency 342, 399
Optimize For Ad Hoc Workloads 105, 160
OPTIMIZE FOR query hint 403
OPTIMIZE FOR UNKNOWN query hint 403
Oracle SE Java Runtime Environment (JRE) 138
Organizational Unit (OU) 507
overcommitting 74
ownership 265
ownership chains 265
package managers 540
Page Faults 599
page file (Windows) 99
Page Free Space (PFS) 83
PAGEIOLATCH_* 582
PAGELATCH_* 582
page-level corruption 84
Page Life Expectancy (PLE) 597
Page Reads 598
page verify option 84, 174, 557
benefits and drawbacks of 100
Cost Threshold for Parallelism (CTFP) 100, 426
defined 425
forcing parallel execution plans 425
max degree of parallelism (MAXDOP) 101, 425
parallel plan operations 100
parameterization 402
parameter sniffing 402
PARTIAL. See CONTAINMENT
partial-restore sequence 486
partitioned views 93
partition elimination 92
partition switching 92
partitioning key 92
partitioning, preventing 62
payloads 297
peer-to-peer replication 497
performance and reliability monitoring tools
Database Engine Tuning Advisor 12
Extended Events GUI 13
management data warehouse 15
Automatic Plan Tuning feature 418
capturing metrics with DMVs and data collectors 592
delayed durability 400
execution plan operators 419
execution plans 401
isolation levels and concurrency 383
parallelism 425
Query Store feature 413
Peripheral Component Interconnect Express (PCIe) 53
authorization vs. ownership 265
database roles 278
Data Definition and Data Manipulation languages 257
Dedicated Administrator Connection (DAC) 283
granting commonly needed 261
logins and users 241
modifying 259
moving logins and permissions 285
necessary to view execution plans 412
overlapping 260
required to access cached plan metadata 406
securing permissions to interact with jobs 614
server roles 273
views, stored procedures, and function permissions 267
worst practices 281
pessimistic concurrency 342, 399
phantom rows 385
physical backup devices 472
physical database architecture 79–98
data compression 93
data files and filegroups 80
file types 79
table partitioning 92
temporary database (TempDB) 96
piecemeal databases 486
plan cache. See procedure cache
Plan Guide feature 403
plan_handle column 405
planned failovers 524
Planning tab (Installation Center)
Configuration Checker tool 3, 136
Data Migration Assistant 4
Upgrade Advisor link 4
Platform Abstraction Layer (PAL) 303
platform as a service (PaaS) 116, 198
point-in-time recovery 468, 485
Policy-Based Management (PBM) 643
PolyBase external tables 361
PolyBase Query Engine 138
Power BI 217
power options 159
power saving 51
automation using 648
availability group automation 656
Backup-SQLDatabase cmdlet 653
cmdlets for 649
creating databases using 211
Get-ChildItem cmdlet 654
help information 650
installing 651
installing offline 652
Invoke-Sqlcmd cmdlet 655
Remove-Item cmdlet 654
using with Azure 660
PowerShell Provider for SQL 11
predicates 585
Premium Storage 112
preproduction environments 252
primary keys 345
principal, defined 241
proactive maintenance 623
processadmin server role 276
production environments 252
product life cycle model 604
Product Updates page 146
Profiler tool 13
Project Hekaton 398
Border Gateway Protocol (BGP) 298
defined 296
File Transfer Protocol 299
HTTP over Transport Layer Security (TLS) 300, 314
Hypertext Transport Protocol (HTTP) 299
Internet Protocol (IP) 296, 300
internet protocol suite 296
protocol encryption 300
Transmission Control Protocol (TCP) ports 296
versions of IP in use today 297
Voice over IP 299
X.509 standard 302
Proxies 612
public database role 281
Public Key Certificates 302
public key encryption (PKE) 301
public server role 276
publishers 497
Pull subscriptions 497
Push subscriber models 497
Query Optimizer 47
examining execution plans using 403
initially configuring 415
purpose of 413
turning on 188
using query store data in your troubleshooting 416
queue depth 52
rainbow tables 295
Random Access Memory (RAM) 45. See also memory
random salts 295
READ COMMITTED 385
READ_COMMITTED_ SNAPSHOT (RCSI) isolation level 393
read-scale availability groups 66
READ UNCOMMITTED (NOLOCK) 390
real data type 336
RebootRequiredCheck 4
RECOMPILE query hint 403
recovery. See also data recovery
checkpoint system 88
Grant Perform Volume Maintenance Task Privilege 139
Minimum Recovery LSN 89
recovery chains, preventing broken 638
recovery interval, setting 90
recovery model setting 174, 182, 464
restarting with recovery 91
strategies for 487
Recovery Point Objective (RPO) 60, 460, 462
Recovery Time Objective (RTO) 60, 90, 460, 463
Red Hat Enterprise Linux (RHEL), availability group configuration 538
redundancy 60
Redundant Array of Independent Disks (RAID) 54, 57
redundant indexes 436
referential integrity 346
RegisterAllProvidersIP setting 535
regular maintenance 623
Remote Desktop Protocol (RDP) 463
Remote Direct Memory Access (RDMA) 57
Remove-Item cmdlet 654
REPAIR_ALLOW_DATA_LOSS parameter 559
Repair feature 136
REPAIR_REBUILD parameter 559
REPEATABLE READ 385
replication 229, 240, 497, 636
Report Services Configuration Manager 20
Resilient File System (ReFS) 368
RESOURCE_SEMAPHORE 582
restart recovery. See recovery
RESTORE VERIFYONLY 632
RESUMABLE index rebuilds 628
RESUMABLE parameter 566
retention policy 624
ring_buffer data collection 584, 588, 589, 594, 595
Role-Based Access Control 223
routing 298
ROWGUIDCOL property 499
row identifier (RID) 433
row-level security 315
rowversion data type 341
run books 463
sa login 254
salts 295
scalability 203
schemas 341
scientific notation 335
secret keys 300
Secure Sockets Layer (SSL) 58
security admin permission 276
security groups 261
security identifier (SID) 172, 242, 246, 266
auditing 319
Azure SQL Database 218
Border Gateway Protocol (BGP) 298
brute-force attacks 294
Certification Authorities (CA) 302
data transmission protocols 296
defense-in-depth 292
dictionary attacks 294
digital certificates 301
distributed-denial-of-service (DDoS) attacks 331
encryption in SQL Server 302
General Data Protection Regulation (GDPR) 291
hashing vs. encryption 294
logins and users 241
moving security objects from one server to another 289
moving SQL Server logins and permissions 285
network security 58
permissions in SQL Server 257
permissions worst practices 281
securing Azure infrastructure as a service 326
securing data in motion 314
security principles and protocols 292
server access security 68
SQL injection 293
symmetric and asymmetric encryption 300
seek time 52
SELECT ALL USER SECURABLES permission 264
SELECT INTO syntax 343
SELECT statements 385
sensitive data 311
sequences 347
Serial ATA (SATA) 52
Serial Attached SCSI (SAS) 52
SERIALIZABLE isolation 385, 398
serveradmin server role 276
server components. See database infrastructure
Grant Perform Volume Maintenance Task Privilege 139
SQL Server PolyBase Engine service 138
Server Message Block (SMB) 54
Server Registration feature 24
server roles 273
server volume alignment 127
Service accounts 255
Service Broker feature 244
service endpoints 330
Service-Level Agreement (SLA) 460
Service Master Key (SMK) 303, 306
Service Packs (SPs) 604
Service Principal Name (SPN) 69
servicing model 604
session_id column 387
SET TRANSACTION ISOLATION LEVEL command 391
setupadmin server role 277
SetupCompatibilityCheck 4
sharding 121
Shared Global Allocation Map (SGAM) 83
SHOWPLAN permission 263
Shrink Database task 627
Simple Mail Transfer Protocol (SMTP) 608
simple recovery model 464, 469, 487
simultaneous multithreading (SMT) 49, 75
single-user mode 195
sliding window partition strategy 375
Slowly Changing Dimension (SCD) 399
smalldatetime data type 336
smallint data type 336
smallmoney data type 336
smart setup 146
SMB 3.0 protocol 57
SNAPSHOT isolation level 393
Snapshot Isolation mode 186
snippets 29
soft-NUMA 50
solid-state drives 53
SORT_IN_TEMPDB option 565
SOS_SCHEDULER_YIELD 582
SPARSE keyword 352
spatial data types 339
spatial indexes 452
spatial queries 340
spatial reference ID (SRID) 340
specialized data types 339
graph tables 362
memory-optimized tables 357, 397
PolyBase external tables 361
system-versioned temporal tables 354
split brain. See partitioning
Split-Merge tool 121
sp_sequence_get_range stored procedure 349
sp_who2 command 387
sp_who command 387
SQL-authenticated logins 172
SQLCMD 9
SQL injection attacks 293
administering multiple 638
auditing 319
compared to Azure SQL Database 117
databases, adding 169
databases, moving and removing 189
failover cluster instance configuration 510
installing and configuring features 164
installing new instances 134
maintaining 623
Maintenance Plans 625
managed backups 123
minimizing installation footprint 128
new servicing model 604
post-installation server configuration 151
pre-installation considerations 127
timeouts 386
upgrading 505
volume usage and settings 127
administering SQL Server Agent operators 618
availability group environment 621
Azure alternative to 216
event forwarding 642
Job Activity Monitor 38
job, scheduling and monitoring 614
job history, configuring and viewing 615
job step security 612
jobs, configuring 612
notifying operators with alerts 39, 618
operators 40
overview of 37
securing permissions to interact with jobs 614
setting up 158
Azure alternatives to 217
configuration and setup 168
installing 142
limiting memory usage by 154
SQL Server Authentication 243
SQL Server Configuration Manager 11
database deployment using 181
installing 137
tools included in 41
SQL Server Import And Export Wizard 42
SQL Server Integration Services
Azure alternatives to 217
benefits of 41
installing 143
moving logins by using 286
SQL Server Management Studio 21–41
Activity Monitor tool 33
customizing menus and shortcuts 31
database creation using 180
download size 22
error logs 32
features of 23
filtering objects 27
IntelliSense tools 29
Maintenance Plans and 634
releases and versions 21
Server 478
Server Registration feature 24
snippets 29
SQLCMD mode 9
SQL Server Agent 37
upgrading 22
SQL Server memory manager 46
editions 131
performance and reliability monitoring tools 12
server editions 169
SQL Server Data Tools 41
SQL Server Management Studio 21
SQL Server Reporting Services 18
tools and services included with 7
SQL Server Profiler 13
Azure alternatives to 217
configuration and setup 165
limiting memory usage by 155
Report Services Configuration Manager 20
automating 147
changing decisions after 134
Grant Perform Volume Maintenance Tasks feature 139
initiating 135
installing core features 142
logging setup 147
Mixed Mode authentication 141
smart setup 146
TempDB database 140
SQL Server Surface Area Configuration 157
SQL Server Transaction Log Shipping 63
sql_variant data type 345
configuration and setup 164
SSISDB Wizard 41
SSMS_IsInternetConnected 4
Standard edition, appropriate use of 132
Standard Storage 112
autocreate database statistics 184
index statistics 453
index usage statistics 445
updating index statistics 569, 624
STGeomFromText method 339
STONITH 546
STOPAT option 485
STOPBEFOREMARK option 485
storage. See data storage
Storage-Area Network (SAN) 56, 128
Storage Spaces 57
stored procedures 267
Stretch Database 122
subnets 327
subscribers 497
Surface Area Configuration 157
Surround With Snippets option 29
swap file. See page file (Windows)
sysadmin server role 274
sys. dm_db_requests 387
sys_dm_db_sessions 387
sys.dm_exec_requests 576
sys.dm_exec_sessions 576
sys.dm_os_performance_counters 592
sys. server_principals 242
sys.sp_ cdc_enable_db stored procedure 380
system_health 586
system-versioned temporal tables 354
SYSTEM_VERSIONING option 357
alphanumeric data types 334
binary data types 338
binary large objects (BLOBs) 367
capturing modifications to data 377
cascading 346
computed columns 352
constraints 346
data type selection 333
graph tables 362
hierarchyid data type 339, 344
keys and relationships 345
memory-optimized tables 357, 397
numeric data types 334
numeric types 335
PolyBase external tables 361
referential integrity 346
rowversion data type 341
sequences 347
spatial data types 339
specialized data types 339
special table types 354
sql_variant data type 345
string data and collation 335
system-versioned temporal tables 354, 381
table partitioning 370
temporal tables 381
Unicode support 335
uniqueidentifier data type 343
user-defined data types (UDTs) 350
user-defined types 350
XML data type 341
defined 370
defining partitions 372
partition design guidelines 374
sliding window partition strategy 375
vertical partitioning 357, 377
tail-of-the-log backups 474
targets 585
Target Server Memory 600
Target server (TSX) 638
TCP/IP stack 297
turning on post-installation 158
telemetry_xevent 586
buffer pool usage of 47
default settings for 140
locating files on VMs 116
managing 96
thin provisioning 75
ThreadHasAdminPrivilegeCheck 4
threat detection. See auditing and threat detection
Threat Detection feature 318
ticket-granting ticket (TGT) 69
time data type 337
time-outs 386
timestamp data type 342
time zones 337
tinyint data type 336
TotalMemoryLimit 154
Total Server Memory 599
Trace Flag 3226 163
Trace Flag 8002 107
Trace Flags 1118/1117 97
transactional replication 499
backups 474
checkpoint system 88
delayed durability 85
file extension 85
file size and performance 91
incomplete transactions 86
log files required 85
Log Sequence Number (LSN) 86
log truncation 87
Minimum Recovery LSN (MinLSN) 89
MinLSN and active log 91
purpose of 85
recovering corrupt 560
recovery interval, setting 90
restarting with recovery 91
space issues 88
successful vs. unsuccessful transactions 85
virtual log files (VLFs) 86
Write-Ahead Logging (WAL) 85
Transmission Control Protocol (TCP) port 296
transparent data encryption (TDE) 174, 219, 303, 308
Transport Control Protocol (TCP) 58, 158, 207
Transport Security Layer (TSL) 58
tree structures 344
Triple Data Encryption Standard (3DES) 306
error 1225 245
error 11732 350
error 41305 399
error 41325 399
using query store data in 416
TRUNCATE TABLE command 258
creating databases using 213
moving server permissions by using 288
moving server roles by using 288
moving SQL Server–authenticated logins by using 287
moving Windows-authenticated logins by using 287
T-SQL statements 632
two-way synchronization 378
Unicode, table design and 335
uniform extents 81
unique constraints 347
uniqueidentifier data type 343
Universal Authentication 243
unsigned integers 336
UPDATE STATISTICS operation 564
USE PLAN query hint 404
user 99
user-defined data types (UDTs) 350
user-defined routes 328
user-defined types 350
users. See logins and users
VARBINARY(MAX) columns 368
varchar colum 335
Verify Backup Integrity 632
vertical partitioning 357, 377
VertiPaqMemoryLimit 155
vi editor 540
VIEW DEFINTION permission 263
VIEW SERVER STATE permission 263
virtual CPU (vCPU) 76
virtual hard drives (VHDs) 112
virtual IP resource 547
Virtual Local-Area Network (VLAN) 58
virtual log files (VLFs) 86
Azure VMs, performance optimization 111
Azure VMs, sizing 115
benefits of 73
main players 73
purpose of 73
resource provisioning for 74
simultaneous multithreading and 49
Virtual Network Name (VNN) 62, 507
virtual network service endpoints 330
Virtual Private Network (VPN) 124
VMware 73
Voice over IP 299
defined 52
server volume alignment 127
WAIT_AT_LOW_PRIORITY option 566
WAIT_XTP_RECOVERY 583
Watch Live Data 586
wear-leveling 53
Web edit, appropriate use of 132
Windows authentication 243
Windows Management Instrumentation (WMI) alerts 40
Windows Server Failover Clustering 61, 500, 507, 516, 519
Windows Server Power Options 159
Windows Server Update Services 146
WITH CHANGE_TRACKING_CONTEXT clause 380
WITH RECOVERY option 482
WmiServiceStateCheck 4
worker threads 104
working set 46
WorkingSetMaximum 156
WorkingSetMinimum 156
workload groups 98
workloads, protecting important 600
World Wide Web (the web 299
Write-Ahead Logging (WAL) 85
write-amplification 53
write conflict error 399
WRITELOG 583
X.509 standard 302
XE_FILE_TARGET_TVF 583
XE_LIVE_TARGET_TVF 583
XEvent Profiler 13
XEvent Profiler tool 584
XML indexes 453