Resource Governor is the correct tool for this scenario. Creating a workload group with a CAP_CPU_PERCENT limit allows the reporting queries to run but prevents them from consuming all available resources and destabilizing the primary transactional workload. This provides immediate, fine-grained control. Elastic pools manage resources across databases, not queries within a single database. Automatic tuning helps with plan regression but doesn’t proactively cap resource usage for specific query patterns. Read Scale-Out is for offloading read-only workloads, which could be part of a solution, but the core requirement is to limit the impact of specific queries, which is best done with Resource Governor.

The principle of least privilege is key. Removing broad role memberships (like db_datareader) and replacing them with a contained user with explicit, minimal permissions to only the necessary schema directly addresses the over-provisioning. Enabling Microsoft Defender for SQL (which includes threat detection) provides proactive monitoring for anomalous access patterns. Dynamic Data Masking only hides data in results, it doesn’t prevent access. Moving data to a separate database is a major architectural change and may not be practical. While enabling Advanced Data Security is good, it doesn’t by itself fix the excessive permissions; the corrective action (removing from db_datareader) is only part of the full solution described in the correct answer.

In Azure Synapse dedicated SQL pools, Clustered Columnstore Indexes are the standard for large fact tables due to high compression and query performance on analytics workloads. Hash distribution is preferred when there are frequent large joins on a common column, as it keeps joined data on the same compute node, minimizing data movement. Heaps can be a good staging choice. Round Robin is a default but is less optimal for joins. Replicated tables are excellent for small dimension tables but not for large facts. Traditional Clustered Indexes are not the primary performance structure for analytics in this environment.

For an accidental data modification like a dropped table, the fastest and most appropriate method is the standard point-in-time restore (PITR) using the automated backups that Azure SQL Database maintains (with 7-35 days retention by default). This creates a new database from a specific timestamp before the incident. Geo-restore is for disaster recovery when the primary region is unavailable. Restoring from a long-term retention (LTR) backup is for restoring from a point beyond the PITR window (e.g., 10 weeks ago), not for the most recent data loss event, and does not involve manually applying transaction logs in the PaaS service.

The Business Critical service tier for SQL Managed Instance uses a built-in, always-on availability group architecture with several synchronous replicates. It provides high availability within the same region with an RPO of 0 (no data loss) and a very low RTO (typically < 10s for unplanned, < 1m for planned) due to instant failover. Zone-redundant HA is a feature of the *General Purpose* tier, not a separate option. Geo-replication and auto-failover groups are for disaster recovery *across* regions, which meets a different requirement than the highest availability *within* a region specified here.

Elastic pools are cost-effective when databases have complementary, unpredictable usage patterns that “average out.” In this case, the low overall pool utilization indicates the pool is over-provisioned for the average load, but the predictable, isolated spikes on specific databases are causing performance issues. The most direct solution is to move those specific high-demand databases out to their own standalone servers (or a higher-tier pool) where they can be sized appropriately, allowing you to right-size and potentially downgrade the original pool for the remaining databases, optimizing cost and performance. Increasing the pool size is costly and inefficient for a few spiking DBs. Auto-pause isn’t suitable for pools with regular business-hour spikes. Hyperscale is a specific service tier, not a feature you enable on individual databases within a standard pool.

Table partitioning is the classic strategy for tiered storage based on age. In Azure, you can implement this by using partition switching to move old partitions to a separate database in a lower, cheaper service tier (e.g., Basic or Standard). Using Elastic Query, you can create an external data source that allows the primary database to query the archived data seamlessly, minimizing application changes. Temporal tables manage history automatically but keep it in the same database/tier, not cheaper storage. Moving to Blob Storage with PolyBase is more complex for querying and changes the query method. Cosmos DB is a different data model and is not a direct, query-transparent archive for relational data.

The SQL Database Projects extension in Azure Data Studio (or SSDT in Visual Studio) allows for declarative database development. You define the desired state of the database schema in a project, which can be version-controlled with Git. Building the project produces a DACPAC (or BACPAC) file. This artifact can then be deployed consistently across environments using a CI/CD pipeline in Azure DevOps, ensuring reliable, repeatable, and automated deployments. The other options are more manual, error-prone, and lack proper source control integration and deployment orchestration.

When a query retains the same plan but performs orders of magnitude slower, the most common cause in a cloud migration context is a mismatch in resource power (CPU, IO, Memory). The first step is to compare the performance level (e.g., DTU/vCore count) of the Azure SQL Database tier to the specifications of the source on-premises server. The cloud tier may simply be under-provisioned for this workload. While blocking and waits should be checked (and are a good general troubleshooting step), the scenario emphasizes the plan is the same and the degradation is extreme, pointing most directly to a resource ceiling. Network latency would typically not cause a 15x slowdown for a query that processes data on the server. Distribution keys are relevant for distributed systems like Synapse, not a regular Azure SQL DB.

Resource Governor is the correct tool for this scenario. Creating a workload group with a CAP_CPU_PERCENT limit allows the reporting queries to run but prevents them from consuming all available resources and destabilizing the primary transactional workload. This provides immediate, fine-grained control. Elastic pools manage resources across databases, not queries within a single database. Automatic tuning helps with plan regression but doesn’t proactively cap resource usage for specific query patterns. Read Scale-Out is for offloading read-only workloads, which could be part of a solution, but the core requirement is to limit the impact of specific queries, which is best done with Resource Governor.0