DP-300 Practice Test 9 — 10 Questions

Question 1

Your team uses Azure SQL Database and wants to use Microsoft Entra ID authentication. You need to create a contained database user mapped to a Microsoft Entra ID application (service principal). Which T-SQL command should you use?

Accepted Answer

CREATE USER [app-name] FROM EXTERNAL PROVIDER;. Option D is correct because `CREATE USER [app-name] FROM EXTERNAL PROVIDER` creates a contained database user mapped to a Microsoft Entra ID identity (user, group, or application) directly in the database, without requiring a server-level login. This is the required syntax for authenticating a service principal (application) in Azure SQL Database using Microsoft Entra ID authentication.

Answer

CREATE USER [app-name] FROM LOGIN [app-name];

Answer

CREATE LOGIN [app-name] FROM EXTERNAL PROVIDER;

Answer

CREATE USER [app-name] WITH PASSWORD = 'password';

Question 2

You have an Azure SQL Database that stores sensitive customer data. You need to ensure that the data is encrypted at rest using a customer-managed key stored in Azure Key Vault. What should you configure?

Accepted Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key from Azure Key Vault.. Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault encrypts the database at rest, using a key that you control and rotate independently. This meets the requirement for encryption at rest with a customer-managed key, as TDE performs real-time I/O encryption and decryption of the data and log files without requiring application changes.

Answer

Configure dynamic data masking (DDM).

Answer

Implement row-level security (RLS) to restrict access.

Answer

Enable Always Encrypted for the sensitive columns.

Question 3

You need to automate the deployment of schema changes to multiple Azure SQL Databases in different regions. The solution must support rollback and version control. Which technology should you use?

Accepted Answer

Use Azure DevOps with a database project and release pipelines.. Azure DevOps with a database project and release pipelines is the correct choice because it provides source control for schema changes, automated deployment across multiple environments, and built-in rollback capabilities through pipeline versioning and deployment history. This approach aligns with infrastructure-as-code principles, enabling consistent, repeatable, and auditable schema deployments to Azure SQL Databases in different regions.

Answer

Use Azure Data Factory to run stored procedures for schema changes.

Answer

Use SQL Server Agent jobs to run deployment scripts on schedule.

Answer

Use Azure Automation with PowerShell scripts to execute T-SQL scripts.

Question 4

You manage an Azure SQL Database that supports a critical application. You need to automate the process of rebuilding indexes that have fragmentation above 30% on a weekly basis. The solution must use built-in database features and minimize performance impact. What should you do?

Accepted Answer

Enable automatic tuning and set the 'Force plan' and 'Create index' options.. Option C is correct because Azure SQL Database's automatic tuning feature includes the 'Create index' option, which can automatically rebuild indexes with high fragmentation when enabled. This uses built-in database features without requiring external scripts or jobs, and it minimizes performance impact by leveraging the database engine's own intelligent scheduling and execution.

Answer

Create a SQL Agent job that runs ALTER INDEX REBUILD on all indexes with fragmentation >30%.

Answer

Use Azure Automation to run a PowerShell script that checks fragmentation and rebuilds indexes.

Answer

Schedule a weekly job using elastic jobs to reorganize all indexes.

Question 5

Refer to the exhibit. You are monitoring index fragmentation in an Azure SQL Database. You need to automate the rebuild of this index when fragmentation exceeds 50%. The rebuild must be online to minimize downtime. Which T-SQL statement should you include in your automated maintenance job?

Accepted Answer

ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = ON);. Option B is correct because it uses the `ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = ON)` statement, which rebuilds all indexes on the table online, allowing concurrent user access and minimizing downtime. This meets the requirement to automate the rebuild when fragmentation exceeds 50%, as online rebuilds are supported in Azure SQL Database for this purpose.

Answer

ALTER INDEX Orders ON Orders REBUILD;

Answer

ALTER INDEX ALL ON Orders REORGANIZE;

Answer

ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = OFF);

Question 6

Drag and drop the steps to configure automatic tuning for an Azure SQL Database in the correct order.

Question 7

Drag and drop the steps to migrate an on-premises SQL Server database to Azure SQL Database using the Data Migration Assistant (DMA) in the correct order.

Question 8

Drag and drop the steps to configure a SQL Server Agent job in Azure SQL Managed Instance to run a maintenance task in the correct order.

Question 9

Drag and drop the steps to configure an Azure SQL Database elastic pool in the correct order.

Question 10

You are migrating an on-premises SQL Server 2012 database to Azure SQL Managed Instance. The database is 5 TB and uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. What is the best approach to migrate while preserving TDE?

Accepted Answer

Back up the certificate and private key to a .pfx file, restore the .pfx to the target managed instance, then restore the database backup.. Option C is correct because TDE in SQL Server relies on a certificate (or asymmetric key) that must be present in the target instance to decrypt the database backup. By backing up the certificate and private key to a .pfx file and restoring it to Azure SQL Managed Instance, you ensure the target has the necessary encryption keys to read the backup. Azure SQL Managed Instance supports restoring TDE-protected backups only if the corresponding certificate is first restored into the master database.

Answer

Use Azure Data Studio to import the certificate directly from the local machine store during migration.

Answer

Disable TDE on the source database, migrate the backup, then enable TDE on the target.

Answer

Create a master key in the target managed instance and then restore the database; the certificate will be imported automatically.