63 Practice Questions & Answers
You are managing an Azure SQL Database instance with Hyperscale pricing tier. A user reports slow query performance during peak hours. Which monitoring solution should you implement first to identify performance bottlenecks?
-
A
Increase the DTU allocation immediately without analysis
-
B
Migrate to a dedicated SQL pool in Azure Synapse
-
C
Enable Query Store and review top resource-consuming queries
✓ Correct
-
D
Configure alerts only for CPU usage above 90%
Explanation
Query Store provides detailed insights into query performance metrics and resource consumption, making it the primary tool for identifying performance bottlenecks. This should be analyzed before making any scaling decisions.
Your organization requires all Azure SQL Database backups to be retained for 7 years for compliance purposes. What is the most cost-effective approach to achieve this requirement?
-
A
Manually export databases to Azure Blob Storage every week
-
B
Maintain multiple read-only replicas across regions
-
C
Configure point-in-time restore (PITR) for 2555 days
-
D
Use long-term retention (LTR) policies with weekly full backups stored in geo-redundant storage
✓ Correct
Explanation
Long-term retention (LTR) is specifically designed for extended backup retention requirements and provides cost-effective storage in Azure Blob Storage while maintaining compliance with retention policies.
You need to ensure that an Azure SQL Managed Instance can communicate securely with an on-premises SQL Server using a hybrid connection. Which networking configuration is required?
-
A
Deploy a self-hosted integration runtime only
-
B
Use Azure Service Bus relay without any VPN configuration
-
C
Enable public endpoint on the Managed Instance and configure firewall rules
-
D
Configure a VNet with appropriate subnet and establish a Site-to-Site VPN or ExpressRoute connection
✓ Correct
Explanation
Azure SQL Managed Instance requires proper VNet integration and a secure connection method like Site-to-Site VPN or ExpressRoute for on-premises connectivity, ensuring encrypted communication.
A critical production database experiences sudden performance degradation. You suspect blocking is occurring. Which dynamic management view (DMV) should you query to identify blocking chains?
-
A
sys.dm_exec_sessions and sys.dm_exec_requests to correlate session and blocking information
✓ Correct
-
B
sys.dm_server_memory_clerks for memory allocation tracking
-
C
sys.dm_db_index_usage_stats to review index usage patterns
-
D
sys.dm_io_virtual_file_stats for I/O analysis only
Explanation
Querying sys.dm_exec_sessions and sys.dm_exec_requests together allows you to identify blocking chains by correlating session IDs with blocking_session_id values, enabling quick resolution of blocking issues.
You are implementing a disaster recovery solution for Azure SQL Database. The Recovery Time Objective (RTO) is 1 hour and Recovery Point Objective (RPO) is 5 minutes. Which approach best meets these requirements?
-
A
Rely on automated backups with point-in-time restore
-
B
Use active geo-replication with automatic failover groups
✓ Correct
-
C
Use geo-redundant storage (GRS) for backups only
-
D
Configure manual backups to secondary region weekly
Explanation
Active geo-replication with automatic failover groups provides near-continuous replication (RPO of seconds), meeting the 5-minute RPO requirement, and enables quick failover to meet the 1-hour RTO.
An Azure SQL Database is experiencing high DTU consumption. You need to identify which applications are consuming resources. What should you implement?
-
A
Application Insights integration with SQL Database and analyze query dependencies
✓ Correct
-
B
Increase DTU limits without investigation
-
C
Disable all non-essential background jobs immediately
-
D
Only monitor CPU percentage in Azure Monitor alerts
Explanation
Application Insights provides end-to-end visibility into application dependencies and database query performance, helping identify which applications are consuming resources and enabling targeted optimization.
You need to configure transparent data encryption (TDE) for an Azure SQL Database. Where should the encryption key be stored for optimal security?
-
A
In a SQL Server credential stored in master database
-
B
In the application configuration file
-
C
In Azure Blob Storage with shared access signature (SAS) token
-
D
In Azure Key Vault as a customer-managed key (BYOK)
✓ Correct
Explanation
Customer-managed keys stored in Azure Key Vault provide the highest security for TDE, enabling key rotation, audit logging, and compliance with regulatory requirements like HIPAA and PCI-DSS.
Your organization is consolidating multiple on-premises SQL Server instances into Azure SQL Managed Instance. What is the primary advantage of using Managed Instance over Azure SQL Database for this scenario?
-
A
Lower cost compared to Azure SQL Database
-
B
No need for any migration planning or schema changes
-
C
Automatic scaling without any manual intervention
-
D
Native support for SQL Server features like SQL Agent, CLR, and linked servers with minimal application changes
✓ Correct
Explanation
Azure SQL Managed Instance provides near 100% SQL Server compatibility, supporting features like SQL Agent, CLR, and linked servers that are not available in Azure SQL Database, reducing migration complexity.
You are troubleshooting high CPU usage on an Azure SQL Database. The database is using vCore-based pricing tier. Which action should be your first step?
-
A
Immediately upgrade to a higher vCore tier without analysis
-
B
Switch to DTU-based pricing tier for automatic scaling
-
C
Review Query Store data and execution plans to identify poorly performing queries before scaling
✓ Correct
-
D
Disable all indexes to reduce CPU overhead
Explanation
Query Store analysis should precede any scaling decisions to identify optimization opportunities (missing indexes, query plan regressions) that may resolve the issue more cost-effectively than scaling.
An Azure SQL Managed Instance requires network connectivity to multiple on-premises data centers across different regions. What is the recommended networking approach?
-
A
Deploy all instances in a single region and use VPN tunnels only
-
B
Configure site-to-site VPN to one region and rely on inter-region peering
-
C
Use a single Managed Instance with public endpoint and configure firewall rules for all on-premises IPs
-
D
Deploy multiple Managed Instances in different Azure regions connected via private Link and establish ExpressRoute from each region to corresponding on-premises data centers
✓ Correct
Explanation
This architecture provides redundancy, low-latency connectivity, and security by using regional Managed Instances with Private Link and dedicated ExpressRoute connections, meeting multi-region requirements.
You need to implement row-level security (RLS) on an Azure SQL Database table containing sensitive customer data. What is a key consideration when designing the RLS policy?
-
A
RLS policies automatically encrypt data at rest without additional configuration
-
B
The predicate function must efficiently filter rows using indexed columns to avoid performance degradation
✓ Correct
-
C
RLS policies can only be applied to tables, not views
-
D
Performance impact is negligible regardless of table size or predicate complexity
Explanation
RLS predicate performance is critical; poorly written predicates can significantly impact query performance. Using indexed columns and sargable predicates ensures the policy doesn't become a bottleneck.
An Azure SQL Database contains financial data that must comply with GDPR requirements. A user requests deletion of their personal information. What is the best approach to handle this requirement?
-
A
Export the entire database and manually remove rows from the backup
-
B
Disable the user's login immediately without removing actual data
-
C
Restore from a backup to an earlier point before the user's data was created
-
D
Implement a data retention and purging strategy using update or delete statements, and document changes in audit logs
✓ Correct
Explanation
GDPR compliance requires actual deletion of personal data with audit trails. Implemented update/delete strategies with proper logging provide a compliant and auditable approach to data deletion.
You are configuring SQL Server Agent jobs on Azure SQL Managed Instance. A job fails intermittently. Where should you check for error details?
-
A
SQL Agent job history, msdb database error logs, and Azure activity logs
✓ Correct
-
B
Azure Portal metrics for the instance only
-
C
Only the Windows Event Viewer on the client machine
-
D
The default SQL Server error log in the Binn directory
Explanation
Azure SQL Managed Instance stores SQL Agent job history in msdb, provides detailed error logs, and records job execution details in Azure activity logs, providing comprehensive diagnostic information.
Your organization requires database indexing strategy to improve query performance on an Azure SQL Database. Which approach is most effective for identifying missing indexes?
-
A
Use DMV sys.dm_db_missing_index_details combined with Query Store data to identify high-impact missing indexes
✓ Correct
-
B
Create indexes for all columns used in WHERE clauses automatically
-
C
Monitor transaction logs for frequently accessed columns
-
D
Request all developers to specify required indexes during design phase
Explanation
The sys.dm_db_missing_index_details DMV combined with Query Store provides data-driven recommendations for missing indexes, showing estimated improvement impact and helping prioritize index creation.
An Azure SQL Database experiences deadlocks during peak transaction processing. What is the recommended first troubleshooting step?
-
A
Migrate to a higher pricing tier to increase concurrency limits
-
B
Enable trace flag 1222 to capture detailed deadlock graph information in the error log for analysis
✓ Correct
-
C
Immediately increase the number of database connection pools
-
D
Switch the database to single-user mode to prevent concurrent transactions
Explanation
Trace flag 1222 captures the deadlock graph, showing which transactions and objects are involved in deadlocks, enabling root cause analysis and targeted resolution of the deadlock issue.
You need to implement a backup strategy for Azure SQL Database that balances cost and compliance. The backup retention requirement is 30 days. Which configuration is most appropriate?
-
A
Use transaction log backups every 5 minutes with no differential backups
-
B
Manually perform daily full backups and store in Azure Blob Storage indefinitely
-
C
Enable automated backups with 30-day retention; use point-in-time restore for compliance needs within the retention period
✓ Correct
-
D
Disable automated backups and rely on geo-replication only
Explanation
Azure SQL Database automated backups with configurable retention meet the 30-day requirement cost-effectively, and point-in-time restore capability enables recovery to any point within the retention period.
An Azure SQL Managed Instance is configured with a virtual network. You need to ensure that certain applications cannot directly connect to the instance. What should you implement?
-
A
Use only named pipes without network connectivity
-
B
Configure Network Security Group (NSG) rules to restrict inbound traffic to specific application subnets on port 1433
✓ Correct
-
C
Remove the public endpoint without implementing subnet-level restrictions
-
D
Disable the TCP/IP protocol on the instance completely
Explanation
NSG rules provide granular control over network access, allowing you to restrict connections to the Managed Instance on port 1433 to only authorized application subnets while maintaining necessary connectivity.
You are configuring Always Encrypted for sensitive customer data in Azure SQL Database. A developer needs to query encrypted data in their application. What key consideration must be addressed?
-
A
Always Encrypted requires database-side decryption for all queries
-
B
Encryption keys should be shared across all developer workstations in plaintext
-
C
The application must store Column Master Key (CMK) and have proper key store access to decrypt data at the client side
✓ Correct
-
D
The SQL Server must store all encryption keys in the database itself
Explanation
Always Encrypted requires client-side decryption; applications must securely store and access the Column Master Key from Azure Key Vault or other key stores to decrypt data, maintaining end-to-end encryption.
Your organization is monitoring Azure SQL Database performance using Azure Monitor. You need to set up an alert for when average CPU exceeds 80%. How should this be configured?
-
A
Use Query Store alerts for CPU-intensive queries only
-
B
Create a metric alert in Azure Monitor with CPU percentage metric and set threshold to 80% with appropriate aggregation and time window
✓ Correct
-
C
Configure SQL Server alerts using sp_add_alert stored procedure
-
D
Enable Performance Insights without configuring any specific thresholds
Explanation
Azure Monitor metric alerts are the native Azure solution for monitoring SQL Database performance metrics like CPU, enabling proactive alerting with configurable thresholds, aggregation periods, and notification actions.
You are migrating a large on-premises SQL Server database to Azure SQL Database using Azure Database Migration Service (DMS). The database contains 5 TB of data. What is the most important pre-migration consideration?
-
A
Migrate only tables without constraints to reduce migration time
-
B
Perform migration during business hours without planning downtime
-
C
Validate source and target schema, assess compatibility issues, and plan for minimal downtime using online migration if possible
✓ Correct
-
D
Simply copy all data without schema validation
Explanation
Pre-migration validation of schema compatibility, data assessment, and choosing online vs. offline migration strategy based on downtime tolerance are critical for successful large-scale database migration.
An Azure SQL Database maintains connection pools for web application servers. During maintenance windows, you need to gracefully handle active connections. What approach should you use?
-
A
Close the database immediately without notice to active users
-
B
Wait indefinitely for all connections to close naturally without taking action
-
C
Use the KILL command with NOCHECK option to terminate connections, but recommend application retry logic to recover
✓ Correct
-
D
Migrate all data to a new database instance instantly
Explanation
KILL NOCHECK terminates connections without waiting for rollback, suitable for maintenance. Combined with application-side retry logic and connection pooling, this enables graceful handling of maintenance windows.
You need to audit all data modifications in an Azure SQL Database table containing financial records. What is the most comprehensive auditing approach?
-
A
Use row version triggers only for identifying changes
-
B
Manually log all changes in an application-side audit table
-
C
Enable both SQL Audit at the server level and Transparent Data Encryption, and configure change tracking for specific tables requiring detailed modification history
✓ Correct
-
D
Rely on transaction log backups for audit purposes without other configuration
Explanation
SQL Audit provides comprehensive action logging for compliance, while Change Tracking captures modification details efficiently. Together, they provide audit trails for financial regulatory compliance.
An Azure SQL Managed Instance experiences intermittent high memory usage. You need to investigate whether TempDB is causing the issue. Where should you check first?
-
A
Increase TempDB size without investigating cause
-
B
Restart the instance immediately to clear TempDB
-
C
Check Azure Portal CPU metric only
-
D
Query sys.dm_db_file_space_usage and sys.dm_db_session_space_usage DMVs to analyze TempDB consumption and identify consuming sessions
✓ Correct
Explanation
These DMVs provide detailed information about TempDB space usage by session and object, enabling identification of memory-consuming operations before taking corrective action.
You are implementing a read-only replica strategy for an Azure SQL Database serving heavy reporting workloads. Which replication approach allows real-time data visibility while offloading queries from the primary instance?
-
A
Export data to Azure Data Warehouse nightly
-
B
Use query hints to force read operations to use slower I/O paths
-
C
Use read-only secondary replicas through active geo-replication or elastic pools with read-only endpoints
✓ Correct
-
D
Create a scheduled copy of the database daily
Explanation
Active geo-replication and read-only secondary replicas provide near real-time data visibility with dedicated read-only endpoints, allowing reporting queries to run independently without impacting primary instance performance.
An Azure SQL Database backup fails intermittently with storage account errors. What should you verify first?
-
A
Switch to a different backup method without troubleshooting
-
B
Contact Microsoft support immediately without investigating
-
C
Manually delete all previous backups to free space
-
D
Ensure the storage account has sufficient capacity, proper access permissions, and network connectivity to the Azure SQL Database instance
✓ Correct
Explanation
Storage capacity, IAM permissions, and network accessibility are common backup failure causes. Verifying these prerequisites often resolves intermittent backup failures without requiring support escalation.
You need to configure automatic tuning for an Azure SQL Database. What are the key components that should be monitored and automatically adjusted?
-
A
Database restart frequency adjustment
-
B
Manual index creation only without automation
-
C
Automatic plan correction for query plan regressions, index creation and removal based on workload patterns, and parameter tuning for improved execution
✓ Correct
-
D
User login frequency monitoring
Explanation
Azure SQL Database automatic tuning monitors query performance, automatically creates beneficial indexes, removes unused indexes, and applies plan corrections to maintain consistent query performance.
An Azure SQL Database needs to isolate sensitive data access from standard application users. Which feature should be implemented alongside role-based access control?
-
A
Disable views and use only direct table access
-
B
Dynamic Data Masking (DDM) to mask sensitive columns and restrict visibility based on user roles and context
✓ Correct
-
C
Separate the data into multiple unrelated databases
-
D
Increase password complexity requirements only
Explanation
Dynamic Data Masking masks sensitive data values (SSN, credit cards) in query results based on user roles, providing data protection without modifying stored data or creating separate database structures.
You need to monitor Azure SQL Database performance and receive alerts when CPU usage exceeds 80%. Which Azure service should you use?
-
A
Azure Monitor with metric alerts
✓ Correct
-
B
SQL Server Management Studio alerts
-
C
Azure Service Health
-
D
Azure Advisor recommendations only
Explanation
Azure Monitor is the primary service for creating metric-based alerts on Azure SQL Database performance metrics like CPU usage. SQL Server Management Studio alerts are for on-premises SQL Server instances.
When configuring Azure SQL Database firewall rules, what is the purpose of the default firewall rule that denies all traffic?
-
A
To automatically block distributed denial-of-service attacks
-
B
To prevent accidental data exposure by requiring explicit allow rules
✓ Correct
-
C
To reduce the number of audit log entries
-
D
To ensure backup operations can still proceed
Explanation
The default deny-all firewall stance in Azure SQL Database follows the principle of least privilege, requiring administrators to explicitly define which IP addresses or services can connect.
You are implementing a disaster recovery strategy for a mission-critical Azure SQL Database. Which replication option provides the lowest recovery time objective (RTO)?
-
A
Automatic failover groups with read-write replicas
✓ Correct
-
B
Long-term retention backups to Azure Blob Storage
-
C
Geo-replication with manual failover
-
D
Point-in-time restore with daily backups
Explanation
Automatic failover groups provide automatic failover with near-zero data loss and minimal RTO (typically under 5 minutes), whereas manual geo-replication requires intervention and long-term retention requires restore operations.
What is the primary advantage of using Azure SQL Database elastic pools for multiple databases with variable workloads?
-
A
They automatically partition data across multiple servers
-
B
They allow resource sharing among databases while maintaining performance isolation
✓ Correct
-
C
They provide lower backup costs by sharing backup storage
-
D
They eliminate the need for database-level encryption
Explanation
Elastic pools allow multiple databases to share compute resources (DTUs or vCores) while maintaining performance boundaries, optimizing costs for workloads with varying resource demands.
You need to implement row-level security (RLS) on an Azure SQL Database table to restrict users from viewing data for departments other than their own. Which component is essential for this implementation?
-
A
An Azure Key Vault access policy granting each user individual permissions
-
B
Azure AD conditional access policies at the tenant level
-
C
Transparent data encryption with a customer-managed key
-
D
A security predicate function that evaluates user context and filters rows accordingly
✓ Correct
Explanation
RLS in SQL Database relies on security predicate functions that automatically filter query results based on user context. TDE, Key Vault policies, and conditional access address different security concerns.
When troubleshooting a slow-running query in Azure SQL Database, you observe high CPU usage with low disk I/O. What is the most likely cause?
-
A
Inefficient query logic or complex joins consuming CPU resources
✓ Correct
-
B
Missing database indexes
-
C
Insufficient storage space causing query plan degradation
-
D
Network bandwidth saturation
Explanation
High CPU with low disk I/O suggests the query optimizer is performing intensive computation (such as complex joins or mathematical operations) rather than reading from storage. Missing indexes would typically show high disk I/O.
You are configuring advanced data security for an Azure SQL Database. Which feature provides real-time alerts when potentially malicious activities are detected?
-
A
SQL Server Agent jobs with custom monitoring scripts
-
B
Azure Policy compliance checks
-
C
Query Store historical analysis
-
D
Advanced Threat Protection (ATP)
✓ Correct
Explanation
Advanced Threat Protection detects anomalies and suspicious database activities in real-time, providing immediate alerts. SQL Server Agent, Azure Policy, and Query Store serve different monitoring purposes.
An organization requires that all data in their Azure SQL Database be encrypted at rest using a customer-managed key. Which Azure service should host the encryption key?
-
A
Azure Key Vault
✓ Correct
-
B
Application settings in Azure App Configuration
-
C
Azure Storage account
-
D
Azure Cosmos DB
Explanation
Azure Key Vault is the dedicated service for managing and storing encryption keys with controlled access, auditing, and rotation capabilities. Other services are not designed for key management.
You need to audit all data modifications in an Azure SQL Database for compliance purposes. Which auditing feature captures the most detailed information about data changes?
-
A
SQL Server audit with SCHEMA_OBJECT_ACCESS_GROUP for specific tables
-
B
Temporal tables with automatic time-travel queries
-
C
Change tracking to identify which rows were modified
-
D
Database-level auditing with object-level scope
✓ Correct
Explanation
Database-level auditing with object-level scope provides detailed logs of all SQL statements including INSERT, UPDATE, and DELETE operations. Change tracking shows what changed but not who or when; temporal tables enable querying historical data but aren't primarily for auditing compliance.
When migrating a large on-premises SQL Server database to Azure SQL Database, you need to minimize downtime during cutover. Which tool is specifically designed for this scenario?
-
A
SQL Server Management Studio backup and restore
-
B
bcp utility with parallel import
-
C
Azure Data Factory copy activities
-
D
Azure Database Migration Service (DMS) with minimal downtime migration
✓ Correct
Explanation
Azure DMS provides minimal downtime migration capability with continuous synchronization and automated cutover features. SSMS backup/restore, ADF, and bcp are batch operations that require longer service interruptions.
You are configuring backup retention for an Azure SQL Database and need to retain daily backups for 35 days for compliance. What is the maximum retention period available with long-term retention (LTR) backups?
-
A
35 days maximum
-
B
Up to 10 years with configured retention policies
✓ Correct
-
C
1 year maximum
-
D
90 days maximum
Explanation
Azure SQL Database long-term retention supports backup retention periods of up to 10 years through configurable policies (weekly, monthly, yearly retention options), exceeding the 35-day automatic retention limit.
An Azure SQL Database experiences connection pool exhaustion under peak load. Which configuration change would most directly address this issue?
-
A
Configure geo-replication to distribute connections across regions
-
B
Implement connection pooling at the application layer to reuse connections efficiently
✓ Correct
-
C
Increase the database compute tier to provide more connection capacity per tier
-
D
Enable Transparent Data Encryption to reduce connection overhead
Explanation
Connection pooling at the application layer reuses persistent connections, reducing the overhead of establishing new connections. Higher compute tiers don't increase connection limits; TDE doesn't affect connection pooling; geo-replication distributes data, not connections.
You need to implement dynamic data masking on a sensitive column in Azure SQL Database to hide values from non-privileged users. Which data masking function would you use to show only the first two characters of an email address?
-
A
DEFAULT masking function with asterisk replacement
-
B
PARTIAL masking function with 'email' domain
-
C
CUSTOM masking function with regular expression pattern
✓ Correct
-
D
EMAIL masking function with suffix replacement
Explanation
The CUSTOM masking function with regex patterns provides fine-grained control to show specific character positions. The EMAIL and DEFAULT functions provide less granular control; PARTIAL isn't a standard DDM function.
When analyzing slow query performance in Azure SQL Database, you examine the execution plan and notice a scan operation with high estimated subtree cost. What should be your first investigation step?
-
A
Migrate the database to a managed instance for better query optimization
-
B
Disable parameterized queries to allow the optimizer more flexibility
-
C
Review the query predicate and index strategy to determine if an index would reduce scans to seeks
✓ Correct
-
D
Immediately increase the compute tier to provide more CPU resources
Explanation
A scan operation with high cost typically indicates missing or suboptimal indexes. Reviewing the query predicate and index design should precede resource scaling, which is more expensive and may not solve the underlying issue.
You are implementing a multi-region disaster recovery strategy using Azure SQL Database. After failover to a secondary region, you notice the read-write endpoint is not responding. What is the most likely cause?
-
A
The application connection string is still pointing to the primary region's endpoint
✓ Correct
-
B
The failover group automatic failover setting was disabled
-
C
The secondary region's firewall rules differ from the primary region
-
D
Replication lag exceeded the configured failover threshold
Explanation
After failover, applications must be redirected to the failover group's read-write listener endpoint, not the original primary endpoint. Automatic failover setting, firewall rules, and replication lag are separate concerns that wouldn't prevent the endpoint from responding.
Your Azure SQL Database is configured with a vCore-based service tier. You want to enable the ability to pause compute during off-peak hours. Which service tier supports the pause/resume feature?
-
A
Business Critical tier
-
B
General Purpose tier
-
C
Serverless tier
✓ Correct
-
D
Hyperscale tier
Explanation
The Serverless compute tier supports auto-pause during idle periods, automatically resuming when activity resumes. This feature allows cost savings for non-continuous workloads. Other tiers require manual scaling or run continuously.
You need to restore an Azure SQL Database to a specific point in time after detecting data corruption. The database uses geo-replication. Which restore approach would prevent data corruption from replicating to the secondary replica?
-
A
Perform point-in-time restore on the primary database before the corruption occurred
✓ Correct
-
B
Disable replication, restore the primary, then re-enable replication
-
C
Restore the database to a new database instance, then swap DNS records
-
D
Fail over to the secondary replica immediately to prevent corruption propagation
Explanation
Point-in-time restore on the primary reverts the database to a clean state before corruption. Failover to the secondary won't help since replication is synchronous (corruption has already replicated); restoring to a new instance requires additional administrative steps.
An Azure SQL Database query that previously performed well is now experiencing timeout errors. The query plan appears unchanged, but you notice significant memory pressure in Azure Monitor metrics. What is the most likely cause?
-
A
The transaction isolation level was changed to SERIALIZABLE requiring excessive memory for lock management
-
B
The table statistics are stale and causing inaccurate cardinality estimates during plan compilation
-
C
Another application workload is consuming available memory, leaving insufficient resources for the query
✓ Correct
-
D
The database collation was changed, requiring additional memory for string comparison operations
Explanation
Memory pressure metrics indicate resource contention from concurrent workloads. While stale statistics, isolation level changes, and collation modifications could affect performance, they wouldn't typically manifest as memory pressure with an unchanged plan.
You are configuring security for Azure SQL Database and need to ensure that specific IP addresses from your corporate network can connect. Which firewall rule type should you use?
-
A
Virtual network service endpoint for network-level access control
-
B
Transparent Data Encryption with IP-based key vault access
-
C
Azure AD authentication requiring multi-factor authentication
-
D
IP firewall rule allowing specific IP addresses or ranges
✓ Correct
Explanation
IP firewall rules directly allow specific IP addresses or CIDR ranges. VNet service endpoints are preferred for Azure services but apply to entire subnets; Azure AD and TDE address authentication and encryption respectively.
You need to identify which Azure SQL Database queries are consuming the most resources. Which feature provides a queryable interface to analyze query performance metrics and resource consumption?
-
A
Extended Events with file target storage
-
B
SQL Trace with profiler output
-
C
Query Store with dynamic management views
✓ Correct
-
D
SQL Server Agent job history
Explanation
Query Store captures query execution statistics and resource consumption in queryable tables, enabling analysis of expensive queries. Agent job history tracks jobs, not queries; Extended Events requires manual parsing; SQL Trace is deprecated in newer versions.
When configuring Azure SQL Database for high availability, you need to understand the difference between failover groups and auto-failover groups. What is the primary distinction?
-
A
Failover groups are free; auto-failover groups incur additional licensing costs
-
B
Failover groups support only single-region redundancy; auto-failover groups support multi-region geo-replication
-
C
Auto-failover groups automatically initiate failover without manual intervention; standard groups require database administrator action
✓ Correct
-
D
Auto-failover groups provide synchronous replication; standard failover groups only support asynchronous replication
Explanation
Automatic failover groups trigger failover automatically based on health checks and configured failover policies, whereas standard failover groups require manual failover initiation. Both support geo-replication and have similar replication characteristics.
You have implemented Azure SQL Database with Azure AD authentication and now need to grant database permissions to an Azure AD group. Which T-SQL approach is correct for this scenario?
-
A
CREATE LOGIN [Azure_AD_Group_Name] WITH PASSWORD = 'complex_password'; CREATE USER [Azure_AD_Group_Name] FOR LOGIN [Azure_AD_Group_Name];
-
B
CREATE USER [Azure_AD_Group_Name] FROM EXTERNAL PROVIDER; GRANT SELECT ON schema::dbo TO [Azure_AD_Group_Name];
✓ Correct
-
C
ADD MEMBER [Azure_AD_Group_Name] TO ROLE db_datareader;
-
D
CREATE ROLE [Azure_AD_Group_Name]; ALTER ROLE [Azure_AD_Group_Name] ADD MEMBER;
Explanation
Azure AD groups in SQL Database require the FROM EXTERNAL PROVIDER syntax in CREATE USER statements. Options B uses SQL authentication (incompatible with AD groups); options C and D have incorrect syntax for Azure AD principals.
An Azure SQL Database requires encryption of data in transit to prevent interception. Which connection parameter ensures that all data between the application and database is encrypted using TLS?
-
A
Encrypt=true in the connection string
✓ Correct
-
B
SSL_Mode=REQUIRE in the connection string
-
C
TLS_Version=1.2 in the connection string
-
D
Trust Server Certificate=false in the connection string
Explanation
The Encrypt=true parameter in Azure SQL Database connection strings enforces TLS encryption for all data in transit. TLS_Version specifies version preference; SSL_Mode applies to PostgreSQL; Trust Server Certificate controls certificate validation.
You are implementing a backup strategy for an Azure SQL Database that must support recovery from ransomware attacks. Which backup approach provides the best protection against encrypted backup files?
-
A
Immutable backups in Azure Blob Storage with retention lock policies
✓ Correct
-
B
Continuous replication to a read-only secondary database in another region
-
C
Automated backup with encryption using service-managed keys
-
D
Regular manual backups stored on local network-attached storage
Explanation
Immutable backups with retention lock prevent modification or deletion, protecting against ransomware encryption and deletion. Service-managed keys don't prevent deletion; on-premises storage is vulnerable to ransomware; read-only replicas still share the same infrastructure.
When monitoring Azure SQL Database resource utilization, you observe frequent DTU throttling events during peak hours. What is the most cost-effective scaling strategy for this unpredictable variable workload?
-
A
Distribute the workload across multiple smaller single databases
-
B
Implement elastic pools to allow resource sharing across multiple databases
-
C
Migrate to vCore-based pricing with auto-scaling enabled
✓ Correct
-
D
Upgrade to a static higher DTU tier to eliminate throttling
Explanation
vCore-based service tiers with auto-scaling adjust compute resources based on demand, optimizing costs for variable workloads. Static tier upgrades are expensive for variable loads; elastic pools help with multiple databases; distribution increases management complexity.
You need to audit Azure SQL Database login attempts, including failed authentication events. Which diagnostic log category captures this information?
-
A
QueryStoreRuntimeStatistics provides query performance baselines
-
B
SQLSecurityAuditEvents captures authentication and authorization actions
✓ Correct
-
C
SQLQueryStoreWaitStatistics tracks query execution waits
-
D
SQLInsights provides performance recommendations based on telemetry
Explanation
SQLSecurityAuditEvents logs authentication attempts and authorization events, including failures. The other categories focus on performance insights and query statistics rather than security events.
You need to monitor query performance in Azure SQL Database. Which Dynamic Management View (DMV) would you use to identify the top resource-consuming queries?
-
A
sys.dm_tran_locks
-
B
sys.dm_os_waiting_tasks
-
C
sys.dm_db_index_usage_stats
-
D
sys.dm_exec_query_stats
✓ Correct
Explanation
sys.dm_exec_query_stats returns aggregate performance statistics for cached query execution plans, making it ideal for identifying resource-heavy queries. The other DMVs track waiting tasks, locks, and index usage respectively.
Your Azure SQL Database is experiencing blocking issues. You want to configure automatic plan correction for parameterization issues. Which feature should you enable?
-
A
Automatic tuning with force last good plan option
✓ Correct
-
B
Extended Events tracing
-
C
Intelligent Insights
-
D
Query Store with reactive tuning
Explanation
Automatic tuning with the 'force last good plan' option automatically corrects execution plan regressions by reverting to previously known good plans. Intelligent Insights is diagnostic, Query Store is informational, and Extended Events is for tracing.
You are configuring backup retention for a critical Azure SQL Database. The company requires recovery point objective (RPO) of 1 hour. What is the minimum frequency you must configure for transaction log backups?
-
A
Every 2 hours
-
B
Every 30 minutes
-
C
Every hour
✓ Correct
-
D
Every 12 hours
Explanation
To meet a 1-hour RPO, transaction log backups must occur at least every hour to ensure you can recover data within that timeframe. More frequent backups reduce the RPO further.
You need to implement row-level security (RLS) on a sensitive table in Azure SQL Database. After creating the security policy, users report they cannot see their required data. What is the most likely cause?
-
A
Row-level security is not supported in Azure SQL Database
-
B
Users lack SELECT permissions on the underlying table
-
C
The security predicate logic is incorrectly filtering rows
✓ Correct
-
D
The database compatibility level is below 130
Explanation
The most common issue with RLS implementation is incorrect predicate logic that unintentionally filters out legitimate rows. RLS is fully supported in Azure SQL Database, and permissions are separate from RLS filtering.
You are configuring Azure SQL Database for high availability. Which replication option provides the shortest recovery time objective (RTO)?
-
A
Manual database copies to another region
-
B
Active geo-replication with failover groups
✓ Correct
-
C
Long-term retention backups
-
D
Automated backups with point-in-time restore
Explanation
Active geo-replication with failover groups enables automatic failover with minimal RTO (typically seconds), while backups and manual copies have longer recovery times measured in minutes to hours.
You are troubleshooting a tempdb space issue in Azure SQL Database. What action should you take first?
-
A
Review sys.dm_db_session_space_usage to identify sessions consuming tempdb space
✓ Correct
-
B
Restart the database server immediately
-
C
Drop all stored procedures using temporary tables
-
D
Increase the service tier to gain more resources
Explanation
Before taking action, you should investigate which sessions are consuming tempdb space using the appropriate DMV. This diagnostic step prevents unnecessary service tier changes or disruptive restarts.
Your organization requires encryption of data at rest in Azure SQL Database. Which feature provides transparent encryption without application changes?
-
A
Always Encrypted with deterministic encryption
-
B
Application-level encryption in stored procedures
-
C
Transparent Data Encryption (TDE)
✓ Correct
-
D
Azure Key Vault integration with manual key rotation
Explanation
Transparent Data Encryption (TDE) encrypts data at rest automatically without requiring application changes, whereas Always Encrypted requires application modifications and is column-level encryption.
You are implementing a disaster recovery solution for Azure SQL Database. The recovery time objective (RTO) is 4 hours and recovery point objective (RPO) is 1 hour. Which backup strategy best meets these requirements with optimal cost efficiency?
-
A
Daily full backups with geo-replication and automated backups
-
B
Real-time active geo-replication to a secondary region with continuous synchronization
-
C
Hourly transaction log backups to geo-redundant storage with weekly full backups
✓ Correct
-
D
Continuous backup with 7-day retention and geo-redundant storage
Explanation
Hourly transaction log backups provide the 1-hour RPO while weekly full backups keep costs reasonable, and the 4-hour RTO is achievable through restore operations from geo-redundant storage without the expense of continuous geo-replication.
You need to audit database access changes in Azure SQL Database. Which audit target provides the best option for long-term compliance storage with immutable records?
-
A
Change Data Capture (CDC) on system tables
-
B
Extended Events with file target in local storage
-
C
Azure SQL Database Auditing with Azure Storage destination using append blob
✓ Correct
-
D
SQL Server Audit with event log destination
Explanation
Azure SQL Database Auditing with append blob storage in Azure Storage provides immutable audit records suitable for compliance, whereas Extended Events is for performance monitoring and CDC tracks data changes, not access.
You are optimizing an Azure SQL Database query that performs a large multi-table join. The execution plan shows table scans instead of index seeks. What is the most appropriate first diagnostic step?
-
A
Check the Query Store for historical execution plans and verify statistics freshness with sys.dm_db_stats_properties
✓ Correct
-
B
Rewrite the query using hint forcing index usage
-
C
Immediately create composite indexes on all join columns
-
D
Scale up the database service tier to increase memory for caching
Explanation
Before making schema changes, you should verify that statistics are up-to-date since stale statistics cause poor plan choices. Query Store provides historical context for comparison and helps identify recent regressions.