book

Administering Microsoft Azure SQL Solutions: Hands-on Preparation and Practice for Exam DP-300

Name: Administering Microsoft Azure SQL Solutions: Hands-on Preparation and Practice for Exam DP-300
Author: Geoff Hiten
ISBN: 9798868815850

by Geoff Hiten

September 2025

Intermediate to advanced

388 pages

9h 44m

English

Apress

Read now

Unlock full access

Certification Study Companion Series
Administering Microsoft Azure SQL Solutions
Table of Contents
About the Author
About the Technical Reviewer
1. About the DP-300 Exam, This Book, and Azure
Taking the TestTime ManagementThe QuestionsLimits, Boundaries, and ConstraintsCloud Computing FundamentalsOn-Demand Self-ServiceResource AbstractionUsage-Based PricingElasticity and ScalabilityBroad Network AccessibilityCloud vs. On-PremisesAzure FundamentalsAzure Storage BasicsAzure Networking FundamentalsPublic NetworkingPrivate NetworkingHow About BothChapter Summary
Part I: Plan and Implement Azure SQL Data Platform Resources
2. Plan and Implement Azure SQL Database Solutions
Introduction to Azure Database AdministrationAzure Database RolesAzure Database SQL OfferingsAzure SQL DatabaseVirtual Machines in AzureAzure SQL IaaS ExtensionAzure SQL Managed InstanceAzure PaaS SQL ServicesBilling ModelsService TiersGeneral Purpose/Business CriticalDTU Standard/PremiumAzure SQL DB Special CasesElastic PoolHyperscaleServerlessDeploying Azure SQL ServicesAzure SQL Managed InstanceNetworkingAzure SQL EdgePreview FeaturesElastic JobsChapter Summary
3. Configure Resources for Scale and Performance
SQL Data File Storage: Azure VMsSize, IOPS, and ThroughputSQL Data File Storage: Managed InstanceSQL Data File Storage: Business Critical StorageConfigure Azure SQL Database for Scale and PerformanceCompute + Storage Blade DetailsElastic PoolHyperscaleServerlessAzure SQL Managed Instance Scalability and PerformanceAzure SQL VM Scalability and PerformanceVM SeriesData Engineering for Scale and PerformanceTable PartitioningData ShardingData CompressionChapter Summary
4. Plan and Implement a Migration Strategy
Cloud Adoption Framework ProcessEvaluate Requirements for the MigrationInventory and AssessmentAzure MigrateAzure Migrate ApplianceData Migration AssistantAzure Database Migration ServiceAzure Data StudioThird-Party ToolsEvaluate Offline or Online Migration StrategiesImplement an Offline Migration StrategyImplement an Online Migration StrategyOnline Migrate to Azure SQL VMOnline Migrate to Azure SQL Managed InstanceAzure SQL Managed Instance LinkConfigure ReplicationExecute FailoverOnline Migrate to Azure SQL DatabasePerform Post-migration ValidationsTroubleshoot a MigrationSet Up SQL Data Sync for AzureImplement a Migration to AzureImplement a Migration Between Azure SQL ServicesChapter and Part Summary

Part II: Implement a Secure Environment
5. Configure Database Authentication and Authorization
Configure Authentication by Using Active Directory and Microsoft Entra IDEntra ID AuthenticationCreate Users from Entra ID IdentitiesConfigure Security PrincipalsConfigure Database- and Object-Level Permissions Using Graphical ToolsApply Principle of Least Privilege for All SecurablesTroubleshoot Authentication and Authorization IssuesGeneral Authentication and Authorization TroubleshootingTroubleshooting Windows Auth for SQL MIManage Authentication and Authorization by Using T-SQLChapter Summary
6. Implement Security for Data at Rest and Data in Transit
Implement Transparent Data Encryption (TDE)Deploying Azure Key VaultConnecting AKV to Azure SQL PaaS ServicesConnecting AKV to Azure SQL DBConnecting AKV to Azure SQL MIConnecting AKV to Azure SQL VMImplement Object-Level EncryptionColumn-Level EncryptionEncrypting Stored ProceduresConfigure Server- and Database-Level Firewall RulesServer-Level Firewall RulesDatabase-Level Firewall RulesFirewall Rules InteractionImplement Always EncryptedAlways Encrypted with Secure EnclavesConfigure Transport Layer Security (TLS)Chapter Summary
7. Implement Compliance Controls for Sensitive Data
Apply a Data Classification StrategyConfigure Server and Database AuditsAuditing Azure SQL DBAuditing Azure SQL Managed InstanceImplement Data Change TrackingImplement Dynamic Data MaskingManage Database Resources by Using Azure PurviewImplement Azure SQL Database LedgerImplement Row-Level SecurityConfigure Microsoft Defender for SQLChapter and Part Summary
Part III: Monitor, Configure, and Optimize Database Resources
8. Monitor Resource Activity and Performance
Prepare an Operational Performance BaselineDetermine Sources for Performance MetricsAzure SQL PaaS Performance MetricsAzure SQL IaaS MetricsInterpret Performance MetricsConfigure and Monitor Activity and PerformanceAzure Storage AccountEvent HubLog Analytics WorkspaceProvision Log AnalyticsConfigure SQL Services for Log AnalyticsAlerting and Reporting with Azure Log AnalyticsCost Management with Azure Log AnalyticsMonitor by Using SQL InsightsMonitor by Using Extended EventsChapter Summary
9. Monitor and Optimize Query Performance
Query PlansConfigure the Query StoreMonitor by Using the Query StoreRegressed QueriesOverall Resource ConsumptionTop Resource Consuming QueriesQueries With Forced PlansQueries With High VariationQuery Wait StatisticsTracked QueriesIdentify Sessions That Cause BlockingDeadlocksData NormalizationTransaction Isolation LevelsIdentify Performance Issues Using Dynamic Management Views (DMVs)Identify and Implement Index Changes for QueriesRow-Version IndexesColumnStore IndexesRecommend Query Construct Modifications Based on Resource UsageSubqueries vs. JoinsShrouded SARGsAssess the Use of Query Hints for Query PerformanceReview Execution PlansChapter Summary
10. Configure Database Solutions for Optimal Performance
Implement Index Maintenance TasksImplement Statistics Maintenance TasksImplement Database Integrity ChecksConfigure Database Automatic TuningForce PlansCreate an IndexDrop an IndexEnable Automatic Query TuningConfigure Server Settings for PerformanceVM Support for Storage TypesHost CachingStriping Disks for PerformanceConfigure Resource Governor for PerformanceImplement Database-Scoped ConfigurationConfigure Compute and Storage Resources for ScalingConstrained CoresGeneral PurposeBandwidth FullConfigure Intelligent Query Processing (IQP)Adaptive Query ProcessingTable Variable Deferred CompilationParameter-Sensitive OptimizationEnabling Intelligent Query ProcessingChapter and Part Summary
Part IV: Configure and Manage Automation
11. Create and Manage SQL Agent Jobs
SQL AgentSQL Agent JobsSQL Agent Job StepsSQL Agent SchedulesManage Schedules for Regular MaintenanceMaintenance PlansMaintenance ScriptsConfigure Job Alerts and NotificationsAlertsOperatorsTroubleshoot SQL Agent JobsChapter Summary
12. Automate Deployment of Database Resources
Azure Resource ManagerToolsGitHubVisual Studio CodeAutomate Deployment by Using Azure Resource Manager Templates (ARM Templates) and BicepAzure Resource Manager TemplatesBicepAutomate Deployment by Using PowerShellAutomate Deployment by Using Azure CLIAutomate Scaling Up or Scaling DownMonitor and Troubleshoot DeploymentsAzure Verified ModulesChapter Summary
13. Create and Manage Database Tasks in Azure
Create and Configure Elastic JobsElastic Jobs Use CasesElastic Jobs ComponentsElastic Job AgentElastic Jobs DatabaseJobsTarget GroupsElastic Jobs SecurityManaging Elastic JobsPortalJobsSecurityPowerShellT-SQLCreate and Configure Database Tasks by Using AutomationAutomate Database Workflows by Using Azure Logic AppsConfigure Alerts and Notifications on Database TasksChapter and Part Summary
Part V: Plan and Configure a High Availability and Disaster Recovery (HA/DR) Environment
14. Recommend a HA/DR Strategy for Database Solutions
Recommend a HA/DR Strategy Based on Recovery Point Objective/Recovery Time Objective (RPO/RTO) RequirementsHigh AvailabilityAvailability SetsAvailability ZonesDisaster RecoveryEvaluate HA/DR for Hybrid DeploymentsAzure Site RecoverySQL Server Always-On Availability GroupsAzure SQL Managed Instance LinkEvaluate Azure-Specific HA/DR SolutionsPaaS SQLAzure SQL Disaster Recovery SolutionsGeo-redundant BackupsAutomation ScriptsGeo-replication and SQL Failover GroupsIaaS SQLSQL Always-On Availability GroupsSQL Server Failover ClustersRecommend a Testing Procedure for a HA/DR SolutionAzure Site RecoveryChapter Summary
15. Perform Backup and Restore of a Database
SQL Backup and Restore InternalsFull BackupsTransaction Log BackupsDifferential BackupsRestore DatabaseRestore LogRestore DifferentialRecommend a Database Backup and Restore StrategyPerform a Database Backup by Using Database ToolsCreating a Credential via SSMSBackup ExampleCreate Full BackupCreate First Log BackupCreate Differential BackupReviewing the BackupsPerform a Database Restore by Using Database ToolsExample 1: Restore Full and Log to StandbyExample 2: Restore Full, Diff, and LogPerform a Database Restore to a Point in TimeConfigure Long-Term Backup RetentionBack Up and Restore a Database by Using T-SQLChapter Summary
16. Configure HA/DR for Database Solutions
Configure Active Geo-replicationConfigure Auto-failover GroupsFailover PolicyConfigure an Always-On Availability GroupEndpoint RedirectionAzure Template for SQL High AvailabilityConfigure Quorum Options for a Windows Server Failover ClusterConfigure Failover Clustered Instances on Azure Virtual MachinesConfigure Log ShippingMonitor and Troubleshoot a HA/DR SolutionChapter, Part, and Book Summary
Index

Content preview from Administering Microsoft Azure SQL Solutions: Hands-on Preparation and Practice for Exam DP-300

G. HitenAdministering Microsoft Azure SQL SolutionsCertification Study Companion Serieshttps://doi.org/10.1007/979-8-8688-1585-0_9

9. Monitor and Optimize Query Performance

Geoff Hiten¹

This second chapter of the part on Azure SQL Performance and Optimization focuses on the actual queries executed by the SQL engine sent from the external workload. Knowing what queries consume the most resources and take the most time to execute allows us to focus improvement efforts on queries that have significant impact on the overall system performance. Most SQL Systems exhibit performance skew with a few queries consuming the most resources. This ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Exam Ref DP-300 Administering Microsoft Azure SQL Solutions

Publisher Resources

ISBN: 9798868815850Purchase Link Publisher Website

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Administering Microsoft Azure SQL Solutions: Hands-on Preparation and Practice for Exam DP-300

by Geoff Hiten

9. Monitor and Optimize Query Performance

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.