DP-300 Practice Test 36 — 15 Questions

Question 1

You are the database administrator for a SQL Server 2019 instance on Azure Virtual Machines. The instance hosts a database that must be available during a planned operating system update that requires a restart of the virtual machine. You need to ensure that the database remains online with minimal downtime. What should you do?

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

Accepted Answer

Configure an Always On availability group with a synchronous secondary replica on a second VM.. To keep the database online during a planned OS update that requires a VM restart, you should use an Always On availability group with a synchronous secondary replica. Before restarting the primary VM, you manually fail over to the secondary, which becomes the new primary and keeps the database online. After the update, you can fail back. Other options either cause downtime or are not suitable for this scenario.

Answer

Enable Azure Site Recovery for the virtual machine.

Answer

Use the SQL Server IaaS Agent extension to apply the update without restarting.

Answer

Create a database snapshot and restore it after the update.

Question 2

You are configuring an elastic job in Azure SQL Database to run a T-SQL script on all databases within an elastic pool. The script must run on a schedule. You have already created the job agent, job, and target group. You need to ensure that the job step executes against every database in the pool, including databases added later. What should you configure for the target group?

Accepted Answer

Set the target group to include the elastic pool, and set the job step to target that group.. Elastic job target groups can be defined at the elastic pool level. When the job executes, the agent resolves the target group to the current set of databases in the pool, so any database added afterward is included automatically. This provides dynamic, scalable targeting without manual updates.

Answer

Set the target group to include the logical server, and set the job step to target that group.

Answer

Add each database individually to the target group and update the group when new databases are added.

Answer

Create a separate job for each database and schedule them individually.

Question 3

You need to automate the deployment of an Azure SQL Database and its schema to multiple environments (dev, test, prod) using a repeatable process. The solution must support version control and CI/CD integration. What should you use?

Accepted Answer

Azure DevOps Pipelines with a DACPAC deployment task. Azure DevOps Pipelines with a DACPAC deployment task provide a robust CI/CD solution for deploying Azure SQL Database schemas. The DACPAC encapsulates the schema, and pipelines integrate with Git for version control. This enables automated, repeatable deployments across multiple environments with minimal manual intervention, aligning with DevOps best practices.

Answer

Azure Automation runbooks that execute sqlcmd scripts

Answer

Azure Data Factory with a copy activity

Answer

Azure Logic Apps with a SQL Server connector

Question 4

You have an Azure SQL Database named InventoryDB in the North Europe region. The database is in the General Purpose service tier. The business requires that InventoryDB remains available if a single availability zone within the North Europe region fails. You need to configure the database to meet this requirement with minimal downtime. What should you do?

Accepted Answer

Enable zone redundancy for InventoryDB.. Zone redundancy is the feature designed to protect against availability zone failures within a region. It replicates databases synchronously across zones, ensuring automatic failover and minimal downtime. Other options either address regional disasters, require manual intervention, or do not provide zone-level protection.

Answer

Change the service tier to Business Critical.

Answer

Configure active geo-replication with a secondary database in the same region.

Answer

Configure an auto-failover group with a secondary server in West Europe.

Question 5

You are deploying an Azure SQL Database and need to ensure that the database files are encrypted at rest using a key that you manage in Azure Key Vault. You also need to ensure that the key is automatically rotated. What should you configure?

Accepted Answer

Enable Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault.. TDE with customer-managed keys in Azure Key Vault allows you to control the encryption key and manage its lifecycle. You can configure automatic key rotation by setting a rotation policy in Key Vault and enabling the TDE protector to use the latest key version. This satisfies both the encryption at rest and key management requirements.

Answer

Enable Transparent Data Encryption (TDE) with service-managed keys.

Answer

Enable Dynamic Data Masking with Azure Key Vault integration.

Answer

Enable Always Encrypted with column master keys in Azure Key Vault.

Question 6

You administer an Azure SQL Database named SalesDB that uses the Business Critical service tier. The database is deployed in the West US 2 region, which does not support availability zones. The application requires a recovery time objective (RTO) of less than 30 seconds for a zonal failure within the region. You need to meet the RTO requirement with minimal administrative effort. What should you do?

Accepted Answer

Migrate the database to a region that supports availability zones and enable zone redundancy.. Zone redundancy in the Business Critical tier provides automatic failover across availability zones with an RTO of less than 30 seconds. Because West US 2 lacks availability zone support, the database must be moved to a region that supports them. Configuring cross-region replication or read replicas does not address zonal failures with the required RTO. Migrating and enabling zone redundancy is the correct solution.

Answer

Enable zone redundancy on the database.

Answer

Create a read-scale out replica in the same region.

Answer

Configure an auto-failover group with a secondary in East US 2.

Question 7

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.

Question 8

You administer an Azure SQL Database named HRDB. The security team requires that all data written to the database be encrypted with a customer-managed key that is stored in Azure Key Vault, and that the key be automatically rotated every 90 days. You need to configure Transparent Data Encryption (TDE) with Bring Your Own Key (BYOK). What should you do first?

Accepted Answer

Create an Azure Key Vault with purge protection enabled and grant the logical server's managed identity the Key Vault Crypto Service Encryption User role.. To configure TDE with BYOK, you first need an Azure Key Vault with purge protection enabled and the logical server's managed identity granted the appropriate Key Vault Crypto Service Encryption User role. This allows the server to access and use the key as the TDE protector. Without this prerequisite, the key cannot be used for encryption.

Answer

Enable Always Encrypted on all columns in HRDB to use the customer-managed key.

Answer

Export the existing TDE protector from the master database and upload it to Azure Key Vault.

Answer

Create a database-scoped credential that references the Azure Key Vault key and assign it to the database.

Question 9

You manage an Azure SQL Database that runs an online transaction processing (OLTP) workload. The database is in the General Purpose service tier with 4 vCores. During month-end processing, you observe that the database is hitting its maximum allowed log write throughput, causing delays. You need to increase the maximum log write throughput without changing the service tier. What should you do?

Accepted Answer

Scale up to 8 vCores.. In the General Purpose service tier, log write throughput is directly proportional to the number of vCores. Scaling up vCores is the only way to increase log write throughput while remaining in the same service tier. The other options either change the service tier, affect storage size, or add replication overhead without solving the bottleneck.

Answer

Increase the max size of the database.

Answer

Enable the Business Critical service tier.

Answer

Configure active geo-replication.

Question 10

You are the database administrator for a SQL Server 2019 instance on Azure Virtual Machines. The instance hosts a database that must be available during a planned operating system update that requires a restart of the virtual machine. You need to ensure that the database remains online with minimal downtime. What should you do?

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

Accepted Answer

Create a security policy that uses a predicate function comparing the tenant ID column to the result of DATABASE_PRINCIPAL_ID().. Row-level security in Azure SQL Database uses a security policy with an inline table-valued predicate function. When the predicate compares the tenant column to the current database principal, the engine transparently filters rows for every query, including aggregates, preventing cross-tenant visibility without application changes. Views, masking, and impersonation each leave gaps or add maintenance overhead that the scenario rules out.

Answer

Create a view for each tenant that filters rows by tenant ID, and grant tenants access only to their view.

Answer

Configure a database-scoped credential and use EXECUTE AS USER in every stored procedure that queries tenant data.

Answer

Enable Dynamic Data Masking on the tenant ID column so tenants cannot see values belonging to other tenants.

Question 11

You are the database administrator for a SQL Server 2019 instance on Azure Virtual Machines. The instance hosts a database that must be available during a planned operating system update that requires a restart of the virtual machine. You need to ensure that the database remains online with minimal downtime. What should you do?

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

Accepted Answer

Create a contained database user in the target database that maps to the managed identity and grant the required permissions.. When an application uses a managed identity to connect to Azure SQL Database, the identity must be represented as a contained database user in the target database. The user is created with CREATE USER [identity-name] FROM EXTERNAL PROVIDER, and then permissions are granted. This allows the identity to authenticate and access only the objects it needs, following least privilege. Without this user, the token-based login will not map to a database principal.

Answer

Add the managed identity as a server-level Microsoft Entra ID administrator.

Answer

Store the managed identity's client secret in Azure Key Vault and configure the application to retrieve it.

Answer

Enable Microsoft Entra ID authentication on the server and set the managed identity as the server admin.

Question 12

You have an Azure SQL Database that uses the Hyperscale service tier. The database is 4 TB and has a readable secondary in a different region. You need to ensure that if the primary region fails, the secondary can be promoted to primary with minimal data loss and without reconfiguring the application connection string. What should you implement?

Accepted Answer

Configure an auto-failover group that includes the primary and secondary databases.. Auto-failover groups are the recommended solution for cross-region disaster recovery when you need a stable listener endpoint that does not change after failover. They support Hyperscale tier databases and provide automatic failover with minimal data loss. Active geo-replication requires manual connection string updates, and zone redundancy only provides local high availability. Therefore, the failover group is the only option that meets all requirements.

Answer

Create a geo-secondary using the Azure CLI and configure the application to use the secondary's server name.

Answer

Configure active geo-replication and update the application connection string to point to the secondary after failover.

Answer

Enable zone redundancy on the primary database and rely on automatic failover within the region.

Question 13

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 14

You administer an Azure SQL Database named FinanceDB. Auditors require that all SELECT statements against a table named Ledger be recorded with the identity of the caller, and that the audit records be retained for seven years in immutable storage. You need to configure auditing to meet these requirements. What should you do?

Accepted Answer

Enable auditing with the target set to a storage account configured with an immutability policy and a retention period of seven years.. Auditing to a storage account with a time-based immutability policy both records caller identity for the monitored statements and enforces immutable retention for the required period. Retention of 0 is not immutable, Log Analytics retention is mutable, and Event Hubs is a transient streaming target. Only the immutability policy on the storage account meets the auditors' requirement.

Answer

Enable auditing with the target set to Log Analytics and set the workspace retention to seven years.

Answer

Enable auditing with the target set to Event Hubs and forward events to a consumer application.

Answer

Enable server auditing with the target set to a storage account and set retention to 0.

Question 15

You are deploying a new Azure SQL Database for an internal HR application. The database will store employee records and must be encrypted at rest using a key that your organization rotates every 90 days. The key must be stored in Azure Key Vault and must not be accessible to Microsoft. You need to configure Transparent Data Encryption (TDE) to meet these requirements. What should you do first?

Accepted Answer

Create an Azure Key Vault, generate a key, and grant the Azure SQL logical server's managed identity access to the key.. The requirement is to use a customer-managed key stored in Azure Key Vault for TDE, ensuring Microsoft cannot access the key. The first step is to create the Key Vault and key, then grant the Azure SQL logical server's managed identity access to that key. This enables the server to use the key for TDE. Only after this trust is established can you configure the database to use the key. Other options either use service-managed keys or address unrelated security aspects.

Answer

Enable TDE with a service-managed key on the Azure SQL logical server.

Answer

Create a database master key (DMK) in the master database and encrypt it with a password.

Answer

Configure a firewall rule to allow the application to connect to the database.