Free DP-300 Sample Questions — Administering Relational Databases on Microsoft Azure

You have an Azure SQL database. Users report that the executions of a stored procedure are slower than usual. You suspect that a regressed query is causing the performance issue. You need to view the query execution plan to verify whether a regressed query is causing the issue. The solution must minimize effort. What should you use?

• A. Performance Recommendations in the Azure portal
• B. Extended Events in Microsoft SQL Server Management Studio (SSMS)
• C. Query Store in Microsoft SQL Server Management Studio (SSMS)
• D. Query Performance Insight in the Azure portal

You are planning disaster recovery for the failover group of an Azure SQL Database managed instance. Your company's SLA requires that the database in the failover group become available as quickly as possible if a major outage occurs. You set the Read/Write failover policy to Automatic. What are two results of the configuration? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.

• A. In the event of a datacenter or Azure regional outage, the databases will fail over automatically
• B. In the event of an outage, the databases in the primary instance will fail over immediately
• C. In the event of an outage, you can selectively fail over individual databases
• D. In the event of an outage, you can set a different grace period to fail over each database
• E. In the event of an outage, the minimum delay for the databases to fail over in the primary instance will be one hour

You have an Azure subscription. You need to deploy an instance of SQL Server on Azure Virtual Machines. The solution must meet the following requirements: • Custom performance configuration, such as IOPS, capacity, and throughout, must be supported. • Costs must be minimized. Which type of disk should you include in the solution?

• A. Premium SSD v2
• B. Premium SSD
• C. Standard SSD
• D. Ultra SSD

You have an Azure subscription. You need to deploy an Azure SQL database. The solution must meet the following requirements: • Dynamically scale CPU resources. • Ensure that the database can be paused to reduce costs. What should you use?

• A. the Business Critical service tier
• B. the serverless compute tier
• C. an elastic pool
• D. the General Purpose service tier

You are planning a solution that will use Azure SQL Database. Usage of the solution will peak from October 1 to January 1 each year. During peak usage, the database will require the following: ✑ 24 cores ✑ 500 GB of storage ✑ 124 GB of memory ✑ More than 50,000 IOPS During periods of off-peak usage, the service tier of Azure SQL Database will be set to Standard. Which service tier should you use during peak usage?

• A. Business Critical
• B. Premium
• C. Hyperscale

You have a Microsoft SQL Server 2019 instance in an on-premises datacenter. The instance contains a 4-TB database named DB1. You plan to migrate DB1 to an Azure SQL Database managed instance. What should you use to minimize downtime and data loss during the migration?

• A. distributed availability groups
• B. database mirroring
• C. Always On Availability Group
• D. Azure Database Migration Service

You are designing an enterprise data warehouse in Azure Synapse Analytics that will contain a table named Customers. Customers will contain credit card information. You need to recommend a solution to provide salespeople with the ability to view all the entries in Customers. The solution must prevent all the salespeople from viewing or inferring the credit card information. What should you include in the recommendation?

• A. row-level security
• B. data masking
• C. Always Encrypted
• D. column-level security

You have an instance of SQL Server on Azure Virtual Machines named VM1. You plan to schedule a SQL Server Agent job that will rebuild indexes of the databases hosted on VM1. You need to configure the account that will be used by the agent. The solution must use the principle of least privilege. Which operating system user right should you assign to the account?

• A. Increase scheduling priority
• B. Log on as a service
• C. Profile system performance
• D. Log on as a batch job

You have an Azure SQL database named DB1. You need to display the estimated execution plan of a query by using the query editor in the Azure portal. What should you do first?

• A. Run the SET SHOWPLAN_ALL Transact-SQL statement
• B. For DB1, set QUERY_CAPTURE_MODE of Query Store to All
• C. Run the SET FORCEPLAN Transact-SQL statement
• D. Enable Query Store for DB1

You have an Azure Data Factory that contains 10 pipelines. You need to label each pipeline with its main purpose of either ingest, transform, or load. The labels must be available for grouping and filtering when using the monitoring experience in Data Factory. What should you add to each pipeline?

• A. an annotation
• B. a resource tag
• C. a run group ID
• D. a user property
• E. a correlation ID

You have an on-premises app named App1 that stores data in an on-premises Microsoft SQL Server 2019 database named DB1. You plan to deploy additional instances of App1 to separate Azure regions. Each region will have a separate instance of App1 and DB1. The separate instances of DB1 will sync by using Azure SQL Data Sync. You need to recommend a database service for the deployment. The solution must minimize administrative effort. What should you include in the recommendation?

• A. Azure SQL Managed instance
• B. Azure SQL Database single database
• C. Azure Database for PostgreSQL
• D. SQL Server on Azure virtual machines

You have an Azure subscription that contains an Azure SQL database named db1. You need to implement SQL insights for db1. Which two resources should you create first? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

• A. a storage account
• B. a virtual machine
• C. an Azure logic app
• D. an Azure function
• E. a Log Analytics workspace

Your on-premises network contains a Microsoft SQL Server 2016 server that hosts a database named db1. You have an Azure subscription. You plan to migrate db1 to an Azure SQL managed instance. You need to create the SQL managed instance. The solution must minimize the disk latency of the instance. Which service tier should you use?

• A. Business Critical
• B. Hyperscale
• C. General Purpose
• D. Premium

You have an Azure SQL Database managed instance named SQLMI1. A Microsoft SQL Server Agent job runs on SQLMI1. You need to ensure that an automatic email notification is sent once the job completes. What should you include in the solution?

• A. From SQL Server Configuration Manager (SSCM), enable SQL Server Agent
• B. From SQL Server Management Studio (SSMS), run sp_set_sqlagent_properties
• C. From SQL Server Management Studio (SSMS), create a Database Mail profile
• D. From the Azure portal, create an Azure Monitor action group that has an Email/SMS/Push/Voice action

You have a version-8.0 Azure Database for MySQL database. You need to identify which database queries consume the most resources. Which tool should you use?

• A. Query Store
• B. Metrics
• C. Query Performance Insight
• D. Alerts

You have an Azure SQL database named DB1. You need to encrypt DB1. The solution must meet the following requirements: • Encrypt data in motion. • Support comparison operators. • Provide randomized encryption. What should you include in the solution?

• A. Always Encrypted with secure enclaves
• B. Always Encrypted
• C. column-level encryption
• D. Transparent Data Encryption (TDE)

You have an Azure subscription. The subscription contains an instance of SQL Server on Azure Virtual Machines named SQL1 and an Azure Automation account named account1. You need to configure account1 to restart the SQL Server Agent service if the service stops. Which setting should you configure?

• A. Start/Stop VM
• B. Change tracking
• C. Update management
• D. State configuration (DSC)

You have an Always On availability group deployed to Azure virtual machines. The availability group contains a database named DB1 and has two nodes named SQL1 and SQL2. SQL1 is the primary replica. You need to initiate a full backup of DB1 on SQL2. Which statement should you run?

• A. BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/mycontainer/DB1.bak' with (Differential, STATS=5, COMPRESSION);
• B. BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/mycontainer/DB1.bak' with (COPY_ONLY, STATS=5, COMPRESSION);
• C. BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/mycontainer/DB1.bak' with (File_Snapshot, STATS=5, COMPRESSION);
• D. BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/mycontainer/DB1.bak' with (NoInit, STATS=5, COMPRESSION);

You have the following resources: • 15 SQL Server on Azure Virtual Machines instances • 20 Azure SQL databases You need to recommend a solution to centrally monitor the resources for security vulnerabilities. What should you include in the recommendation?

• A. database audits
• B. Microsoft Defender
• C. SQL insights
• D. Azure SQL Auditing

You have an Azure AD tenant and a logical Microsoft SQL server named SQL1 that hosts several Azure SQL databases. You plan to assign Azure AD users permissions to the databases automatically by using Azure Automation. You need to create the required Automation accounts. Which two accounts should you create? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

• A. From the Azure Active Directory admin center create a service principal
• B. From the Azure Active Directory admin center, create a user-assigned managed identity for SQL1
• C. On SQL1, create a SQL user in the databases
• D. On SQL1, create a SQL login
• E. From the Azure Active Directory admin center, create an external identity