Administering Relational Databases on Microsoft Azure

Here you have the best Microsoft DP-300 practice exam questions

  • You have 331 total questions to study from
  • Each page has 5 questions, making a total of 67 pages
  • You can navigate through the pages using the buttons at the bottom
  • This questions were last updated on November 18, 2024
Question 1 of 331

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.

    Correct Answer: A, C, E

    When sizing an Azure SQL Database elastic pool, it is essential to consider several key metrics. First, the total size of all the databases is crucial, as it determines the storage requirements for the pool. Second, the number of concurrently peaking databases multiplied by the peak CPU utilization per database is important to assess the maximum computational demand. Lastly, the total number of databases multiplied by the average CPU utilization per database helps in gauging the average computational load. These three metrics together provide a comprehensive understanding of the necessary resources to meet the demands of the workload effectively. Therefore, total size of all the databases, number of concurrently peaking databases * peak CPU utilization per database, and total number of databases * average CPU utilization per database are the correct metrics to use.

Question 2 of 331

DRAG DROP -

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:

    Correct Answer:

    Step 1: Attach the SSISDB database

    Step 2: Turn on the TRUSTWORTHY property and the CLR property

    If you are restoring the SSISDB database to an SQL Server instance where the SSISDB catalog was never created, enable common language runtime (clr)

    Step 3: Open the master key for the SSISDB database

    Restore the master key by this method if you have the original password that was used to create SSISDB. open master key decryption by password = 'LS1Setup!' --'Password used when creating SSISDB'

    Alter Master Key Add encryption by Service Master Key

    Step 4: Encrypt a copy of the master key by using the service master key

    Reference:

    https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog

Question 3 of 331

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?

    Correct Answer: C

    Columnstore compression is the correct choice because it offers significant space reduction while also ensuring optimal performance for queries. While columnstore archival compression provides even greater space reduction, it compromises performance, which is not ideal given the requirement to maximize performance. Traditional row or page compression techniques do not achieve the same level of compression or performance improvements as columnstore compression.

Question 4 of 331

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?

    Correct Answer: B

    B

    This table lists the key features for PolyBase and the products in which they're available.

    Incorrect Answers:

    C: Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications. It applies to both Azure SQL Database and SQL

    Server.

    D: Azure SQL Database and Azure SQL Managed Instance automatic tuning provides peak performance and stable workloads through continuous performance tuning based on AI and machine learning.

    Reference:

    https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-versioned-feature-summary

Question 5 of 331

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?

    Correct Answer: D

    To minimize downtime and data loss during the migration of a 4-TB database to an Azure SQL Database managed instance, Azure Database Migration Service is the most suitable option. This service supports online migrations, which limit downtime to the duration of the cutover at the end of the migration, thus ensuring minimal disruption to the application and reduced data loss.