CCNA Plan and implement data platform resources Questions

Why this answer

How to eliminate wrong answers

Option A is wrong because log shipping is not supported to Azure SQL Database; it only works between on-premises SQL Server instances or to SQL Server on Azure VMs, not to Azure SQL Database. Option B is wrong because an offline migration using Azure Database Migration Service would require taking the source database offline, causing significant downtime, which contradicts the requirement for minimal downtime. Option C is wrong because exporting a BACPAC file and importing it to Azure SQL Database is an offline process that does not support ongoing replication, leading to downtime, and it also does not handle unsupported features like FileTables automatically.

Why this answer

How to eliminate wrong answers

Option A is wrong because auto-failover groups with read-write failover policy are designed for group-level failover of multiple databases and have a default RPO of up to 5 seconds, but the failover is automatic and may not guarantee an RTO under 1 hour due to potential delays in detection and DNS propagation; more critically, the question specifies a manual failover requirement. Option B is wrong because geo-restore from geo-redundant backups has an RPO measured in hours (typically 1-12 hours) and an RTO that can exceed several hours, making it unsuitable for sub-5-second RPO. Option D is wrong because long-term retention backups are taken daily, resulting in an RPO of up to 24 hours, and restoring from them can take hours, failing both the RPO and RTO targets.

Why this answer

How to eliminate wrong answers

Option A is wrong because the General Purpose tier uses asynchronous replication with active geo-replication, which cannot guarantee zero data loss (RPO > 0) during a regional failure. Option B is wrong because Hyperscale tier named replicas are read-only and do not support synchronous replication or automatic failover for write workloads. Option C is wrong because SQL Server on Azure VMs with Always On Availability Groups requires manual configuration and management of the underlying infrastructure, and while it can achieve synchronous replication, it is not a fully managed PaaS service like Azure SQL Managed Instance, and automatic failover with zero data loss is not guaranteed without additional setup.

Why this answer

How to eliminate wrong answers

Option A is wrong because the serverless compute tier does not support read scale-out; read scale-out is a feature of the provisioned Hyperscale and Business Critical tiers, not General Purpose. Option B is wrong because setting auto-pause delay to 0 (disabled) is not a valid configuration; the minimum auto-pause delay is 60 minutes, and disabling auto-pause entirely would prevent the cost-saving benefits of serverless. Option C is wrong because a 1-minute auto-pause delay would cause the database to pause frequently during idle periods, leading to cold-start latency when bursts occur, which contradicts the requirement to handle sudden activity without significant latency.

Why this answer

How to eliminate wrong answers

Option A is wrong because active geo-replication with a readable secondary in another Azure region typically has an RPO of up to 5 seconds and an RTO of 1 hour or more, failing to meet the 30-second RTO requirement. Option B is wrong because a failover group with a secondary in a paired region also has an RPO of up to 5 seconds and an RTO of 1 hour, which exceeds the required 30-second RTO. Option D is wrong because an auto-failover group to a secondary region similarly has an RPO of up to 5 seconds and an RTO of 1 hour, and while it automates failover, it still cannot achieve the sub-minute RTO needed.

Why this answer

How to eliminate wrong answers

Option A is wrong because transactional replication requires schema and data to be initially synchronized, and while it can support minimal downtime, it introduces additional complexity and overhead for a large, high-transaction database, and is not the recommended primary migration tool for SQL Managed Instance. Option B is wrong because offline migration using DMS requires the source database to be taken offline during the data copy, causing significant downtime, which contradicts the requirement to minimize downtime. Option C is wrong because exporting a 500 GB database as a BACPAC file is a slow, offline process that does not support ongoing replication, leading to extended downtime and potential data loss for a high-transaction workload.

Why this answer

How to eliminate wrong answers

Option A is wrong because Get-AzSqlDatabase does not return schema details; schema information is obtained via T-SQL queries or tools like Get-AzSqlDatabaseSchema. Option B is wrong because backup retention policies are managed with Get-AzSqlDatabaseBackupLongTermRetentionPolicy or Get-AzSqlDatabaseShortTermRetentionPolicy, not Get-AzSqlDatabase. Option C is wrong because user permissions are listed using T-SQL commands (e.g., sys.database_permissions) or Get-AzSqlDatabaseUser, not the database-level cmdlet.

Why these pairings

Why this answer

How to eliminate wrong answers

Option B is wrong because Automatic tuning is a feature that uses AI to optimize query performance (e.g., index creation, plan forcing), but it does not offload read workloads to a separate replica. Option C is wrong because Connection pooling is a client-side technique that reuses database connections to reduce latency, but it does not redirect read traffic to a secondary database. Option D is wrong because Active geo-replication creates readable secondary replicas in different regions for disaster recovery and geo-distributed reads, but it requires manual connection string changes or application logic to route read traffic, unlike the transparent, built-in read scale-out feature.

Why this answer

How to eliminate wrong answers

Option A is wrong because the Basic tier does not support zone redundancy or geo-replication; it is designed for development and testing with no high availability or disaster recovery capabilities. Option C is wrong because the General Purpose tier uses asynchronous data replication and does not support zone redundancy; it can have geo-replication but cannot guarantee zero data loss during failover due to asynchronous replication. Option D is wrong because the Hyperscale tier supports zone redundancy but does not support active geo-replication for automated failover to a secondary region; its geo-replication is limited to named replicas with manual failover, not automated failover with no data loss.

Why this answer

How to eliminate wrong answers

Option A is wrong because Microsoft Purview Data Map is a data governance and cataloging service, not a security detection or alerting tool for SQL injection. Option B is wrong because Microsoft Defender XDR (Extended Detection and Response) is a unified security platform for endpoints, identities, and cloud apps, but it does not directly provide SQL injection detection for Azure SQL Managed Instance; that capability is part of Defender for SQL. Option C is wrong because Microsoft Intune is a mobile device management (MDM) and mobile application management (MAM) service, not a tool for configuring SQL security policies or detecting SQL injection attacks.

Why this answer

How to eliminate wrong answers

Option A is wrong because offline migration with automatic cutover requires the source database to be taken offline for the entire duration of data copy, which would exceed the 4-hour downtime window for a 1.5 TB database. Option B is wrong because transactional replication does not natively support Service Broker or cross-database queries as a migration method; it is designed for ongoing replication of specific tables, not full database migration with schema and dependencies. Option D is wrong because Azure backup to URL and restore to Managed Instance is an offline method that would require the database to be offline for the entire backup and restore process, exceeding the 4-hour window, and it does not inherently configure TDE with customer-managed keys or Entra ID authentication.

Why this answer

How to eliminate wrong answers

Option A is wrong because sys.dm_exec_requests shows currently executing requests at the moment of querying, not historical CPU consumption over the last 24 hours. Option B is wrong because sys.dm_exec_sessions provides session-level information such as login time and host details, but does not contain per-query CPU usage statistics. Option D is wrong because sys.dm_os_performance_counters returns operating system performance counter values (e.g., CPU utilization percentage), not the specific queries responsible for high CPU usage.

Why this answer

How to eliminate wrong answers

Option B is wrong because the DTU-based purchasing model bundles compute, storage, and I/O into fixed tiers, offering less granular control over resources and no reserved capacity option, making it less cost-effective for predictable workloads that need to scale. Option C is wrong because the Hyperscale service tier is designed for large databases with high storage and rapid scaling needs, but it uses a pay-as-you-go model without reserved capacity discounts, leading to higher costs for predictable, moderate workloads. Option D is wrong because the Serverless compute tier auto-pauses and scales based on demand, which is ideal for intermittent workloads but not for predictable performance with reserved resources, and it does not offer reserved capacity pricing.

Why this order

Why this answer

How to eliminate wrong answers

Option B is wrong because Managed Instance does not support the KEEP_TDE option; TDE must be configured using Azure Key Vault, not a self-signed certificate. Option C is wrong because you cannot upload a certificate file directly to a Managed Instance via SSMS; TDE in Azure SQL Managed Instance is managed exclusively through Azure Key Vault. Option D is wrong because disabling TDE on the source database would require decrypting the entire database, which is unnecessary and introduces significant downtime and risk; the correct approach is to migrate the TDE-protected database and re-enable TDE using a key vault key.

Why this answer

How to eliminate wrong answers

Option A is wrong because the Hyperscale tier, while offering high performance and fast scaling, does not support zone redundancy for the primary replica; it only supports zone redundancy for secondary replicas, so a zonal failure could cause data loss or downtime for the primary. Option B is wrong because the Standard tier does not offer zone redundancy; it uses local redundant storage (LRS) and can only provide availability within a single zone, so a zonal failure would result in data loss and downtime. Option D is wrong because the Basic tier does not support zone redundancy or geo-replication; it is designed for development and testing with no SLA for high availability, and active geo-replication is not available on the Basic tier.

Why this answer

How to eliminate wrong answers

Option B is wrong because manually creating a new database and copying data introduces significant downtime and complexity, and is not necessary when Azure SQL Database supports online scaling. Option C is wrong because enabling read scale-out only offloads read-only workloads to a readable secondary replica; it does not increase the compute or storage capacity of the primary database to handle peak-hour performance degradation. Option D is wrong because horizontal sharding is a design pattern for distributing data across multiple databases to handle massive scale, not a quick operational fix for a single database experiencing performance issues during peak hours.

Why this answer

How to eliminate wrong answers

Option A is wrong because Elastic pools are designed for managing multiple databases with variable but predictable resource needs, not for automatically scaling a single large database in response to unpredictable spikes; they require manual configuration of eDTU or vCore limits and do not auto-scale individual databases. Option B is wrong because manual scaling using Azure CLI requires human intervention to execute commands, which defeats the requirement for automatic scaling without manual intervention. Option C is wrong because the Serverless compute tier auto-pauses during inactivity and is intended for intermittent, low-traffic workloads, not for handling unpredictable high-traffic spikes; it has a maximum vCore limit and does not provide the rapid, elastic scaling needed for flash sales.

Why this answer

How to eliminate wrong answers

Option A is wrong because the General Purpose tier does not have a built-in read scale-out feature; readable secondaries are only available in the Business Critical tier or via active geo-replication. Option C is wrong because upgrading to the Business Critical tier would significantly increase costs, which contradicts the requirement to avoid increasing costs significantly. Option D is wrong because creating an elastic pool does not provide a readable secondary for read isolation; it distributes databases but does not offload read queries from a single database's primary replica.

Why this answer

How to eliminate wrong answers

Option B is wrong because adding the client's IP address to the ipRules array requires public network access to be enabled; with publicNetworkAccess set to 'Disabled', all IP firewall rules are ignored. Option C is wrong because setting publicNetworkAccess to 'Enabled' and adding an IP firewall rule would expose the server to the public internet, which is unnecessary and less secure when a virtual network rule can provide private connectivity. Option D is wrong because creating a server-level firewall rule to allow all Azure services (0.0.0.0/0) also requires public network access to be enabled and does not restrict traffic to a specific virtual network; it would allow any Azure service, not just the client in vnet2.

Why this answer

How to eliminate wrong answers

Option A is wrong because SQL Server on Azure Virtual Machines is an Infrastructure-as-a-Service (IaaS) offering where you are responsible for manually applying SQL Server updates and version upgrades, including major version changes. Option B is wrong because Azure SQL Database elastic pool is built on the same PaaS engine as single databases, but it is a deployment model for pooling resources across multiple databases, not a separate engine version; it also receives automatic updates, but the question asks for a deployment option that 'automatically provides the latest engine version,' and elastic pools share the same automatic update mechanism as single databases, so the distinction is that the question's correct answer is the single database as the most direct and common example, though elastic pools also qualify; however, the exam expects single database as the canonical answer. Option D is wrong because Azure SQL Managed Instance is also a PaaS offering with automatic updates, but it runs a near-100% compatible SQL Server engine that may lag slightly behind the latest engine version available in single databases, and it requires a manual decision to opt into preview or newer engine versions via configuration, so it does not 'automatically provide the latest engine version' without any action.

Why this answer

How to eliminate wrong answers

Option A is wrong because Standard tier has a maximum storage limit of 1 TB, not 4 TB. Option B is wrong because Hyperscale supports up to 100 TB, which exceeds the 4 TB requirement, but its architecture (with page servers and buffer pool extension) is not designed for a fixed 4 TB limit and is intended for very large databases, not the specific 4 TB cap. Option C is wrong because Basic tier is limited to 2 GB of storage, far below the 4 TB requirement.

Why this answer

How to eliminate wrong answers

Option A is wrong because SQL Vulnerability Assessment is a service that scans for potential database vulnerabilities and misconfigurations, not a feature that logs query execution. Option B is wrong because Advanced Threat Protection detects anomalous activities and potential threats, but it does not provide a full audit trail of all queries. Option C is wrong because Microsoft Defender for Cloud is a unified security management platform that provides security recommendations and threat protection across cloud workloads, but it does not itself enable per-query auditing to a Log Analytics workspace.

Why this answer

How to eliminate wrong answers

Option A is wrong because the General Purpose service tier uses remote storage and asynchronous replication, which cannot guarantee zero data loss during a zonal failure; it relies on page blob replication that may lose recent writes. Option B is wrong because Read scale-out provides a read-only replica for offloading read workloads, but it does not protect against zonal failures or ensure availability with zero data loss for write operations. Option C is wrong because Geo-redundant backup storage (RA-GRS) protects against regional disasters by storing backups in a paired region, but it does not provide real-time failover or zero data loss during a zonal failure within the primary region.

Why this answer

How to eliminate wrong answers

Option B is wrong because the Hyperscale tier is designed for large, high-throughput production workloads with rapid scaling and read scale-out, not for cost optimization of a small dev/test database; it incurs higher base costs even at 1 vCore and disabling auto-pause means compute runs 24/7. Option C is wrong because the provisioned General Purpose tier with 2 vCores and auto-pause disabled runs compute continuously, incurring charges for idle hours overnight and weekends, which is wasteful for a non-production database. Option D is wrong because the Business Critical tier is a premium tier with high availability and local SSD storage, intended for mission-critical workloads; even with 1 vCore and auto-pause enabled, it is significantly more expensive than serverless and over-provisioned for development testing.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because Hyperscale tier supports rapid scaling and read scale-out but does not provide a 99.995% SLA or automatic cross-region failover; its high-availability is zone-redundant within a single region. Option B is wrong because zone-redundant configuration protects against datacenter failures within a region, not against a full regional outage, and its SLA is 99.995% only for the Business Critical tier, but it lacks cross-region failover. Option D is wrong because Azure SQL Managed Instance with a failover group provides disaster recovery but its SLA is 99.99%, not 99.995%, and it is not a deployment option for Azure SQL Database.

Why this answer

How to eliminate wrong answers

Option A is wrong because Always Encrypted protects data at rest and in transit but does not enforce authentication or IP-based blocking, and SQL authentication does not meet the Microsoft Entra authentication requirement. Option C is wrong because Transparent Data Encryption (TDE) encrypts data at rest but does not control authentication or credentialless connections, and connection strings typically contain credentials. Option D is wrong because Azure Defender for SQL provides security monitoring and threat detection, not authentication or credentialless connectivity, and a service principal still requires credential management (e.g., client secret or certificate) unless combined with managed identity.

Why this answer

How to eliminate wrong answers

Option A is wrong because Managed Instance with failover groups provides automated failover but does not support readable secondaries for global read-scale; secondaries are not readable until failover occurs, so it does not address low-latency reads across regions. Option C is wrong because a single database in one region cannot serve users in other regions with low latency, as all traffic must traverse the network to that single region, increasing latency. Option D is wrong because an elastic pool in one region similarly confines all databases to a single region, failing to provide geo-distributed read access or low latency for multi-region users.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because creating a new partition scheme with a different partition function does not address the lack of a suitable index for point or range lookups; partitioning alone does not provide an index seek capability. Option C is wrong because rebuilding the clustered columnstore index with a different compression setting (e.g., COLUMNSTORE_ARCHIVE) would not change the scan-based access method for date-range queries and could degrade performance further. Option D is wrong because changing the clustered index to a rowstore B-tree on the partition key would eliminate the columnstore index, breaking the analytics workload that relies on columnstore compression and batch-mode processing.

Why this answer

How to eliminate wrong answers

Why this answer

How to eliminate wrong answers

Option B is wrong because standard geo-replication requires manual initiation of failover and does not support automatic failover, failing the 'without manual intervention' requirement. Option C is wrong because local redundancy with automatic failover refers to zone-redundant configurations within a single region, not cross-region failover, and cannot meet the RPO of 5 seconds for regional disasters. Option D is wrong because read-scale out is a feature for offloading read-only workloads to a replica in the same region, not for disaster recovery or automatic failover to a secondary region.

Why this answer

How to eliminate wrong answers

Option A is wrong because elastic database query is a read-only feature that allows querying remote Azure SQL databases via external tables, but it does not support write operations or replace the need to refactor cross-database queries that involve updates or complex joins across databases. Option B is wrong because Azure SQL Database does not support linked servers; linked servers are a SQL Server feature that requires a full instance, which is not available in the single database or elastic pool model. Option C is wrong because migrating to Azure SQL Managed Instance would preserve cross-database query capability, but it is not the best remediation—it avoids refactoring rather than addressing the root cause, and it may increase cost and complexity unnecessarily.

Why this answer

How to eliminate wrong answers

Option A is wrong because exporting a 5 TB database to a BACPAC file and importing via SqlPackage is an offline process that locks the database for the entire duration, causing significant downtime, and the BACPAC format is not optimized for very large databases, often leading to failures or extremely slow performance. Option C is wrong because backup and restore using URL with log shipping is not supported for Azure SQL Database (it is only supported for SQL Server on Azure VMs or on-premises); Azure SQL Database does not allow direct log shipping or restore of native backups from on-premises. Option D is wrong because transactional replication from on-premises to Azure SQL Database is not supported as a publisher-to-subscriber topology for Azure SQL Database as a target; Azure SQL Database can only be a subscriber in transactional replication, and even then, it requires specific configurations and does not support all data types or schema changes, making it unsuitable for a full migration of a 5 TB database with indexes.

Why this answer

How to eliminate wrong answers

Option B (Geo-replication) is wrong because geo-replication uses asynchronous replication by default, which can result in data loss of up to 5 seconds of transactions during a failover. Option C (Snapshot replication) is wrong because snapshot replication is a point-in-time copy mechanism used for reporting or backups, not for continuous synchronization or automatic failover. Option D (Asynchronous replication) is wrong because asynchronous replication does not guarantee zero data loss; it commits transactions on the primary first and replicates later, potentially losing uncommitted data during a sudden outage.

Why this answer

How to eliminate wrong answers

Option A is wrong because Transparent Data Encryption (TDE) encrypts data at rest but does not audit queries or log access to sensitive columns; server-level audit specifications for schema changes only track structural modifications, not data access. Option B is wrong because Dynamic Data Masking obfuscates data from non-privileged users but does not log actual data values; Azure SQL Database Auditing alone captures query events but not the actual data values accessed unless combined with a feature like SQL Server Audit that includes data capture. Option D is wrong because Azure SQL Database Auditing with diagnostic settings sends audit logs to Log Analytics but, by default, does not include the actual data values from queries; it only records that a query occurred, not the sensitive column values.

Why this answer

How to eliminate wrong answers

Option A is wrong because long-term backup retention is designed for archival and compliance purposes, not for meeting low RPO/RTO targets; it retains backups for years but cannot restore within 30 minutes or with a 5-second data loss. Option B is wrong because automated backups with geo-restore have an RPO of up to 1 hour (based on backup frequency) and an RTO that can exceed several hours due to the time required to restore a full backup to a different region, failing both the 5-second RPO and 30-minute RTO. Option C is wrong because zone-redundant configuration protects against a single availability zone failure within a region, not a complete regional outage, and thus cannot satisfy the requirement for regional disaster recovery.

Why this answer

How to eliminate wrong answers

Option B (Azure SQL Database serverless compute tier) is wrong because it is designed for intermittent, unpredictable workloads with auto-scaling and auto-pausing, which does not provide the predictable, isolated resource guarantees needed for multi-tenant SaaS with per-tenant performance management. Option C (Azure SQL Database single databases with DTU-based tier) is wrong because each database is isolated with fixed resources, but it lacks the ability to manage performance at the tenant level across a pool; you would need to over-provision for each tenant, leading to inefficiency and higher costs. Option D (Azure SQL Managed Instance with multiple databases) is wrong because it is a fully managed instance of SQL Server with shared resources across databases, offering no per-database resource isolation or min/max settings, making it unsuitable for predictable tenant-level performance isolation.

Why this answer

How to eliminate wrong answers

Option A is wrong because Azure SQL Managed Instance actually offers a wider range of built-in instance-level features (e.g., SQL Agent, cross-database queries, linked servers) compared to Azure SQL Database, which is a platform-as-a-service offering with fewer instance-scoped capabilities. Option C is wrong because Azure SQL Managed Instance supports databases up to 100 TB in size (with Business Critical tier), while Azure SQL Database Hyperscale supports up to 100 TB as well, so this is not a differentiating factor. Option D is wrong because both Azure SQL Database and Azure SQL Managed Instance support active geo-replication with readable secondaries; this feature does not uniquely favor Azure SQL Database over Managed Instance.

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because exporting a 2 TB database as a BACPAC file is an offline process that would cause extended downtime, and BACPAC is not designed for high-transaction workloads due to performance bottlenecks and lack of incremental sync. Option C is wrong because performing a full backup and restore followed by log backups is an offline migration method; while log backups can reduce data loss, the initial restore requires the database to be offline, and applying subsequent log backups still incurs downtime during the final restore. Option D is wrong because transactional replication requires schema and data modifications to be replicated, adds complexity for a full database migration, and is not optimized for a one-time bulk migration; it is better suited for ongoing synchronization of specific objects rather than a complete database move.

Why this answer

How to eliminate wrong answers

Option A is wrong because long-term retention (LTR) for 90 days is used for archival or compliance purposes beyond the default retention, not for a 7-day point-in-time restore requirement. Option B is wrong because configuring point-in-time restore for 35 days exceeds the required 7-day window and incurs additional storage costs unnecessarily. Option D is wrong because point-in-time restore for 1 day provides only a 1-day recovery window, which is insufficient for the 7-day requirement.

Why this answer

How to eliminate wrong answers

Option A is wrong because enabling a system-assigned managed identity on the Azure SQL Database itself is not required; Purview uses its own managed identity to connect, not the database's identity. Option B is wrong because SQL authentication with a dedicated login and firewall rule does not use a managed identity, violating the security team's requirement. Option D is wrong because creating a user-assigned managed identity and assigning it to the database is unnecessary; Purview's own managed identity (system-assigned or user-assigned) is used for authentication, and the database does not need its own identity for this scan.

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because migrating to SQL Server on Azure Virtual Machines, while offering full control, is unnecessary when Managed Instance already supports the required features and reduces management overhead. Option C is wrong because Azure SQL Database elastic query is designed for querying remote databases across different servers or instances, not for native cross-database queries within the same instance, and it does not support SQL Agent proxies. Option D is wrong because Azure SQL Database does not support SQL Server Agent proxies and has limited cross-database query capabilities (only within elastic pools using elastic query), making it unsuitable for this workload.

Why this answer

Why this answer

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.

Why this answer

How to eliminate wrong answers

Option B is wrong because hosting all databases on a single SQL Server on Azure Virtual Machines does not allow independent scaling of each database; all databases share the same instance resources, and scaling requires resizing the VM. Option C is wrong because a single Azure SQL Managed Instance also shares resources across all databases in that instance, preventing independent scaling per database and often incurring higher costs for mixed workloads. Option D is wrong because deploying each database as a single Azure SQL Database with its own DTU-based tier is less cost-effective than an elastic pool when databases have varying and often low utilization, as each database pays for its peak capacity independently.

Why this answer

How to eliminate wrong answers

Option B is wrong because General Purpose serverless does not support zone redundancy or read-scale replicas, and geo-replication alone does not guarantee 30-second failover or 99.99% SLA. Option C is wrong because General Purpose does not offer read-scale replicas (only Business Critical and Hyperscale do), and active geo-replication failover is manual or requires custom automation, not automated within 30 seconds. Option D is wrong because Hyperscale with zone redundancy does not include a readable secondary replica for read-scale (it uses page servers and a log service, not a dedicated readable secondary), and its failover behavior is not designed for sub-30-second automated failover across regions.

Why this answer

How to eliminate wrong answers

Option A is wrong because creating a plan guide to force a specific plan would lock in one plan for all parameter values, which could lead to suboptimal performance for other values and does not address the root cause of high compile time. Option B is wrong because 'optimize for ad hoc workloads' only defers the cost of storing single-use plans, but does not prevent recompilation due to parameter sniffing; it does not reduce compile time for frequently executed queries. Option C is wrong because adding Query Store and forcing the last good plan still relies on a single plan for all executions, which may not be optimal for varying parameter values and does not reduce compile time; it only ensures plan stability after a regression.

Why this answer

How to eliminate wrong answers

Option A is wrong because backup and restore across regions has an RPO of at least 1 hour (due to the 1-hour backup frequency) and requires manual or scripted restore, which cannot achieve automatic failover or the lowest possible RPO/RTO. Option B is wrong because setting a grace period of 0 would cause immediate failover on any connectivity issue, leading to unnecessary failovers and potential data loss; also, including only one database ignores the requirement to cover all critical databases. Option C is wrong because active geo-replication requires application code changes to handle failover (e.g., updating connection strings) and does not provide automatic failover without custom health checks, which adds complexity and violates the 'no application code changes' requirement.

Why this answer

How to eliminate wrong answers

Option B is wrong because a database snapshot is a point-in-time, static copy that does not reflect ongoing changes, making it unsuitable for real-time reporting and requiring manual refresh. Option C is wrong because a geo-replica is designed for disaster recovery across regions, not for offloading reporting within the same region; it introduces latency and additional cost without the same low-latency read scale-out benefits. Option D is wrong because enabling read scale-out on the database by adding a secondary replica is the same concept as the readable secondary in an auto-failover group, but the phrase 'adding a secondary replica' is ambiguous and not a standalone configuration; the correct implementation is through an auto-failover group, which provides the readable secondary endpoint.

Why this answer

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.

Why this answer

Why this answer

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.

Why this answer

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.

Why this answer

Why this answer

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.

Why this answer

How to eliminate wrong answers

Option A is wrong because auto-failover groups alone do not guarantee an RPO of 5 seconds; they rely on the underlying replication method, which defaults to asynchronous replication but can be configured with Active Geo-Replication for tighter RPO, but the option does not specify Active Geo-Replication, so it may use standard geo-replication with higher RPO. Option C is wrong because zone-redundancy within a single region protects only against zonal failures, not regional outages, and cannot meet the RPO/RTO for a global application requiring cross-region failover. Option D is wrong because geo-restore of automated backups has an RPO of 5-10 minutes (backup frequency) and an RTO of hours (restore time), failing the 5-second RPO and 1-hour RTO requirements.

Why this answer

How to eliminate wrong answers

Why this answer

How to eliminate wrong answers

Option A is wrong because Azure SQL Database in the General Purpose tier does not provide readable secondaries; it uses local SSD storage for tempdb but relies on remote storage (Azure Premium Blob) for data and log, and its high availability model does not include readable replicas. Option B is wrong because SQL Server on Azure VMs with Always On Availability Groups requires significant administrative overhead to configure, maintain, and monitor the Windows Server Failover Cluster, listener, and replica synchronization, which contradicts the 'least administrative overhead' requirement. Option D is wrong because Azure SQL Database in the Hyperscale tier, while offering readable secondaries and high availability, is designed for very large databases (up to 100 TB) and may introduce higher latency for write-heavy workloads due to its log-based page server architecture, and it is not the optimal choice for a 1 TB database when Business Critical provides a simpler, fully managed solution with lower overhead.

Why this answer

How to eliminate wrong answers

Option A is wrong because Azure Database Migration Service (DMS) is a tool for performing the actual migration (online or offline), not for assessing compatibility; it assumes the source is already compatible. Option B is wrong because Azure Data Studio with the SQL Server migration extension is a newer tool that combines assessment and migration, but it relies on DMA under the hood for assessment and is not the primary standalone assessment tool for complex features like CLR and cross-database queries. Option D is wrong because SQL Server Management Studio (SSMS) lacks a built-in compatibility assessment feature for Azure SQL Managed Instance; it can be used to run scripts but does not provide automated analysis of Agent jobs, cross-database queries, or CLR assemblies.

Why this answer

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.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because exporting to BACPAC and importing to Managed Instance does not preserve server-level objects like logins, jobs, or SQL Server Agent alerts, and it requires significant downtime as the database must be offline during export/import. Option C is wrong because transactional replication can replicate data changes but does not migrate server-level objects (logins, jobs, alerts) and requires complex setup for schema and object synchronization, making it unsuitable for a full migration with minimal downtime. Option D is wrong because backing up to URL and restoring to Managed Instance preserves the database but does not migrate server-level objects such as logins, jobs, or SQL Server Agent alerts; additionally, the restore process incurs downtime as the database must be offline during the restore.

Why this answer

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.

Why this answer

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.

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because auto-failover groups require the secondary to be in the same region or a paired region, but they do not support automatic failover with minimal data loss; they rely on asynchronous replication and can lose up to 5 seconds of data, but the key issue is that auto-failover groups are designed for manual or automatic failover with a defined replication policy, yet they still use asynchronous replication, which may not meet the 'minimal data loss' requirement as precisely as Active Geo-Replication with a readable secondary. Option C is wrong because deploying a failover group with manual failover requires human intervention to trigger the failover, which does not satisfy the 'automatic failover' requirement. Option D is wrong because geo-restore recovers the database from the most recent geo-replicated backup, which can result in up to one hour of data loss (RPO of 1 hour), far exceeding the 'minimal data loss' requirement.

Why this answer

How to eliminate wrong answers

Option A is wrong because shrinking the database is a temporary fix that does not address the root cause of reaching the storage limit; it may also cause fragmentation and performance degradation. Option B is wrong because scaling up to a higher service objective (e.g., Business Critical) is unnecessary and more expensive; the issue is storage capacity, not performance or I/O limits, and the General Purpose tier supports up to 4 TB with appropriate max size settings. Option C is wrong because the error is about overall database size, not specifically the transaction log; increasing log file size would not help if the data file has reached its max size, and Azure SQL Database manages log file growth automatically within the allocated storage.

Why this answer

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.

Why this order