Index
A
Affinity mask, 129, 131
Always Encrypted, 870
administering keys
Column Master Key Cleanup dialog
box, 406, 407
Column Master Key Rotation dialog
box, 405, 406
Secure Enclaves, 404, 405
sys.column_encryption_keys, 403
sys.column_encryption_key_
values, 404
sys.column_master_keys, 403
attestation service, 394
column encryption key, 393
column master key, 393
database engine, 393
defined, 392
implementation
attestation server, 395, 396
cryptographic objects, 397, 398
encryption types and feature
compatibility, 400
guarded host, 396
insert data, 401, 402
key store, 399
secure enclaves, 399
VBS, 394
secure enclaves, 394
AlwaysOn Availability
benefits and considerations, 608
PROSQLADMIN-C cluster, 608
ReadScale database, 608–611
select databases page, 611, 612
Specify Availability Group options, 611
Specify Replicas page
Backup Preferences tab, 614, 615
Endpoints tab, 613, 614
Listener tab, 615, 616
Read-Only Routing tab, 616, 617
Replicas tab, 612, 613
Select Initial Data Synchronization
page, 617, 618
AlwaysOn Availability Groups (AOAG)
administrative considerations, 528, 529
automatic page repair, 476, 477
data-tier applications, 473
failover
asynchronous, 521–524
synchronous, 518–520
HA (see High availability (HA))
monitoring
AlwaysOn dashboard, 525–527
AlwaysOn health trace, 527, 528
synchronizing uncontained
objects, 524
topology, 475
VMs, 472

AlwaysOn dashboard, 525–527
AlwaysOn health trace, 527, 528
Atomic, Consistent, Isolated and Durable
(ACID), 177, 671
Autocommit transactions, 670, 682
Automation, 124, 809
B
Backup database
BULK LOGGED recovery
model, 411, 412
creation, 412–415
devices, 417, 418
media set, 418
differential, 415, 429
filegroup, 420, 430
full backup, 415, 428
full backup–only, 419
full/differential/transaction logs, 420
FULL recovery model, 410
full/transaction log, 420
log backup, 415
media, 416
mirrored media set, 431
partial, 421
SIMPLE recovery model, 410, 411
SQL server management studio
AES algorithm, 424
Backup Options page, 424
filegroups, 423
General page, 421, 422
Media Option page, 424
reliability section, 424
transaction log, 429
T-SQL
backup options, 425
backup set options, 426
error management options, 427
log-specific options, 428
media set options, 427
miscellaneous options, 428
WITH options, 426
Bash, 108
B-tree, 245, 247
clustered index scan, 247
leaf level, 247
root node, 247
uniquifier, 247
bw-tree, 272
C
Cell-level encryption, 386
authenticator column, 390
cryptographic functions, 386
DECRYPTBYKEY() function, 391, 392
DecryptByKey parameters, 391
duplicate database selection, 387, 388
EncryptByKey() parameters, 389
HASHBYTES() function, 390
salt value, 389
SensitiveData table, 387
Central Management Server
(CMS), 325, 866
Always Encrypted, 870
Connection Properties, 869
New Server Group, 872
Registered Servers, 867, 868, 873
Chapter6LogFragmentation
create database, 196, 197
log reuse waits, 200, 201
log size and VLFs number, 197
sys.databases, 200
transaction log, 202
Chocolatey, 116
INDEX

Classifier function, 893, 895–896
Client connectivity tools, 25
Client tools backwards compatibility, 25
Client tools SDK, 26
Clustered columnstore indexes, 263–265
Clustered indexes, 245
CREATE CLUSTERED INDEX, 249, 250
DROP INDEX, 252
primary key clustering, 248
tables with, 247
tables without, 246
WITH options, 250–252
Collation
accent sensitive, 54
binary collation sort order, 58, 59
case sensitivity, 54, 55
configuration page, 54
sort order, 56, 57
Columnstore indexes, 261–263
Consistency errors, 293
CREATE TABLE T-SQL, 234
Cryptographic functions, 386
D
Database administrators (DBAs), 15, 165,
180, 329, 418
Database consistency
DBCC CHECKDB (see DBCC
CHECKDB)
detecting errors
bad checksum error, 302
CHECKSUM, 295, 298
Corrupt Table, 300, 301
event types, 299
memory-optimization
tables, 303, 304
options page, 297
page verify option, 296
querying suspect_pages, 302, 303
suspect_pages, 299
TORN_PAGE_DETECTION, 295, 296
error
605 error, 293
823 error, 294
824 error, 294
5180 error, 294
7105 error, 295
system database corruption
reattaching database, 306
rebuild parameters, 305
repair instance parameters, 307
select instance page, 306
VLDBs, 323
Database Engine service, 23, 116, 143
Database-level security
built-in database roles, 341, 342
contained databases, 348–351
schemas, 345–348
Securables tab, 343
T-SQL, 344
Database snapshot
benefits, 572
data-tier application, 571
definition, 569
implementation, 572–576
metadata-driven script, 572
querying, 571–573
recovering data, 576, 577
sparse file, 569
Data Protection API (DPAPI), 372
Data quality server, 25
Data structure
B-tree, 245
clustered index scan, 247
leaf level, 247
INDEX

root node, 247
uniquifier, 247
heap, 246
DBCC CHECKALLOC, 319, 324
DBCC CHECKCATALOG, 318
DBCC CHECKCONSTRAINTS
arguments, 321
DBCC CHECKDB, 308
arguments, 308, 309
CHECKALLOC, 319
CHECKCATALOG, 318
CHECKCONSTRAINTS, 321
CHECKFILEGROUP, 319
CHECKIDENT, 320
CHECKTABLE, 319
configure notification, 313
corrupt page, 311
Corrupt Table error, 315
emergency mode, 315–318
errors in job history, 311
new operator selection, 312
REPAIR_ALLOW_DATA_LOSS, 313
REPAIR_REBUILD, 313
repairing corruption, 314
running command, 310
suggested repair option, 314
suspect_pages table, 315
TempDB space, 310
DBCC CHECKFILEGROUP, 319
DBCC CHECKIDENT arguments, 320
DBCC CHECKTABLE, 319
DB_ID() function, 626
@DBName parameter, 652
dbo.suspect_pages, 299
Deadlocks
chronology, 668
deadlock monitor, 668
minimize, 670
observation, 695, 696
scripts, 668
victim’s query window, 669
DecryptByKey parameters, 391
Deferred transactions, 681
Degree of parallelism (DOP), 749
Disaster recovery (DR)
AOAG (see AlwaysOn Availability
Groups (AOAG))
database creation, 534–537
GUI configuration
ad-hoc restores, 537
Copy Files tab, 541, 542
Initialize Secondary Database
tab, 540, 541
Log Shipping Monitor
Settings, 545, 546
monitor server, 544
New Job Schedule, 539, 542–545
No Recovery Mode, 542
proxy account, 537
Restore Transaction Log
tab, 543, 544
Save Log Shipping
Configuration, 546, 547
Transaction Log Backup
Settings, 538
Transaction Log Shipping page, 537
vs. HA, 480–482
log shipping, 477–480
recovery point objective, 536
T-SQL configuration
@backup_job_id output, 547, 548
sp_add_log_shipping_primary_
database Parameters, 546–549
sp_add_log_shipping_primary_
secondary system, 549
Data structure (cont.)
INDEX

sp_add_log_shipping_secondary_
database paremeters, 553, 554
sp_add_log_shipping_secondary_
primary system, 550–553
sp_processlogshippingmonitor
secondary parameters, 555, 556
Distributed Replay
architecture, 761
clients configuration, 764
options, 764
components, 760
controller configuration
Access Permissions, 763
Launch and Activation
permissions, 763
logging level, 762
Security tab, 762
database creation, 769–771
DReplay.exe.preprocess.
config, 765, 767
DReplay.exe.replay.config, 766, 768
target synchronization, 772, 773
trace
arguments, 806
converting, 803, 804
events, event fields and
actions, 773–792
Extended Event Session, 793–800
generate activity, 800–802
preprocessing, 804, 805
Distributed Replay client, 26
Distributed Replay controller, 26
Docker, 111
-d switch, 113
Dynamic link library (DLL), 241–244
Dynamic management views (DMVs), 909
binary mask, 918
CPU utilization, 913, 917
disk latency, 920
Performance Monitor, 909
resetting statistics, 917
resource pool stats, 911
sales user and manger login, 912
sys.dm_resource_governor_
resource_pool_affinity, 917
sys.dm_resource_governor_
resource_pools, 913
sys.dm_resource_governor_
resource_pool_volumes, 918
sys.dm_resource_governor_
workload_groups, 915
workload group stats, 910
E
Editions and license models, 4
CAL, 4
developer, 5
enterprise, 5
express, 5
operational teams, 6
Server Core, 5
standard, 5
TCO, 3
web, 5
EncryptByKey() parameters, 389
Encryption, 371
Encryption hierarchy
asymmetric keys, 372
certificate authority, 372
DPAPI, 372
SQL Server
backup and restore Database
Master Key, 374
Database Master Key selection, 374
EKM module, 376
INDEX

hierarchy, 375
Restore Service Master Key, 373
Service Master Key, 373
symmetric key, 371
End of life cycle (EOL), 17
Event session
database creation, 704–707
dialog box creation
Advanced page, 713
Data Storage page, 712, 713
General page, 708
module_start event, 708–711
page_splits event, 710, 711
T-SQL
arguments, 713
WITH options, 714
EXEC keyword, 127
Explicit transactions, 670
Extended Events
actions, 702
channels, 700, 701
data collection
Aggregation dialog box, 722
analyzing data with T-SQL, 723–727
Columns dialog box, 721
Customers table, 717, 719
data viewer grid, 723
data viewer toolbar, 720
event_file target, 719, 720
operating system data
Collector Set, 730
Data Collector Set, 733, 734
generate activity, 734
merging trace files, 735
Perfmon Data, 727–729
Properties dialog box, 731, 732
trace file location, 732, 733
T-SQL command, 734
Windows Performance
Analyzer, 735, 736
WMI-Activity provider, 731
package, 700
predicates, 702
session (see Event session)
targets, 701
types and maps, 702, 704
Extensible Key Management
(EKM) module, 376
External fragmentation, 275, 276
F
Failover Clustered Instance (FCI)
active/active configuration, 466
node, 465, 466
Quorum, 469–471
three-plus node
configurations, 467–469
Filegroups
adding files, 183–186
backup and restore strategies, 181
expanding files, 186, 187
FILESTREAM
binary cache, 170
buffer cache, 170
container, 170
database properties dialog box, 171
drop-down box, 171, 172
FileTable, 175–177
folder hierarchy, 174, 175
T-SQL commands, 172
Windows cache, 170
log maintenance
Chapter6Log
Fragmentation, 195–197, 200, 202
Encryption hierarchy (cont.)
INDEX

log file count, 194, 195
log reuse waits, 200, 201
log size and VLFs number, 197
recovery model, 192
shrinking, 194, 195
structure, 192
sys.databases, 200
transaction log, 202
VLFs per GB, 198
memory-optimized
strategies, 182, 183
memory-optimized tables, 177–179
performance strategies, 180
primary file, 166
round-robin approach, 166–170
secondary files, 166
shrinking files, 187–190
storage tiering strategies, 181, 182
FILESTREAM
binary cache, 170
buffer cache, 170
container, 170
database properties dialog box, 171
drop-down box, 171, 172
FileTable, 175–177
folder hierarchy, 174, 175
T-SQL commands, 172
Windows cache, 170
Filtered index, 260, 261
FORCE keyword, 373
Fragmentation of index, 275, 276
detection of, 276–278
external fragmentation, 275, 276
internal fragmentation, 275
page split, 275
removal of, 278–280
Fully qualified domain name
(FQDN), 499
G
GETDATE() function, 217
Graphical user interface (GUI), 29, 230
GUI installation
Installation Center
Advanced tab, 36–38
Installation tab, 31, 32
Maintenance tab, 33–35
Options tab, 39
Planning tab, 30, 31
Resources tab, 35, 36
Tools tab, 34, 35
Stand-Alone Database Engine Instance
collation (see Collation)
Completion page, 70
Data Directories tab, 62
Distributed Replay Client page, 68
Distributed Replay Controller
page, 67
Feature Selection page, 45–48
FILESTREAM tab, 66
Global Rules page, 42
Install Rules page, 45
Install Setup Files page, 44
Instance Configuration
page, 48–50
License Terms page, 41
MaxDOP tab, 63, 64
Memory tab, 64, 65
Microsoft Update page, 43
Product Key page, 40
Product Updates page, 43
Ready to Install page, 68, 69
selecting service accounts, 50–53
Server Configuration tab, 60–62
Setup Role page, 45
TempDB tab, 62, 63
INDEX

H
Hardware requirements
storage
Disk Block Size, 16
file placement, 13, 14
SAN, 15
strategic minimum
requirements, 7
Hash collision, 267
Hash index, 234, 268
Heap, 246
Hekaton, 236
Heterogeneous operating systems
installing SQL server, in Docker
container
Chocolatey package, 116
creating database, 118
creating image file, 119–124
Docker file, 114
Docker image, 111
-d switch, 113
file instruction, 113, 114
Kubernetes, 125
MCR, 112
running Docker image, 112
Start.ps1, 116
installing SQL server, on Linux
adding sa password, 105
Bash script, 108–110
parameters, 105, 107
systemctl tool, 107
ufw, 107
versions, 101–103
High availability (HA)
AlwaysOn failover clustering
active/active configuration, 466
node, 465, 466
Quorum, 469–471
three-plus node
configurations, 467–469
cost of downtime, 462, 463
creation
database page selection, 495, 496
data synchronization page, 503
dialog box, 504–508
introduction page, 494
results page, 503
specify name page, 494, 495
synchronous commit
mode, 508
tabs, 496–503
validation page, 503
database creation, 486–492
vs. DR, 480–482
level of availability
calculation, 459, 460
percentage, 458
proactive maintenance, 461
SLAs & SLOs, 460
SQL server, 458
Linux groups
adding database, 512, 514
create certificates, 509, 510
create endpoints, 511
create group, 511
create Listener, 515
DAG, 515–517
grant permissions, 512
secondary replica, 512
RPO & RTO, 461, 462
SQL Server
configuration, 492–494
standby classifications, 463, 464
Hybrid Buffer Pool, 161–162
INDEX

I
Implicit transactions, 670
Indexes
clustered columnstore indexes, 263–265
clustered indexes, 245
CREATE CLUSTERED
INDEX, 249, 250
DROP INDEX, 252
primary key clustering, 248
tables with, 247
tables without, 246
WITH options, 250–252
columnstore indexes, 261–263
fragmentation, 275, 276
detection of, 276–278
external fragmentation, 275, 276
internal fragmentation, 275
page split, 275
removal of, 278–280
in-memory Indexes, 266
nonclustered hash index, 266–272
nonclustered indexes, 272
missing indexes, 273–276
nonclustered columnstore indexes, 265
nonclustered indexes, 253, 254
covering indexes, 254, 255
CREATE NONCLUSTERED INDEX,
255–260
DROP INDEX, 260
filtered index, 260, 261
partitioned indexes, 283, 284
statistics, 284–286
creation, 288
filtered statistics, 286, 287
updation, 289, 290
Index Scan operator, 222, 223
Initial data synchronization, 502, 580
In-memory Indexes, 266
nonclustered hash index, 266–272
nonclustered indexes, 272
In-Memory OLTP, 232
Instance configuration
MAXDOP, 136–138
Max Server Memory (MB), 138–141
Min Server Memory (MB), 138–141
ports
communication process, 147
database engine, 149
dynamic ports, 148
IP addresses tab, 151
port 445, 147
port 1433, 148
protocol tab, 150
static port, 151
processor affinity
affinity I/O mask, 131
affinity mask, 129–131
bit maps, 133, 134
NUMA node, 131
sp_configure, 128–130
system databases
Master, 153
Model, 154
MSDB, 153
mssqlsystemresource, 152
TempDB, 154–157
trace flags
DBCC TRACEOFF, 142–144
DBCC TRACEON, 141–143
startup parameters, 143
trace flag 3042, 145
trace flag 3226, 145
trace flag 3625, 146
Windows Server Core, 144
INDEX

Instance installation
/ACTION parameter, 75
command line
parameters, 78–80
/FEATURES parameter, 76
/IACCEPTSQLSERVERLICENSE
TERMS, 74
installation progress, 79
optional parameters, 85
product update, 89–91
smoke test, 80–83
troubleshooting
detail.txt, 83
log files, 84, 85
summary.txt, 83
SystemConfigurationCheck_
Report.htm file, 84
Instance-level security
AOAG, 335
fixed server roles, 334, 335
granting permissions, 340, 341
login creation
CHECK_POLICY, 339
Enforce Password Policy, 338
Securables tab, 338
Server Roles tab, 338
SSMS, 337
Status tab, 338
T-SQL, 339
User Mapping tab, 338
mixed-mode authentication
SA account, 333
security vulnerability, 333
New Server Role, 335, 336
Security tab, 334
Windows authentication
ownership chain, 333
security identifier, 333
SQLUsers group, 332
Windows user/group, 332
Integration services, 25
Internal fragmentation, 275
is_Advanced, 129
is_dynamic, 129
J
Jobs
database mail
configuration, 829, 832
advanced page, 839
Alert System page, 834
BACKUP DATABASE
command, 838
Browser service, 840
job creation, 836, 837
Logging Level, 833
notifications page, 842
operator creation, 835, 836
PowerShell script, 840
Profile page, 830
Public Profiles tab, 832
schedules page, 841
Select Configuration Task
page, 830
steps page, 840
system parameters
configuration, 834
monitoring and maintenance, 842
alerts, 845–847
execution, 843, 844
job history, 844
K
Kubernetes, 125
INDEX

L
Local distributor, 578
Locally attached storage
RAID 0 volume, 8
RAID 1 volume, 9
RAID 5 volume, 10
RAID 10 volume, 12
Locking
compatibility, 666, 667
granularity, 659, 660
LOCK_ESCALATION, 661
observation, 691–695
online maintenance, 661–666
partitioning, 667
Lock types, 666
intent, 667
Log shipping
DR (see Disaster recovery (DR))
failing over, 557
monitoring
Log Shipping Primary Server
Alert, 564
Log Shipping Secondary Server
Alert, 565
Response tab, 565, 566
Server Agent Alerts, 564
sp_help_log_shipping_monitor, 566
switching roles
Disabling Log Shipping Jobs, 558, 559
reconfigure log shipping, 559–563
reconfigure monitoring, 563
M
Machine Learning Server, 24
Managed service accounts, 52
Master database, 153
Master data services, 27
Master server (MSX), 848, 853
Memory-optimized tables
creation, 234–237
disk-based tables, 236–240
DLL, 241–244
durability, 233
In-Memory OLTP, 232
migration, 240, 241
Merge replication, 581, 582
Metadata
buffer cache usage, 630–632
capacity management
file details, 635
powerShell, 637, 638
sys.dm_db_file_space_usage, 632
sys.dm_io_virtual_file_stats, 633
sys.master_files, 634, 635
xp_fixeddrives, 636
catalog view, 624
DATALENGTH, 626
definition, 623
driven automation, rebuild
indexes, 656, 657
dynamic management view, 624–626
information schema view, 624
perfmon counters
Average Latch Wait Time (ms), 642
sys.dm_os_performance_
counters, 639
Type 65792, 640
Type 272696320, 641
Type 272696576, 641
Type 537003264, 641
Type 1073874176, 642, 643
types, 640
registry values, 627, 628
server and instance level, 626
service details, 628, 629
INDEX

waits
reset, 645
states, 645
sys.dm_os_wait_stats, 645
tasks, 646–648
types, 644
Metadata database
sys.dm_db_page_info
columns, 648–651
parameters, 648
Microsoft Assessment and
Planning (MAP), 4
toolkit, 35
Microsoft Container Registry (MCR), 112
Microsoft Cryptographic API
(MSCAPI), 376
Missing indexes, 273–276
Mixed-mode authentication, 61
Model database, 154
MSDB database, 153
mssqlsystemresource database, 152
Multiserver jobs
master jobs
Multiserver job conversion, 856
sp_add_jobserver parameters, 855
sp_delete_jobserver parameters, 855
sp_msx_enlist stored procedure, 855
sp_update_job procedure, 854
sp_update_jobstep procedure, 854
MSX and TSX servers
Login Credentials page, 853
Master Server operator page, 851
MsxEncryptChannel
Options, 848, 849
proxy creation, 850
Registry updation, 849
Target Servers page, 852
target servers
Download Instructions tab, 859
Force Defection button, 857
Post Instructions button, 857
Start Job, TARGETSERVER1, 858
synchronize clocks, 857
Target Server Status tab, 856
N
New technology file system
(NTFS), 16, 369
Nonclustered columnstore indexes, 265
Nonclustered hash index, 266–272
Nonclustered indexes, 253, 254, 272
covering indexes, 254, 255
CREATE NONCLUSTERED
INDEX, 255–260
DROP INDEX, 260
filtered index, 260, 261
Non-uniform memory access
(NUMA) node, 131
O
OBJECT_ID() function, 626
Object-level security
column-level permissions, 352
OBJECT phrase, 351
Operating systems
configuration
assigning user rights (see User
rights assignments)
Background Service, 19, 20
gold build, 18
power plan, 18
EOL, 17
Over-subscribed processors, 131
Metadata (cont.)
INDEX

P
Page split, 275
Partitioned indexes, 283, 284
$PARTITION function, 216, 217
Partitions
concepts
function, 207
hierarchy, 208
index alignment, 207, 208
partitioning Key, 206, 207
scheme, 207
definition, 205
elimination, 221, 222
implementation
existing table, 211–215
new table creation, 210, 211
object creation, 209, 210
$PARTITION function, 216, 217
structure, 206
Piecemeal restore, 181, 451–454
Policy-based management (PBM)
central management server, 866–874, 886
enterprise management, 887–889
import and export, 886, 887
policy evaluation with PowerShell,
889, 890
condition, 864
description, 863
evaluation modes, 865
On Change:Log Only, 865
On Change:Prevent, 866
On Demand, 865
On Schedule, 865
supported execution types, 866
ExecuteSQL() parameters, 884
ExecuteWQL() parameters, 884, 885
facets, 863
policy, 865
simple static policy, 874
BrokenPolicy Database creation, 878
Description page, 877, 878
ExcludeSystemDatabases condition
creation, 877
New Condition dialog box creation,
875, 876
New Policy dialog box creation, 875
prevents unwanted activity, 880–883
target, 864
Primary role property, 506
Principle of least privilege, 51, 595
Publication Access List (PAL), 605–607
Q
QUERY_CAPTURE_POLICY options, 744
Query Store
configuration, 744
database, 739–741
issues
plan, 757
Regressed Query report, 756
stored procedures, 757
properties tab, 745
queries, 746
SET options, 742, 743
SSMS (see SSMS reports)
T-SQL objects
catalog views, 751, 752
wait statistics mappings, 752–755
R
RECONFIGURE command, 128, 129
RECONFIGURE WITH OVERRIDE
command, 128
INDEX

Recovery model, 192
Recovery point objective
(RPO), 461, 496, 536
Recovery time objective
(RTO), 461, 500
Redundant array of inexpensive disks
(RAID), 8
Remote distributor, 578
Repair option, 33
Replication
components, 579–581
merge, 581, 582
snapshot, 579, 580
transactional (see Transactional
replication)
@RequiredSnapshots parameter, 652
Resource Governor, see Resource pool
Resource pool
arguments, 897, 899
business rules, 905
classifier function, 896
creation, 906, 907
instance preparation, 904
testing, 908
configuration, 894
creation, 899
definition, 893
DMVs
binary mask, 918
CPU usage, 917
CPU utilization, 913
disk latency, 920
Performance Monitor, 909
resetting statistics, 917
resource pool stats, 911
sales user and manger login, 912
sys.dm_resource_governor_
resource_pools, 913
sys.dm_resource_governor_
resource_pool_volumes, 918
sys.dm_resource_governor_
workload_groups, 915
workload group stats, 910
IOPS limits, 898
memory limit, 896
properties dialog, 896
sys.dm_resource_governor_resource_
pool_affinity, 917
workload groups, 895, 900–902
Restore database
filegroups, 446–448
page, 450–453
piecemeal, 452–455
Point in Time, 445–449
SQL Server Management Studio
backup timeline page, 432, 433
file page, 433, 434
General page, 431, 432
Options page, 434, 435
Tail-Log, 435
T-SQL
error management options, 439
media set options, 438
miscellaneous options, 439, 440
restore arguments, 437
restore options, 436
WITH options, 437, 438
Row versioning, 677
S
Serial advanced technology attachment
(SATA), 15
Server Audit
AUDIT_CHANGE_GROUP, 364
Audit-ProSQLAdmin, 355
INDEX

creation, 353–356
database audit specification, 359–363
enabling and invoking, 358, 359
FOR SERVER AUDIT clause, 357
SensitiveData table, 365
SERVER_ROLE_MEMBER_CHANGE_
GROUP, 357, 358
target, 352
T-SQL, 356
Windows Application log, 352
Service-level agreements (SLAs), 460
Session Timeout property, 506
Snapshot replication, 579, 580
Solid state drives (SSDs), 14
sp_configure, 128–130
ALTER SERVER CONFIGURATION, 136
MAXDOP, 138
Max Server Memory, 141
Min Server Memory, 141
processor affinity, 133
sp_estimate_data_compression_savings,
228, 229
Split brain, 469
SQL Server Agent, 809
alerts, 814
SQL Server events, 814
SQL Server performance
conditions, 814
and responses, 809
WMI events, 814
jobs, 812, 813
advanced page, 823
credentials creation, 825, 826
database mail configuration, 829
environment preparation, 818–820
Frequency and Duration
sections, 829
general page, 821, 822
New Job Schedule box, 824
notifications page, 825
proxy creation, 827
removing old backups, 822
schedule creation, 828
SCOM, 824
SQLUser and WinUser, 817
steps page, 821
operator, 811
schedules, 810, 811
security
database roles, 815
proxy accounts, 817
SQL Server Analysis Services (SSAS), 24, 149
SQL Server Installation Center, 29
SQL Server Integration Services (SSIS), 130
SQL Server Management Studio (SSMS)
distributor/publisher
Add Filter, 592, 593
Agent Security page, 594, 595
Article Properties, 591, 592
Articles page, 589, 590
Distribution Database page, 585, 586
distributor page, 582, 583
Filter Table Rows page, 591, 592
Publication Database page, 587, 588
Publication Type page, 588, 589
Publishers page, 586, 587
Snapshot Agent page, 593, 594
Snapshot Agent Security, 596, 597
Snapshot Folder page, 584, 585
SQL Server Agent Start page, 583, 584
Wizard Actions page, 596
PAL, 605–607
subscriber
Distribution Agent, 603–606
Distribution Agent Location
page, 597, 598
INDEX

General page, 599, 600
Initialize Subscriptions
page, 604, 605
Publication page, 597
Subscribers page, 598, 599
Synchronization Schedule page, 604
Wizard Actions page, 605
SQL Server Memory Pool, 139
SQL Server security model
database-level security
built-in database roles, 341, 342
contained databases, 348–351
schemas, 345–348
Securables tab, 343
T-SQL, 344
DBAs, 329
hierarchy
database users, 331
domain user/group, 330
principal, 330, 331
securables, 329
instance-level (see Instance-level
security)
object-level security
column-level permissions, 352
OBJECT phrase, 351
security reports
SQL Data Discovery and
Classification, 365–367
Vulnerability Assessment, 367, 369
Server Audit
AUDIT_CHANGE_GROUP, 364
Audit-ProSQLAdmin, 355
creation, 353–356
database audit
specification, 359–363
enabling and invoking, 358, 359
FOR SERVER AUDIT clause, 357
SERVER_ROLE_MEMBER_
CHANGE_GROUP, 357, 358
SensitiveData table, 365
target, 352
T-SQL, 356
Windows Application log, 352
SSMS reports, 747
CPU drill-through, 751
details, 748
Overall Resource
Consumption, 747, 748
Query Wait Statistics, 750
standard grid view, 749
Top Resource Consumers, 749, 750
Standby classifications, 463, 464
Statistics of index, 284–286
creation, 288
filtered statistics, 286, 287
updation, 289, 290
SWITCH function, 221
Synchronous Commit Mode, 508
sys.fn_dump_dblog(), 443
sys.fn_PageResCracker, 648
systemctl tool, 107
T
Tables
compression
columnstore, 227
data compression wizard, 230, 231
dictionary compression, 226
maintenance, 231, 232
page structure, 226, 227
prefix compression, 225
row compression, 224, 225
SQL Server Management Studio
(SSMS) (cont.)
INDEX

sp_estimate_data_compression_
savings, 228, 229
structure, 223
tables and partitions, 230, 231
variable-length metadata, 223
memory-optimized tables (see
Memory-optimized tables)
partition
definition, 205
elimination, 221, 222
existing table, 211–215
function, 207
hierarchy, 208
implementing sliding
windows, 218–221
index alignment, 207, 208
new table creation, 210, 211
object creation, 209, 210
$PARTITION function, 216, 217
partitioning key, 206, 207
scheme, 207
structure, 206
Target servers (TSX), 848
TDE, see Transparent Data
Encryption (TDE)
TempDB database, 154–157
Tipping point, 254
Transactional replication
definition, 580
peer-to-peer, 581
uses, 580
working, 580
Transactions
atomic transaction, 671–674
consistent property, 674
in-memory OLTP
cross-container, 684, 685
Read Committed isolation, 682
Read Committed Snapshot
isolation, 682
Repeatable Read, 683
retry logic, 686
Serializable, 683
snapshot isolation, 683
isolation
durable, 679–681
dirty read, 675
nonrepeatable read, 675
optimistic levels, 677–679
pessimistic level, 676, 677
phantom read, 676
observation, 687–691
Save point, 671–673
Transmission Control Protocol (TCP), 498
Transparent Data Encryption (TDE), 377
Backup certificate, 384
FILESTREAM filegroup, 377
implementation, 378
Database Encryption key, 384
Database Master Key selection,
380–383
database progress, 379
In-Memory filegroup, 377
restore and encrypted database, 385
Server Certificate restore
database, 385
TempDB, 378
Trusted Platform Module (TPM), 394
U
Uncomplicated Firewall (ufw), 107
User rights assignments
instant file, 19, 20
locking pages, 20
SQL audit, 21, 22
INDEX

V
Very large databases (VLDBs), 323
DBCC CHECKALLOC, 324
DBCC CHECKDB with physical only
option, 324
secondary server, 325
split work load, 324
Virtual accounts, 52
Virtual machines (VMs), 472
Vulnerability assessment, 367, 369
W, X, Y, Z
wget command, 102
whole-value substitution attack, 389
Windows Authentication
model, 837
Windows Server
Core Installation
configuration file
auto-install
script, 97, 98
PowerShell, 98, 100
PROSQLADMINCONF2, 94, 95
SQLAutoInstall.ps1, 96
SQLPROSQLADMIN
CONF1, 92, 93
instance (see Instance
Installation)
NET Framework 3.5, 73
INDEX