.png "☁️ Top 200 Azure Data Engineer Interview Questions & Answers (2026) 💻🔥 DP-300 Exam Blueprint, Azure SQL, Security, Performance Tuning, HADR, Automation & Real-World Scenarios | FreeLearning365")
☁️ Top 200 Azure Data Engineer Interview Questions & Answers 💻🚀
DP-300 & Beyond – Latest Exam Rules, Hands-On Scenarios, Real-World Depth
*Welcome to Tech Book & FreeLearning365 – your free, hands‑on learning community!
At @FreeLearning365 we believe that mastering Azure data engineering isn’t about memorising theory – it’s about building secure, high‑performance, and resilient data solutions that power real businesses. This guide is built exactly for that: 200 of the most asked Azure Data Engineer interview questions with detailed, up‑to‑date answers, aligned to the DP-300 (Administering Microsoft Azure SQL Solutions) exam objectives and the broader data engineering ecosystem. You’ll find the latest DP‑300 skill distribution, hundreds of hands‑on scenarios, and practical command‑by‑command walkthroughs. No fluff – only the skills that get you hired and make you a true Azure data professional.* 🚀
📊 DP‑300 Exam Blueprint (Latest)
Use this to understand the question distribution in our guide.
| Domain | Weight | Questions in This Guide |
|---|---|---|
| Plan and Implement Data Platform Resources | 20–25% | ~40 questions |
| Implement a Secure Environment | 20–25% | ~40 questions |
| Monitor and Optimize Operational Resources | 20–25% | ~40 questions |
| Optimize Query Performance | 20–25% | ~40 questions |
| Automate Tasks | 10–15% | ~20 questions |
| Plan and Implement HADR Environment | 10–15% | ~20 questions |
The remaining questions cover cross‑cutting scenario‑based challenges, making you interview‑ready from every angle.
🧱 1. Plan & Implement Data Platform Resources (1–40)
1. What is Azure SQL Database?
A fully managed, intelligent, relational cloud database service. It handles patching, backups, and high availability automatically.
2. Difference between Azure SQL Database and SQL Managed Instance?
SQL DB is a single database with logical server. Managed Instance provides near‑100% SQL Server compatibility, including SQL Agent, cross‑DB queries, and instance‑level features.
3. What is a logical server in Azure SQL?
A container that hosts one or more databases. It provides a centralised firewall, logins, and administration endpoint.
4. How to choose between DTU and vCore purchasing models?
DTU bundles compute, storage, and IO into a single measure. vCore gives independent compute and storage scaling, better transparency, and Azure Hybrid Benefit support.
5. What are service tiers in Azure SQL Database?
General Purpose: balanced compute and storage (remote blob storage).
Business Critical: local SSD, higher IO, and built‑in HA via availability groups.
Hyperscale: highly scalable storage up to 100 TB, fast scale‑out read replicas.
6. How do you provision a new Azure SQL Database using Azure CLI?
az sql db create --resource-group myRG --server myServer --name myDB --service-objective S0
7. What is elastic pool?
A shared resource pool for multiple databases to efficiently use and share DTUs/vCores, ideal for unpredictable workloads.
8. When should you use an elastic pool vs single database?
Use elastic pools when you have many databases with low average usage but occasional peaks, to optimise cost.
9. How to move a database into an elastic pool?
ALTER DATABASE myDB MODIFY (SERVICE_OBJECTIVE = ELASTIC_POOL (name = myPool));
10. What is a contained database user?
A user that is authenticated within the database (no server login), making the database portable. Created with CREATE USER ... WITH PASSWORD.
11. How to connect to Azure SQL using Azure AD authentication?
First create an Azure AD admin on the server, then create users from Azure AD identities, and connect using Authentication=Active Directory Integrated or Active Directory Password in connection strings.
12. What is Azure SQL Managed Instance’s virtual network requirement?
Managed Instance is deployed into a dedicated subnet in your Azure VNet, enabling private IP connectivity.
13. What are service endpoints and private endpoints for Azure SQL?
Service endpoints allow VNet traffic to Azure SQL with a firewall rule. Private endpoints create a private IP in your VNet for the logical server, fully isolating traffic.
14. How to configure a private endpoint for Azure SQL Database?
Create a private endpoint in the portal or CLI, target the SQL Server resource, integrate with a private DNS zone. Approve the connection from the server firewall settings.
15. What is Azure Hybrid Benefit for SQL Server?
Use your on‑premises SQL Server licenses with active Software Assurance to pay reduced rates on Azure SQL (vCore model).
16. What are database collations and why do they matter?
Collation defines character sorting and comparison rules. Must be considered when migrating databases to avoid data corruption or query errors.
17. How to copy a database using Azure portal?
In the database blade, click “Copy”, provide target server and name. It creates a transactionally consistent copy.
18. What is a geo‑replica?
A readable secondary database in another Azure region for disaster recovery and read scale‑out.
19. How to create a geo‑replica?
ALTER DATABASE myDB ADD SECONDARY ON SERVER secondaryServer;
Or via portal.
20. What is failover group?
A group of databases that fail over together across regions with read‑write and read‑only listeners, simplifying application recovery.
21. How to configure auto‑failover group?
In the primary server, create a failover group, add secondary server, and choose databases. The listener endpoint (failover-group-name.database.windows.net) is used by the app.
22. What is the difference between active geo‑replication and auto‑failover groups?
Active geo‑replication provides readable secondaries but manual failover (unless paired with failover groups). Failover groups provide automatic failover policy and DNS listener.
23. What is a “logical server” vs “managed instance”?
Logical server is a DNS‑based container for databases with a shared admin. Managed Instance is a full SQL Server instance with its own collation, agent, and VNet integration.
24. How to change a database’s service tier?
ALTER DATABASE myDB MODIFY (EDITION = 'BusinessCritical', SERVICE_OBJECTIVE = 'BC_Gen5_2');
25. What are Hyperscale benefits?
Fast database scaling, near‑instantaneous backups, and ability to scale storage up to 100 TB independently of compute.
26. How to enable Hyperscale?
Create a new database with Edition = Hyperscale and choose vCores. Existing databases cannot be converted directly without migration.
27. What is “serverless” compute tier?
Automatically pauses during inactivity and scales compute based on workload, billed per second.
28. When would you use serverless?
For databases with intermittent, unpredictable usage patterns where cost savings from pausing are significant.
29. How to create a serverless database?
CREATE DATABASE myDB (EDITION = 'GeneralPurpose', COMPUTE = SERVERLESS, MIN_VCORE = 0.5, MAX_VCORE = 2, AUTO_PAUSE_DELAY = 60);
30. How to monitor and set up alerts for DTU/vCore usage?
Use Azure Monitor metrics (dtu_consumption_percent), create an alert rule with threshold and action group.
31. What is Azure Arc‑enabled SQL Managed Instance?
A hybrid deployment that runs Azure SQL Managed Instance on your own Kubernetes infrastructure, managed via Azure Arc.
32. How does Azure SQL Database handle automatic backups?
Full backups weekly, differential backups every 12–24 hours, transaction log backups every 5–10 minutes. Stored in geo‑redundant storage (configurable).
33. What is long‑term retention (LTR)?
Store monthly/yearly backups beyond the standard 7‑35 day retention for up to 10 years.
34. How to configure LTR using PowerShell?
Set-AzSqlDatabaseBackupLongTermRetentionPolicy -ResourceGroupName $rg -ServerName $s -DatabaseName $db -WeeklyRetention P12W -YearlyRetention P5Y -WeekOfYear 5
35. What is “Zone Redundancy” for Azure SQL DB?
Places database replicas across availability zones within a region, protecting against zonal failures.
36. How to enable zone redundancy?
Choose Business Critical or Premium service tier and select “Zone redundant” option during creation.
37. What is the “maintenance window”?
A configurable time window for planned Azure maintenance (patching) to minimize disruption.
38. How to set a maintenance window?
Set-AzSqlDatabase -ResourceGroupName $rg -ServerName $s -DatabaseName $db -MaintenanceConfigurationId "/subscriptions/.../providers/Microsoft.Maintenance/maintenanceConfigurations/MyWindow"
39. What is Query Performance Insight?
A built‑in Azure portal tool that identifies top CPU and IO consuming queries and shows performance recommendations.
40. What is the Azure SQL Analytics solution?
A Log Analytics workspace solution that collects telemetry from multiple databases for advanced monitoring and alerting.
🔐 2. Implement a Secure Environment (41–80)
41. What are the authentication methods for Azure SQL?
SQL authentication (username/password), Azure AD authentication (password, integrated, service principal, managed identity).
42. How to set up Azure AD admin for Azure SQL?
In portal, go to Azure SQL Server → Azure Active Directory → Set admin. This account can create other Azure AD users.
43. What is Azure AD–only authentication?
Disables SQL authentication entirely, forcing all connections to use Azure AD.
44. How to create an Azure AD contained database user?
CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER;
45. What is a system‑assigned managed identity?
An automatically managed identity in Azure AD for Azure resources, allowing a resource to authenticate without credentials.
46. How to grant a managed identity access to Azure SQL?
Create a user for the managed identity:
CREATE USER [myAppName] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [myAppName];
47. What are firewall rules in Azure SQL?
Rules that allow/block access based on client IP address. Server‑level and database‑level.
48. How to add a firewall rule using T‑SQL?
EXEC sp_set_firewall_rule N'AllowOffice', '203.0.113.0', '203.0.113.255';
49. How to allow Azure services to access your database?
Enable “Allow Azure services and resources to access this server” in firewall settings. (Be cautious – this includes all Azure subscriptions.)
50. What is Virtual Network Rule?
Allows access from a specific VNet subnet via a service endpoint, eliminating the need for IP‑based rules.
51. How to create a VNet rule?
az sql server vnet-rule create -g myRG -s myServer -n myVnetRule --vnet-name myVnet --subnet mySubnet
52. What is Azure Private Link?
Provides private IP connectivity to Azure SQL over a private endpoint, completely isolating traffic.
53. How to enable Transparent Data Encryption (TDE)?
Encryption at rest is enabled by default. You can bring your own key (BYOK) using Azure Key Vault.
54. How to set up customer‑managed TDE with Key Vault?
In server → Transparent Data Encryption → select “Customer‑managed key” and choose a key from Key Vault. Ensure server has Get, Wrap, Unwrap permissions.
55. What is Always Encrypted?
Encrypts sensitive data at the column level, with keys never revealed to the database engine. Encryption/decryption happens on the client.
56. How to create a column master key in Always Encrypted?
Using SSMS or PowerShell: New-SqlColumnMasterKeySettings -KeyStoreProviderName 'AzureKeyVault' ...
57. What are the two types of encryption in Always Encrypted?
Randomized encryption (no operations allowed) and deterministic encryption (supports equality lookups, grouping, joins).
58. When to use deterministic vs randomized?
Deterministic for columns used in WHERE, GROUP BY, JOIN. Randomized for data that only needs to be retrieved, not searched.
59. What is Dynamic Data Masking (DDM)?
Hides sensitive data from unauthorized users by applying masking rules. Data is not encrypted; it’s masked in query results.
60. How to define a masking rule?
ALTER TABLE Customers ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');
61. How to grant unmask permission?
GRANT UNMASK TO [privilegedUser];
62. What is Row‑Level Security (RLS)?
Restricts row access based on user identity. Implemented with a table‑valued function and a security policy.
63. How to implement RLS?
Create a function that returns 1 for rows the user can see, then:
CREATE SECURITY POLICY SalesFilter ADD FILTER PREDICATE dbo.fn_securitypredicate(UserName) ON dbo.Sales;
64. How does RLS work for Azure AD users?
Use SUSER_SNAME() or USER_NAME() to get the current user’s identity and filter accordingly.
65. What is Advanced Threat Protection (ATP)?
Detects anomalous activities indicating potential security threats, like SQL injection, brute force, or data exfiltration.
66. How to enable ATP?
In server → Microsoft Defender for Cloud → enable “Microsoft Defender for SQL”. Configure alerts.
67. What is vulnerability assessment?
Scans databases for security misconfigurations, excessive permissions, and unprotected data, providing actionable recommendations.
68. How to schedule regular vulnerability assessments?
In the portal, enable “Periodic recurring scans” and provide an email or storage account for reports.
69. What is a ledger database in Azure SQL?
Provides cryptographic data integrity with append‑only tables, proving that data hasn’t been tampered with.
70. What are the two ledger table types?
Updatable ledger tables (with history) and append‑only ledger tables.
71. How to create an updatable ledger table?
CREATE TABLE [AccountBalance] ( [AccountID] int, [Balance] money ) WITH (LEDGER = ON (APPEND_ONLY = OFF));
72. What is the database ledger digest?
A cryptographic summary of all committed transactions, stored externally (Azure Blob) to verify integrity.
73. How to enable ledger digest?
ALTER DATABASE SCOPED CREDENTIAL ... -- to store in blob ALTER DATABASE [DB] SET LEDGER = ON;
74. What is Azure SQL auditing?
Tracks database events and writes them to an audit log in Azure Storage, Log Analytics, or Event Hub.
75. How to configure auditing at the server level?
In server → Auditing → enable, select storage destination. All databases inherit server settings.
76. What is the difference between server‑level and database‑level auditing?
Server‑level captures events for all databases. Database‑level allows granular per‑database configuration.
77. What are the key audit action groups?BATCH_COMPLETED_GROUP, SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP, FAILED_DATABASE_AUTHENTICATION_GROUP, DATABASE_OBJECT_CHANGE_GROUP, etc.
78. How to query audit logs stored in Azure Storage?
Use the Azure portal Log Analytics integration or directly download blob files and import into SQL or Power BI.
79. What is “Data discovery & classification”?
A built‑in tool that discovers and labels sensitive columns (e.g., credit card, SSN) and recommends masking or encryption.
80. How to run a data classification scan?
In the portal → Data Discovery & Classification → “Classify your data” to view recommendations and apply labels.
📈 3. Monitor & Optimize Operational Resources (81–120)
81. What is Azure Monitor metrics for Azure SQL?
Platform metrics like CPU percentage, DTU used, IO percentage, storage space. Collected automatically, visualised in portal.
82. How to create an alert on high CPU?
Azure Monitor → Alerts → New alert rule → resource = SQL database, condition = cpu_percent > 80, action group to email.
83. What is Intelligent Insights?
An AI‑powered performance diagnostics tool that identifies database performance slowdowns, with root cause analysis.
84. What is Automatic Tuning in Azure SQL?
Automatically applies performance recommendations like force plan, create/drop indexes, based on your preferences.
85. How to enable automatic plan correction?
In database → Automatic tuning → set “Force Plan” to ON, and optionally “Create Index” and “Drop Index”.
86. What is Query Store?
A built‑in repository that captures query execution plans, runtime stats, and wait statistics for troubleshooting.
87. How to enable Query Store?
ALTER DATABASE current SET QUERY_STORE = ON; ALTER DATABASE current SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
88. What are the key Query Store reports?
Top Resource Consuming Queries, Regressed Queries, Queries with High Variation, Tracked Queries.
89. How to find a query that suddenly became slow using Query Store?
Use the “Regressed Queries” report – it compares recent performance to a baseline.
90. What is the Query Performance Insight tool in the portal?
A simplified version of Query Store showing top queries by CPU, duration, and IO, with drill‑down into plans.
91. What is the “auto‑index” feature?
Part of Automatic Tuning, it identifies missing indexes, tests them, and if beneficial, applies them (and rolls back if regressed).
92. What are Wait Statistics and why are they important?
Wait stats show where queries spend time waiting (locks, IO, CPU). They help identify bottlenecks like PAGEIOLATCH, LCK_M_X.
93. How to capture wait statistics using DMVs?
SELECT * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC;
94. What is the “sys.dm_db_resource_stats” DMV?
Returns CPU, IO, memory usage aggregated every 15 seconds, useful for diagnosing resource pressure.
95. How to monitor elastic pool resource usage?
Use sys.elastic_pool_resource_stats DMV or portal metrics for pool‑level eDTU/vCore consumption.
96. What is the “SQL Analytics” solution in Log Analytics?
Collects telemetry from multiple databases (including Query Store data) into a centralised workspace for cross‑server monitoring.
97. How to set up log shipping for SQL Managed Instance?
Managed Instance does not support traditional log shipping; use auto‑failover groups or Azure SQL Managed Instance link to SQL Server 2022.
98. What is the “tempdb” configuration in Azure SQL?
In Managed Instance, tempdb size and number of files can be configured. In SQL DB, it’s managed automatically but you can scale files by vCores.
99. How to check tempdb contention?
Query sys.dm_tran_active_transactions and sys.dm_os_waiting_tasks for PAGELATCH_* waits in tempdb.
100. What is “buffer pool extension” and is it available?
Not in Azure SQL DB; use Hyperscale or memory‑optimised objects for large data sets.
101. How to monitor database storage space?
Use the portal metrics storage, storage_percent, or sys.dm_db_file_space_usage.
102. How to shrink a database file?
DBCC SHRINKFILE (logical_file_name, target_size_MB);
Shrinking causes fragmentation; only use when necessary.
103. What is “intelligent query processing” (IQP)?
A family of features (adaptive joins, memory grant feedback, interleaved execution) that improve query performance automatically.
104. What is memory grant feedback?
If a query gets too much or too little memory, IQP adjusts grants in subsequent executions.
105. How does automatic plan correction relate to forced plans?
The system detects plan regression and forces the previously good plan from Query Store.
106. What is the “sp_query_store_force_plan” procedure?
Manually force a plan: EXEC sp_query_store_force_plan @query_id, @plan_id;
107. What is the “sys.dm_exec_query_stats” DMV?
Returns aggregate performance stats for cached query plans, including execution count, total CPU, total IO.
108. How to find queries with the highest CPU usage in the last hour?
SELECT TOP 10 query_hash, SUM(total_worker_time) AS total_cpu FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) GROUP BY query_hash ORDER BY total_cpu DESC;
109. What is a “waiting task” and how to find the current waits?
Use sys.dm_exec_requests combined with sys.dm_os_waiting_tasks to see blocked tasks.
110. What is the “blocking session” and how to resolve?
Query sys.dm_exec_requests where blocking_session_id > 0. Kill the blocker if necessary after analysis.
111. How to enable automated backups with geo‑redundancy?
Set backup storage redundancy to “Geo‑redundant” in the database configuration.
112. How to restore a deleted database?
In the portal, go to the server → “Deleted databases”, select one, and restore to the point of deletion.
113. What is “Point‑in‑Time Restore” (PITR)?
Restore a database to any point within its retention period, creating a new database.
114. How to perform PITR using Azure CLI?
az sql db restore --dest-name NewDB --resource-group rg --server s --source-database origDB --time "2025-06-01T12:00:00"
115. What is “auto‑scale” in vCore model?
Not automatic; you can manually scale up/down vCores. Serverless tier does auto‑scale compute.
116. What is “Resource Governance” in Managed Instance?
Limits CPU, memory, and IO per database to ensure predictable performance.
117. How to configure resource limits for a database in Managed Instance?
ALTER DATABASE myDB MODIFY MAXSIZE = 500 GB; ALTER DATABASE myDB MODIFY (SERVICE_OBJECTIVE = 'GP_GEN5_8');
118. What is “database scoped configuration” for MAXDOP?
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;
119. How to set legacy cardinality estimation?
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
120. What is “sp_whoisactive” alternative in Azure SQL?
Use sys.dm_exec_requests, sys.dm_exec_sessions, and sys.dm_os_waiting_tasks. The stored procedure can be installed manually.
⚡ 4. Optimize Query Performance (121–160)
121. What are the key factors for query performance in Azure SQL?
Indexing, statistics, plan choice, concurrency, wait types, and resource capacity.
122. How to identify missing indexes?
Use DMV sys.dm_db_missing_index_details and sys.dm_db_missing_index_groups.
123. How to create an index with included columns?
CREATE NONCLUSTERED INDEX IX_orders_date ON orders (OrderDate) INCLUDE (Amount, CustomerID);
124. What is a filtered index?
An index with a WHERE clause, reducing size and improving performance for specific queries.
125. When to use a columnstore index?
For large fact tables in analytical queries with aggregations, improves compression and batch mode processing.
126. What is the difference between clustered and nonclustered columnstore?
Clustered columnstore is the table itself, nonclustered is a separate copy that can be combined with a rowstore base table.
127. What is a covering index?
An index that contains all columns referenced in a query, eliminating key lookups.
128. How to update statistics?
UPDATE STATISTICS dbo.Sales;
129. What is auto‑update statistics?
By default, statistics update when 20% of rows + 500 rows have changed. Can be configured with AUTO_UPDATE_STATISTICS and AUTO_UPDATE_STATISTICS_ASYNC.
130. How to check statistics last update?
SELECT name, stats_date(object_id, stats_id) FROM sys.stats WHERE object_id = OBJECT_ID('Sales');
131. What is parameter sniffing?
The plan is generated based on the first parameter value, which may be suboptimal for later executions.
132. How to fix parameter sniffing?
Use OPTION (RECOMPILE) or OPTION (OPTIMIZE FOR (@param UNKNOWN)) or query hint DISABLE_PARAMETER_SNIFFING.
133. What is the difference between RECOMPILE and OPTIMIZE FOR UNKNOWN?
RECOMPILE creates a new plan every execution; OPTIMIZE FOR UNKNOWN uses average density, avoiding sniffing but maintaining plan cache.
134. What is a “query plan guide”?
A mechanism to force a specific query plan or apply hints to queries without changing code.
135. How to create a plan guide?
EXEC sp_create_plan_guide @name = N'Guide1', @stmt = N'SELECT ...', @type = N'SQL', @module_or_batch = NULL, @hints = N'OPTION (OPTIMIZE FOR (@id = 5))';
136. What is the Adaptive Join feature?
Intelligent Query Processing can dynamically choose between hash join and nested loops based on actual row counts.
137. What is Scalar UDF inlining?
Automatically transforms inline scalar UDFs into scalar expressions, boosting performance.
138. How to find unused indexes?
Query sys.dm_db_index_usage_stats where user_seeks = 0 and user_scans = 0 and user_lookups = 0.
139. What is index fragmentation and how to detect?sys.dm_db_index_physical_stats returns avg_fragmentation_in_percent. Use REBUILD or REORGANIZE based on threshold.
140. How to rebuild an index online in Azure SQL?
ALTER INDEX IX_name ON table REBUILD WITH (ONLINE = ON);
Online rebuilds are available in Business Critical and Premium tiers; General Purpose may require resumable online index rebuild.
141. What is resumable online index rebuild?
A rebuild that can be paused and resumed, very useful for large tables. Available in all tiers.
142. How to pause/resume index rebuild?
ALTER INDEX IX_name ON table PAUSE; ALTER INDEX IX_name ON table RESUME;
143. What is the “key lookup” operation?
Occurs when a nonclustered index does not cover the query; it performs a clustered index seek for each row. Eliminate by including columns.
144. How to use the Database Engine Tuning Advisor (DTA) in Azure?
Export workload from Query Store, run DTA in on‑prem SSMS against a copy of the database, or use the portal’s performance recommendations.
145. What is “wait_info” in Query Store?
Captures wait categories per query, helping understand if a query waits on locks, IO, or memory.
146. How to analyze a query plan in Azure Data Studio?
Enable “Actual Execution Plan”, run query, then view graphical plan. Use the “Compare Execution Plans” extension.
147. What is “batch mode on rowstore”?
IQP allows batch mode execution on rowstore tables if certain conditions are met, improving performance.
148. What is “approximate query processing”?
Functions like APPROX_COUNT_DISTINCT provide fast, approximate results with minimal resource usage.
149. How to use APPROX_COUNT_DISTINCT?
SELECT APPROX_COUNT_DISTINCT(CustomerID) FROM Sales;
150. What is “table partitioning” in Azure SQL?
Splitting a large table across multiple filegroups (partitions) based on a column like date. Improves maintenance and query elimination.
151. How to implement partitioning?
Create partition function, partition scheme, then create table on scheme.
152. How to switch partitions for sliding window?
ALTER TABLE Sales SWITCH PARTITION 1 TO SalesArchive PARTITION 1;
153. What are “query performance insights” from the portal?
Top queries and automatic recommendations (force plan, create index, etc.).
154. How to export Query Store data for offline analysis?
Use sys.query_store_query and sys.query_store_plan to export to CSV, or use SSMS Query Store reports.
155. What is “execution plan reusability”?
Plans are cached; if a query changes (even spaces), it gets a new plan. Use parameterized queries for reuse.
156. What is the “plan cache” and how to clear?ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; (be careful).
157. How to measure IO latency impact on performance?
Check sys.dm_io_virtual_file_stats for io_stall_* metrics and compare with wait types PAGEIOLATCH_*.
158. What is “read‑only routing” in Managed Instance?
Automatically directs ApplicationIntent=ReadOnly connections to readable secondaries.
159. How to set up a readable secondary?
In Business Critical or Hyperscale, secondaries are built‑in; you can add more replicas and configure routing list.
160. What is “scale‑out” for Azure SQL?
Read scale‑out: using built‑in replicas for read‑only workloads without extra cost in Premium/Business Critical.
⚙️ 5. Automate Tasks (161–180)
161. What tools automate Azure SQL tasks?
Azure Automation runbooks, Azure Functions, Azure Logic Apps, Azure Data Factory, and Azure CLI/PowerShell.
162. How to automate index maintenance using Azure Automation?
Create a PowerShell runbook that connects to each database and runs Ola Hallengren’s scripts or custom REBUILD logic. Schedule it.
163. How to schedule a T‑SQL script without SQL Agent?
Use Azure Automation with a PowerShell runbook that invokes Invoke-SqlCmd, or Azure Logic Apps with SQL connector.
164. What is the “elastic database jobs” feature?
A service for running T‑SQL jobs across multiple Azure SQL databases and elastic pools.
165. How to create an elastic job?
Create a job agent, define target groups, and add jobs with T‑SQL scripts.
EXEC jobs.sp_add_job @job_name='WeeklyIndexMaintenance';
166. What is the difference between elastic jobs and SQL Agent on Managed Instance?
SQL Agent is only available on Managed Instance (and SQL Server). Elastic jobs are a cross‑database, cloud‑native alternative for SQL DB.
167. How to automate database copy for testing?
Use Azure PowerShell to run Copy-AzSqlDatabase or Restore-AzSqlDatabase with scheduled triggers.
168. How to trigger a runbook on a schedule?
Create a schedule in Azure Automation and link it to the runbook.
169. How to refresh a database from production to development using Azure DevOps?
Pipeline task: Azure PowerShell to copy/restore production database to dev server, with parameter overrides.
170. What is the “Auto-Pause” feature in serverless?
Automatically pauses the database after a specified period of inactivity; resumes on next login.
171. How to configure auto‑pause via ARM template?
Set autoPauseDelay property in the database resource.
172. How to automate the export of a database to a .bacpac file?
Use PowerShell New-AzSqlDatabaseExport or Azure CLI az sql db export. Store in blob storage.
173. What is the “Azure SQL Database DevOps” capability?
Integrate with source control, CI/CD pipelines to deploy schema changes via DacPac or SQL scripts.
174. How to use Azure CLI in a DevOps pipeline for SQL tasks?
Azure CLI task with az sql db ... commands. Use service connection for authentication.
175. How to automate vulnerability assessment scans?
Enable periodic scans and email notifications, or use REST API / PowerShell to trigger scans on demand.
176. What is “Azure Policy” for Azure SQL?
Enforces compliance rules (e.g., auditing enabled, firewall restrictions) across all resources.
177. How to assign a policy to require TDE?
Built‑in policy “Transparent Data Encryption on SQL databases should be enabled” can be assigned at subscription level.
178. How to automate long‑term retention backups?
Set LTR policy once and it automatically takes backups; you can also script Set-AzSqlDatabaseBackupLongTermRetentionPolicy.
179. What is “database watcher” in Azure SQL?
A monitoring solution (in preview) that collects detailed workload data for advanced analysis and remediation.
180. How to use Event Grid for automation?
Azure SQL can send events (e.g., new database created) to Event Grid, triggering Azure Functions or Logic Apps.
🛡️ 6. Plan & Implement High Availability & Disaster Recovery (181–200)
181. What high‑availability options exist for Azure SQL Database?
General Purpose: remote storage with local compute (availability 99.99%).
Business Critical: Always On Availability Groups with local SSD (99.995%).
Hyperscale: separate compute and storage with multiple compute replicas (99.995%).
182. How does Business Critical achieve HA?
Three secondary replicas (one of which can be used for read scale‑out) synchronously commit transactions.
183. What is the SLA for Azure SQL Database?
99.99% (General Purpose), 99.995% (Business Critical and Hyperscale). For managed instance same.
184. How to design for disaster recovery across regions?
Use active geo‑replication or auto‑failover groups. Ensure application connection string uses failover group listener.
185. What is the Recovery Time Objective (RTO) and Recovery Point Objective (RPO) for geo‑replication?
RTO is minutes (manual failover). RPO can be zero for synchronous secondaries (but that is within region). For geo, async replication has RPO < 5 seconds typically.
186. How to test a failover without impacting production?
Use the “planned failover” of a failover group during maintenance window, or create a geo‑replica and test failover to a new server.
187. What is “forced failover”?
When the primary region is down, you can force failover to the secondary with possible data loss because the secondary is async.
188. How to perform a forced failover using PowerShell?
Switch-AzSqlDatabaseFailoverGroup -ResourceGroupName $rg -ServerName $secondaryServer -FailoverGroupName $fg
189. What is “zone‑redundant” configuration?
Places database replicas across Availability Zones in the same region, protecting against zone outages. Available in Business Critical and Premium.
190. How to configure zone redundancy for Managed Instance?
During creation, choose “Zone redundancy enabled”. Not all regions support it.
191. What is “failback” after a forced failover?
Once primary region is back, you can fail back by doing another planned failover from the current primary.
192. What is “read scale‑out” and how to enable?
It allows read‑only workloads on secondary replicas. In Business Critical and Hyperscale, it’s automatic. In General Purpose, it’s not available.
193. What is the purpose of a “maintenance window” in HA design?
To align planned maintenance with low‑activity periods, reducing unplanned failover impact.
194. How to design an architecture for 99.995% availability?
Use Business Critical or Hyperscale within a single region, plus a geo‑replica in another region with auto‑failover group.
195. What is “backup storage redundancy”?
Choose locally redundant (LRS), zone‑redundant (ZRS), or geo‑redundant (GRS) for automatic backups. GRS ensures backups survive regional outage.
196. How to restore a database from a geo‑redundant backup if the primary region is down?
Perform a geo‑restore to any server in another region using Azure portal or az sql db restore with --source-deleted or --time pointing to the backup.
197. What is “accelerated database recovery” (ADR)?
Reduces recovery time by persisting version stores, enabling instant rollback of long‑running transactions.
198. How to enable ADR?
ALTER DATABASE current SET ACCELERATED_DATABASE_RECOVERY = ON;
On by default for new databases.
199. What is the “sql instance link” for Managed Instance?
A feature that connects SQL Server 2022 to Azure SQL Managed Instance for hybrid DR and near real‑time replication.
200. How to plan for disaster recovery in a multi‑region, multi‑database application?
Group databases into failover groups per service tier, ensure all dependencies (e.g., Azure AD, Key Vault) are available in DR region, automate DNS updates, and regularly perform DR drills.
🔥 7. Real‑World Scenario‑Based Questions (201–220 – Bonus Deep Dives)
These are the questions that separate the book‑smart from the battle‑tested. Each answer shows the approach, the tools, and the outcome.
201. Scenario: A critical production database suddenly hits 100% CPU, causing timeouts. Walk through your troubleshooting.
Connect with DAC (Dedicated Admin Connection) if possible.
Check
sys.dm_exec_requestsfor long‑running queries,blocking_session_id.Look at
sys.dm_os_wait_statsfor high CPU signals likeSOS_SCHEDULER_YIELD.Query
sys.dm_exec_query_statsfor top CPU consumers in the last minutes.If one query is identified, examine its plan with
sys.dm_exec_query_plan.As a quick mitigation, run
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHEif plan regression, or kill blocking session.Long‑term: add missing indexes, update statistics, and consider adjusting MAXDOP or forced plan via Query Store.
202. Scenario: After deploying a schema change, a critical report query runs 10x slower. How do you fix it?
Check Query Store for plan regression. Compare the before and after plans. If the new plan is suboptimal, force the previous good plan using sp_query_store_force_plan. If the schema change introduced a needed adjustment, provide proper indexing or rewrite the query. Monitor and confirm.
203. Scenario: You need to migrate an on‑premises SQL Server database with 2 TB of data to Azure SQL Managed Instance with minimal downtime.
Use Azure Database Migration Service (DMS) with offline migration (if downtime acceptable) or online migration for near‑zero downtime. Configure backup to URL and restore to Managed Instance using native backup/restore, or use transactional replication. For minimal downtime, use Azure DMS online migration with continuous sync.
204. Scenario: Your Azure SQL database receives frequent brute‑force login attempts from unknown IPs. What do you do?
Enable Advanced Threat Protection. Configure firewall rules to block malicious IPs dynamically. Consider using Azure Firewall or NSG to restrict access to only known VNet IPs. Enforce Azure AD authentication with conditional access policies (if using Azure AD). Set up alert on many failed logins.
205. Scenario: You have a read‑heavy workload and want to offload reporting queries to a secondary without changing application connection strings.
For Business Critical, enable read scale‑out and configure the listener with ApplicationIntent=ReadOnly in connection strings for reporting apps. Alternatively, use Geo‑replication to create a readable secondary in same region, but applications must connect to it separately. For seamless offloading, use read scale‑out.
206. Scenario: Users complain that a dashboard query sometimes takes 2 seconds, sometimes 30 seconds. Same parameters.
Parameter sniffing. Capture the query from Query Store, identify the two plans. Use sp_query_store_force_plan to force the fast one, or add OPTION (OPTIMIZE FOR UNKNOWN) if minor variation acceptable. Consider adding a covering index to make both fast.
207. Scenario: A stored procedure inserts millions of rows into a heap table and then queries it. The query is slow. Fix it.
Heap tables cause table scans. Add a clustered index on the join/filter column after the insert, or better, stage data in a temporary table with index and then swap/insert. Or use a table with a clustered columnstore index if analytics.
208. Scenario: You need to ensure that a database can be restored to any point within the last 35 days and that backups survive region outage.
Set backup retention to 35 days, and choose geo‑redundant backup storage (GRS). Configure long‑term retention (LTR) for monthly backups for longer compliance.
209. Scenario: An application using Entity Framework generates many small ad‑hoc queries, causing CPU spikes. How to reduce load?
Enable Automatic Tuning (force plan). Use Query Performance Insights to identify top queries and add missing indexes. Consider using a read‑only replica for reporting, and use database caching or a results cache (e.g., Redis) for repeated queries.
210. Scenario: Your managed instance needs to run a SQL Agent job that depends on a file in Azure Blob Storage. How to set up secure access?
Create a credential with a Shared Access Signature (SAS) or Managed Identity. Use Azure AD authentication for the Managed Identity to access storage, assign Storage Blob Data Reader role to the instance’s identity. Then in SQL Agent, run BULK INSERT or use OPENROWSET.
211. Scenario: A developer accidentally drops a table in production. Restore is needed with minimal data loss and without restoring the entire database.
Use point‑in‑time restore to a new database to the moment before the drop. Then copy the dropped table from the restored database to the original one using SELECT INTO or export/import. Drop the temporary database.
212. Scenario: You need to implement data masking for a call centre application where agents can see only the last four digits of SSN.
Use Dynamic Data Masking: ALTER TABLE Customers ALTER COLUMN SSN ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)'); Grant UNMASK to supervisors.
213. Scenario: A critical slow query cannot be changed because it’s from a vendor application. What can you do?
Use Query Store to force a better plan. Or create a plan guide with a OPTION (RECOMPILE) or hint. If indexing is allowed, add a covering index. If the vendor forbids, escalate to vendor.
214. Scenario: You want to ensure that an Azure SQL database and its failover group are created with the same configuration across dev, test, prod using IaC.
Use Bicep or Terraform to define the server, databases, failover groups, and maintain in source control. Automate deployment via DevOps pipelines.
215. Scenario: After migrating to Azure SQL, a legacy application that uses sp_xp_cmdshell fails. What alternative?xp_cmdshell is not available in Azure SQL DB (only Managed Instance with caution). Replace with Azure Elastic Jobs, Azure Functions, or PowerShell runbook that performs the OS operation externally and updates the database.
216. Scenario: How to keep two Azure SQL databases in different regions synchronised bidirectionally?
Use SQL Data Sync (Preview) for bidirectional sync. It’s not recommended for high‑volume transactional workloads due to latency and conflict resolution. For one‑way, active geo‑replication.
217. Scenario: A database experiences sudden IO throttling, causing queries to wait on PAGEIOLATCH_*. What can you do?
IO is governed by DTU/vCore tiers and limits. Scale up to a higher service objective with more IOPS, or switch to Business Critical for local SSD. Check if queries are scanning large tables; add indexes.
218. Scenario: You need to encrypt the Salary column in a way that even the DBA can’t see it, but the application can still do equality searches.
Use Always Encrypted with deterministic encryption. Store column master key in Azure Key Vault. Application uses a driver that performs encryption/decryption.
219. Scenario: A database participates in an elastic pool, but one “noisy” database is consuming most resources and impacting others.
Set per‑database resource governance limits: ALTER DATABASE noisyDB MODIFY (MAX_CPU_PERCENT = 30) or adjust pool limits. Use sys.elastic_pool_resource_stats to identify.
220. Scenario: You need to deploy SQL scripts to multiple environments with rollback capability using Azure DevOps.
Use a CI/CD pipeline with a DacPac deployment (SqlAzureDacpacDeployment task). DacPac tracks state and can generate rollback scripts. Alternatively, use a migration tool with versioning.
✨ Final Words from @FreeLearning365
Becoming an Azure Data Engineer is a journey of continuous learning and practical problem‑solving. The DP‑300 exam validates that you can administer Azure SQL solutions in production, but true mastery comes from handling the surprises – the 2am CPU spike, the failed migration, the query that suddenly crawls. This guide is your companion for interviews and for the job itself.
Keep building, keep breaking things in dev, and always share your knowledge. That’s how we all level up.
If this guide helped you, share it with your network, tag @FreeLearning365 and @techbook24, and let’s keep the learning alive. 🚀
#AzureDataEngineer #DP300 #AzureSQL #DataEngineering #CloudInterview #FreeLearning365 #TechBook
No comments:
Post a Comment
Thanks for your valuable comment...........
Md. Mominul Islam