DP-300: Administering Relational Databases on Microsoft Azure → DP-300: Administering Relational Databases on Microsoft Azure Topic 2

DP-300: Administering Relational Databases on Microsoft Azure Topic 2

Question #: 14
Topic #: 2
You are designing a security model for an Azure Synapse Analytics dedicated SQL pool that will support multiple companies.
You need to ensure that users from each company can view only the data of their respective company.
Which two objects should you include in the solution? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. a column encryption key
B. asymmetric keys
C. a function
D. a custom role-based access control (RBAC) role
E. a security policy

Selected Answer: CE

Question #: 15
Topic #: 7
You have an Azure subscription that contains 50 instances of SQL Server on Azure Virtual Machines. The instances host 500 Azure SQL databases.
You need to ensure that all the databases have the same configuration. The solution must meet the following requirements:
✑ Auditing must be enabled.
✑ Azure Defender must be enabled.
✑ Public network access must be disabled.
✑ Administrative effort must be minimized.
Which two resources should you create in the subscription? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. an Azure Automation runbook
B. an Azure Policy initiative
C. an Azure Policy assignment
D. an Azure Automation account
E. an Azure Policy definition

Selected Answer: CE

Question #: 15
Topic #: 5
You have four Azure subscriptions. Each subscription contains multiple Azure SQL databases.
You need to update the column and index statistics for the databases.
What should you use?

A. an Azure Automation runbook
B. a SQL Agent job
C. Azure SQL Analytics
D. automatic tuning in Azure SQL Database

Selected Answer: A

Question #: 15
Topic #: 3
You have an Azure Databricks resource.
You need to log actions that relate to changes in compute for the Databricks resource.
Which Databricks services should you log?

A. clusters
B. jobs
C. DBFS
D. SSH
E. workspace

Selected Answer: A

Question #: 15
Topic #: 2
You have an Azure subscription that contains an Azure Data Factory version 2 (V2) data factory named df1. DF1 contains a linked service.
You have an Azure Key vault named vault1 that contains an encryption kay named key1.
You need to encrypt df1 by using key1.
What should you do first?

A. Disable purge protection on vault1.
B. Remove the linked service from df1.
C. Create a self-hosted integration runtime.
D. Disable soft delete on vault1.

Selected Answer: A

Question #: 15
Topic #: 6
You have an Azure subscription that uses a domain named contoso.com.
You have two Azure VMs named DBServer1 and DBServer2. Each of them hosts a default SQL Server instance. DBServer1 is in the East US Azure region and contains a database named DatabaseA. DBServer2 is in the West US Azure region.
DBServer1 has a high volume of data changes and low latency requirements for data writes.
You need to configure a new availability group for DatabaseA. The secondary replica will reside on DBServer2.
What should you do?

A. Configure the primary endpoint as TCP://DBServer1.contoso.com:445, configure the secondary endpoint as TCP://DBServer2.contoso.com:445, and set the availability mode to Asynchronous.
B. Configure the primary endpoint as TCP://DBServer1.contoso.com:445, configure the secondary endpoint as TCP://DBServer2.contoso.com:445, and set the availability mode to Synchronous.
C. Configure the primary endpoint as TCP://DBServer1.contoso.com:5022, configure the secondary endpoint as TCP://DBServer2.contoso.com:5022, and set the availability mode to Asynchronous.
D. Configure the primary endpoint as TCP://DBServer1.contoso.com:5022, configure the secondary endpoint as TCP://DBServer2.contoso.com:5022, and set the availability mode to Synchronous.

Selected Answer: C

Question #: 15
Topic #: 4
You have an Azure SQL managed instance.
You need to gather the last execution of a query plan and its runtime statistics. The solution must minimize the impact on currently running queries.
What should you do?

A. Generate an estimated execution plan.
B. Generate an actual execution plan.
C. Run sys.dm_exec_query_plan_stats.
D. Generate Live Query Statistics.

Selected Answer: B

Question #: 16
Topic #: 7
You have an instance of SQL Server on Azure Virtual Machine named SQL1.
You need to monitor SQL1 and query the metrics by using Kusto query language. The solution must minimize administrative effort.
Where should you store the metrics?

A. Azure Event Hubs
B. a Log Analytics workspace
C. Azure SQL Database
D. an Azure Blob storage container

Selected Answer: B

Question #: 16
Topic #: 3
Your company uses Azure Stream Analytics to monitor devices.
The company plans to double the number of devices that are monitored.
You need to monitor a Stream Analytics job to ensure that there are enough processing resources to handle the additional load.
Which metric should you monitor?

A. Input Deserialization Errors
B. Late Input Events
C. Early Input Events
D. Watermark delay

Selected Answer: B

Question #: 16
Topic #: 1
You have an Azure Synapse Analytics workspace named WS1 that contains an Apache Spark pool named Pool1.
You plan to create a database named DB1 in Pool1.
You need to ensure that when tables are created in DB1, the tables are available automatically as external tables to the built-in serverless SQL pool.
Which format should you use for the tables in DB1?

A. JSON
B. CSV
C. Parquet
D. ORC

Selected Answer: C

Question #: 16
Topic #: 2
You have an Azure subscription that contains a server named Server1. Server1 hosts two Azure SQL databases named DB1 and DB2.
You plan to deploy a Windows app named App1 that will authenticate to DB2 by using SQL authentication.
You need to ensure that App1 can access DB2. The solution must meet the following requirements:
✑ App1 must be able to view only DB2.
✑ Administrative effort must be minimized.
What should you create?

A. a contained database user for App1 on DB2
B. a login for App1 on Server1
C. a contained database user from an external provider for App1 on DB2
D. a contained database user from a Windows login for App1 on DB2

Selected Answer: A

Question #: 16
Topic #: 6
You have an on-premises multi-tier application named App1 that includes a web tier, an application tier, and a Microsoft SQL Server tier. All the tiers run on Hyper-
V virtual machines.
Your new disaster recovery plan requires that all business-critical applications can be recovered to Azure.
You need to recommend a solution to fail over the database tier of App1 to Azure. The solution must provide the ability to test failover to Azure without affecting the current environment.
What should you include in the recommendation?

A. Azure Backup
B. Azure Information Protection
C. Windows Server Failover Cluster
D. Azure Site Recovery

Selected Answer: D

Question #: 17
Topic #: 4
You have an Azure SQL Database managed instance.
The instance starts experiencing performance issues.
You need to identify which query is causing the issue and retrieve the execution plan for the query. The solution must minimize administrative effort.
What should you use?

A. SQL Profiler
B. Extended Events
C. Query Store
D. dynamic management views

Selected Answer: D

Question #: 17
Topic #: 2
You create five Azure SQL Database instances on the same logical server.
In each database, you create a user for an Azure Active Directory (Azure AD) user named User1.
User1 attempts to connect to the logical server by using Azure Data Studio and receives a login error.
You need to ensure that when User1 connects to the logical server by using Azure Data Studio, User1 can see all the databases.
What should you do?

A. Create User1 in the master database.
B. Assign User1 the db_datareader role for the master database.
C. Assign User1 the db_datareader role for the databases that User1 creates.
D. Grant SELECT on sys.databases to public in the master database.

Selected Answer: A

Question #: 17
Topic #: 3
You manage an enterprise data warehouse in Azure Synapse Analytics.
Users report slow performance when they run commonly used queries. Users do not report performance changes for infrequently used queries.
You need to monitor resource utilization to determine the source of the performance issues.
Which metric should you monitor?

A. Local tempdb percentage
B. DWU percentage
C. Data Warehouse Units (DWU) used
D. Cache hit percentage

Selected Answer: D

Question #: 17
Topic #: 1
You are designing an anomaly detection solution for streaming data from an Azure IoT hub. The solution must meet the following requirements:
✑ Send the output to an Azure Synapse.
✑ Identify spikes and dips in time series data.
✑ Minimize development and configuration effort.
Which should you include in the solution?

A. Azure SQL Database
B. Azure Databricks
C. Azure Stream Analytics

Selected Answer: C

Question #: 18
Topic #: 5
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

Selected Answer: C

Question #: 18
Topic #: 2
You have an Azure virtual machine named VM1 on a virtual network named VNet1. Outbound traffic from VM1 to the internet is blocked.
You have an Azure SQL database named SqlDb1 on a logical server named SqlSrv1.
You need to implement connectivity between VM1 and SqlDb1 to meet the following requirements:
✑ Ensure that VM1 cannot connect to any Azure SQL Server other than SqlSrv1.
✑ Restrict network connectivity to SqlSrv1.
What should you create on VNet1?

A. a VPN gateway
B. a service endpoint
C. a private endpoint
D. an ExpressRoute gateway

Selected Answer: A

Question #: 18
Topic #: 3
You have an Azure Synapse Analytics dedicated SQL pool named Pool1 and a database named DB1. DB1 contains a fact table named Table.
You need to identify the extent of the data skew in Table1.
What should you do in Synapse Studio?

A. Connect to Pool1 and query sys.dm_pdw_nodes_db_partition_stats.
B. Connect to the built-in pool and run DBCC CHECKALLOC.
C. Connect to Pool1 and run DBCC CHECKALLOC.
D. Connect to the built-in pool and query sys.dm_pdw_nodes_db_partition_stats.

Selected Answer: A

Question #: 18
Topic #: 1
You are creating a new notebook in Azure Databricks that will support R as the primary language but will also support Scala and SQL.
Which switch should you use to switch between languages?

A. \\[] B. %
C. \\[] D. @

Selected Answer: B

Question #: 18
Topic #: 4
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.

Selected Answer: A

Question #: 19
Topic #: 7
You have an Azure subscription that contains the resources shown in the following table.

DP-300 Administering Relational Databases on Microsoft Azure Question 19 Topic 7

You need to configure a connection between VM1 and MI1. The solution must meet the following requirements:
✑ The connection must be encrypted.
✑ Network latency must be minimized.
What should you implement?

A. a site-to-site VPN
B. virtual network peering
C. private endpoints
D. service endpoints

Selected Answer: C

Question #: 19
Topic #: 3
You have an Azure Synapse Analytics dedicated SQL pool.
You run PDW_SHOWSPACEUSED(‘dbo.FactInternetSales’); and get the results shown in the following table.

DP-300 Administering Relational Databases on Microsoft Azure Question 19 Topic 3

Which statement accurately describes the dbo.FactInternetSales table?

A. The table contains less than 10,000 rows.
B. All distributions contain data.
C. The table uses round-robin distribution
D. The table is skewed.

Selected Answer: A

Question #: 19
Topic #: 6
You have a new Azure subscription.
You create an Azure SQL Database instance named DB1 on an Azure SQL Database server named Server1.
You need to ensure that users can connect to DB1 in the event of an Azure regional outage. In the event of an outage, applications that connect to DB1 must be able to connect without having to update the connection strings.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. From the properties of DB1, configure geo-replication.
B. From the properties of Server1, add a failover group.
C. Create a new Azure SQL Database server named Server2.
D. From the properties of Server1, configure retention for DB1.
E. Create a new Azure SQL Database instance named DB2.

Selected Answer: BC

Question #: 19
Topic #: 5
You need to trigger an Azure Data Factory pipeline when a file arrives in an Azure Data Lake Storage Gen2 container.
Which resource provider should you enable?

A. Microsoft.EventHub
B. Microsoft.EventGrid
C. Microsoft.Sql
D. Microsoft.Automation

Selected Answer: B

Question #: 20
Topic #: 5
You deploy an instance of SQL Server on Azure Virtual Machines named VM1.

You need to create a SQL Server Agent job that will rebuild indexes of the databases hosted on VM1. The solution must use the principle of least privilege.

What should you create first?

A. a local Windows account
B. a user-assigned managed identity in Azure AD
C. a system-assigned managed identity in Azure AD
D. an Elastic Job agent

Selected Answer: C

Question #: 20
Topic #: 4
You have an instance of SQL Server on Azure Virtual Machines that has a database named DB1.
You plan to implement Azure SQL Data Sync for DB1.
Which isolation level should you configure?

A. SERIALIZABLE
B. SNAPSHOT
C. READ UNCOMMITTED
D. READ COMMITTED

Selected Answer: B

Question #: 21
Topic #: 1
You plan to build a structured streaming solution in Azure Databricks. The solution will count new events in five-minute intervals and report only events that arrive during the interval.
The output will be sent to a Delta Lake table.
Which output mode should you use?

A. complete
B. append
C. update

Selected Answer: B

Question #: 21
Topic #: 3
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have SQL Server 2019 on an Azure virtual machine.
You are troubleshooting performance issues for a query in a SQL Server instance.
To gather more information, you query sys.dm_exec_requests and discover that the wait type is PAGELATCH_UP and the wait_resource is 2:3:905856.
You need to improve system performance.
Solution: You shrink the transaction log file.
Does this meet the goal?

A. Yes
B. No

Selected Answer: B

Question #: 22
Topic #: 5
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.

Selected Answer: BC

Question #: 22
Topic #: 3
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have SQL Server 2019 on an Azure virtual machine.
You are troubleshooting performance issues for a query in a SQL Server instance.
To gather more information, you query sys.dm_exec_requests and discover that the wait type is PAGELATCH_UP and the wait_resource is 2:3:905856.
You need to improve system performance.
Solution: You change the data file for the master database to autogrow by 10 percent.
Does this meet the goal?

A. Yes
B. No

Selected Answer: A

Question #: 22
Topic #: 2
You have an Azure subscription that contains a logical SQL server named Server1. The master database of Server1 contains a user named User1.
You need to ensure that User1 can create databases on Server1.
Which database role should you assign to User1?

A. db_owner
B. dbmanager
C. dbo
D. db_ddladmin

Selected Answer: A

Question #: 22
Topic #: 4
A data engineer creates a table to store employee information for a new application. All employee names are in the US English alphabet. All addresses are locations in the United States. The data engineer uses the following statement to create the table.

DP-300 Administering Relational Databases on Microsoft Azure Question 22 Topic 4

You need to recommend changes to the data types to reduce storage and improve performance.
Which two actions should you recommend? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. Change Salary to the money data type.
B. Change PhoneNumber to the float data type.
C. Change LastHireDate to the datetime2(7) data type.
D. Change PhoneNumber to the bigint data type.
E. Change LastHireDate to the date data type.

Selected Answer: AE

Question #: 23
Topic #: 4
You have an Azure SQL database.
You identify a long running query.
You need to identify which operation in the query is causing the performance issue.
What should you use to display the query execution plan in Microsoft SQL Server Management Studio (SSMS)?

A. Live Query Statistics
B. an estimated execution plan
C. an actual execution plan
D. Client Statistics

Selected Answer: C

Question #: 23
Topic #: 2
You have an on-premises Microsoft SQL Server 2019 instance named SQL1 that hosts a database named db1. You have an Azure subscription that contains an
Azure SQL managed instance named MI1 and an Azure Storage account named storage1.
You plan to migrate db1 to MI1 by using the backup and restore process.
You need to ensure that you can back up db1 to storage1. The solution must meet the following requirements:
✑ Use block blob storage.
✑ Maximize security.
What should you do on storage1?

A. Generate a shared access signature (SAS).
B. Create an access policy.
C. Rotate the storage keys.
D. Enable infrastructure encryption.

Selected Answer: A

Question #: 23
Topic #: 1
You have a SQL pool in Azure Synapse that contains a table named dbo.Customers. The table contains a column name Email.
You need to prevent nonadministrative users from seeing the full email addresses in the Email column. The users must see values in a format of aXXX@XXXX.com instead.
What should you do?

A. From the Azure portal, set a mask on the Email column.
B. From the Azure portal, set a sensitivity classification of Confidential for the Email column.
C. From Microsoft SQL Server Management Studio, set an email mask on the Email column.
D. From Microsoft SQL Server Management Studio, grant the SELECT permission to the users for all the columns in the dbo.Customers table except Email.

Selected Answer: A

Question #: 23
Topic #: 5
You have an Azure subscription.

You plan to deploy an instance of SQL Server on Azure Virtual Machines by using an Azure Marketplace image.

You need to register the SQL Server IaaS Agent extension (SqlIaasExtension). The solution must meet the following requirements:

• Install critical updates for SQL Server automatically.
• Minimize performance impact on the virtual machine.

Which management mode should you select?

A. full
B. lightweight
C. NoAgent

Selected Answer: A

Question #: 23
Topic #: 7
You have an Azure virtual machine named VM1 that runs Windows Server 2022 and hosts a Microsoft SQL Server 2019 instance named SQL1.

You need to configure SQL1 to use mixed mode authentication.

Which procedure should you run?

A. sp_addremotelogin
B. xp_instance_regwrite
C. sp_change_users_login
D. xp_grant_login

Selected Answer: C

Question #: 23
Topic #: 3
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have SQL Server 2019 on an Azure virtual machine.
You are troubleshooting performance issues for a query in a SQL Server instance.
To gather more information, you query sys.dm_exec_requests and discover that the wait type is PAGELATCH_UP and the wait_resource is 2:3:905856.
You need to improve system performance.
Solution: You reduce the use of table variables and temporary tables.
Does this meet the goal?

A. Yes
B. No

Selected Answer: D

Question #: 24
Topic #: 5
You have an Azure subscription that contains 20 Azure SQL databases.

You create a Transact-SQL statement to perform index maintenance on a database.
You need to schedule the statement to run once daily against each database by using Transact-SQL commands.

What should you use to schedule the statement?

A. an Azure function
B. a SQL Server Agent Job
C. an elastic job
D. Azure Automation

Selected Answer: C

Question #: 24
Topic #: 6
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have two Azure SQL Database servers named Server1 and Server2. Each server contains an Azure SQL database named Database1.
You need to restore Database1 from Server1 to Server2. The solution must replace the existing Database1 on Server2.
Solution: From the Azure portal, you delete Database1 from Server2, and then you create a new database on Server2 by using the backup of Database1 from
Server1.
Does this meet the goal?

A. Yes
B. No

Selected Answer: A

Question #: 24
Topic #: 2
You have an Azure SQL database named DB1.
A user named User1 has an Azure Active Directory (Azure AD) account.
You need to provide User1 with the ability to add and remove columns from the tables in DB1. The solution must use the principle of least privilege.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. Assign the database user the db_owner role.
B. Create a contained database user.
C. Create a login and an associated database user.
D. Assign the database user the db_ddladmin role.

Selected Answer: BD

Question #: 24
Topic #: 1
You have an Azure Databricks workspace named workspace1 in the Standard pricing tier. Workspace1 contains an all-purpose cluster named cluster1.
You need to reduce the time it takes for cluster1 to start and scale up. The solution must minimize costs.
What should you do first?

A. Upgrade workspace1 to the Premium pricing tier.
B. Configure a global init script for workspace1.
C. Create a pool in workspace1.
D. Create a cluster policy in workspace1.

Selected Answer: A

Question #: 24
Topic #: 3
You have an Azure SQL database named db1 on a server named server1.
You need to modify the MAXDOP settings for db1.
What should you do?

A. Connect to db1 and run the sp_configure command.
B. Connect to the master database of server1 and run the sp_configure command.
C. Configure the extended properties of db1.
D. Modify the database scoped configuration of db1.

Selected Answer: D

Question #: 24
Topic #: 4
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

Selected Answer: A

Question #: 25
Topic #: 1
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have an Azure Synapse Analytics dedicated SQL pool that contains a table named Table1.
You have files that are ingested and loaded into an Azure Data Lake Storage Gen2 container named container1.
You plan to insert data from the files into Table1 and transform the data. Each row of data in the files will produce one row in the serving layer of Table1.
You need to ensure that when the source data files are loaded to container1, the DateTime is stored as an additional column in Table1.
Solution: In an Azure Synapse Analytics pipeline, you use a Get Metadata activity that retrieves the DateTime of the files.
Does this meet the goal?

A. Yes
B. No

Selected Answer: A

Question #: 25
Topic #: 4
You have an Azure subscription that contains an Azure SQL database. The database contains a table named table1.

You execute the following Transact-SQL statements.

DP-300 Administering Relational Databases on Microsoft Azure Question 25 Topic 4

You need to reduce the time it takes to perform analytic queries on the database.

Which configuration should you enable?

A. ROW_MODE_MEMORY_GRANT_FEEDBACK
B. BATCH_MODE_MEMORY_GRANT_FEEDBACK
C. BATCH_MODE_ADAPTIVE_JOINS
D. BATCH_MODE_ON_ROWSTORE

Selected Answer: D

Question #: 25
Topic #: 6
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have two Azure SQL Database servers named Server1 and Server2. Each server contains an Azure SQL database named Database1.
You need to restore Database1 from Server1 to Server2. The solution must replace the existing Database1 on Server2.
Solution: You run the Remove-AzSqlDatabase PowerShell cmdlet for Database1 on Server2. You run the Restore-AzSqlDatabase PowerShell cmdlet for
Database1 on Server2.
Does this meet the goal?

A. Yes
B. No

Selected Answer: B

Question #: 25
Topic #: 3
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have SQL Server 2019 on an Azure virtual machine.
You are troubleshooting performance issues for a query in a SQL Server instance.
To gather more information, you query sys.dm_exec_requests and discover that the wait type is PAGELATCH_UP and the wait_resource is 2:3:905856.
You need to improve system performance.
Solution: You create additional tempdb files.
Does this meet the goal?

A. Yes
B. No

Selected Answer: D

Question #: 26
Topic #: 6
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have two Azure SQL Database servers named Server1 and Server2. Each server contains an Azure SQL database named Database1.
You need to restore Database1 from Server1 to Server2. The solution must replace the existing Database1 on Server2.
Solution: You restore the backup for Database1 from Server1 to the Server2 by using the RESTORE Transact-SQL command with the REPLACE option.
Does this meet the goal?

A. Yes
B. No

Selected Answer: B

Question #: 26
Topic #: 1
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have an Azure Synapse Analytics dedicated SQL pool that contains a table named Table1.
You have files that are ingested and loaded into an Azure Data Lake Storage Gen2 container named container1.
You plan to insert data from the files into Table1 and transform the data. Each row of data in the files will produce one row in the serving layer of Table1.
You need to ensure that when the source data files are loaded to container1, the DateTime is stored as an additional column in Table1.
Solution: You use an Azure Synapse Analytics serverless SQL pool to create an external table that has an additional DateTime column.
Does this meet the goal?

A. Yes
B. No

Selected Answer: B

Question #: 26
Topic #: 3
You have SQL Server on an Azure virtual machine.
You need to add a 4-TB volume that meets the following requirements:
✑ Maximizes IOPs
✑ Uses premium solid state drives (SSDs)
What should you do?

A. Attach two mirrored 4-TB SSDs.
B. Attach a stripe set that contains four 1-TB SSDs.
C. Attach a RAID-5 array that contains five 1-TB SSDs.
D. Attach a single 4-TB SSD.

Selected Answer: D

Question #: 27
Topic #: 5
You manage 100 Azure SQL managed instances located across 10 Azure regions.

You need to receive voice message notifications when a maintenance event affects any of the 10 regions. The solution must minimize administrative effort.

What should you do?

A. From the Azure portal, create a service health alert.
B. From the Azure portal, create an Azure Advisor operational excellence alert.
C. From the Azure portal, configure an activity log alert.
D. From Microsoft SQL Server Management Studio (SSMS), configure a SQL Server agent job.

Selected Answer: A

Question #: 27
Topic #: 3
You have an Azure SQL database named db1 on a server named server1.
The Intelligent Insights diagnostics log identifies that several tables are missing indexes.
You need to ensure that indexes are created for the tables.
What should you do?

A. Run the DBCC SQLPERF command.
B. Run the DBCC DBREINDEX command.
C. Modify the automatic tuning settings for db1.
D. Modify the Query Store settings for db1.

Selected Answer: D

Question #: 27
Topic #: 4
You have a Microsoft SQL Server 2019 database named DB1 and an Azure SQL managed instance named SQLMI1.

You need to move a SQL Server Agent job from DB1 to SQLMI1.

Which job attribute is unsupported in SQLMI1?

A. log to table
B. email notifications
C. schedules
D. output files

Selected Answer: D

Question #: 27
Topic #: 6
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);

Selected Answer: B

Question #: 27
Topic #: 1
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have an Azure Synapse Analytics dedicated SQL pool that contains a table named Table1.
You have files that are ingested and loaded into an Azure Data Lake Storage Gen2 container named container1.
You plan to insert data from the files into Table1 and transform the data. Each row of data in the files will produce one row in the serving layer of Table1.
You need to ensure that when the source data files are loaded to container1, the DateTime is stored as an additional column in Table1.
Solution: You use a dedicated SQL pool to create an external table that has an additional DateTime column.
Does this meet the goal?

A. Yes
B. No

Selected Answer: A

Question #: 28
Topic #: 3
You have an Azure SQL managed instance named SQL1 and two Azure web apps named App1 and App2.
You need to limit the number of IOPs that App2 queries generate on SQL1.
Which two actions should you perform on SQL1? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. Enable query optimizer fixes.
B. Enable Resource Governor.
C. Enable parameter sniffing.
D. Create a workload group.
E. Configure In-memory OLTP.
F. Run the Database Engine Tuning Advisor.
G. Reduce the Max Degree of Parallelism value.

Selected Answer: BD

Question #: 29
Topic #: 5
You have two Azure virtual machines named Server1 and Server2 that run Windows Server 2022 and are joined to an Active Directory Domain Services (AD DS) domain named contoso.com.

Both virtual machines have a default instance of Microsoft SQL Server 2019 installed. Server1 is configured as a master server, and Server2 is configured as a target server.

On Server1, you create a proxy account named contoso\sqlproxy.

You need to ensure that the SQL Server Agent job steps can be downloaded from Server1 and run on Server2.

Which two actions should you perform? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point.

A. On Server2, grant the contoso\sqlproxy account the Impersonate a client after authentication user right.
B. On Server2, grant the contoso\sqlproxy account the Access this computer from the network user right.
C. On Server2, create a proxy account.
D. On Server1, set the AllowDownloadedJobsToMatchProxyName registry entry to 1.
E. On Server2, set the AllowDownloadedJobsToMatchProxyName registry entry to 1.

Selected Answer: DE

Question #: 29
Topic #: 4
You have an Azure SQL managed instance.

You need to configure the SQL Server Agent service to email job notifications.

Which statement should you execute?

A. EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = ‘sysadmin_dbmail_profile’;
B. EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = ‘application_dbmail_profile’;
C. EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = ‘AzureManagedInstance_dbmail_profile’;
D. EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = ‘sys_dbmail_profile’;

Selected Answer: C

Question #: 29
Topic #: 1
You plan to deploy an app that includes an Azure SQL database and an Azure web app. The app has the following requirements:
✑ The web app must be hosted on an Azure virtual network.
✑ The Azure SQL database must be assigned a private IP address.
✑ The Azure SQL database must allow connections only from a specific virtual network.
You need to recommend a solution that meets the requirements.
What should you include in the recommendation?

A. Azure Private Link
B. a network security group (NSG)
C. a database-level firewall
D. a server-level firewall

Selected Answer: A

Question #: 29
Topic #: 3
You have an Azure SQL database named db1 on a server named server1.
The Intelligent Insights diagnostics log identifies queries that cause performance issues due to tempDB contention.
You need to resolve the performance issues.
What should you do?

A. Implement memory-optimized tables.
B. Run the DBCC FLUSHPROCINDB command.
C. Replace the sequential index keys with nonsequential keys.
D. Run the DBCC DBREINDEX command.

Selected Answer: A

Question #: 30
Topic #: 2
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

Selected Answer: B

Question #: 30
Topic #: 5
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)

Selected Answer: D

Question #: 30
Topic #: 1
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

Selected Answer: A

Question #: 30
Topic #: 6
You have an Azure subscription that contains an Azure SQL database named SQL1.

SQL1 is in an Azure region that does not support availability zones.

You need to ensure that you have a secondary replica of SQL1 in the same region.

What should you use?

A. log shipping
B. active geo-replication
C. Microsoft SQL Server failover clusters
D. auto-failover groups

Selected Answer: B

Question #: 31
Topic #: 3
You have SQL Server 2019 on an Azure virtual machine that runs Windows Server 2019. The virtual machine has 4 vCPUs and 28 GB of memory.
You scale up the virtual machine to 8 vCPUs and 64 GB of memory.
You need to reduce tempdb contention without negatively affecting server performance.
What is the number of secondary data files that you should configure for tempdb?

A. 2
B. 4
C. 8
D. 64

Selected Answer: C

Question #: 31
Topic #: 6
You have an Azure SOI database named SQLDb1 that contains the resources shown in the following table.

DP-300 Administering Relational Databases on Microsoft Azure Question 31 Topic 6

Column1 contains JSON data.

You need to compress Column1. The solution must minimize the amount of storage used.

What should you use?

A. the COMPRESS() function
B. columnstore archive compression
C. row compression
D. columnstore compression

Selected Answer: A

Question #: 31
Topic #: 2
You have an Azure subscription that contains an instance of SQL Server on an Azure virtual machine named SQLVM1 and a user named User1. SQLVM1 hosts a database named DB1.

You need to ensure that User1 can create a scheduled task to perform a full backup of DB1. The solution must use the principle of least privilege.

Which built-in database role should you assign to User1?

A. db_owner
B. SQLAgentReaderRole
C. SQLAgentUserRole
D. SQLAgentOperatorRole

Selected Answer: A

Question #: 32
Topic #: 6
You have an Azure subscription that contains two instances of SQL Server on Azure Virtual Machines named VM1 and VM2. Both instances run Microsoft SQL Server 2019 CU8.

You need to deploy a failover cluster instance (FCI) to VM1 and VM2 that will use Azure shared disks. The solution must maximize resiliency.

Which quorum option should you use?

A. node majority with a cloud witness
B. node majority with no witness
C. node majority with a file share witness
D. node majority with a disk witness

Selected Answer: D

Cart

Practice Exam

Protect Your System: Understanding CVE-2024-3400 Zero-Day Vulnerability

AWS Certified Cloud Practitioner CLF-C02 Exam Part 6

Cart