Courseiva

CCNA Plan and implement data platform resources Questions

24 of 99 questions · Page 2/2 · Plan and implement data platform resources · Answers revealed

Practice these questionsDomain overviewAll questions
76
MCQeasy

Your company is adopting Microsoft Defender XDR for enhanced security. You need to enable Microsoft Defender for SQL for your Azure SQL Database to receive security alerts and vulnerability assessments. What is the first step you must take?

A.Configure Microsoft Intune to manage access.
B.Run a vulnerability assessment scan from the Azure portal.
C.Configure Microsoft Purview to scan the database.
D.Enable Microsoft Defender for SQL on the Azure SQL logical server.
AnswerD

Defender for SQL is enabled at the Azure SQL logical server level, which then applies protection to every database hosted on that server. Enabling it on the server satisfies the requirement to receive security alerts and vulnerability assessments across the databases.

Why this answer

Microsoft Defender for SQL must be enabled at the logical server level before any security alerts or vulnerability assessments can be applied to the Azure SQL Database. This server-level enablement activates the Defender for SQL features (including vulnerability assessment and threat detection) for all databases under that server, making it the prerequisite step.

Exam trap

The trap here is that candidates may think they need to first run a vulnerability assessment scan (Option B) to see alerts, but the scan itself requires Defender for SQL to be enabled first, making the enablement the true first step.

How to eliminate wrong answers

Option A is wrong because Microsoft Intune is a mobile device management (MDM) and mobile application management (MAM) service, not related to enabling security features for Azure SQL Database. Option B is wrong because running a vulnerability assessment scan requires Defender for SQL to already be enabled; attempting to run it first would fail or be unavailable. Option C is wrong because Microsoft Purview is a data governance and catalog service used for data discovery and classification, not for enabling SQL security alerts or vulnerability assessments.

Practice this question →
77
MCQeasy

You need to deploy a new Azure SQL Database that will store sensitive financial data. The database must use customer-managed keys for encryption at rest, and the keys must be stored in Azure Key Vault. You want to ensure that the keys are automatically rotated and that the database remains available during rotation. What should you configure?

A.Enable Always Encrypted with column master keys stored in Azure Key Vault and configure automatic key rotation.
B.Enable transparent data encryption (TDE) with a customer-managed key stored in Azure Key Vault and configure automatic key rotation.
C.Use dynamic data masking with a customer-managed key to encrypt sensitive columns.
D.Configure Azure Disk Encryption on the underlying virtual machines hosting the Azure SQL Database.
AnswerB

Transparent data encryption (TDE) with a customer-managed key in Azure Key Vault allows you to control the encryption key and meet compliance requirements. Azure SQL Database supports automatic key rotation, where the database automatically uses the latest key version from Key Vault. This ensures the database remains available during rotation because the key is updated seamlessly without downtime.

Why this answer

Transparent data encryption (TDE) with a customer-managed key in Azure Key Vault is the correct solution for encryption at rest with customer-controlled keys. Azure SQL Database supports automatic key rotation, which seamlessly updates the database to use the latest key version. Always Encrypted is for client-side encryption, Azure Disk Encryption is for IaaS VMs, and dynamic data masking is for masking, not encryption.

Exam trap

The trap here is confusing Always Encrypted with TDE; Always Encrypted protects data in use and requires application changes, while TDE encrypts data at rest and supports automatic key rotation.

Practice this question →
78
MCQhard

Your company requires that all Azure SQL Databases use Transparent Data Encryption (TDE) with customer-managed keys (CMK) stored in Azure Key Vault. The security policy mandates that the key must be rotated every 90 days. You need to implement this requirement with minimal administrative overhead. What should you do?

A.Manually rotate the key in Key Vault every 90 days and update the database
B.Use Azure SQL Database server-side TDE with a service-managed key
C.Store the key in the database and use a custom rotation script
D.Use Azure Key Vault key rotation policy to automatically rotate the key every 90 days
AnswerD

Azure Key Vault's automated rotation policy regenerates the key version on a 90-day schedule, satisfying the mandated rotation interval without manual intervention. Because TDE with customer-managed keys references the Key Vault key, new versions are picked up automatically, delivering the minimal administrative overhead the stem requires.

Why this answer

Azure Key Vault supports automatic key rotation policies that can be configured to rotate a customer-managed key (CMK) every 90 days without any manual intervention. When this key is used for TDE in Azure SQL Database, the database automatically uses the latest version of the key from Key Vault, so no additional steps are needed to update the database after rotation. This satisfies the security policy with minimal administrative overhead.

Exam trap

The trap here is that candidates may confuse automatic key rotation in Key Vault with manual rotation or service-managed keys, failing to recognize that Azure Key Vault's built-in rotation policy directly satisfies the CMK and rotation requirements with zero administrative overhead.

How to eliminate wrong answers

Option A is wrong because manually rotating the key every 90 days and updating the database introduces significant administrative overhead, contradicting the requirement for minimal overhead. Option B is wrong because service-managed keys do not meet the requirement for customer-managed keys (CMK); they are managed by Microsoft, not the customer. Option C is wrong because storing the key in the database is not supported for TDE in Azure SQL Database; TDE keys must be stored in Azure Key Vault or managed by the service, and a custom rotation script would add unnecessary complexity and overhead.

Practice this question →
79
Multi-Selectmedium

Which TWO of the following are supported high availability features in Azure SQL Managed Instance?

Select 2 answers
A.Zone-redundant deployment for Business Critical tier
B.Database mirroring
C.Always On Availability Groups (built-in)
D.Log shipping to a secondary instance
E.Windows Server Failover Clustering
AnswersA, C

Zone-redundant deployment spreads Business Critical replicas across availability zones, protecting against datacentre-level failure with automatic failover. This is a supported high availability feature in Azure SQL Managed Instance, unlike General Purpose, which relies on local redundancy only.

Why this answer

Option A is correct because Azure SQL Managed Instance in the Business Critical service tier supports zone-redundant deployment, which places the primary and secondary replicas across different availability zones to protect against datacenter-level failures. Option C is correct because SQL Managed Instance includes a built-in Always On Availability Groups mechanism that automatically provisions and manages replicas for high availability, with the Business Critical tier using local SSD and multiple secondary replicas. Database mirroring (B) is a deprecated SQL Server feature and is not offered as a supported HA option in Azure SQL Managed Instance.

Log shipping to a secondary instance (D) is not a built-in HA feature of SQL Managed Instance, though it can be configured manually for disaster recovery scenarios. Windows Server Failover Clustering (E) is an underlying infrastructure concept used by SQL Server on-premises and Azure VMs, but it is not exposed or supported as a user-configurable HA feature in Azure SQL Managed Instance.

Exam trap

The trap here is that candidates often confuse on-premises SQL Server high availability features (like database mirroring, log shipping, or manual Windows clustering) with the fully managed, built-in capabilities of Azure SQL Managed Instance, leading them to select unsupported legacy options.

Practice this question →
80
MCQeasy

You are configuring managed backup for an Azure SQL Managed Instance as shown in the exhibit. What is the purpose of this configuration?

A.To enable geo-replication for the managed instance.
B.To configure automated backups of the managed instance to Azure Blob Storage.
C.To set up disaster recovery across Azure regions.
D.To configure point-in-time restore for the managed instance.
AnswerB

Managed backup automates full, differential and transaction log backups of the managed instance directly to Azure Blob Storage, giving retention control without manual jobs. This satisfies the exhibit's configuration purpose of automated backup to blob storage.

Why this answer

The configuration shown in the exhibit is for managed backup, which automatically schedules and stores full, differential, and transaction log backups of the Azure SQL Managed Instance to Azure Blob Storage. This ensures that backups are retained and available for restore operations without manual intervention, fulfilling the core purpose of automated backups to Azure Blob Storage.

Exam trap

The trap here is that candidates often confuse the purpose of configuring automated backups (which creates the backup chain) with the point-in-time restore feature (which uses that chain), leading them to select Option D instead of the correct Option B.

How to eliminate wrong answers

Option A is wrong because geo-replication for Azure SQL Managed Instance is configured via failover groups, not through managed backup settings; managed backup does not replicate data to another region. Option C is wrong because disaster recovery across Azure regions is achieved by setting up a failover group with a secondary managed instance in a paired region, not by enabling managed backup. Option D is wrong because point-in-time restore is a feature that relies on the backup chain created by managed backup, but the configuration itself is for creating those backups, not for performing the restore operation.

Practice this question →
81
MCQmedium

You have an Azure SQL Database with sensitive customer data. You need to mask the credit card numbers so that only users with the 'Unmask' permission can see the full number. Non-privileged users should see only the last four digits. Which feature should you implement?

A.Column-level security
B.Row-level security
C.Dynamic Data Masking
D.Always Encrypted
AnswerC

Dynamic Data Masking applies a masking rule at query time, returning only the last four digits to users lacking UNMASK permission while privileged users see full values. This directly meets the requirement without altering stored data or requiring application changes.

Why this answer

Dynamic Data Masking (DDM) is the correct feature because it allows you to obfuscate sensitive data in query results without changing the underlying database. You can define a mask on the credit card column that shows only the last four digits by default, and grant the UNMASK permission to privileged users so they see the full value. This directly meets the requirement of role-based partial masking without altering the stored data.

Exam trap

The trap here is that candidates confuse Dynamic Data Masking with Always Encrypted, thinking encryption is required for masking, but DDM is purely a presentation-layer obfuscation that does not encrypt the underlying data.

How to eliminate wrong answers

Option A is wrong because Column-level security controls read access at the column level (e.g., denying SELECT on a column), but it cannot partially mask data within a column; it either grants or denies full visibility. Option B is wrong because Row-level security filters entire rows based on a predicate function, but it cannot mask individual column values within a row. Option D is wrong because Always Encrypt encrypts data at the client side and never exposes plaintext to the database engine, making it impossible to show partial values (like last four digits) to non-privileged users without complex client-side logic.

Practice this question →
82
MCQmedium

You are designing a solution for storing audit logs from Azure SQL Database. The logs must be retained for 7 years and must be immutable to prevent tampering. Which Azure service should you use?

A.Use Azure Files share with read-only permissions
B.Send logs to Azure Log Analytics workspace
C.Store logs in an audit table in Azure SQL Database
D.Azure Blob Storage with immutable storage policy
AnswerD

Azure Blob Storage with an immutable storage policy enforces write-once, read-many retention using time-based legal holds, preventing modification or deletion for the specified period. This satisfies both the 7-year retention and tamper-proof immutability requirements for the audit logs.

Why this answer

Azure Blob Storage with an immutable storage policy (WORM – Write Once, Read Many) is the correct choice because it ensures that audit logs cannot be modified or deleted for a specified retention period (7 years). This meets the immutability and retention requirements for compliance with regulations such as SOX or HIPAA. Azure SQL Database audit logs can be directly streamed to Azure Blob Storage, making it a seamless and secure storage solution.

Exam trap

The trap here is that candidates often confuse 'immutable' with 'read-only permissions' (Option A) or assume that a database table (Option C) can be made immutable by restricting permissions, but true immutability requires storage-level WORM enforcement that cannot be bypassed by any user or process.

How to eliminate wrong answers

Option A is wrong because Azure Files share with read-only permissions does not provide true immutability; permissions can be changed by an administrator, and the underlying data can be modified or deleted, failing the tamper-proof requirement. Option B is wrong because Azure Log Analytics workspace is designed for real-time monitoring and analysis, not long-term immutable storage; it has a maximum retention period of 730 days (2 years) and does not support WORM policies. Option C is wrong because storing logs in an audit table in Azure SQL Database does not guarantee immutability; data can be altered or deleted by users with appropriate permissions, and the database itself is not designed for write-once, read-many compliance storage.

Practice this question →
83
MCQmedium

You are deploying an Azure SQL Database for a new line-of-business application. The application's workload is unpredictable, with periods of near-zero activity and sudden bursts of high read/write volume. The database must scale compute resources automatically without manual intervention, and you want to minimize cost during idle periods. You create the database using the vCore purchasing model. Which service tier should you select?

A.Serverless
B.General Purpose
C.Business Critical
D.Hyperscale
AnswerA

Serverless is a compute tier for single databases in the vCore purchasing model that automatically scales compute based on workload demand and can auto-pause during inactive periods, reducing cost to storage only. It bills per second of compute usage. This matches the requirement for automatic scaling and cost minimization during idle periods without manual intervention.

Why this answer

Serverless compute automatically scales vCores up and down based on workload demand and pauses the database during inactivity, billing only for storage while paused. This directly addresses the need for automatic scaling and cost minimization during idle periods. Other service tiers require manual scaling and do not auto-pause, so they cannot meet the stated requirements.

Exam trap

The trap here is assuming that any vCore-based service tier provides automatic scaling, when in fact only the Serverless compute tier offers auto-scaling and auto-pause.

Practice this question →
84
MCQmedium

You are reviewing an ARM template snippet that configures a long-term retention (LTR) policy for an Azure SQL Database. Based on the exhibit, how long will weekly backups be retained?

A.4 weeks.
B.4 days.
C.4 months.
D.4 years.
AnswerA

The LTR policy's weekly retention setting defines how long each weekly full backup copy remains restorable. The exhibit specifies a weekly retention period of 4 weeks, so weekly backups are kept for four weeks before expiry.

Why this answer

In the ARM template snippet, the weeklyRetention property is set to 'P4W' (ISO 8601 duration format), which means 4 weeks. Therefore, weekly backups are retained for exactly 4 weeks.

Exam trap

The trap is misinterpreting the ISO 8601 duration 'P4W'. 'P4W' represents 4 weeks, not 1 month (P1M), 4 days (P4D), or 4 years (P4Y). Many candidates mistakenly assume 'W' means something else or misread the number, leading to incorrect options like 4 days, 4 months, or 4 years.

How to eliminate wrong answers

Option B is wrong because '4 days' would correspond to a duration like 'P4D' in ISO 8601, not 'P1M'. Option C is wrong because '4 months' would be 'P4M', not 'P1M'. Option D is wrong because '4 years' would be 'P4Y', not 'P1M'.

The trap is misinterpreting the ISO 8601 duration format, where 'P1M' specifically means 1 month, not 4 of any unit.

Practice this question →
85
MCQeasy

You are planning the deployment of a new Azure SQL Database for a line-of-business application. The application's workload is not yet known, and you must keep the monthly cost as low as possible while still being able to scale compute resources up or down without redeploying the database. You also need to ensure that storage is billed based on the actual data and log used rather than a pre-provisioned maximum. Which purchasing model and service tier should you choose?

A.Provisioned compute tier with General Purpose service tier
B.Serverless compute tier with General Purpose service tier
C.Hyperscale service tier with provisioned compute
D.Provisioned compute tier with Business Critical service tier
AnswerB

The serverless compute tier automatically scales compute based on workload demand and bills per second of compute used, with a configurable auto-pause delay that can stop compute entirely during inactive periods. Storage is billed based on the actual data and log used rather than a pre-provisioned maximum. This directly meets the requirements of low cost for an unknown workload and the ability to scale without redeployment.

Why this answer

The serverless compute tier in the General Purpose service tier is designed for unpredictable workloads and cost optimisation. It automatically scales compute based on demand and can auto-pause during inactive periods, billing only for compute used. Storage is billed based on actual data and log usage.

Provisioned tiers reserve compute capacity and bill continuously, making them less suitable when the workload is unknown and cost must be minimised.

Exam trap

The trap here is assuming that any service tier can be combined with serverless compute; serverless is only available with the General Purpose service tier.

Practice this question →
86
MCQeasy

You are evaluating Azure SQL Database for a new application that requires the database to be isolated from other Azure tenants and to have a dedicated compute and storage resources. The application also requires support for SQL Server Agent and cross-database queries. Which Azure SQL deployment option should you choose?

A.Azure SQL Database hyperscale
B.Azure SQL Database elastic pool
C.Azure SQL Managed Instance
D.Azure SQL Database single database
AnswerC

Azure SQL Managed Instance provides a near-100% compatible SQL Server surface area, including support for SQL Server Agent, cross-database queries, and CLR. It is deployed into a dedicated subnet within an Azure virtual network, providing network isolation. It also offers dedicated compute and storage resources per instance. This meets all the stated requirements for isolation, SQL Server Agent, and cross-database queries.

Why this answer

Azure SQL Managed Instance is the only Azure SQL deployment option that provides a dedicated instance with support for SQL Server Agent, cross-database queries, and network isolation via a dedicated subnet. Single database and elastic pools do not support these features. Hyperscale is a service tier, not a deployment option, and lacks these capabilities.

Therefore, Azure SQL Managed Instance is the correct choice.

Exam trap

The trap here is assuming that Azure SQL Database single database can support SQL Server Agent and cross-database queries; these are only available in Azure SQL Managed Instance.

Practice this question →
87
MCQeasy

Your organization requires that all Azure SQL Database backups be retained for 10 years to meet compliance requirements. Which backup retention policy should you configure?

A.Configure point-in-time restore (PITR) retention to 10 years.
B.Enable automatic tuning to optimize backups.
C.Configure long-term retention (LTR) policy.
D.Enable geo-redundant backup storage (GRS).
AnswerC

Long-term retention stores full backups in read-access geo-redundant blob storage for up to ten years, satisfying the compliance requirement. The default point-in-time and short-term policies cap at 35 days, so only LTR reaches the mandated decade.

Why this answer

Long-term retention (LTR) in Azure SQL Database allows you to retain full database backups for up to 10 years, which meets the compliance requirement for 10-year backup retention. LTR policies are configured separately from point-in-time restore (PITR) and store backups in isolated containers for extended periods, ensuring regulatory compliance.

Exam trap

The trap here is that candidates often confuse point-in-time restore (PITR) retention with long-term retention (LTR), assuming PITR can be extended to years, but Azure SQL Database caps PITR at 35 days, making LTR the only option for multi-year compliance.

How to eliminate wrong answers

Option A is wrong because point-in-time restore (PITR) retention is limited to a maximum of 35 days for Azure SQL Database, not 10 years, so it cannot satisfy the 10-year compliance requirement. Option B is wrong because automatic tuning optimizes query performance and index management, not backup retention, and has no impact on backup duration or compliance. Option D is wrong because geo-redundant backup storage (GRS) provides geographic redundancy for backups but does not extend the retention period beyond the default PITR or LTR limits; it is a storage option, not a retention policy.

Practice this question →
88
MCQmedium

You are configuring security for an Azure SQL Database that will be accessed by multiple applications. You need to implement a solution that allows applications to connect using their own managed identities without storing credentials in connection strings. What should you configure?

A.Enable Microsoft Entra ID authentication and assign managed identities to the applications.
B.Use Always Encrypted with column master key in Azure Key Vault.
C.Configure firewall rules to allow application IP addresses.
D.Enable SQL Server authentication and create a login for each application.
AnswerA

Microsoft Entra ID authentication lets each application present its own managed identity, so Azure SQL Database validates the token issued by the platform rather than a stored password. This satisfies the stem's requirement to avoid credentials in connection strings, since the identity's secret is managed and rotated by Azure outside application code.

Why this answer

Microsoft Entra ID authentication allows Azure SQL Database to trust tokens issued by Entra ID for managed identities. By assigning a managed identity to each application, the application can acquire an access token from Azure Managed Identity endpoints and present it to the database without ever storing credentials in connection strings. This eliminates the need for passwords or connection string secrets.

Exam trap

The trap here is that candidates often confuse authentication mechanisms (like Always Encrypted or firewall rules) with identity-based access control, mistakenly thinking they eliminate credential storage when they only address encryption or network filtering.

How to eliminate wrong answers

Option B is wrong because Always Encrypted with a column master key in Azure Key Vault protects data at rest and in transit by encrypting specific columns, but it does not address authentication or eliminate the need for credentials in connection strings. Option C is wrong because configuring firewall rules to allow application IP addresses controls network access but still requires a username and password (or other authentication) in the connection string; it does not remove credential storage. Option D is wrong because enabling SQL Server authentication and creating a login for each application still requires storing a username and password in the connection string, which violates the requirement to avoid credential storage.

Practice this question →
89
MCQmedium

You are configuring security for an Azure SQL Database that will be accessed by multiple applications. Each application uses a separate service principal managed in Microsoft Entra ID. You need to ensure that each service principal has the minimum required permissions to access only its own set of tables. What should you implement?

A.Create a contained database user for each service principal and grant the db_owner role.
B.Create a contained database user for each service principal and grant SELECT, INSERT, UPDATE, DELETE on specific tables.
C.Create a server-level login for each service principal and assign db_datareader role in the database.
D.Configure a server-level firewall rule for each service principal IP address.
AnswerB

Contained database users authenticate directly against Microsoft Entra ID at the database layer, so each service principal maps to its own user. Granting table-scoped DML rights enforces least privilege, satisfying the requirement that each application reaches only its own tables.

Why this answer

It creates a contained database user for each service principal (mapped to the Microsoft Entra ID identity) and grants only the specific table-level permissions (SELECT, INSERT, UPDATE, DELETE) required for that application. This follows the principle of least privilege by avoiding broad database roles and ensuring each service principal can only access its own set of tables.

Exam trap

The trap here is that candidates often confuse server-level logins with contained database users for Microsoft Entra ID principals, or mistakenly think that broad roles like db_datareader satisfy the 'minimum required permissions' requirement when the question explicitly demands table-level scoping.

How to eliminate wrong answers

Option A is wrong because granting the db_owner role provides full administrative control over the entire database, far exceeding the minimum required permissions and violating least privilege. Option C is wrong because server-level logins are not supported for Microsoft Entra ID service principals; you must use contained database users, and db_datareader grants read access to all tables, not just specific ones. Option D is wrong because firewall rules control network access at the server level, not permissions to specific tables, and service principals authenticate via Microsoft Entra ID tokens, not IP addresses.

Practice this question →
90
MCQmedium

You are configuring a new Azure SQL Database. The application that will use the database requires that all connections be encrypted and that the database be protected against SQL injection attacks. You also need to minimize administrative effort for monitoring and threat detection. What should you implement?

A.Enable Dynamic Data Masking and configure Azure Monitor alerts for failed logins.
B.Configure Always Encrypted with column encryption and enable auditing to a storage account.
C.Enable Transparent Data Encryption (TDE) and configure a firewall rule to allow only the application's IP address.
D.Enforce SSL connections and enable Azure Defender for SQL (Advanced Threat Protection) with vulnerability assessment.
AnswerD

Enforcing SSL connections ensures that all data in transit is encrypted. Azure Defender for SQL provides advanced threat detection, including alerts for potential SQL injection attacks, and vulnerability assessment helps identify and remediate security misconfigurations. This combination addresses encrypted connections, SQL injection protection, and minimizes monitoring effort through automated alerts, making it the correct solution for the scenario.

Why this answer

Enforcing SSL connections ensures encrypted communication, and Azure Defender for SQL provides advanced threat detection, including SQL injection alerts, along with vulnerability assessment. This combination directly meets the requirements for encrypted connections, SQL injection protection, and reduced monitoring effort. Other options either address different security aspects or do not provide the necessary threat detection and connection encryption.

Exam trap

The trap here is confusing data-at-rest encryption with connection encryption and assuming that firewall rules alone can prevent SQL injection.

Practice this question →
91
MCQhard

You are designing a data platform for a global SaaS company. The application requires a relational database that can handle up to 50 TB of data and supports high-frequency inserts. The database must be able to scale compute independently from storage and provide fast restores (within minutes) for large databases. Which Azure SQL offering should you choose?

A.Azure SQL Database Business Critical tier
B.Azure SQL Database Standard tier
C.Azure SQL Database Hyperscale tier
D.Azure SQL Managed Instance Business Critical
AnswerC

Hyperscale separates compute from storage and uses snapshot-based backups, so it scales independently and restores 50 TB databases in minutes rather than hours. Its architecture also sustains the high-frequency insert workload the stem requires, unlike other Azure SQL tiers.

Why this answer

The Hyperscale tier of Azure SQL Database is designed for workloads up to 100 TB, decouples compute from storage (allowing independent scaling), and uses a log-based architecture with page servers to enable fast restores (typically within minutes, regardless of database size). This directly matches the requirements for 50 TB data, high-frequency inserts, independent compute/storage scaling, and rapid restore times.

Exam trap

The trap here is that candidates often confuse the Business Critical tier's high availability and performance features with the ability to handle large data volumes and fast restores, overlooking the strict 4 TB size limit and lack of compute/storage decoupling.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database Business Critical tier has a maximum size of 4 TB, far below the required 50 TB, and does not support independent compute/storage scaling. Option B is wrong because Azure SQL Database Standard tier is limited to 1 TB and is designed for lower performance workloads, not high-frequency inserts or fast restores. Option D is wrong because Azure SQL Managed Instance Business Critical has a maximum size of 16 TB, insufficient for 50 TB, and while it offers some scaling, it does not provide the same level of compute/storage decoupling or sub-minute restore capabilities as Hyperscale.

Practice this question →
92
Drag & Dropmedium

Drag and drop the steps to troubleshoot a high CPU usage issue in Azure SQL Database in the correct order.

Drag or tap steps into the slots.

Steps
Order
1Step 1
2Step 2
3Step 3
4Step 4

Why this order

Start by identifying high CPU queries, analyze plans, check for missing indexes, implement fixes, then monitor.

Practice this question →
93
MCQhard

You are planning to deploy an Azure SQL Managed Instance to host several databases migrated from an on-premises SQL Server. The applications use cross-database queries, SQL Server Agent jobs, and CLR assemblies. You need to ensure the instance can support these features and that the network configuration allows the instance to be reached from an on-premises network over a site-to-site VPN. What should you do first?

A.Create a virtual network with a gateway subnet and configure a site-to-site VPN gateway, then deploy the instance into the same subnet as the gateway.
B.Create a virtual network with a subnet delegated to Microsoft.Sql/managedInstances and configure a private endpoint for the instance.
C.Create a virtual network with a subnet delegated to Microsoft.Sql/managedInstances and enable public endpoint access on the instance.
D.Create a virtual network with a dedicated subnet delegated to Microsoft.Sql/managedInstances and configure a site-to-site VPN gateway.
AnswerD

Azure SQL Managed Instance must be deployed into a dedicated subnet delegated to Microsoft.Sql/managedInstances, and the subnet must not contain other resources. A site-to-site VPN gateway in the same virtual network or a peered network provides connectivity from on-premises. This configuration supports cross-database queries, SQL Server Agent, and CLR because those are built-in managed instance capabilities.

Why this answer

Azure SQL Managed Instance must be placed in a dedicated subnet delegated to Microsoft.Sql/managedInstances, and that subnet cannot host other resources. To connect from on-premises over a site-to-site VPN, you also need a VPN gateway in the virtual network or a peered network. The managed instance natively supports cross-database queries, SQL Server Agent, and CLR, so the network and subnet configuration is the critical first step.

Exam trap

The trap here is assuming that a managed instance can share a subnet with a VPN gateway, when it requires its own dedicated delegated subnet.

Practice this question →
94
MCQmedium

You have an Azure SQL Managed Instance that is experiencing performance degradation. You suspect a query is causing excessive blocking. You need to identify the blocking chain and the resource holding the lock. Which DMV should you query?

A.sys.dm_exec_requests
B.sys.dm_tran_locks and sys.dm_os_waiting_tasks
C.sys.dm_exec_query_stats
D.sys.dm_tran_active_snapshot_database_transactions
AnswerB

sys.dm_tran_locks reveals the granted and requested locks with their owning sessions, while sys.dm_os_waiting_tasks exposes the blocking chain through blocking_session_id. Together they satisfy the stem's need to identify both the blocking chain and the lock holder.

Why this answer

To identify the blocking chain and the specific resource holding the lock, you need to combine lock metadata with wait information. sys.dm_tran_locks shows current locks and their resource types (e.g., RID, KEY, PAGE, OBJECT), while sys.dm_os_waiting_tasks reveals which sessions are waiting on those locks and the blocking session ID. Together, these DMVs allow you to trace the blocking chain from the blocked session back to the blocker and pinpoint the exact resource causing contention.

Exam trap

The trap here is that candidates often pick sys.dm_exec_requests (Option A) because it shows wait_type and blocking_session_id, but it lacks the granular lock resource information (e.g., RID, KEY) that sys.dm_tran_locks provides, which is essential for identifying the exact resource holding the lock.

How to eliminate wrong answers

Option A is wrong because sys.dm_exec_requests shows currently executing requests and their wait types, but it does not provide detailed lock resource information (e.g., which specific row or key is locked) needed to identify the exact resource holding the lock. Option C is wrong because sys.dm_exec_query_stats aggregates query performance metrics (CPU, I/O, duration) over time and does not contain real-time lock or blocking chain data. Option D is wrong because sys.dm_tran_active_snapshot_database_transactions is specific to snapshot isolation level transactions and tracks version store usage, not blocking chains or lock resources.

Practice this question →
95
MCQhard

You are reviewing an ARM template for creating a new Azure SQL Database. The template uses the above JSON to create a database named 'db2' from 'db1'. The source database 'db1' is currently in a failed state due to a storage issue. What will be the result of deploying this template?

A.The deployment will fail because db1 is not in a recoverable state.
B.It will create an empty database because the source is not accessible.
C.It will delete db1 and create db2 as a replacement.
D.It will create a new database by recovering db1 to its last known good state.
AnswerA

A point-in-time restore or database copy requires the source database to be online and recoverable. Because db1 is in a failed state from a storage issue, the control-plane operation cannot read its backups, so the ARM deployment returns an error rather than creating db2.

Why this answer

The ARM template creates a new database by copying from a source database. Azure SQL Database requires the source database to be in an online and healthy state to perform a copy operation. Since db1 is in a failed state due to a storage issue, it is not accessible for copying, so the deployment will fail.

Exam trap

The trap here is that candidates may confuse a database copy with a point-in-time restore, assuming that a failed source can still be used to create a new database via recovery, but the copy operation explicitly requires an online source.

How to eliminate wrong answers

Option B is wrong because Azure SQL Database does not create an empty database when the source is inaccessible; the copy operation requires a valid, online source. Option C is wrong because the ARM template does not include a delete operation; it only creates a new database from a source, and Azure SQL Database does not automatically delete the source during a copy. Option D is wrong because the template specifies a copy operation, not a point-in-time restore; recovering to a last known good state would require a different ARM template or a restore command, not a database copy.

Practice this question →
96
MCQmedium

You are a database administrator for a company that runs a SaaS application on Azure SQL Database. The application's workload is unpredictable, with rapid bursts of activity that last only a few minutes. You need to ensure that the database can handle these bursts without manual intervention, while minimizing cost during idle periods. What should you do?

A.Configure the database to use the Hyperscale service tier with a fixed number of vCores.
B.Enable the serverless compute tier for the database.
C.Create a read scale-out replica and route all write operations to the primary replica.
D.Implement elastic database pool with multiple databases sharing resources.
AnswerB

The serverless compute tier automatically scales compute resources based on workload demand and pauses the database during inactive periods, billing only for storage. This directly addresses unpredictable bursts by scaling up during activity and scaling down or pausing when idle, minimizing cost. It requires no manual intervention and is ideal for intermittent, unpredictable workloads.

Why this answer

The serverless compute tier is specifically designed for single databases with unpredictable or intermittent usage. It automatically scales compute based on demand and pauses during inactivity, which directly addresses the need to handle bursts without manual intervention while minimizing cost. Other options either require manual scaling or are optimized for different scenarios such as large databases or read offloading.

Exam trap

The trap here is assuming that any auto-scaling feature, such as read scale-out or elastic pools, will handle unpredictable compute bursts for a single database.

Practice this question →
97
MCQmedium

You are migrating an on-premises SQL Server 2019 database to Azure SQL Managed Instance. The database uses cross-database queries and SQL Server Agent jobs. You need to ensure that the migration is as seamless as possible and that these features continue to work after migration. What should you do first?

A.Create a new Azure SQL Managed Instance with the same configuration as the on-premises server.
B.Enable cross-database queries on the Azure SQL Managed Instance after migration.
C.Run the Data Migration Assistant (DMA) to assess compatibility and identify unsupported features.
D.Use the Azure Database Migration Service (DMS) to perform an online migration.
AnswerC

Data Migration Assistant (DMA) is the recommended tool to assess on-premises SQL Server databases for migration to Azure SQL Managed Instance. It identifies compatibility issues, unsupported features, and provides recommendations. For a database using cross-database queries and SQL Server Agent, DMA will verify that these features are supported in Azure SQL Managed Instance and highlight any necessary changes before migration, ensuring a smoother process.

Why this answer

Before migrating to Azure SQL Managed Instance, you should run the Data Migration Assistant (DMA) to assess the source database for compatibility. DMA identifies features like cross-database queries and SQL Server Agent that are supported in Azure SQL Managed Instance and flags any issues. This assessment ensures a seamless migration by allowing you to address problems beforehand.

Other steps are either part of the migration process or post-migration tasks, not the initial action.

Exam trap

The trap here is skipping assessment and directly using a migration tool, which can lead to failures due to undetected compatibility issues.

Practice this question →
98
MCQmedium

You are configuring Azure SQL Database for an e-commerce application that experiences variable traffic. You need to ensure that the database can automatically scale resources based on demand without manual intervention. The solution must also support scaling to zero compute when not in use to save costs. Which Azure SQL Database offering should you use?

A.Azure SQL Database serverless.
B.Azure SQL Database elastic pool.
C.Azure SQL Database Hyperscale.
D.Azure SQL Database Business Critical tier.
AnswerA

Azure SQL Database serverless automatically scales compute based on workload demand and can pause, scaling compute to zero during inactivity, billing only for storage while paused. This satisfies both constraints: no manual intervention for variable e-commerce traffic, and cost savings through zero-compute scaling when the database is unused.

Why this answer

Azure SQL Database serverless is the correct choice because it automatically scales compute resources based on demand and supports pausing the database when idle, effectively scaling to zero compute to save costs. This aligns perfectly with the requirements of variable traffic and cost optimization without manual intervention.

Exam trap

The trap here is that candidates may confuse the auto-scaling of serverless with the resource pooling of elastic pools, or assume Hyperscale's high performance includes cost-saving idle scaling, but only serverless offers the specific 'scale to zero' capability.

How to eliminate wrong answers

Option B is wrong because Azure SQL Database elastic pool provides resource sharing among multiple databases but does not support scaling to zero compute; it maintains a minimum resource allocation. Option C is wrong because Azure SQL Database Hyperscale is designed for large databases with high storage and throughput needs, not for automatic scaling to zero compute or cost savings through pausing. Option D is wrong because the Business Critical tier offers high availability and performance with fixed compute resources, lacking the ability to scale to zero or pause automatically.

Practice this question →
99
MCQmedium

You are managing an Azure SQL Managed Instance that hosts multiple databases for a financial application. You need to implement a security solution that meets compliance requirements by auditing all database activity and sending the audit logs to a centralized Log Analytics workspace for analysis. The solution must also support real-time alerts on suspicious activities. What should you configure?

A.Enable Microsoft Defender for Cloud and configure SQL vulnerability assessment.
B.Enable auditing to a storage account and use Microsoft Intune for monitoring.
C.Enable auditing to a Log Analytics workspace and integrate with Microsoft Sentinel.
D.Enable Microsoft Purview Data Map for the managed instance.
AnswerC

Auditing to a Log Analytics workspace centralises all database activity for compliance analysis, while Microsoft Sentinel ingests those same logs to provide real-time analytics rules and alerts on suspicious activity, satisfying both the centralised logging and real-time alerting constraints.

Why this answer

Auditing to a Log Analytics workspace allows centralized collection of audit logs, which can then be integrated with Microsoft Sentinel for real-time analytics, threat detection, and automated alerting on suspicious activities. This meets both the compliance requirement for auditing and the operational need for real-time alerts.

Exam trap

The trap here is that candidates may confuse Microsoft Defender for Cloud (which provides vulnerability assessment and security recommendations) with a full auditing and SIEM solution, or mistakenly think that a storage account plus Intune can provide real-time alerting, when in fact only Log Analytics with Sentinel delivers both centralized auditing and real-time threat detection.

How to eliminate wrong answers

Option A is wrong because Microsoft Defender for Cloud and SQL vulnerability assessment focus on security posture and vulnerability scanning, not on auditing all database activity or sending logs to a Log Analytics workspace for real-time alerts. Option B is wrong because while auditing to a storage account captures logs, Microsoft Intune is a mobile device management (MDM) and endpoint management tool, not a monitoring or alerting solution for database audit logs. Option D is wrong because Microsoft Purview Data Map is designed for data governance, cataloging, and lineage, not for auditing database activity or providing real-time security alerts.

Practice this question →
← PreviousPage 2 of 2 · 99 questions total

Ready to test yourself?

Try a timed practice session using only Plan and implement data platform resources questions.

Start 20-question session