Microsoft DP-300 Exam Dumps

Microsoft DP-300 Sample Question Answers

Question # 1

You need to design a data retention solution for the Twitter feed data records. The solutionmust meet the customer sentiment analytics requirements.Which Azure Storage functionality should you include in the solution?

A. time-based retention 
B. change feed 
C. lifecycle management 
D. soft delete 

Show Answer

Question # 2

You need to implement the surrogate key for the retail store table. The solution must meetthe sales transaction dataset requirements.What should you create?

A. a table that has a FOREIGN KEY constraint 
B. a table the has an IDENTITY property 
C. a user-defined SEQUENCE object 
D. a system-versioned temporal table 

Show Answer

Question # 3

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. 

Show Answer

Question # 4

You have a Microsoft SQL Server 2017 server. You need to migrate the server to Azure. The solution must meet the following requirements: • Ensure that the latest version of SQL Server is used. • Support the SQL Server Agent service. Minimize administrative effort. What should you use? 

A. SQL Server on Azure Virtual Machines
 B. Azure SQL Database 
C. an Azure SQL Database elastic pool 
D. Azure SQL Managed Instance

Show Answer

Question # 5

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. Hyperscale 
B. General Purpose
 C. Premium 
D. Business Critical 

Show Answer

Question # 6

You have a on-premises Microsoft SQL Server named SQL1 that hosts five databases. You need to migrate the databases to an Azure SQL managed instance. The solution must minimize downtime and prevent data loss. What should you use? 

A. log shipping 
B. Always On availability groups 
C. Database Migration Assistant 
D. Backup and Restore 

Show Answer

Question # 7

You have an Azure SQL managed instance named MI1. You need to implement automatic tuning for the databases of MI1. What should you do? 

A. Use the REST API to call the patch operation and modify the AutomaticTuningServerMode property. 
B. Use Transact-SQL to enable the force_last_good_plan option. 
C. From the Azure portal, configure automatic tuning. 

Show Answer

Question # 8

Your on-premises network contains a server that hosts a 60-TB database named DB 1. The network has a 10-Mbps internet connection. You need to migrate DB 1 to Azure. The solution must minimize how long it takes to migrate the database. What should you use?

A. Azure Migrate 
B. Data Migration Assistant (DMA) 
C. Azure Data BOX 
D. Azure Database Migration Service 

Show Answer

Question # 9

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. a Log Analytics workspace 
B. Azure Event Hubs 
C. Azure SQL Database 
D. an Azure Blob storage container 

Show Answer

Question # 10

You have two on-premises Microsoft SQL Server 2019 instances named SQL1 and SQL2. You need to migrate the databases hosted on SQL 1 to Azure. The solution must meet the following requirements: The service that hosts the migrated databases must be able to communicate with SQL2 by using linked server connections. Administrative effort must be minimized. What should you use to host the databases?

A. a single Azure SQL database
 B. an Azure SQL Database elastic pool 
C. SQL Server on Azure Virtual Machines 
D. Azure SQL Managed Instance 

Show Answer

Question # 11

You have an Azure SQL database named DB1. You have a table name Table1 that has 20 columns of type CHAR(400). Row compression for Table1 is enabled. During a database audit, you discover that none of the fields contain more than 150 characters. You need to ensure that you can apply page compression to Table1. What should you do?

A. Configure the columns as sparse. 
B. Change the column type to nvarchar (MAX). 
C. Change the column type to varchar (MAX). 
D. Change the column type to varchar (200). 

Show Answer

Question # 12

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 

Show Answer

Question # 13

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. 

Show Answer

Question # 14

You have an on-premises Microsoft SQL Server 2019 server that hosts a database named DB1. You have an Azure subscription that contains an Azure SQL managed instance namedSQLMI1 and a virtual network named VNET1. SQLMI1 resides on VNET1. The onpremises network connects to VNET1 by using an ExpressRoute connection. You plan to migrate DB1 to SQLMI1 by using Azure Database Migration Service. You need to configure VNET1 to support the migration. What should you do?

A. Configure service endpoints. 
B. Configure virtual network peering. 
C. Deploy an Azure firewall. 
D. Configure network security groups (NSGs). 

Show Answer

Question # 15

You have an Azure SQL managed instance. You need to enable SQL Agent Job email notifications. What should you do?

A. Use the Agent XPs option. 
B. Enable the SQL Server Agent. 
C. Run the sp_configure command. 
D. Run the sp set_agent properties command. 

Show Answer

Question # 16

You need to migrate an on-premises Microsoft SQL Server database to Azure SQL Database. The solution must minimize downtime. What should you do?

A. Configure Transaction Log Shipping. 
B. Implement Always On availability groups. 
C. C. Configure transactional replication. 
D. Import a BACPAC. 

Show Answer

Question # 17

You have an on-premises Microsoft SQL server that uses the FileTables and Filestream features. You plan to migrate to Azure SQL. Which service should you use?

A. Azure SQL Database 
B. SQL Server on an Azure Virtual Machine 
C. Azure SQL Managed Instance 
D. Azure Database for MySQL 

Show Answer

Question # 18

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. 

Show Answer

Question # 19

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 

Show Answer

Question # 20

You have an Azure SQL database named DB1.You need to display the estimated execution plan of a query by using the query editor inthe 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. 

Show Answer

Question # 21

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 namedUser1.User1 attempts to connect to the logical server by using Azure Data Studio and receives alogin error.You need to ensure that when User1 connects to the logical server by using Azure DataStudio, 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 Userl creates. 
D. Grant select on sys.databases to public in the master database. 

Show Answer

Question # 22

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 statedgoals. Some question sets might have more than one correct solution, while othersmight not have a correct solution.After you answer a question in this section, you will NOT be able to return to it. As aresult, 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 waittype 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 

Show Answer

Question # 23

You are planning a solution that will use Azure SQL Database. Usage of the solution willpeak from October 1 to January 1 each year.During peak usage, the database will require the following:24 cores500 GB of storage124 GB of memoryMore than 50,000 IOPSDuring periods of off-peak usage, the service tier of Azure SQL Database will be set toStandard.Which service tier should you use during peak usage?

A. Business Critical 
B. Premium 
C. Hyperscale 

Show Answer

Question # 24

You have an Azure subscription that contains a server named Server1. Server1 hosts twoAzure SQL databases named DB1 and DB2.You plan to deploy a Windows app named App1 that will authenticate to DB2 by using SQLauthentication.You need to ensure that App1 can access DB2. The solution must meet the followingrequirements: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 

Show Answer

Question # 25

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. 

Show Answer

Question # 26

You have an Azure SQL managed instance named SQLMI1 that hosts 10 databases.You need to implement alerts by using Azure Monitor. The solution must meet the followingrequirements:Minimize costs.Aggregate Intelligent Insights telemetry from each database.What should you do?

A. From the Diagnostic settings of each database, select Send to Log Analytics. 
B. From the Diagnostic settings of each database, select Stream to an event hub. 
C. From the Diagnostic settings of SQLMI1. select Send to Log Analytics. 
D. From the Diagnostic settings of SQLMI1. select Stream to an event hub. 

Show Answer

Question # 27

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 forthe query. The solution must minimize administrative effort.What should you use?

A. the Azure portal 
B. Extended Events 
C. Query Store 
D. dynamic management views 

Show Answer

Question # 28

You have an Azure SQL managed instance named SQL1 and two Azure web apps namedApp1 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 thesolution.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. 

Show Answer

Question # 29

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 statedgoals. Some question sets might have more than one correct solution, while othersmight not have a correct solution.After you answer a question in this section, you will NOT be able to return to it. As aresult, 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 waittype 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 

Show Answer

Question # 30

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. RuntheDBCC SQLPERF command. 
B. Run the dbcc dbreindex command. 
C. Modify the automatic tuning settings for db1. 
D. Modify the Query Store settings for db1. 

Show Answer

Question # 31

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 the 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 

Show Answer