| About the Author |
|
xix | |
| About the Technical Reviewer |
|
xxi | |
| Acknowledgments |
|
xxiii | |
| Introduction |
|
xxv | |
|
Part I Installation and Configuration |
|
|
1 | (162) |
|
Chapter 1 Planning the Deployment |
|
|
3 | (26) |
|
Editions and License Models |
|
|
3 | (3) |
|
|
|
6 | (11) |
|
Specifying Strategic Minimum Requirements |
|
|
7 | (1) |
|
|
|
7 | (10) |
|
Operating Systems Considerations |
|
|
17 | (1) |
|
Configuring the Operating System |
|
|
18 | (4) |
|
|
|
18 | (1) |
|
Optimizing for Background Services |
|
|
18 | (1) |
|
|
|
19 | (3) |
|
|
|
22 | (5) |
|
|
|
23 | (1) |
|
|
|
24 | (1) |
|
|
|
24 | (1) |
|
|
|
25 | (1) |
|
Client Connectivity Tools |
|
|
25 | (1) |
|
|
|
25 | (1) |
|
Client Tools Backward Compatibility |
|
|
25 | (1) |
|
|
|
26 | (1) |
|
Distributed Replay Controller |
|
|
26 | (1) |
|
Distributed Replay Client |
|
|
26 | (1) |
|
SQL Client Connectivity SDK |
|
|
26 | (1) |
|
|
|
27 | (1) |
|
|
|
27 | (2) |
|
Chapter 2 GUI Installation |
|
|
29 | (44) |
|
|
|
29 | (10) |
|
|
|
29 | (2) |
|
|
|
31 | (1) |
|
|
|
32 | (2) |
|
|
|
34 | (1) |
|
|
|
35 | (1) |
|
|
|
36 | (3) |
|
|
|
39 | (1) |
|
Installing a Stand-Alone Database Engine Instance |
|
|
39 | (31) |
|
|
|
40 | (5) |
|
The Feature Selection Page |
|
|
45 | (3) |
|
The Instance Configuration Page |
|
|
48 | (2) |
|
Selecting Service Accounts |
|
|
50 | (3) |
|
|
|
53 | (6) |
|
Provisioning Instance Security |
|
|
59 | (2) |
|
|
|
61 | (6) |
|
Configuring Distributed Replay |
|
|
67 | (1) |
|
Completing the Installation |
|
|
68 | (2) |
|
|
|
70 | (3) |
|
Chapter 3 Server Core Installation |
|
|
73 | (28) |
|
|
|
73 | (18) |
|
|
|
74 | (3) |
|
|
|
77 | (1) |
|
|
|
77 | (2) |
|
|
|
79 | (3) |
|
Troubleshooting the Installation |
|
|
82 | (3) |
|
|
|
85 | (4) |
|
|
|
89 | (2) |
|
|
|
91 | (4) |
|
Automatic Installation Routines |
|
|
95 | (5) |
|
Enhancing the Installation Routine |
|
|
96 | (2) |
|
|
|
98 | (2) |
|
|
|
100 | (1) |
|
Chapter 4 Installation on Heterogeneous Operating Systems |
|
|
101 | (26) |
|
Installing SQL Server on Linux |
|
|
101 | (10) |
|
Installing SQL Server Manually |
|
|
102 | (3) |
|
|
|
105 | (3) |
|
|
|
108 | (3) |
|
Installing SQL Server in a Docker Container |
|
|
111 | (14) |
|
Running a Microsoft-Supplied Docker Image |
|
|
111 | (2) |
|
Creating a Simple Docker Image for SQL Server |
|
|
113 | (6) |
|
Creating a Configurable Docker Image for SQL Server |
|
|
119 | (6) |
|
|
|
125 | (1) |
|
|
|
125 | (2) |
|
Chapter 5 Configuring the Instance |
|
|
127 | (36) |
|
|
|
127 | (19) |
|
|
|
127 | (2) |
|
Processor and Memory Configuration |
|
|
129 | (12) |
|
|
|
141 | (5) |
|
|
|
146 | (6) |
|
|
|
146 | (2) |
|
Ports Required by SQL Server |
|
|
148 | (1) |
|
Configuring the Port That the Instance Will Listen On |
|
|
149 | (3) |
|
|
|
152 | (7) |
|
Mssqlsystemresource (Resource) |
|
|
152 | (1) |
|
|
|
153 | (1) |
|
|
|
153 | (1) |
|
|
|
154 | (1) |
|
|
|
154 | (5) |
|
|
|
159 | (2) |
|
|
|
161 | (1) |
|
|
|
162 | (1) |
|
Part II Database Administration |
|
|
163 | (164) |
|
Chapter 6 Database Configuration |
|
|
165 | (40) |
|
|
|
165 | (18) |
|
|
|
166 | (4) |
|
|
|
170 | (7) |
|
Memory-Optimized Filegroups |
|
|
177 | (3) |
|
Strategies for Structured Filegroups |
|
|
180 | (2) |
|
Strategies for Memory-Optimized Filegroups |
|
|
182 | (1) |
|
File and Filegroup Maintenance |
|
|
183 | (7) |
|
|
|
183 | (3) |
|
|
|
186 | (1) |
|
|
|
187 | (3) |
|
Database Scoped Configurations |
|
|
190 | (1) |
|
|
|
191 | (11) |
|
|
|
192 | (1) |
|
|
|
193 | (1) |
|
|
|
194 | (1) |
|
|
|
195 | (7) |
|
|
|
202 | (3) |
|
Chapter 7 Table Optimizations |
|
|
205 | (40) |
|
|
|
205 | (18) |
|
|
|
206 | (3) |
|
Implementing Partitioning |
|
|
209 | (7) |
|
Monitoring Partitioned Tables |
|
|
216 | (2) |
|
|
|
218 | (3) |
|
|
|
221 | (2) |
|
|
|
223 | (9) |
|
|
|
224 | (1) |
|
|
|
225 | (2) |
|
|
|
227 | (1) |
|
|
|
228 | (4) |
|
|
|
232 | (12) |
|
|
|
233 | (1) |
|
Creating and Managing Memory-Optimized Tables |
|
|
233 | (3) |
|
|
|
236 | (4) |
|
Table Memory Optimization Advisor |
|
|
240 | (1) |
|
Natively Compiled Objects |
|
|
241 | (3) |
|
|
|
244 | (1) |
|
Chapter 8 Indexes and Statistics |
|
|
245 | (48) |
|
|
|
245 | (8) |
|
Tables Without a Clustered Index |
|
|
246 | (1) |
|
Tables with a Clustered Index |
|
|
247 | (1) |
|
Clustering the Primary Key |
|
|
248 | (1) |
|
Administering Clustered Indexes |
|
|
249 | (4) |
|
|
|
253 | (8) |
|
|
|
254 | (1) |
|
Administering Nonclustered Indexes |
|
|
255 | (5) |
|
|
|
260 | (1) |
|
Indexes for Specialized Application |
|
|
261 | (11) |
|
|
|
261 | (2) |
|
Clustered Columnstore Indexes |
|
|
263 | (2) |
|
Nonclustered Columnstore Indexes |
|
|
265 | (1) |
|
|
|
266 | (6) |
|
|
|
272 | (12) |
|
|
|
273 | (2) |
|
|
|
275 | (5) |
|
Resumable Index Operations |
|
|
280 | (3) |
|
|
|
283 | (1) |
|
|
|
284 | (3) |
|
|
|
286 | (1) |
|
|
|
287 | (1) |
|
|
|
287 | (3) |
|
|
|
290 | (3) |
|
Chapter 9 Database Consistency |
|
|
293 | (34) |
|
|
|
293 | (15) |
|
Understand Consistency Errors |
|
|
293 | (2) |
|
Detecting Consistency Errors |
|
|
295 | (9) |
|
System Database Corruption |
|
|
304 | (4) |
|
|
|
308 | (15) |
|
|
|
308 | (5) |
|
|
|
313 | (2) |
|
|
|
315 | (3) |
|
Other DBCC Commands for Corruption |
|
|
318 | (5) |
|
Consistency Checks on VLDBs |
|
|
323 | (2) |
|
DBCC CHECKDB with PHYSICALJMLY |
|
|
324 | (1) |
|
Backing Up WITH CHECKSUM and DBCC CHECKALLOC |
|
|
324 | (1) |
|
|
|
324 | (1) |
|
Offloading to a Secondary Server |
|
|
325 | (1) |
|
|
|
325 | (2) |
|
Part III Security, Resilience, and Scaling Workloads |
|
|
327 | (294) |
|
Chapter 10 SQL Server Security Model |
|
|
329 | (42) |
|
|
|
329 | (3) |
|
Implementing Instance-Level Security |
|
|
332 | (9) |
|
|
|
334 | (3) |
|
|
|
337 | (3) |
|
|
|
340 | (1) |
|
Implementing Database-Level Security |
|
|
341 | (10) |
|
|
|
341 | (4) |
|
|
|
345 | (3) |
|
Creating and Managing Contained Users |
|
|
348 | (3) |
|
Implementing Object-Level Security |
|
|
351 | (1) |
|
|
|
352 | (13) |
|
|
|
353 | (3) |
|
Creating a Server Audit Specification |
|
|
356 | (2) |
|
Enabling and Invoking Audits |
|
|
358 | (1) |
|
Database Audit Specifications |
|
|
359 | (5) |
|
|
|
364 | (1) |
|
|
|
365 | (4) |
|
SQL Data Discovery and Classification |
|
|
365 | (2) |
|
|
|
367 | (2) |
|
|
|
369 | (2) |
|
|
|
371 | (38) |
|
|
|
371 | (6) |
|
|
|
371 | (1) |
|
SQL Server Encryption Concepts |
|
|
372 | (5) |
|
Transparent Data Encryption |
|
|
377 | (9) |
|
|
|
378 | (6) |
|
|
|
384 | (2) |
|
Managing Cell-Level Encryption |
|
|
386 | (6) |
|
|
|
391 | (1) |
|
|
|
392 | (15) |
|
Implementing Always Encrypted |
|
|
394 | (9) |
|
|
|
403 | (4) |
|
|
|
407 | (2) |
|
Chapter 12 Backups and Restores |
|
|
409 | (48) |
|
|
|
409 | (9) |
|
|
|
409 | (3) |
|
Changing the Recovery Model |
|
|
412 | (3) |
|
|
|
415 | (1) |
|
|
|
416 | (2) |
|
|
|
418 | (3) |
|
|
|
419 | (1) |
|
Full and Transaction Log Backups |
|
|
419 | (1) |
|
Full, Differential, and Transaction Log Backups |
|
|
420 | (1) |
|
|
|
420 | (1) |
|
|
|
421 | (1) |
|
|
|
421 | (10) |
|
Backing Up in SQL Server Management Studio |
|
|
421 | (3) |
|
|
|
424 | (7) |
|
|
|
431 | (10) |
|
Restoring in SQL Server Management Studio |
|
|
431 | (5) |
|
|
|
436 | (5) |
|
Restoring to a Point in Time |
|
|
441 | (4) |
|
Restoring Files and Pages |
|
|
445 | (6) |
|
|
|
446 | (2) |
|
|
|
448 | (3) |
|
|
|
451 | (3) |
|
|
|
454 | (3) |
|
Chapter 13 High Availability and Disaster Recovery Concepts |
|
|
457 | (28) |
|
|
|
458 | (6) |
|
|
|
458 | (3) |
|
Recovery Point Objective and Recovery Time Objective |
|
|
461 | (1) |
|
|
|
462 | (1) |
|
Classification of Standby Servers |
|
|
463 | (1) |
|
High Availability and Recovery Technologies |
|
|
464 | (19) |
|
AlwaysOn Failover Clustering |
|
|
464 | (7) |
|
AlwaysOn Availability Groups |
|
|
471 | (6) |
|
|
|
477 | (3) |
|
|
|
480 | (3) |
|
|
|
483 | (2) |
|
Chapter 14 Implementing AlwaysOn Availability Groups |
|
|
485 | (48) |
|
Implementing AlwaysOn Availability Groups |
|
|
486 | (22) |
|
|
|
492 | (2) |
|
Creating the Availability Group |
|
|
494 | (14) |
|
Availability Groups on Linux |
|
|
508 | (7) |
|
Distributed Availability Groups |
|
|
515 | (3) |
|
Managing AlwaysOn Availability Groups |
|
|
518 | (12) |
|
|
|
518 | (6) |
|
Synchronizing Uncontained Objects |
|
|
524 | (1) |
|
|
|
525 | (3) |
|
Other Administrative Considerations |
|
|
528 | (2) |
|
|
|
530 | (3) |
|
Chapter 15 Implementing Log Shipping |
|
|
533 | (36) |
|
Implementing Log Shipping for DR |
|
|
533 | (24) |
|
|
|
536 | (10) |
|
|
|
546 | (11) |
|
|
|
557 | (9) |
|
Failing Over Log Shipping |
|
|
557 | (1) |
|
|
|
558 | (6) |
|
|
|
564 | (2) |
|
|
|
566 | (3) |
|
Chapter 16 Scaling Workloads |
|
|
569 | (52) |
|
|
|
569 | (8) |
|
Implementing Database Snapshots |
|
|
572 | (4) |
|
Recovering Data from a Snapshot |
|
|
576 | (1) |
|
|
|
577 | (30) |
|
|
|
577 | (2) |
|
|
|
579 | (3) |
|
Implementing Transactional Replication |
|
|
582 | (25) |
|
Adding AlwaysOn Readable Secondary Replicas |
|
|
607 | (11) |
|
Benefits and Considerations |
|
|
608 | (1) |
|
Implementing Readable Secondaries |
|
|
608 | (10) |
|
|
|
618 | (3) |
|
Part IV Performance and Maintenance |
|
|
621 | (302) |
|
Chapter 17 SQL Server Metadata |
|
|
623 | (36) |
|
Introducing Metadata Objects |
|
|
623 | (3) |
|
Server-Level and Instance-Level Metadata |
|
|
626 | (5) |
|
|
|
627 | (1) |
|
|
|
628 | (1) |
|
Analyzing Buffer Cache Usage |
|
|
629 | (2) |
|
Metadata for Capacity Planning |
|
|
631 | (8) |
|
|
|
631 | (4) |
|
Using File Stats for Capacity Analysis |
|
|
635 | (4) |
|
Metadata for Troubleshooting and Performance Tuning |
|
|
639 | (8) |
|
Retrieving Perfmon Counters |
|
|
639 | (5) |
|
|
|
644 | (3) |
|
|
|
647 | (5) |
|
Metadata-Driven Automation |
|
|
652 | (5) |
|
Dynamically Cycling Database Snapshots |
|
|
652 | (3) |
|
Rebuilding Only Fragmented Indexes |
|
|
655 | (2) |
|
|
|
657 | (2) |
|
Chapter 18 Locking and Blocking |
|
|
659 | (40) |
|
|
|
659 | (9) |
|
|
|
659 | (2) |
|
Locking Behaviors for Online Maintenance |
|
|
661 | (5) |
|
|
|
666 | (1) |
|
|
|
667 | (1) |
|
|
|
668 | (2) |
|
|
|
668 | (2) |
|
|
|
670 | (1) |
|
Understanding Transactions |
|
|
670 | (11) |
|
|
|
671 | (10) |
|
Transaction with In-Memory OLTP |
|
|
681 | (6) |
|
|
|
682 | (2) |
|
Cross-Container Transactions |
|
|
684 | (2) |
|
|
|
686 | (1) |
|
Observing Transactions, Locks, and Deadlocks |
|
|
687 | (9) |
|
|
|
687 | (4) |
|
Observing Locks and Contention |
|
|
691 | (4) |
|
|
|
695 | (1) |
|
|
|
696 | (3) |
|
Chapter 19 Extended Events |
|
|
699 | (40) |
|
|
|
699 | (5) |
|
|
|
700 | (1) |
|
|
|
700 | (1) |
|
|
|
701 | (1) |
|
|
|
702 | (1) |
|
|
|
702 | (1) |
|
|
|
702 | (2) |
|
|
|
704 | (1) |
|
Creating an Event Session |
|
|
704 | (13) |
|
Using the New Session Dialog Box |
|
|
707 | (6) |
|
|
|
713 | (4) |
|
Viewing the Collected Data |
|
|
717 | (10) |
|
Analyzing Data with Data Viewer |
|
|
717 | (6) |
|
Analyzing Data with T-SQL |
|
|
723 | (4) |
|
Correlating Extended Events with Operating System Data |
|
|
727 | (9) |
|
Correlating Events with Perfmon Data |
|
|
727 | (2) |
|
Integrating Event Sessions with Operating System-Level Events |
|
|
729 | (7) |
|
|
|
736 | (3) |
|
|
|
739 | (20) |
|
Enabling and Configuring Query Store |
|
|
739 | (7) |
|
Working with Query Store Data |
|
|
746 | (10) |
|
|
|
747 | (4) |
|
Query Store T-SQL Objects |
|
|
751 | (5) |
|
Resolving Issues with Query Store |
|
|
756 | (1) |
|
|
|
757 | (2) |
|
Chapter 21 Distributed Replay |
|
|
759 | (50) |
|
Distributed Replay Concepts |
|
|
760 | (1) |
|
Distributed Replay Components |
|
|
760 | (1) |
|
Distributed Replay Architecture |
|
|
761 | (1) |
|
Configuring the Environment |
|
|
761 | (7) |
|
Configuring the Controller |
|
|
762 | (1) |
|
|
|
763 | (2) |
|
|
|
765 | (3) |
|
Working with Distributed Replay |
|
|
768 | (39) |
|
|
|
772 | (1) |
|
|
|
773 | (30) |
|
|
|
803 | (4) |
|
|
|
807 | (2) |
|
Chapter 22 Automating Maintenance Routines |
|
|
809 | (54) |
|
|
|
809 | (39) |
|
SQL Server Agent Concepts |
|
|
810 | (4) |
|
SQL Server Agent Security |
|
|
814 | (3) |
|
Creating SQL Server Agent Jobs |
|
|
817 | (25) |
|
Monitoring and Managing Jobs |
|
|
842 | (3) |
|
|
|
845 | (3) |
|
|
|
848 | (12) |
|
Configuring the MSX and TSX Servers |
|
|
848 | (6) |
|
|
|
854 | (2) |
|
|
|
856 | (4) |
|
|
|
860 | (3) |
|
Chapter 23 Policy-Based Management |
|
|
863 | (30) |
|
|
|
863 | (3) |
|
|
|
863 | (1) |
|
|
|
864 | (1) |
|
|
|
864 | (1) |
|
|
|
865 | (1) |
|
|
|
865 | (1) |
|
Central Management Servers |
|
|
866 | (8) |
|
|
|
874 | (11) |
|
|
|
874 | (9) |
|
Creating an Advanced Policy |
|
|
883 | (2) |
|
|
|
885 | (5) |
|
Importing and Exporting Policies |
|
|
886 | (1) |
|
Enterprise Management with Policies |
|
|
887 | (2) |
|
Evaluating Policies with PowerShell |
|
|
889 | (1) |
|
|
|
890 | (3) |
|
Chapter 24 Resource Governor |
|
|
893 | (30) |
|
Resource Governor Concepts |
|
|
893 | (3) |
|
|
|
893 | (2) |
|
|
|
895 | (1) |
|
|
|
895 | (1) |
|
Implementing Resource Governor |
|
|
896 | (13) |
|
|
|
896 | (4) |
|
|
|
900 | (3) |
|
Creating a Classifier Function |
|
|
903 | (5) |
|
Testing the Classifier Function |
|
|
908 | (1) |
|
Monitoring Resource Governor |
|
|
909 | (12) |
|
Monitoring with Performance Monitor |
|
|
909 | (4) |
|
|
|
913 | (8) |
|
|
|
921 | (2) |
| Index |
|
923 | |
Lisainfo e-raamatute kohta