Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 301:

  DRAG DROP

  You have SQL Server on an Azure virtual machine.

  You need to use Policy-Based Management in Microsoft SQL Server to identify stored procedures that do not comply with your naming conventions.

  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:

  

  Correct Answer:

  

  Reference: https://www.mssqltips.com/sqlservertip/2298/enforce-sql-server-database-naming-conventions-using-policy-based-management/

• Question 302:

  DRAG DROP

  You have an Azure subscription that contains an Azure SQL managed instance named SQLMi1 and a SQL Agent job named Backupdb. Backupdb performs a daily backup of the databases hosted on SQLMi1.

  You need to be notified by email if the job fails.

  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.

  NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

  Select and Place:

  

  Correct Answer:

  

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/job-automation-managed-instance

• Question 303:

  DRAG DROP

  You have an Azure Active Directory (Azure AD) tenant named contoso.com that contains a user named user1@contoso.com and an Azure SQL managed instance named SQLMI1.

  You need to ensure that user1@contoso.com can create logins in SQLMI1 that map to Azure AD service principals.

  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:

  

  Correct Answer:

  

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/aad-security-configure-tutorial

• Question 304:

  DRAG DROP

  You have an Azure subscription that contains the resources shown in the following table.

  

  You need to back up db1 to mysqlbackups, and then restore the backup to a new database named db2 that is hosted on SQL1. The solution must ensure that db1 is backed up to a stripe set.

  Which three Transact-SQL statements should you execute in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.

  Select and Place:

  

  Correct Answer:

  

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-ver15

• Question 305:

  DRAG DROP

  You have an Azure SQL database named DB1. DB1 contains a table that has a column named Col1.

  You need to encrypt the data in Col1.

  Which four actions should you perform for DB1 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:

  

  Correct Answer:

  

  Reference: https://www.sqlshack.com/an-overview-of-the-column-level-sql-server-encryption/

• Question 306:

  DRAG DROP You create a new Azure SQL managed instance named SQL1 and enable Database Mail extended stored You need to ensure that SQ Server Agent jobs running on SQL 1 can notify when a failure Occurs Which three actions should you perform in sequence? To answer. move the appropriate actions from the list of actions to answer area and arrange them in correct order.

  Select and Place:

  

  Correct Answer:

  

• Question 307:

  DRAG DROP

  You have an Azure subscription that contains an Azure SQL database named SQLDb1.

  SQLDb1 contains a table named Table1.

  You plan to deploy an Azure web app named webapp1 that will export rows in Table1 that have changed.

  You need to ensure that webapp1 can identity the changes to Table.

  The solution must meet the following requirements:

  1.

  Minimize compute times.

  2.

  Minimize storage.

  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:

  

  Correct Answer:

  

  Step 1: Connect to SQLDB1 and run the following Transact-SQL statement ALTER DATABASE SQLDb1 SET CHANGE_TRACKING - ON

  Before you can use change tracking, you must enable change tracking at the database level.

  The following example shows how to enable change tracking by using ALTER DATABASE.

  ALTER DATABASE AdventureWorks2012

  SET CHANGE_TRACKING = ON

  (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

  Step 2: Connect to SQLDB1 and run the following Transact-SQL statement ALTER TABLE dbo.Table1 ENABLE CHANGE TRACKING

  Enable Change Tracking for a Table

  Change tracking must be enabled for each table that you want tracked. When change tracking is enabled, change tracking information is maintained for all rows in the table that are affected by a DML operation.

  The following example shows how to enable change tracking for a table by using ALTER TABLE.

  ALTER TABLE Person.Contact

  ENABLE CHANGE_TRACKING

  WITH (TRACK_COLUMNS_UPDATED = ON)

  Step 3: From webapp1, connect to SQLDb1, obtain the initial dataset, and run the CHANGETABLE() function.

  Obtain changes by using change tracking functions

  The CHANGETABLE function is used to obtain change tracking information.

  This rowset function is used to query for change information. The function queries the data stored in the internal change tracking tables. The function returns a results set that contains the primary keys of rows that have changed together with

  other change information such as the operation, columns updated and version for the row.

  Incorrect:

  * CHANGE_TRACKING_CURRENT_VERSION() function

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-tracking-sql-server https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/work-with-change-tracking-sql-server

• Question 308:

  DRAG DROP

  You have an Azure subscription.

  You plan to deploy a new Azure virtual machine that will host a Microsoft SQL Server instance.

  You need to configure the disks on the virtual machine. The solution must meet the following requirements:

  1.

  Minimize latency for transaction logs.

  2.

  Minimize the impact on IO Of the virtual machine.

  Which type of disk should you use for each workload?

  To answer. drag the appropriate disk types to the correct workloads. Each disk type may be used once, 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:

  

  Correct Answer:

  

  Box 1: Premium SSD

  Storage: Performance best practices for SQL Server on Azure VMs.

  Place tempdb on the local ephemeral SSD (default D:\) drive for most SQL Server workloads that are not part of Failover Cluster Instance (FCI) after choosing the optimal VM size.

  Select Premium SSD instead of Standard SSD for better performance.

  Box 2: Ultra Disk

  For the log drive plan for capacity and test performance versus cost while evaluating the premium P30 - P80 disks

  If submillisecond storage latency is required, use Azure ultra disks for the transaction log.

  For M-series virtual machine deployments consider write accelerator over using Azure ultra disks.

  Reference:

  https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-storage

• Question 309:

  DRAG DROP

  You have a database named db1.

  The log for db1 contains the following entry.

  

  You need to ensure that db1 can process transactions.

  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:

  

  Correct Answer:

  

  Step 1: Remove db1 from the availability group.

  Step 2: Shrink the transaction log file.

  You can use SQL Server Management Studio or Transact-SQL to shrink a data or log file.

  Example:

  SQL

  USE UserDB;

  GO

  DBCC SHRINKFILE (DataFile1, 7);

  GO

  Incorrect:

  * Back up the transaction log file.

  Backup the Log file to free up space within the file (logically/virtually).

  Step 3: Add db1 back to the availability group.

  Note:

  Symptoms

  Consider the following scenario:

  You have Microsoft SQL Server 2012 or a later version installed on a server.

  The instance of SQL Server is a primary replica in Always On Availability Groups environment.

  The autogrow option for transaction log files is set in SQL Server.

  In this scenario, the transaction log may become large and run out of disk space or exceed the MaxSize option set for the transaction log at the primary replica and you receive an error message that resembles the following:

  Error: 9002, Severity: 17, State: 9. The transaction log for database '%.*ls' is full due to 'AVAILABILITY_REPLICA'

  Cause

  This occurs when the logged changes at primary replica are not yet hardened on the secondary replica.

  Workaround

  After you identify the secondary database that makes this occur, try one or more of the following methods to work around this issue temporarily:

  *

  Take the database out of the availability group for the offending secondary.

  Note

  This method will result in the loss of the High Availability/Disaster Recovery scenario for the secondary. You may have to set up the Availability Group again in the future.

  *

  If the redo thread is frequently blocked, disable the Readable Secondary feature by changing the ALLOW_CONNECTIONS parameter of the SECONDARY_ROLE for the replica to NO.

  Note

  This will prevent users from reading the data in the secondary replica which is the root cause of the blocking. Once the redo queue has dropped to an acceptable size, consider enabling the feature again.

  *

  Enable the autogrow setting if it is disabled and there is available disk space.

  *

  Increase the MaxSize value for the transaction log file if it has been reached and there is available disk space.

  *

  Add an additional transaction log file if the current one has reached the system maximum of 2 TB or if additional space is available on another available volume.

  Reference: https://learn.microsoft.com/en-us/troubleshoot/sql/availability-groups/error-9002-transaction-log-large https://thecloudtechnologist.com/2015/03/18/shrinking-sql-log-files-in-an-availability-group-cluster-or-database-mirror/

• Question 310:

  DRAG DROP

  You have an Azure subscription that contains an Azure SQL managed instance, a database named db1, and an Azure web app named Appl. Appl uses db1.

  You need to enable Resource Governor for a App1. The solution must meet the following requirements:

  1.

  App1 must be able to consume all available CPU resources.

  2.

  App1 must have at least half of the available CPU resources always available.

  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.

  NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

  Select and Place:

  

  Correct Answer: