DP-300 Plan and implement data platform resources Practice Test 15 — 15 Questions

Question 1

You are managing an Azure SQL Database that experiences periods of high CPU usage. You need to identify the top resource-consuming queries and their execution plans to optimize performance. You want to use a built-in feature that provides historical query performance data with minimal configuration. What should you use?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Query Store. Query Store is a built-in feature that captures query execution history, plans, and runtime statistics. It requires minimal setup and provides the historical data needed to identify top CPU-consuming queries and analyze their execution plans, making it the best choice for performance tuning.

Answer

Dynamic management views (DMVs) such as sys.dm_exec_query_stats

Answer

Azure SQL Analytics

Answer

Automatic tuning

Question 2

You are a database administrator for a company that uses Azure SQL Database. The company wants to reduce the cost of storing backups. You need to configure the backup storage redundancy to the most cost-effective option while ensuring data durability within a single region. What should you do?

Accepted Answer

Configure locally redundant backup storage.. Locally redundant backup storage is the most cost-effective option that provides durability within a single region. It replicates backups three times within the same region, ensuring data redundancy without the added cost of cross-region replication. Geo-redundant and zone-redundant options offer higher durability but at a higher price.

Answer

Configure zone-redundant backup storage.

Answer

Configure geo-redundant backup storage.

Answer

Configure read-access geo-redundant backup storage.

Question 3

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?

Accepted Answer

Run the Data Migration Assistant (DMA) to assess compatibility and identify unsupported features.. 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.

Answer

Create a new Azure SQL Managed Instance with the same configuration as the on-premises server.

Answer

Enable cross-database queries on the Azure SQL Managed Instance after migration.

Answer

Use the Azure Database Migration Service (DMS) to perform an online migration.

Question 4

You are deploying an Azure SQL Managed Instance for a legacy application that requires SQL Server Agent jobs and cross-database queries. The instance must be able to access an on-premises file share for backup and restore operations. You need to configure network connectivity so that the managed instance can communicate with the on-premises network. What should you implement?

Accepted Answer

Configure a site-to-site VPN or ExpressRoute between the on-premises network and the Azure virtual network that hosts the managed instance.. Azure SQL Managed Instance is deployed in a virtual network subnet and requires a site-to-site VPN or ExpressRoute to communicate with on-premises resources. This provides a private, secure connection that allows the managed instance to access on-premises file shares for backup and restore. Public endpoints, VNet peering, and Private Link do not provide the necessary outbound connectivity to on-premises networks.

Answer

Enable a public endpoint on the managed instance and allow access from the on-premises public IP address.

Answer

Create a VNet peering between the managed instance subnet and the on-premises network.

Answer

Configure an Azure Private Link endpoint for the managed instance and connect it to the on-premises network.

Question 5

You have an Azure SQL Database that is used by a development team. The team works only during business hours and the database can be unavailable outside those hours. You need to minimize compute cost while allowing the database to automatically pause when it is idle and resume when a connection is made. What should you configure?

Accepted Answer

Set the database to the General Purpose service tier with the Serverless compute tier and configure the auto-pause delay.. The Serverless compute tier is the only Azure SQL Database option that automatically pauses compute after an idle period and resumes on the next connection. Configuring it on the General Purpose service tier lets the development team pay only for the compute used during business hours. The auto-pause delay controls how long the database waits before suspending, directly reducing cost for an intermittently used database.

Answer

Set the database to the Hyperscale service tier with one read-scale replica.

Answer

Set the database to the General Purpose service tier with the Provisioned compute tier.

Answer

Set the database to the Business Critical service tier with zone redundancy enabled.

Question 6

You are deploying an Azure SQL Database and need to enforce that all connections to the database use encrypted channels and that the server presents a specific certificate that the client validates. You also need to ensure that the database cannot be accessed from the public internet except through a private endpoint. Which two actions should you perform? (Choose two.)

Accepted Answer

Set the Encrypt connection setting to mandatory in the connection string and use TrustServerCertificate=False with a trusted root certificate.. A private endpoint with public network access disabled ensures the logical server is reachable only through a private IP address inside a virtual network, removing exposure to the public internet. Requiring encryption in the connection string and setting TrustServerCertificate to false forces TLS and makes the client validate the server certificate against a trusted root. Together these actions satisfy both the private connectivity and certificate-validation requirements.

Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault.

Answer

Enable Always Encrypted with secure enclaves for the columns that contain sensitive data.

Answer

Configure an Azure SQL Database firewall rule that allows only the application's public IP address.

Question 7

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?

Accepted Answer

Create a virtual network with a dedicated subnet delegated to Microsoft.Sql/managedInstances and configure a site-to-site VPN gateway.. 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.

Answer

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.

Answer

Create a virtual network with a subnet delegated to Microsoft.Sql/managedInstances and configure a private endpoint for the instance.

Answer

Create a virtual network with a subnet delegated to Microsoft.Sql/managedInstances and enable public endpoint access on the instance.

Question 8

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?

Accepted Answer

Azure SQL Managed Instance. 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.

Answer

Azure SQL Database hyperscale

Answer

Azure SQL Database elastic pool

Answer

Azure SQL Database single database

Question 9

You are deploying a new Azure SQL Database for a line-of-business application. The application's workload is unpredictable, with periods of near-zero activity overnight and heavy transactional bursts during business hours. You need to choose a purchasing model and service tier that minimizes cost while automatically scaling compute resources based on demand. What should you do?

Accepted Answer

Deploy the database using the vCore purchasing model with the Serverless compute tier and set the minimum and maximum vCores.. The Serverless compute tier in the vCore purchasing model is designed for databases with intermittent, unpredictable usage. It automatically scales compute up during active periods and down or pauses during inactivity, billing only for resources consumed. Setting minimum and maximum vCores provides cost control while enabling automatic scaling. This aligns with the need to minimize cost while handling variable demand, unlike fixed DTU or provisioned vCore tiers.

Answer

Deploy the database using the DTU purchasing model with the Standard service tier.

Answer

Deploy the database using the vCore purchasing model with the General Purpose service tier and configure auto-scaling.

Answer

Deploy the database using the vCore purchasing model with the Business Critical service tier and enable geo-replication.

Question 10

You are planning to deploy an Azure SQL Managed Instance to host several databases migrated from an on-premises SQL Server. The instance must support cross-database queries, SQL Server Agent jobs, and Service Broker. You need to ensure that the instance can handle the expected IOPS and throughput requirements. Which configuration should you implement?

Accepted Answer

Deploy the instance in the Business Critical service tier with local SSD storage.. The Business Critical service tier in Azure SQL Managed Instance uses local SSD storage, delivering the lowest latency and highest IOPS and throughput. It also supports cross-database queries, SQL Server Agent, and Service Broker, which are required. The General Purpose tier uses remote storage and may not meet high-performance demands. In-Memory OLTP alone does not overcome storage limitations, and geo-replication is for disaster recovery, not local performance. Therefore, Business Critical with local SSD is the correct choice.

Answer

Deploy the instance in the General Purpose service tier with standard storage.

Answer

Deploy the instance in the General Purpose service tier and enable the In-Memory OLTP feature.

Answer

Deploy the instance in the Business Critical service tier and configure geo-replication for read scale-out.

Question 11

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?

Accepted Answer

Enforce SSL connections and enable Azure Defender for SQL (Advanced Threat Protection) with vulnerability assessment.. 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.

Answer

Enable Dynamic Data Masking and configure Azure Monitor alerts for failed logins.

Answer

Configure Always Encrypted with column encryption and enable auditing to a storage account.

Answer

Enable Transparent Data Encryption (TDE) and configure a firewall rule to allow only the application's IP address.

Question 12

You need to migrate an on-premises SQL Server 2017 database to Azure SQL Database. The database contains a table with a column of data type geography and uses cross-database queries. You must determine the appropriate target platform. What should you do?

Accepted Answer

Migrate to Azure SQL Managed Instance.. Azure SQL Managed Instance supports cross-database queries within the same instance and maintains high compatibility with on-premises SQL Server, including support for the geography data type. Azure SQL Database single database and elastic pools do not support cross-database queries, and geo-replication is for disaster recovery, not query compatibility. Therefore, SQL Managed Instance is the correct migration target.

Answer

Migrate to Azure SQL Database elastic pool.

Answer

Migrate to Azure SQL Database with geo-replication enabled.

Answer

Migrate to Azure SQL Database single database.

Question 13

You are designing a backup strategy for an Azure SQL Database. The database is in the General Purpose service tier and is used for a critical application. You need to ensure that you can restore the database to any point in time within the last 30 days, and you need to retain backups for 10 years for compliance. (Choose two.)

Accepted Answer

Set the point-in-time restore retention policy to 30 days.. To meet the requirements, you need to set the point-in-time restore retention to 30 days and configure long-term retention (LTR) for 10 years. PITR retention can be configured up to 35 days, so 30 days is achievable. LTR allows retention up to 10 years. Backup storage redundancy and geo-replication do not affect retention periods, and SQL Server Agent is not available in Azure SQL Database. Therefore, the correct actions are configuring PITR to 30 days and LTR to 10 years.

Answer

Enable geo-replication for the database.

Answer

Configure automatic backups to a storage account using SQL Server Agent.

Answer

Configure the backup storage redundancy to locally-redundant storage (LRS).

Question 14

You are deploying a new Azure SQL Database for a small internal inventory application. The workload is steady, with predictable usage during business hours, and the application team requires a fixed monthly cost with no risk of unexpected overage charges. You need to choose a purchasing model and service tier that meets these requirements. What should you do?

Accepted Answer

Deploy the database by using the DTU purchasing model with a Standard service tier.. The DTU purchasing model with the Standard service tier provides a fixed monthly cost and is designed for steady, moderate workloads, which matches the small internal inventory application. The vCore model, whether Business Critical or General Purpose with auto-scaling, introduces variable costs that do not meet the fixed-cost requirement, and the Premium DTU tier is unnecessarily costly.

Answer

Deploy the database by using the vCore purchasing model with the General Purpose service tier and configure auto-scaling.

Answer

Deploy the database by using the DTU purchasing model with the Premium service tier.

Answer

Deploy the database by using the vCore purchasing model with the Business Critical service tier.

Question 15

You are deploying an Azure SQL Managed Instance to host several databases migrated from an on-premises SQL Server. The instance must be placed in a dedicated subnet within an Azure virtual network to allow communication with other Azure resources and on-premises systems over a site-to-site VPN. You need to configure the network environment. What should you do?

Accepted Answer

Create a subnet delegated to Microsoft.Sql/managedInstances and associate a network security group (NSG) that allows traffic on the required ports.. Azure SQL Managed Instance must be deployed into a dedicated subnet delegated to Microsoft.Sql/managedInstances. The subnet requires a network security group to control traffic and may require a route table with specific routes. This setup enables secure communication with other Azure resources and on-premises networks over VPN or ExpressRoute, fulfilling the networking requirements.

Answer

Create a subnet and deploy a private endpoint for the SQL Managed Instance, then configure private DNS zones for name resolution.

Answer

Create a subnet and assign a public IP address to the SQL Managed Instance, then configure firewall rules to restrict access.

Answer

Create a subnet and configure a service endpoint for Microsoft.Sql, then attach a route table with a default route to the internet.