About the Authors |
|
xxi | |
Acknowledgments |
|
xxv | |
Foreword |
|
xxix | |
Introduction |
|
xxxi | |
Who this book is for |
|
xxxi | |
How this book is organized |
|
xxxi | |
Conventions |
|
xxxiv | |
Text conventions |
|
xxxiv | |
Book features |
|
xxxv | |
Errata, updates, and book support |
|
xxxv | |
|
|
|
Chapter 1 Get started with SQL Server tools |
|
|
1 | (54) |
|
|
1 | (1) |
|
Install SQL Server with the Installation Center |
|
|
2 | (1) |
|
Plan before an upgrade or installation |
|
|
3 | (3) |
|
Install or upgrade SQL Server |
|
|
6 | (1) |
|
Tools and services installed with the Database Engine |
|
|
7 | (1) |
|
Machine Learning Services |
|
|
7 | (1) |
|
|
8 | (1) |
|
|
9 | (4) |
|
SQL Server Configuration Manager |
|
|
13 | (1) |
|
Performance and reliability monitoring tools |
|
|
14 | (1) |
|
Database Engine Tuning Advisor |
|
|
14 | (1) |
|
|
14 | (2) |
|
Management Data Warehouse |
|
|
16 | (1) |
|
SQL Server Reporting Services (SSRS) |
|
|
16 | (1) |
|
|
16 | (2) |
|
Report Server Configuration Manager |
|
|
18 | (1) |
|
SQL Server Management Studio (SSMS) |
|
|
18 | (1) |
|
|
19 | (1) |
|
Install SQL Server Management Studio |
|
|
19 | (1) |
|
Upgrade SQL Server Management Studio |
|
|
20 | (1) |
|
Features of SQL Server Management Studio |
|
|
20 | (7) |
|
Additional tools in SQL Server Management Studio |
|
|
27 | (4) |
|
|
31 | (1) |
|
|
32 | (5) |
|
|
37 | (3) |
|
|
40 | (1) |
|
|
41 | (2) |
|
Highlighted features in Azure Data Studio |
|
|
43 | (4) |
|
Notebooks in Azure Data Studio |
|
|
47 | (1) |
|
|
48 | (1) |
|
SQL Server Integration Services |
|
|
48 | (3) |
|
SQL Server on Azure Arc-enabled servers |
|
|
51 | (1) |
|
|
52 | (1) |
|
Discontinued and deprecated features |
|
|
53 | (2) |
|
Chapter 2 Introduction to database server components |
|
|
55 | (38) |
|
|
56 | (1) |
|
Understand the working set |
|
|
56 | (1) |
|
Cache data in the buffer pool |
|
|
56 | (1) |
|
Cached plans in the procedure cache |
|
|
57 | (1) |
|
|
58 | (1) |
|
Editions and memory limits |
|
|
59 | (1) |
|
|
59 | (1) |
|
Simultaneous multithreading |
|
|
60 | (1) |
|
Non-uniform memory access |
|
|
61 | (2) |
|
Disable power saving everywhere |
|
|
63 | (1) |
|
|
63 | (1) |
|
|
64 | (1) |
|
Configure the storage layer |
|
|
65 | (5) |
|
Connect to SQL Server over the network |
|
|
70 | (1) |
|
|
71 | (1) |
|
Added complexity with Virtual Local Area Networks |
|
|
71 | (1) |
|
High-availability concepts |
|
|
72 | (1) |
|
|
73 | (1) |
|
|
74 | (1) |
|
|
74 | (3) |
|
The versatility of log shipping |
|
|
77 | (1) |
|
Always On availability groups |
|
|
78 | (4) |
|
|
82 | (1) |
|
Integrated Authentication and Active Directory |
|
|
82 | (3) |
|
|
85 | (2) |
|
Kerberos for Azure SQL Managed Instance |
|
|
87 | (1) |
|
Understand visualization and containers |
|
|
87 | (1) |
|
|
88 | (1) |
|
Provision resources for virtual consumers |
|
|
89 | (1) |
|
When processors are no longer processors |
|
|
90 | (2) |
|
The network is virtual, too |
|
|
92 | (1) |
|
Chapter 3 Design and implement an on-premises database infrastructure |
|
|
93 | (36) |
|
Introduction to SQL Server database architecture |
|
|
93 | (1) |
|
Data files and filegroups |
|
|
94 | (1) |
|
Group data pages with extents |
|
|
95 | (1) |
|
Contents and types of data pages |
|
|
96 | (2) |
|
Verify data pages by using a checksum |
|
|
98 | (1) |
|
Record changes in the transaction log |
|
|
99 | (1) |
|
Flush data to the storage subsystem with checkpoints |
|
|
100 | (1) |
|
Inside the transaction log file |
|
|
100 | (3) |
|
|
103 | (1) |
|
Types of database checkpoints |
|
|
103 | (2) |
|
|
105 | (1) |
|
MinLSN and the active log |
|
|
106 | (1) |
|
A faster recovery with accelerated database recovery |
|
|
107 | (1) |
|
|
108 | (1) |
|
|
109 | (1) |
|
Table and index compression |
|
|
109 | (3) |
|
|
112 | (1) |
|
Manage the temporary database |
|
|
113 | (1) |
|
Storage options for tempdb |
|
|
113 | (1) |
|
Recommended number of files |
|
|
114 | (1) |
|
|
115 | (1) |
|
Manage system usage with Resource Governor |
|
|
115 | (1) |
|
Configure the operating system page file |
|
|
116 | (1) |
|
Take advantage of logical processors with parallelism |
|
|
116 | (3) |
|
SQL Server memory settings |
|
|
119 | (3) |
|
Allocate CPU cores with an affinity mask |
|
|
122 | (2) |
|
File system configuration |
|
|
124 | (5) |
|
|
|
Chapter 4 Install and configure SQL Server instances and features |
|
|
129 | (66) |
|
What to do before installing SQL Server |
|
|
130 | (1) |
|
|
131 | (3) |
|
Important SQL Server volume settings |
|
|
134 | (1) |
|
|
135 | (2) |
|
|
137 | (1) |
|
Plan for multiple SQL Server instances |
|
|
138 | (1) |
|
Install SQL Server on Windows |
|
|
139 | (8) |
|
|
147 | (11) |
|
|
158 | (1) |
|
Automate SQL Server Setup with configuration files |
|
|
158 | (5) |
|
SQL Server on Azure virtual machines |
|
|
163 | (1) |
|
Post-installation server configuration |
|
|
163 | (1) |
|
Post-installation checklist |
|
|
163 | (13) |
|
Post-instaltation configuration of other features |
|
|
176 | (1) |
|
SSISDB initial configuration and setup |
|
|
176 | (1) |
|
SQL Server Reporting Services initial configuration and setup |
|
|
177 | (3) |
|
SQL Server Analysis Services initial configuration and setup |
|
|
180 | (1) |
|
Azure Synapse Link for SQL Server |
|
|
181 | (1) |
|
Container orchestration with Kubernetes |
|
|
182 | (2) |
|
Kubernetes support for SQL Server |
|
|
184 | (1) |
|
Deploy SQL Server in containers |
|
|
185 | (3) |
|
Get started with SQL Server on Kubernetes |
|
|
188 | (1) |
|
Deploy SQL Server on Kubernetes |
|
|
189 | (4) |
|
|
193 | (2) |
|
Chapter 5 Install and configure SQL Server on Linux |
|
|
195 | (20) |
|
|
195 | (1) |
|
Differences between Windows and Linux |
|
|
196 | (3) |
|
Linux distributions supported by SQL Server |
|
|
199 | (1) |
|
Considerations for installing SQL Server on Linux |
|
|
200 | (1) |
|
|
200 | (3) |
|
Install SQL Server on Linux |
|
|
203 | (1) |
|
Installation requirements |
|
|
204 | (1) |
|
Download and install packages |
|
|
204 | (2) |
|
Configure SQL Server on Linux |
|
|
206 | (1) |
|
Use mssql-conf to set up and configure SQL Server |
|
|
207 | (5) |
|
Caveats of SQL Server on Linux |
|
|
212 | (1) |
|
Missing SQL Server features on Linux |
|
|
212 | (3) |
|
Chapter 6 Provision and configure SQL Server databases |
|
|
215 | (34) |
|
Add databases to a SQL Server instance |
|
|
215 | (1) |
|
|
216 | (4) |
|
|
220 | (2) |
|
Upgrade database compatibility levels |
|
|
222 | (3) |
|
Other considerations for migrating databases |
|
|
225 | (4) |
|
Database-scoped configurations |
|
|
229 | (1) |
|
Database properties and options |
|
|
230 | (11) |
|
Move and remove databases |
|
|
241 | (1) |
|
Move user and system databases |
|
|
241 | (1) |
|
Move databases within instances |
|
|
242 | (5) |
|
|
247 | (2) |
|
Chapter 7 Understand table features |
|
|
249 | (76) |
|
|
249 | (1) |
|
General-purpose data types |
|
|
250 | (8) |
|
|
258 | (8) |
|
|
266 | (1) |
|
|
266 | (4) |
|
|
270 | (3) |
|
User-defined data types and user-defined types |
|
|
273 | (1) |
|
|
274 | (1) |
|
|
275 | (1) |
|
|
276 | (1) |
|
System-versioned temporal tables |
|
|
277 | (5) |
|
|
282 | (5) |
|
|
287 | (5) |
|
Store large binary objects |
|
|
292 | (1) |
|
|
293 | (2) |
|
|
295 | (1) |
|
|
295 | (1) |
|
Horizontally partitioned tables and indexes |
|
|
296 | (6) |
|
|
302 | (1) |
|
Capture modifications to data |
|
|
303 | (1) |
|
|
303 | (2) |
|
|
305 | (2) |
|
Query change tracking and change data capture |
|
|
307 | (1) |
|
Compare change tracking, change data capture, and temporal tables |
|
|
308 | (1) |
|
Benefits of PolyBase for external data sources and external tables |
|
|
309 | (1) |
|
Unified data platform features |
|
|
309 | (2) |
|
Install and configure PolyBase |
|
|
311 | (9) |
|
More PolyBase examples, architectures including S3 and URL queries |
|
|
320 | (2) |
|
PolyBase examples with a generic ODBC driver |
|
|
322 | (1) |
|
Azure bulk operations examples |
|
|
323 | (2) |
|
Part III SQL Server management |
|
|
|
Chapter 8 Maintain and monitor SQL Server |
|
|
325 | (68) |
|
Detect, prevent, and respond to database corruption |
|
|
325 | (1) |
|
Set the database's page verify option |
|
|
326 | (3) |
|
Repair database data file corruption |
|
|
329 | (1) |
|
Recover from database transaction log file corruption |
|
|
329 | (1) |
|
Database corruption in Azure SQL Database |
|
|
330 | (1) |
|
Maintain indexes and statistics |
|
|
330 | (1) |
|
Change the fill factor when beneficial |
|
|
331 | (2) |
|
Monitor index fragmentation |
|
|
333 | (1) |
|
|
334 | (8) |
|
Manage database file sizes |
|
|
342 | (2) |
|
Understand and find autogrowth events |
|
|
344 | (1) |
|
|
345 | (2) |
|
Monitor activity with DMOs |
|
|
347 | (1) |
|
Observe sessions and requests |
|
|
347 | (2) |
|
Understand wait types and wait statistics |
|
|
349 | (9) |
|
Monitor with the SQL Assessment API |
|
|
358 | (3) |
|
|
361 | (2) |
|
View Extended Events data |
|
|
363 | (4) |
|
Use Extended Events to capture deadlocks |
|
|
367 | (2) |
|
Use Extended Events to detect autogrowth events |
|
|
369 | (1) |
|
Use Extended Events to detect page splits |
|
|
369 | (1) |
|
|
370 | (1) |
|
Capture performance metrics with DMOs and data collectors |
|
|
371 | (1) |
|
Query performance metrics with DMVs |
|
|
371 | (3) |
|
Capture performance metrics with Performance Monitor |
|
|
374 | (1) |
|
Monitor key performance metrics |
|
|
375 | (4) |
|
Monitor key performance metrics in Linux |
|
|
379 | (1) |
|
Monitor key performance metrics in Azure portal |
|
|
380 | (4) |
|
Protect important workloads with Resource Governor H |
|
|
384 | (2) |
|
Configure the Resource Governor classifier function |
|
|
386 | (1) |
|
Configure Resource Governor resource pools and workload groups |
|
|
387 | (1) |
|
Monitor resource pools and workload groups |
|
|
388 | (1) |
|
Understand the SQL Server servicing model |
|
|
389 | (1) |
|
|
389 | (1) |
|
Plan for the product support life cycle |
|
|
390 | (3) |
|
Chapter 9 Automate SQL Server administration |
|
|
393 | (52) |
|
Foundations of SQL Server automated administration |
|
|
394 | (1) |
|
|
394 | (6) |
|
|
400 | (12) |
|
|
412 | (1) |
|
Basic care and feeding of SQL Server |
|
|
412 | (2) |
|
Use SQL Server maintenance plans |
|
|
414 | (1) |
|
Cover databases with the maintenance plan |
|
|
415 | (1) |
|
|
416 | (7) |
|
Maintenance plan report options |
|
|
423 | (1) |
|
Build maintenance plans using the Maintenance Plan designer in SSMS |
|
|
424 | (2) |
|
Back up availability groups using a secondary replica |
|
|
426 | (2) |
|
Strategies for administering multiple SQL Servers |
|
|
428 | (1) |
|
Master/Target servers for SQL Agent jobs |
|
|
428 | (3) |
|
SQL Server Agent event forwarding |
|
|
431 | (1) |
|
|
431 | (3) |
|
Use PowerShell to automate SQL Server administration |
|
|
434 | (2) |
|
|
436 | (1) |
|
Install the PowerShell SQLServer module |
|
|
436 | (2) |
|
Use PowerShell with SQL Server |
|
|
438 | (4) |
|
Use PowerShell with availability groups |
|
|
442 | (3) |
|
Chapter 10 Develop, deploy, and manage data recovery |
|
|
445 | (42) |
|
Prepare for data recovery |
|
|
446 | (1) |
|
A disaster recovery scenario |
|
|
447 | (2) |
|
Define acceptable data loss: RPO |
|
|
449 | (1) |
|
Define acceptable downtime: RTO |
|
|
450 | (1) |
|
Establish and use a runbook |
|
|
450 | (1) |
|
|
451 | (2) |
|
Understand different types of backups |
|
|
453 | (1) |
|
An overview of SQL Server recovery models |
|
|
454 | (2) |
|
|
456 | (2) |
|
|
458 | (1) |
|
|
459 | (2) |
|
File and filegroup backups |
|
|
461 | (1) |
|
Additional backup options and considerations |
|
|
462 | (5) |
|
Understand backup devices |
|
|
467 | (1) |
|
|
468 | (1) |
|
|
468 | (1) |
|
|
469 | (3) |
|
Back up to S3-compatible storage |
|
|
472 | (1) |
|
Create and verify backups |
|
|
472 | (1) |
|
|
473 | (1) |
|
|
474 | (1) |
|
|
475 | (1) |
|
Restore a database using a full backup |
|
|
476 | (1) |
|
Restore a database with differential and log backups |
|
|
477 | (1) |
|
Restore a database to a point in time |
|
|
478 | (2) |
|
Restore a database piecemeal |
|
|
480 | (1) |
|
Define a recovery strategy |
|
|
481 | (1) |
|
A sample recovery strategy for our DR scenario |
|
|
482 | (2) |
|
Recovery strategies for hybrid and cloud environments |
|
|
484 | (3) |
|
Chapter 11 Implement high availability and disaster recovery |
|
|
487 | (62) |
|
Overview of high-availability and disaster-recovery technologies |
|
|
488 | (1) |
|
Compare HA and DR technologies |
|
|
489 | (1) |
|
|
489 | (3) |
|
Understand the capabilities of failover clustering |
|
|
492 | (2) |
|
Understand the capabilities of availability groups |
|
|
494 | (2) |
|
Configure failover cluster instances |
|
|
496 | (1) |
|
|
497 | (2) |
|
Configure a SQL Server FCI |
|
|
499 | (3) |
|
|
502 | (1) |
|
Design availability groups solutions |
|
|
502 | (2) |
|
Compare different cluster types |
|
|
504 | (3) |
|
Create WSFC for use with availability groups |
|
|
507 | (2) |
|
Understand the database mirroring endpoint |
|
|
509 | (1) |
|
Recent improvements to availability groups |
|
|
509 | (3) |
|
Choose the correct secondary replica availability mode |
|
|
512 | (1) |
|
Understand the impact of secondary replicas on performance |
|
|
513 | (2) |
|
Understand failovers in availability groups |
|
|
515 | (3) |
|
Seeding options when adding replicas |
|
|
518 | (4) |
|
Additional actions after creating an availability group |
|
|
522 | (2) |
|
Read secondary database copies |
|
|
524 | (7) |
|
|
531 | (1) |
|
Implement a hybrid availability group topology |
|
|
531 | (1) |
|
Understand the Azure SQL Managed Instance link feature |
|
|
532 | (2) |
|
Failover and fallback to Azure SQL Managed Instance with database portability |
|
|
534 | (1) |
|
Provision and scale the Azure SQL Managed Instance link feature |
|
|
534 | (3) |
|
Failover and failback tooling and automation |
|
|
537 | (1) |
|
Configure availability groups in SQL Server on Linux |
|
|
537 | (1) |
|
Understand the differences between Windows and Linux clustering |
|
|
537 | (1) |
|
Set up an availability group in SQL Server on Linux |
|
|
538 | (5) |
|
Administer availability groups |
|
|
543 | (1) |
|
Analyze DMVs for availability groups |
|
|
544 | (2) |
|
Analyze wait types for availability groups |
|
|
546 | (1) |
|
Analyze Extended Events for availability groups |
|
|
547 | (1) |
|
Alerts for availability groups |
|
|
548 | (1) |
|
|
|
Chapter 12 Administer instance and database security and permissions |
|
|
549 | (68) |
|
Understand authentication modes |
|
|
549 | (1) |
|
|
550 | (1) |
|
SQL Server Authentication |
|
|
551 | (1) |
|
|
551 | (2) |
|
Advanced types of server principals |
|
|
553 | (1) |
|
Authentication to SQL Server on Linux |
|
|
554 | (1) |
|
Contained database authentication |
|
|
555 | (1) |
|
Grasp security principals |
|
|
555 | (2) |
|
|
557 | (2) |
|
Configure login server principals |
|
|
559 | (23) |
|
|
582 | (11) |
|
Understand permissions and authorization |
|
|
593 | (1) |
|
Permissions for controlling Data Definition Language and Data Manipulation Language |
|
|
593 | (2) |
|
How permissions accumulate |
|
|
595 | (2) |
|
|
597 | (8) |
|
Perform common security administration tasks |
|
|
605 | (1) |
|
|
605 | (3) |
|
Create login with known SID |
|
|
608 | (1) |
|
Migrate SQL Server logins and permissions |
|
|
608 | (6) |
|
Dedicated administrator connection |
|
|
614 | (3) |
|
Chapter 13 Protect data through classification, encryption, and auditing |
|
|
617 | (52) |
|
Privacy in the modern era |
|
|
617 | (1) |
|
General Data Protection Regulation (GDPR) |
|
|
618 | (1) |
|
Microsoft Purview overview |
|
|
619 | (1) |
|
Introduction to security principles and protocols |
|
|
620 | (1) |
|
Secure your environment with defense in depth |
|
|
621 | (2) |
|
The difference between hashing and encryption |
|
|
623 | (2) |
|
A primer on protocols and transmitting data |
|
|
625 | (5) |
|
|
630 | (1) |
|
Protect the data platform |
|
|
631 | (1) |
|
Secure the network with TLS |
|
|
632 | (1) |
|
Data protection from the OS |
|
|
633 | (1) |
|
The encryption hierarchy in detail |
|
|
634 | (1) |
|
Use EKM modules with SQL Server |
|
|
635 | (2) |
|
Master keys in the encryption hierarchy |
|
|
637 | (2) |
|
|
639 | (3) |
|
Protect sensitive columns with Always Encrypted |
|
|
642 | (6) |
|
|
648 | (2) |
|
|
650 | (1) |
|
Protect Azure SQL Database with Microsoft Defender for SQL |
|
|
651 | (2) |
|
|
653 | (1) |
|
|
653 | (1) |
|
|
653 | (1) |
|
Ledger considerations and limitations |
|
|
654 | (1) |
|
Data storage requirements |
|
|
654 | (1) |
|
|
655 | (2) |
|
Audit with SQL Server and Azure SQL Database |
|
|
657 | (1) |
|
|
657 | (6) |
|
|
663 | (1) |
|
Secure Azure infrastructure as a service |
|
|
664 | (1) |
|
|
664 | (2) |
|
User-defined routes and IP forwarding |
|
|
666 | (1) |
|
Additional Azure networking security features |
|
|
667 | (2) |
|
|
|
Chapter 14 Performance tune SQL Server |
|
|
669 | (84) |
|
Understand isolation levels and concurrency |
|
|
671 | (5) |
|
Understand how concurrent sessions become blocked |
|
|
676 | (3) |
|
Change the isolation level |
|
|
679 | (2) |
|
Understand and handle common concurrency scenarios |
|
|
681 | (11) |
|
Understand row version-based concurrency |
|
|
692 | (8) |
|
Understand on-disk versus memory-optimized concurrency |
|
|
700 | (2) |
|
Understand durability settings for performance |
|
|
702 | (1) |
|
Delayed durability database options |
|
|
703 | (2) |
|
How SQL Server executes a query |
|
|
705 | (1) |
|
Understand the query execution process |
|
|
706 | (2) |
|
|
708 | (5) |
|
Understand execution plans |
|
|
713 | (12) |
|
Understand parameterization and parameter sniffing |
|
|
725 | (3) |
|
Explore the procedure cache |
|
|
728 | (5) |
|
|
733 | (2) |
|
Use advanced engine features to tune queries |
|
|
735 | (1) |
|
Internal improvements in SQL Server 2022 |
|
|
736 | (1) |
|
Recent improvements to tempdb |
|
|
736 | (1) |
|
Leverage the Query Store feature |
|
|
737 | (5) |
|
|
742 | (3) |
|
Automatic plan correction |
|
|
745 | (1) |
|
Intelligent query processing |
|
|
746 | (7) |
|
Chapter 15 Understand and design indexes |
|
|
753 | (36) |
|
|
754 | (1) |
|
Choose a proper rowstore clustered index key |
|
|
754 | (4) |
|
The case against intentionally designing heaps |
|
|
758 | (1) |
|
Understand the Optimize_For_Sequential_Key Feature |
|
|
759 | (1) |
|
Design rowstore nonclustered indexes |
|
|
760 | (1) |
|
Understand nonclustered index design |
|
|
761 | (6) |
|
Create filtered nonclustered indexes |
|
|
767 | (1) |
|
Understand the missing indexes feature |
|
|
767 | (4) |
|
Understand and provide index usage |
|
|
771 | (2) |
|
Understand columnstore indexes |
|
|
773 | (1) |
|
Design columnstore indexes |
|
|
774 | (2) |
|
|
776 | (1) |
|
Understand the deltastore of columnstore indexes |
|
|
777 | (1) |
|
Demonstrate the power of columnstore indexes |
|
|
778 | (2) |
|
Understand indexes in memory-optimized tables |
|
|
780 | (1) |
|
Understand hash indexes for memory-optimized tables |
|
|
781 | (1) |
|
Understand nonclustered indexes for memory-optimized tables |
|
|
782 | (1) |
|
Understand index statistics |
|
|
783 | (1) |
|
Automatically create and update statistics |
|
|
783 | (1) |
|
Manually create statistics for on-disk tables |
|
|
784 | (1) |
|
Understand statistics on memory-optimized tables |
|
|
785 | (1) |
|
Understand statistics on external tables |
|
|
785 | (1) |
|
Understand other types of indexes |
|
|
786 | (1) |
|
Understand full-text indexes |
|
|
786 | (1) |
|
Understand spatial indexes |
|
|
786 | (1) |
|
|
787 | (2) |
|
|
|
Chapter 16 Design and implement hybrid and Azure database infrastructure |
|
|
789 | (40) |
|
Cloud computing and Microsoft Azure |
|
|
789 | (1) |
|
|
790 | (1) |
|
Managing Azure with the Azure portal and PowerShell 7 |
|
|
791 | (1) |
|
|
792 | (2) |
|
|
794 | (1) |
|
|
794 | (1) |
|
|
795 | (1) |
|
Cloud models and SQL Server |
|
|
796 | (1) |
|
Infrastructure as a service |
|
|
797 | (8) |
|
|
805 | (16) |
|
|
821 | (5) |
|
|
826 | (1) |
|
Other data services in Azure |
|
|
826 | (1) |
|
|
827 | (1) |
|
Non-relational Azure data offerings |
|
|
827 | (1) |
|
Third-party fully managed data platforms |
|
|
828 | (1) |
|
Chapter 17 Provision Azure SQL Database |
|
|
829 | (40) |
|
Provision an Azure SQL Database logical server |
|
|
830 | (2) |
|
Create an Azure SQL Database server using the Azure portal |
|
|
832 | (1) |
|
Create a server using PowerShell |
|
|
833 | (1) |
|
Establish a connection to your server |
|
|
834 | (2) |
|
|
836 | (1) |
|
Provision a database in Azure SQL Database |
|
|
836 | (1) |
|
Create a database using the Azure portal |
|
|
837 | (2) |
|
Create a database using PowerShell |
|
|
839 | (1) |
|
Create a database using Azure CLI |
|
|
840 | (1) |
|
Create a database using T-SQL |
|
|
841 | (1) |
|
|
842 | (1) |
|
Provision a named replica for a Hyperscale database |
|
|
843 | (1) |
|
Provision an elastic pool |
|
|
844 | (1) |
|
|
845 | (1) |
|
Security in Azure SQL Database |
|
|
846 | (1) |
|
Security features shared with SQL Server 2022 |
|
|
846 | (1) |
|
Server- and database-level firewall |
|
|
846 | (3) |
|
Integrate with virtual networks |
|
|
849 | (1) |
|
Azure Private Link for Azure SQL Database |
|
|
850 | (1) |
|
Control access using Azure AD |
|
|
850 | (3) |
|
Use Azure role-based access control |
|
|
853 | (1) |
|
|
854 | (5) |
|
Microsoft Defender for SQL |
|
|
859 | (2) |
|
Prepare Azure SQL Database for disaster recovery |
|
|
861 | (1) |
|
Understand default disaster recovery features |
|
|
861 | (1) |
|
Manually export database contents |
|
|
862 | (1) |
|
Enable zone-redundant configuration |
|
|
863 | (1) |
|
Configure geo-replication |
|
|
863 | (2) |
|
|
865 | (2) |
|
Use Azure Backup for long-term backup retention |
|
|
867 | (2) |
|
Chapter 18 Provision Azure SQL Managed Instance |
|
|
869 | (46) |
|
What is Azure SQL Managed Instance? |
|
|
872 | (1) |
|
Differences between SQL Server and Azure SQL Managed Instance |
|
|
873 | (10) |
|
Create a SQL managed instance |
|
|
883 | (1) |
|
Select a service tier and service objective |
|
|
884 | (2) |
|
Use the Azure portal to provision a SQL managed instance |
|
|
886 | (5) |
|
Use PowerShell to provision a SQL managed instance |
|
|
891 | (1) |
|
Delete a SQL managed instance |
|
|
892 | (1) |
|
Establish a connection to a SQL managed instance |
|
|
893 | (1) |
|
Create the endpoints via the Azure portal |
|
|
894 | (1) |
|
Create a VPN gateway via PowerShell |
|
|
894 | (4) |
|
Network requirements for SQL managed instances |
|
|
898 | (2) |
|
Migrate data to Azure SQL Managed Instance |
|
|
900 | (1) |
|
Link feature for Azure SQL Managed Instance |
|
|
900 | (1) |
|
Azure Data Migration Service |
|
|
901 | (1) |
|
Migrate with backup and restore |
|
|
901 | (1) |
|
|
902 | (1) |
|
Azure SQL Managed Instance administration features |
|
|
903 | (1) |
|
|
903 | (2) |
|
|
905 | (1) |
|
|
906 | (1) |
|
Monitor SQL managed instances |
|
|
906 | (1) |
|
Link feature for Azure SQL Managed Instance |
|
|
907 | (1) |
|
Azure SQL Managed Instance security features |
|
|
908 | (1) |
|
|
908 | (3) |
|
Azure SQL Managed Instance data protection features |
|
|
911 | (1) |
|
Prevent data exfiltration |
|
|
911 | (1) |
|
|
912 | (1) |
|
|
912 | (1) |
|
|
912 | (1) |
|
|
913 | (1) |
|
|
913 | (2) |
|
Chapter 19 Migrate to SQL Server solutions in Azure |
|
|
915 | (24) |
|
Migration services options |
|
|
915 | (1) |
|
Microsoft Assessment Planning toolkit |
|
|
916 | (1) |
|
Total Cost of Ownership calculator |
|
|
917 | (1) |
|
Database Experimentation Assistant |
|
|
918 | (1) |
|
Azure Data Migration Assistant |
|
|
919 | (2) |
|
Azure Database Migration Service |
|
|
921 | (4) |
|
SQL Server Migration Assistant |
|
|
925 | (1) |
|
Data Access Migration Toolkit |
|
|
926 | (1) |
|
Resolve common migration failures using Database Migration Service |
|
|
926 | (2) |
|
Large object columns with data larger than 32 KB |
|
|
928 | (1) |
|
Final notes for migration |
|
|
928 | (1) |
|
Open source PowerShell migration with dbatools |
|
|
929 | (4) |
|
Migrate with Azure Data Factory |
|
|
933 | (1) |
|
Azure integration runtime |
|
|
933 | (2) |
|
Self-hosted integration runtime |
|
|
935 | (1) |
|
Self-hosted IR servers and nodes |
|
|
935 | (1) |
|
Azure-SSIS integration runtime |
|
|
936 | (1) |
|
Best practices for security and resilience during migration |
|
|
937 | (1) |
|
|
937 | (1) |
|
Cloud requirements for application resilience |
|
|
938 | (1) |
Index |
|
939 | |