Exam DP-300 final

You have 20 Azure SQL databases provisioned by using the vCore purchasing model. You plan to create an Azure SQL Database elastic pool and add the 20 databases. Which three metrics should you use to size the elastic pool to meet the demands of your workload? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point. total size of all the databases. geo-replication support. number of concurrently peaking databases * peak CPU utilization per database. maximum number of concurrent sessions for all the databases. total number of databases * average CPU utilization per database.

You have SQL Server 2019 on an Azure virtual machine that contains an SSISDB database. A recent failure causes the master database to be lost. You discover that all Microsoft SQL Server integration Services (SSIS) packages fail to run on the virtual machine. Which four actions should you perform in sequence to resolve the issue? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct. Select and Place: Add a certificate to an Azure key vault '. Enable Transparent Data Encryption (TDE). Encrypt a copy of the master key by using the service master key. Turn on the TRUSTWORTHY property and the CLR property. Attach the SSISDB database. Open the master key for the SSISDB database.

You have SQL Server 2019 on an Azure virtual machine that contains an SSISDB database. A recent failure causes the master database to be lost. You discover that all Microsoft SQL Server integration Services (SSIS) packages fail to run on the virtual machine. Which four actions should you perform in sequence to resolve the issue? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct. Select and Place: Add a certificate to an Azure key vault. Enable Transparent Data Encryption (TDE). Encrypt a copy of the master key by using the service master key. Turn on the TRUSTWORTHY property and the CLR property. Attach the SSISDB database. Open the master key for the SSISDB database.

You have an Azure SQL database that contains a table named factSales. FactSales contains the columns shown in the following table. FactSales has 6 billion rows and is loaded nightly by using a batch process. You must provide the greatest reduction in space for the database and maximize performance. Which type of compression provides the greatest space reduction for the database?. page compression. row compression. columnstore compression. columnstore archival compression.

You have a Microsoft SQL Server 2019 database named DB1 that uses the following database-level and instance-level features. ✑ Clustered columnstore indexes ✑ Automatic tuning ✑ Change tracking ✑ PolyBase You plan to migrate DB1 to an Azure SQL database. What feature should be removed or replaced before DB1 can be migrated?. Clustered columnstore indexes. PolyBase. Change tracking. Automatic tuning.

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?. distributed availability groups. database mirroring. Always On Availability Group. Azure Database Migration Service.

You have an on-premises Microsoft SQL Server 2016 server named Server1 that contains a database named DB1. You need to perform an online migration of DB1 to an Azure SQL Database managed instance by using Azure Database Migration Service. How should you configure the backup of DB1? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Backup type:. Backup option:.

You have a resource group named App1Dev that contains an Azure SQL Database server named DevServer1. DevServer1 contains an Azure SQL database named DB1. The schema and permissions for DB1 are saved in a Microsoft SQL Server Data Tools (SSDT) database project. You need to populate a new resource group named App1Test with the DB1 database and an Azure SQL Server named TestServer1. The resources in App1Test must have the same configurations as the resources in App1Dev. Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. Change the Active Directory Admin on TestServer1. Change the server name and related variables in the templates. From the database project, deploy the database schema and permissions. Add IP addresses to the firewall. From the Azure portal, export the Azure Resource Manager templates. From the Azure portal, deploy the templates.

You have an Azure Synapse Analytics dedicated SQL pool named Pool1 and an Azure Data Lake Storage Gen2 account named Account1. You plan to access the files in Account1 by using an external table. You need to create a data source in Pool1 that you can reference when you create the external table. How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. CREATE EXTERNAL DATA SOURCE source1 WITH ( LOCATION = 'https://account1._____.core.windows.net', ________ ). Box1:. Box2:.

You are designing a streaming data solution that will ingest variable volumes of data. You need to ensure that you can change the partition count after creation. Which service should you use to ingest the data?. Azure Event Hubs Standard. Azure Stream Analytics. Azure Data Factory. Azure Event Hubs Dedicated.

You have an Azure Synapse Analytics Apache Spark pool named Pool1. You plan to load JSON files from an Azure Data Lake Storage Gen2 container into the tables in Pool1. The structure and data types vary by file. You need to load the files into the tables. The solution must maintain the source data types. What should you do?. Load the data by using PySpark. Load the data by using the OPENROWSET Transact-SQL command in an Azure Synapse Analytics serverless SQL pool. Use a Get Metadata activity in Azure Data Factory. Use a Conditional Split transformation in an Azure Synapse data flow.

You are designing a date dimension table in an Azure Synapse Analytics dedicated SQL pool. The date dimension table will be used by all the fact tables. Which distribution type should you recommend to minimize data movement?. HASH. REPLICATE. ROUND_ROBIN.

HOTSPOT - From a website analytics system, you receive data extracts about user interactions such as downloads, link clicks, form submissions, and video plays. The data contains the following columns: You need to design a star schema to support analytical queries of the data. The star schema will contain four tables including a date dimension. To which table should you add each column? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area: EventCategory:. ChannelGrouping:. TotalEvents:.

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?. JSON. CSV. Parquet. ORC.

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?. Azure SQL Database. Azure Databricks. Azure Stream Analytics.

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?. \\[<language>]. %<language>. \\[<language>]. @<language>.

You are creating a managed data warehouse solution on Microsoft Azure. You must use PolyBase to retrieve data from Azure Blob storage that resides in parquet format and load the data into a large table called FactSalesOrderDetails. You need to configure Azure Synapse Analytics to receive the data. Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. Select and Place: Create a master key on the database. Create an external data source for Azure Blob storage. Create an external file format to map the parquet files. Create an external table FactSalesOrderDetails. Load the data to a staging table. Enable Transparent Data Encryption.

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?. complete. append. update.

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?. From the Azure portal, set a mask on the Email column. From the Azure portal, set a sensitivity classification of Confidential for the Email column. From Microsoft SQL Server Management Studio, set an email mask on the Email column. From Microsoft SQL Server Management Studio, grant the SELECT permission to the users for all the columns in the dbo.Customers table except Email.

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?. Upgrade workspace1 to the Premium pricing tier. Configure a global init script for workspace1. Create a pool in workspace1. Create a cluster policy in workspace1.

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?. Yes. No.

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?. Yes. No.

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?. Yes. No.

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?. Azure Private Link. a network security group (NSG). a database-level firewall. a server-level firewall.

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?. Business Critical. Premium. Hyperscale.

You have a new Azure SQL database. The database contains a column that stores confidential information. You need to track each time values from the column are returned in a query. The tracking information must be stored for 365 days from the date the query was executed. Which three actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point. Turn on auditing and write audit logs to an Azure Storage account. Add extended properties to the column. Turn on auditing and write audit logs to an Event Hub. Apply sensitivity labels named Highly Confidential to the column. Turn on Azure Defender for SQL.

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 all traffic to the public endpoint of SqlSrv1 is blocked. ✑ Minimize the possibility of VM1 exfiltrating data stored in SqlDb1. What should you create on VNet1?. a VPN gateway. a service endpoint. a private link. an ExpressRoute gateway.

You have 40 Azure SQL databases, each for a different customer. All the databases reside on the same Azure SQL Database server. You need to ensure that each customer can only connect to and access their respective database. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point. Implement row-level security (RLS). Create users in each database. Configure the database firewall. Configure the server firewall. Create logins in the master database. Implement Always Encrypted.

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 VPN gateway. a service endpoint. a private link. an ExpressRoute gateway.

You are developing an application that uses Azure Data Lake Storage Gen 2. You need to recommend a solution to grant permissions to a specific application for a limited time period. What should you include in the recommendation?. role assignments. account keys. shared access signatures (SAS). Azure Active Directory (Azure AD) identities.

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?. row-level security. data masking. Always Encrypted. column-level security.

You have a data warehouse in Azure Synapse Analytics. You need to ensure that the data in the data warehouse is encrypted at rest. What should you enable?. Transparent Data Encryption (TDE). Advanced Data Security for this database. Always Encrypted for all columns. Secure transfer required.

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 column encryption key. asymmetric keys. a function. a custom role-based access control (RBAC) role. a security policy.

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?. Disable purge protection on vault1. Remove the linked service from df1. Create a self-hosted integration runtime. Disable soft delete on vault1.

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 contained database user for App1 on DB2. a login for App1 on Server1. a contained database user from an external provider for App1 on DB2. a contained database user from a Windows login for App1 on DB2.

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?. Create User1 in the master database. Assign User1 the db_datareader role for the master database. Assign User1 the db_datareader role for the databases that User1 creates. Grant SELECT on sys.databases to public in the master database.

You receive numerous alerts from Azure Monitor for an Azure SQL database. You need to reduce the number of alerts. You must only receive alerts if there is a significant change in usage patterns for an extended period. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point. Set Threshold Sensitivity to High. Set the Alert logic threshold to Dynamic. Set the Alert logic threshold to Static. Set Threshold Sensitivity to Low. Set Force Plan to On.

You have an Azure SQL database named sqldb1. You need to minimize the amount of space by the data and log files of sqldb1. What should you run?. DBCC SHRINKDATABASE. sp_clean_db_free_space. sp_clean_db_file_free_space. DBCC SHRINKFILE.

You have an Azure SQL Database server named sqlsrv1 that hosts 10 Azure SQL databases. The databases perform slower than expected. You need to identify whether the performance issue relates to the use of tempdb by Azure SQL databases in sqlsrv1. What should you do?. Run Query Store-based queries. Review information provided by SQL Server Profiler-based traces. Review information provided by Query Performance Insight. Run dynamic management view-based queries.

You have an Azure SQL database named sqldb1. You need to minimize the possibility of Query Store transitioning to a read-only state. What should you do?. Double the value of Data Flush interval. Decrease by half the value of Data Flush Interval. Double the value of Statistics Collection Interval. Decrease by half the value of Statistics Collection interval.

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 16 vCPUSs and 64 GB of memory. You need to provide the lowest latency for tempdb. What is the total number of data files that tempdb should contain?. 2. 4. 8. 64.

You have 50 Azure SQL databases. You need to notify the database owner when the database settings, such as the database size and pricing tier, are modified in Azure. What should you do?. Create a diagnostic setting for the activity log that has the Security log enabled. For the database, create a diagnostic setting that has the InstanceAndAppAdvanced metric enabled. Create an alert rule that uses a Metric signal type. Create an alert rule that uses an Activity Log signal type.

You have several Azure SQL databases on the same Azure SQL Database server in a resource group named ResourceGroup1. You must be alerted when CPU usage exceeds 80 percent for any database. The solution must apply to any additional databases that are created on the Azure SQL server. Which resource type should you use to create the alert?. Resource Groups. SQL Servers. SQL Databases. SQL Virtual Machines.

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 vCPUSs and 64 GB of memory. You need to provide the lowest latency for tempdb. What is the total number of data files that tempdb should contain?. 2. 4. 8. 64.

You have SQL Server on an Azure virtual machine that contains a database named DB1. DB1 contains a table named CustomerPII. You need to record whenever users query the CustomerPII table. Which two options should you enable? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point. server audit specification. SQL Server audit. database audit specification. a server principal.

You have an Azure virtual machine based on a custom image named VM1. VM1 hosts an instance of Microsoft SQL Server 2019 Standard. You need to automate the maintenance of VM1 to meet the following requirements: ✑ Automate the patching of SQL Server and Windows Server. ✑ Automate full database backups and transaction log backups of the databases on VM1. ✑ Minimize administrative effort. What should you do first?. Enable a system-assigned managed identity for VM1. Register the Azure subscription to the Microsoft.Sql resource provider. Install an Azure virtual machine Desired State Configuration (DSC) extension on VM1. Register the Azure subscription to the Microsoft.SqlVirtualMachine resource provider.

A company plans to use Apache Spark analytics to analyze intrusion detection data. You need to recommend a solution to analyze network and system activity data for malicious activities and policy violations. The solution must minimize administrative efforts. What should you recommend?. Azure Data Lake Storage. Azure Databricks. Azure HDInsight. Azure Data Factory.

You have an Azure data solution that contains an enterprise data warehouse in Azure Synapse Analytics named DW1. Several users execute adhoc queries to DW1 concurrently. You regularly perform automated data loads to DW1. You need to ensure that the automated data loads have enough memory available to complete quickly and successfully when the adhoc queries run. What should you do?. Assign a smaller resource class to the automated data load queries. Create sampled statistics to every column in each table of DW1. Assign a larger resource class to the automated data load queries. Hash distribute the large fact tables in DW1 before performing the automated data loads.

You are monitoring an Azure Stream Analytics job. You discover that the Backlogged input Events metric is increasing slowly and is consistently non-zero. You need to ensure that the job can handle all the events. What should you do?. Remove any named consumer groups from the connection and use $default. Change the compatibility level of the Stream Analytics job. Create an additional output stream for the existing input stream. Increase the number of streaming units (SUs).

You have an Azure Stream Analytics job. You need to ensure that the job has enough streaming units provisioned. You configure monitoring of the SU % Utilization metric. Which two additional metrics should you monitor? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point. Late Input Events. Out of order Events. Backlogged Input Events. Watermark Delay. Function Events.

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?. clusters. jobs. DBFS. SSH. workspace.

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?. Input Deserialization Errors. Late Input Events. Early Input Events. Watermark delay.

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?. Local tempdb percentage. DWU percentage. Data Warehouse Units (DWU) used. Cache hit percentage.

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?. Connect to Pool1 and query sys.dm_pdw_nodes_db_partition_stats. Connect to the built-in pool and run DBCC CHECKALLOC. Connect to Pool1 and run DBCC CHECKALLOC. Connect to the built-in pool and query sys.dm_pdw_nodes_db_partition_stats.

You have an Azure Synapse Analytics dedicated SQL pool. You run PDW_SHOWSPACEUSED('dbo.FactInternetSales'); and get the results shown in the following table. Which statement accurately describes the dbo.FactInternetSales table?. The table contains less than 10,000 rows. All distributions contain data. The table uses round-robin distribution. The table is skewed.

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?. Yes. No.

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?. Yes. No.

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?. Yes. No.

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?. Connect to db1 and run the sp_configure command. Connect to the master database of server1 and run the sp_configure command. Configure the extended properties of db1. Modify the database scoped configuration of db1.

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?. Yes. No.

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?. Attach two mirrored 4-TB SSDs. Attach two mirrored 4-TB SSDs. Attach a RAID-5 array that contains five 1-TB SSDs. Attach a single 4-TB SSD.

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?. Run the DBCC SQLPERF command. Run the DBCC DBREINDEX command. Modify the automatic tuning settings for db1. Modify the Query Store settings for db1.

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. Enable query optimizer fixes. Enable Resource Governor. Enable parameter sniffing. Configure In-memory OLTP. Run the Database Engine Tuning Advisor. Reduce the Max Degree of Parallelism value. Create a workload group.

You are building a database in an Azure Synapse Analytics serverless SQL pool. You have data stored in Parquet files in an Azure Data Lake Storage Gen2 container. Records are structured as shown in the following sample. The records contain two applicants at most. You need to build a table that includes only the address fields. How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Box 1:. Box 2:.

You plan to create a table in an Azure Synapse Analytics dedicated SQL pool. Data in the table will be retained for five years. Once a year, data that is older than five years will be deleted. You need to ensure that the data is distributed evenly across partitions. The solutions must minimize the amount of time required to delete old data. How should you complete the Transact-SQL statement? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point. Select and Place: Box 1:. Box 2:.

You configure version control for an Azure Data Factory instance as shown in the following exhibit. Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic. NOTE: Each correct selection is worth one point. Box 1:. Box 2:.

You are performing exploratory analysis of bus fare data in an Azure Data Lake Storage Gen2 account by using an Azure Synapse Analytics serverless SQL pool. You execute the Transact-SQL query shown in the following exhibit. Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic. Box 1:. Box 2:.

You are provisioning an Azure SQL database in the Azure portal as shown in the following exhibit. Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic. NOTE: Each correct selection is worth one point. Box 1:. Box 2:.

You have an Azure subscription. You need to deploy an Azure SQL resource that will support cross database queries by using an Azure Resource Manager (ARM) template. How should you complete the ARM template? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Box 1:. Box 2:.

You have the following Azure Resource Manager template. For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point. The template deploys a serverless Azure SQL database. The template deploys a database to an Azure SQL Database managed instance. The pricing tier of the database deployment is based on DTUS.

You have an on-premises Microsoft SQL Server 2019 instance that hosts a database named DB1. You plan to perform an online migration of DB1 to an Azure SQL managed instance by using the Azure Database Migration Service. You need to create a backup of DB1 that is accessible to the Azure Database Migration Service. What should you run for the backup and where should you store the backup? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area: Box 1:. Box 2:.

You have a Microsoft SQL Server database named DB1 that contains a table named Table1. The database role membership for a user named User1 is shown in the following exhibit. Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic. Box 1:. Box 2:.

You have a new Azure SQL database named DB1 on an Azure SQL server named AzSQL1. The only user who was created is the server administrator. You need to create a contained database user in DB1 who will use Azure Active Directory (Azure AD) for authentication. Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. Select and Place: Connect to DB1 by using the Active Directory admin account. Create a user by using the FROM EXTERNAL PROVIDER clause. Connect to DB1 by using the server administrator account. Set the Active Directory Admin for AZSQL1. From the Azure portal, assign the SQL DB Contributor role to the user. Create a login in the master database.

You have an Azure SQL database that contains a table named Employees. Employees contains a column named Salary. You need to encrypt the Salary column. The solution must prevent database administrators from reading the data in the Salary column and must provide the most secure encryption. Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. Select and Place: Encrypt the Salary column by using the randomized encryption type. Create a column encryption key. Enable Transparent Data Encryption (TDE). Encrypt the Salary column by using the deterministic encryption type. Apply a dynamic data mask to the Salary column. Create a column master key.

You have an Azure SQL database named DB1 that contains two tables named Table1 and Table2. Both tables contain a column named a Column1. Column1 is used for joins by an application named App1. You need to protect the contents of Column1 at rest, in transit, and in use. How should you protect the contents of Column1? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Box 1:. Box 2:.

You have an Azure SQL Database instance named DatabaseA on a server named Server1. You plan to add a new user named App1 to DatabaseA and grant App1 db_datareader permissions. App1 will use SQL Server Authentication. You need to create App1. The solution must ensure that App1 can be given access to other databases by using the same credentials. Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. Select and Place: On the master database, run CREATE LOGIN [APPI] FROM EXTERNAL PROVIDER;. On DatabaseA, run CREATE USER [APP1] WITH PASSWORD = 'P©ssW0rd!';. On DatabaseA, run ALTER ROLE db_datareader ADD MEMBER [App1];. On the master database, run CREATE LOGIN [App1] WITH PASSWORD = 'P@aaW0rd!';. On DatabaseA, run CREATE USER [App1] FROM LOGIN [App1];.

You have an Azure subscription that is linked to a hybrid Azure Active Directory (Azure AD) tenant. The subscription contains an Azure Synapse Analytics SQL pool named Pool1. You need to recommend an authentication solution for Pool1. The solution must support multi-factor authentication (MFA) and database-level authentication. Which authentication solution or solutions should you include in the recommendation? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Box 1:. Box 2:.

You have SQL Server on an Azure virtual machine. You review the query plan shown in the following exhibit. For each of the following statements, select yes if the statement is true. Otherwise, select no. NOTE: Each correct selection is worth one point. You will reduce the l/O usage and the query execution time if you force the query plan. You will increase the l/O usage and the query execution time if you create a new index on the SalesOrderHeader table. You will reduce the l/O usage and the query execution time if you include the SubTotal, TaxAmt, and Freight columns in the PK_SalesOrderHeader_SaleSOrderlD index.

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. 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. Change Salary to the money data type. Change PhoneNumber to the float data type. Change LastHireDate to the datetime2(7) data type. Change PhoneNumber to the bigint data type. Change LastHireDate to the date data type.

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)?. Live Query Statistics. an estimated execution plan. an actual execution plan. Client Statistics.

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?. Query Store. Metrics. Query Performance Insight. Alerts.

You have SQL Server on an Azure virtual machine that contains a database named DB1. You have an application that queries DB1 to generate a sales report. You need to see the parameter values from the last time the query was executed. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point. Enable Last_Query_Plan_Stats in the master database. Enable Lightweight_Query_Profiling in DB1. Enable Last_Query_Plan_Stats in DB1. Enable Lightweight_Query_Profiling in the master database. Enable PARAMETER_SNIFFING in DB1.

You deploy a database to an Azure SQL Database managed instance. You need to prevent read queries from blocking queries that are trying to write to the database. Which database option should set?. PARAMETERIZATION to FORCED. PARAMETERIZATION to SIMPLE. Delayed Durability to Forced. READ_COMMITTED_SNAPSHOT to ON.

You have an Azure SQL database. You discover that the plan cache is full of compiled plans that were used only once. You run the select * from sys.database_scoped_configurations Transact-SQL command and receive the results shown in the following table. You need relieve the memory pressure. What should you configure?. LEGACY_CARDINALITY_ESTIMATION. QUERY_OPTIMIZER_HOTFIXES. OPTIMIZE_FOR_AD_HOC_WORKLOADS. ACCELERATED_PLAN_FORCING.

You have SQL Server on an Azure virtual machine that contains a database named DB1. You view a plan summary that shows the duration in milliseconds of each execution of query 1178902 as shown in the following exhibit: What should you do to ensure that the query uses the execution plan which executes in the least amount of time?. Force the query execution plan for plan 1221065. Run the DBCC FREEPROCCACHE command. Force the query execution plan for plan 1220917. Disable parameter sniffing.

You have an Azure SQL database named DB1. The automatic tuning options for DB1 are configured as shown in the following exhibit. For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point. Nonclustered indexes will be added to tables to improve performance. Columns will be added to existing indexes automatically. The query execution plan will revert to a previous plan if query performance degrades.

You have an Azure SQL database named DB1. You run a query while connected to DB1. You review the actual execution plan for the query, and you add an index to a table referenced by the query. You need to compare the previous actual execution plan for the query to the Live Query Statistics. What should you do first in Microsoft SQL Server Management Studio (SSMS)?. For DB1, set QUERY_CAPTURE_MODE of Query Store to All. Run the SET SHOWPLAN_ALL Transact-SQL statement. Save the actual execution plan. Enable Query Store for DB1.

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?. Performance Recommendations in the Azure portal. Extended Events in Microsoft SQL Server Management Studio (SSMS). Query Store in Microsoft SQL Server Management Studio (SSMS). Query Performance Insight in the Azure portal.

You have an Azure SQL database. The database contains a table that uses a columnstore index and is accessed infrequently. You enable columnstore archival compression. What are two possible results of the configuration? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point. Queries that use the index will consume more disk I/O. Queries that use the index will retrieve fewer data pages. The index will consume more disk space. The index will consume more memory. Queries that use the index will consume more CPU resources.

You are designing a dimension table in an Azure Synapse Analytics dedicated SQL pool. You need to create a surrogate key for the table. The solution must provide the fastest query performance. What should you use for the surrogate key?. an IDENTITY column. a GUID column. a sequence object.

You are designing a star schema for a dataset that contains records of online orders. Each record includes an order date, an order due date, and an order ship date. You need to ensure that the design provides the fastest query times of the records when querying for arbitrary date ranges and aggregating by fiscal calendar attributes. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point. Create a date dimension table that has a DateTime key. Create a date dimension table that has an integer key in the format of YYYYMMDD. Use built-in SQL functions to extract date attributes. Use integer columns for the date fields. Use DateTime columns for the date fields.

You are designing an enterprise data warehouse in Azure Synapse Analytics that will store website traffic analytics in a star schema. You plan to have a fact table for website visits. The table will be approximately 5 GB. You need to recommend which distribution type and index type to use for the table. The solution must provide the fastest query performance. What should you recommend? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area: Box 1:. Box 2:.

You have an Azure Data Factory pipeline that is triggered hourly. The pipeline has had 100% success for the past seven days. The pipeline execution fails, and two retries that occur 15 minutes apart also fail. The third failure returns the following error. What is a possible cause of the error?. From 06:00 to 07:00 on January 10, 2021, there was no data in wwi/BIKES/CARBON. The parameter used to generate year=2021/month=01/day=10/hour=06 was incorrect. From 06:00 to 07:00 on January 10, 2021, the file format of data in wwi/BIKES/CARBON was incorrect. The pipeline was triggered too early.

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?. Generate an estimated execution plan. Generate an actual execution plan. Run sys.dm_exec_query_plan_stats. Generate Live Query Statistics.

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?. the Azure portal. Extended Events. Query Store. dynamic management views.

You are building an Azure virtual machine. You allocate two 1-TiB, P30 premium storage disks to the virtual machine. Each disk provides 5,000 IOPS. You plan to migrate an on-premises instance of Microsoft SQL Server to the virtual machine. The instance has a database that contains a 1.2-TiB data file. The database requires 10,000 IOPS. You need to configure storage for the virtual machine to support the database. Which three objects should you create in sequence? To answer, move the appropriate objects from the list of objects to the answer area and arrange them in the correct order. Select and Place: a virtual disk that uses the stripe layout. a virtual disk that uses the mirror layout. a volume. a virtual disk that uses the simple layout. a storage pool.

You have an Azure SQL database named db1. You need to retrieve the resource usage of db1 from the last week. How should you complete the statement? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Box 1:. Box 2:.

You are building an Azure Stream Analytics job to retrieve game data. You need to ensure that the job returns the highest scoring record for each five-minute time interval of each game. How should you complete the Stream Analytics query? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Box 1:. Box 2:.

Your company analyzes images from security cameras and sends alerts to security teams that respond to unusual activity. The solution uses Azure Databricks. You need to send Apache Spark level events, Spark Structured Streaming metrics, and application metrics to Azure Monitor. Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions in the answer area and arrange them in the correct order. Deploy Grafana to an Azure virtual machine. Build a spark-listeners-loganalytics-1.0-SNAPSHOT.jar JAR file. Create Dropwizard counters in the application code. Create a data source in Azure Monitor. Configure the Databricks cluster to use the Databricks monitoring library.

You have an Azure SQL managed instance named SQLMI1 that has Resource Governor enabled and is used by two apps named App1 and App2. You need to configure SQLMI1 to limit the CPU and memory resources that can be allocated to App1. Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. Create a workload group. Create a user-defined classifier function. Modify Resource Governor. Create a contained database user. Create a resource pool.

You have SQL Server on an Azure virtual machine that contains a database named Db1. You need to enable automatic tuning for Db1. How should you complete the statements? To answer, select the appropriate answer in the answer area. NOTE: Each correct selection is worth one point. Box 1:. Box 2:.

You have an Azure SQL database. You are reviewing a slow performing query as shown in the following exhibit. Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic. NOTE: Each correct selection is worth one point. Box 1:. Box 2:.

You plan to create a table in an Azure Synapse Analytics dedicated SQL pool. Data in the table will be retained for five years. Once a year, data that is older than five years will be deleted. You need to ensure that the data is distributed evenly across partitions. The solutions must minimize the amount of time required to delete old data. How should you complete the Transact-SQL statement? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point. Select and Place: Box 1:. Box 2:.