Download Administering Relational Databases on Microsoft Azure.DP-300.NewDumps.2021-02-05.57q.vcex

Vendor: Microsoft
Exam Code: DP-300
Exam Name: Administering Relational Databases on Microsoft Azure
Date: Feb 05, 2021
File Size: 4 MB

How to open VCEX files?

Files with VCEX extension can be opened by ProfExam Simulator.

Demo Questions

Question 1
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?
  1. a VPN gateway
  2. a service endpoint
  3. a private link
  4. an ExpressRoute gateway
Correct answer: C
Explanation:
Azure Private Link enables you to access Azure PaaS Services (for example, Azure Storage and SQL Database) and Azure hosted customer-owned/partner services over a private endpoint in your virtual network. Traffic between your virtual network and the service travels the Microsoft backbone network. Exposing your service to the public internet is no longer necessary. Reference:https://docs.microsoft.com/en-us/azure/private-link/private-link-overview
Azure Private Link enables you to access Azure PaaS Services (for example, Azure Storage and SQL Database) and Azure hosted customer-owned/partner services over a private endpoint in your virtual network. 
Traffic between your virtual network and the service travels the Microsoft backbone network. 
Exposing your service to the public internet is no longer necessary. 
Reference:
https://docs.microsoft.com/en-us/azure/private-link/private-link-overview
Question 2
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.
Correct answer: To work with this question, an Exam Simulator is required.
Explanation:
Box 1: sys.resource_statssys.resource_stats returns CPU usage and storage data for an Azure SQL Database. It has database_name and start_time columns. Box 2: DateAddThe following example returns all databases that are averaging at least 80% of compute utilization over the last one week. DECLARE @s datetime; DECLARE @e datetime; SET @s= DateAdd(d,-7,GetUTCDate()); SET @e= GETUTCDATE(); SELECT database_name, AVG(avg_cpu_percent) AS Average_Compute_Utilization FROM sys.resource_stats WHERE start_time BETWEEN @s AND @e GROUP BY database_name HAVING AVG(avg_cpu_percent) >= 80 Incorrect Answers: sys.dm_exec_requests:sys.dm_exec_requests returns information about each request that is executing in SQL Server. It does not have a column named database_name. sys.dm_db_resource_stats:sys.dm_db_resource_stats does not have any start_time column. Note: sys.dm_db_resource_stats returns CPU, I/O, and memory consumption for an Azure SQL Database database. One row exists for every 15 seconds, even if there is no activity in the database. Historical data is maintained for approximately one hour. Sys.dm_user_db_resource_governance returns actual configuration and capacity settings used by resource governance mechanisms in the current database or elastic pool. It does not have any start_time column. Reference:https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-resource-stats-azure-sql-database
Box 1: sys.resource_stats
sys.resource_stats returns CPU usage and storage data for an Azure SQL Database. It has database_name and start_time columns. 
Box 2: DateAdd
The following example returns all databases that are averaging at least 80% of compute utilization over the last one week. 
DECLARE @s datetime; 
DECLARE @e datetime; 
SET @s= DateAdd(d,-7,GetUTCDate()); 
SET @e= GETUTCDATE(); 
SELECT database_name, AVG(avg_cpu_percent) AS Average_Compute_Utilization FROM sys.resource_stats WHERE start_time BETWEEN @s AND @e GROUP BY database_name HAVING AVG(avg_cpu_percent) >= 80 Incorrect Answers: 
sys.dm_exec_requests:
sys.dm_exec_requests returns information about each request that is executing in SQL Server. It does not have a column named database_name. 
sys.dm_db_resource_stats:
sys.dm_db_resource_stats does not have any start_time column. 
Note: sys.dm_db_resource_stats returns CPU, I/O, and memory consumption for an Azure SQL Database database. One row exists for every 15 seconds, even if there is no activity in the database. 
Historical data is maintained for approximately one hour. 
Sys.dm_user_db_resource_governance returns actual configuration and capacity settings used by resource governance mechanisms in the current database or elastic pool. It does not have any start_time column. 
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-resource-stats-azure-sql-database
Question 3
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. 
Correct answer: To work with this question, an Exam Simulator is required.
Explanation:
Follow these same steps to create striped virtual disk:Create Log Storage Pool. Create Virtual Disk Create Volume Box 1: a storage poolBox 2: a virtual disk that uses stripe layoutDisk Striping: Use multiple disks and stripe them together to get a combined higher IOPS and Throughput limit. The combined limit per VM should be higher than the combined limits of attached premium disks. Box 3: a volumeReference:https://hanu.com/hanu-how-to-striping-of-disks-for-azure-sql-server/
Follow these same steps to create striped virtual disk:
  • Create Log Storage Pool. 
  • Create Virtual Disk 
  • Create Volume 
Box 1: a storage pool
Box 2: a virtual disk that uses stripe layout
Disk Striping: Use multiple disks and stripe them together to get a combined higher IOPS and Throughput limit. The combined limit per VM should be higher than the combined limits of attached premium disks. 
Box 3: a volume
Reference:
https://hanu.com/hanu-how-to-striping-of-disks-for-azure-sql-server/
Question 4
You have SQL Server on an Azure virtual machine that contains a database named DB1. The database reports a CHECKSUM error. 
You need to recover the database. 
How should you complete the statements? To answer, select the appropriate options in the answer area. 
NOTE: Each correct selection is worth one point.
Correct answer: To work with this question, an Exam Simulator is required.
Explanation:
Box 1: SINGLE_USERThe specified database must be in single-user mode to use one of the following repair options. Box 2: REPAIR_ALLOW_DATA_LOSSREPAIR_ALLOW_DATA_LOSS tries to repair all reported errors. These repairs can cause some data loss.  Note: The REPAIR_ALLOW_DATA_LOSS option is a supported feature but it may not always be the best option for bringing a database to a physically consistent state. If successful, the REPAIR_ALLOW_DATA_LOSS option may result in some data loss. In fact, it may result in more data lost than if a user were to restore the database from the last known good backup. Incorrect Answers:REPAIR_FAST Maintains syntax for backward compatibility only. No repair actions are performed. Box 3: MULTI_USERMULTI_USER All users that have the appropriate permissions to connect to the database are allowed. Reference:https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql
Box 1: SINGLE_USER
The specified database must be in single-user mode to use one of the following repair options. 
Box 2: REPAIR_ALLOW_DATA_LOSS
REPAIR_ALLOW_DATA_LOSS tries to repair all reported errors. These repairs can cause some data loss.  
Note: The REPAIR_ALLOW_DATA_LOSS option is a supported feature but it may not always be the best option for bringing a database to a physically consistent state. If successful, the REPAIR_ALLOW_DATA_LOSS option may result in some data loss. In fact, it may result in more data lost than if a user were to restore the database from the last known good backup. 
Incorrect Answers:
REPAIR_FAST 
Maintains syntax for backward compatibility only. No repair actions are performed. 
Box 3: MULTI_USER
MULTI_USER 
All users that have the appropriate permissions to connect to the database are allowed. 
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql
Question 5
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?
  1. Yes
  2. No
Correct answer: B
Explanation:
Instead restore Database1 from Server1 to the Server2 by using the RESTORE Transact-SQL command and the REPLACE option. Note: REPLACE should be used rarely and only after careful consideration. Restore normally prevents accidentally overwriting a database with a different database. If the database specified in a RESTORE statement already exists on the current server and the specified database family GUID differs from the database family GUID recorded in the backup set, the database is not restored. This is an important safeguard. Reference:https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql
Instead restore Database1 from Server1 to the Server2 by using the RESTORE Transact-SQL command and the REPLACE option. 
Note: REPLACE should be used rarely and only after careful consideration. Restore normally prevents accidentally overwriting a database with a different database. If the database specified in a RESTORE statement already exists on the current server and the specified database family GUID differs from the database family GUID recorded in the backup set, the database is not restored. This is an important safeguard. 
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql
Question 6
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. 
Which type of compression provides the greatest space reduction for the database?
  1. page compression
  2. row compression
  3. columnstore compression
  4. columnstore archival compression
Correct answer: D
Explanation:
Columnstore tables and indexes are always stored with columnstore compression. You can further reduce the size of columnstore data by configuring an additional compression called archival compression. Note: Columnstore - The columnstore index is also logically organized as a table with rows and columns, but the data is physically stored in a column-wise data format. Incorrect Answers:B: Rowstore - The rowstore index is the traditional style that has been around since the initial release of SQL Server. For rowstore tables and indexes, use the data compression feature to help reduce the size of the database. Reference:https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression
Columnstore tables and indexes are always stored with columnstore compression. You can further reduce the size of columnstore data by configuring an additional compression called archival compression. 
Note: Columnstore - The columnstore index is also logically organized as a table with rows and columns, but the data is physically stored in a column-wise data format. 
Incorrect Answers:
B: Rowstore - The rowstore index is the traditional style that has been around since the initial release of SQL Server. 
For rowstore tables and indexes, use the data compression feature to help reduce the size of the database. 
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression
Question 7
You plan to move two 100-GB databases to Azure. You need to dynamically scale resources consumption based on workloads. 
The solution must minimize downtime during scaling operations. 
What should you use?
  1. An Azure SQL Database elastic pool
  2. SQL Server on Azure virtual machines
  3. an Azure SQL Database managed instance
  4. Azure SQL databases
Correct answer: A
Question 8
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?
  1. distributed availability groups
  2. database mirroring
  3. log shipping
  4. Database Migration Assistant
Correct answer: A
Explanation:
The Data Migration Assistant (DMA) helps you upgrade to a modern data platform by detecting compatibility issues that can impact database functionality in your new version of SQL Server or Azure SQL Database. DMA recommends performance and reliability improvements for your target environment and allows you to move your schema, data, and uncontained objects from your source server to your target server. Note: SQL Managed Instance supports the following database migration options (currently these are the only supported migration methods):Azure Database Migration Service - migration with near-zero downtime. Native RESTORE DATABASE FROM URL - uses native backups from SQL Server and requires some downtime. Reference:https://docs.microsoft.com/en-us/sql/dma/dma-overview
The Data Migration Assistant (DMA) helps you upgrade to a modern data platform by detecting compatibility issues that can impact database functionality in your new version of SQL Server or Azure SQL Database. DMA recommends performance and reliability improvements for your target environment and allows you to move your schema, data, and uncontained objects from your source server to your target server. 
Note: SQL Managed Instance supports the following database migration options (currently these are the only supported migration methods):
  • Azure Database Migration Service - migration with near-zero downtime. 
  • Native RESTORE DATABASE FROM URL - uses native backups from SQL Server and requires some downtime. 
Reference:
https://docs.microsoft.com/en-us/sql/dma/dma-overview
Question 9
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.
Correct answer: To work with this question, an Exam Simulator is required.
Explanation:
Step 1: Set up the Active Directory Admin for AzSQL1.Step 2: Connect to DB1 by using the server administrator.Sign into your managed instance with an Azure AD login granted with the sysadmin role. Step 3: Create a user by using the FROM EXTERNAL PROVIDER clause.FROM EXTERNAL PROVIDER is available for creating server-level Azure AD logins in SQL Database managed instance. Azure AD logins allow database-level Azure AD principals to be mapped to server-level Azure AD logins. To create an Azure AD user from an Azure AD login use the following syntax:CREATE USER [AAD_principal] FROM LOGIN [Azure AD login] Reference:https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql
Step 1: Set up the Active Directory Admin for AzSQL1.
Step 2: Connect to DB1 by using the server administrator.
Sign into your managed instance with an Azure AD login granted with the sysadmin role. 
Step 3: Create a user by using the FROM EXTERNAL PROVIDER clause.
FROM EXTERNAL PROVIDER is available for creating server-level Azure AD logins in SQL Database managed instance. Azure AD logins allow database-level Azure AD principals to be mapped to server-level Azure AD logins. To create an Azure AD user from an Azure AD login use the following syntax:
CREATE USER [AAD_principal] FROM LOGIN [Azure AD login] 
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql
Question 10
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)?
  1. Live Query Statistics
  2. an estimated execution plan
  3. an actual execution plan
  4. Client Statistics
Correct answer: C
Explanation:
To include an execution plan for a query during execution On the SQL Server Management Studio toolbar, click Database Engine Query. You can also open an existing query and display the estimated execution plan by clicking the Open File toolbar button and locating the existing query. Enter the query for which you would like to display the actual execution plan. On the Query menu, click Include Actual Execution Plan or click the Include Actual Execution Plan toolbar button.       Note: Actual execution plans are generated after the Transact-SQL queries or batches execute.Because of this, an actual execution plan contains runtime information, such as actual resource usage metrics and runtime warnings (if any). The execution plan that is generated displays the actual query execution plan that the SQL Server Database Engine used to execute the queries. Reference:https://docs.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan
To include an execution plan for a query during execution 
  1. On the SQL Server Management Studio toolbar, click Database Engine Query. You can also open an existing query and display the estimated execution plan by clicking the Open File toolbar button and locating the existing query. 
  2. Enter the query for which you would like to display the actual execution plan. 
  3. On the Query menu, click Include Actual Execution Plan or click the Include Actual Execution Plan toolbar button. 
    
Note: Actual execution plans are generated after the Transact-SQL queries or batches execute.
Because of this, an actual execution plan contains runtime information, such as actual resource usage metrics and runtime warnings (if any). The execution plan that is generated displays the actual query execution plan that the SQL Server Database Engine used to execute the queries. 
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan
HOW TO OPEN VCE FILES

Use VCE Exam Simulator to open VCE files
Avanaset

HOW TO OPEN VCEX AND EXAM FILES

Use ProfExam Simulator to open VCEX and EXAM files
ProfExam Screen

ProfExam
ProfExam at a 20% markdown

You have the opportunity to purchase ProfExam at a 20% reduced price

Get Now!