Microsoft DP-300 Administering Microsoft Azure SQL Solutions Online Training

Question #51

SIMULATION

Task 6

You need to ensure that any enhancements made to the Query Optimizer through patches are available to dbl and db2 on sql37006895.

Reveal Solution Hide Solution

Question #52

SIMULATION

Task 7

You plan to create an automation runbook that will create database users in db1 from Azure AD identities. You need to configure sq1370O6895 to support the creation of new database users.

Reveal Solution Hide Solution

Question #53

SIMULATION

Task 8

You plan to perform performance testing of db1.

You need prevent db1 from reverting to the last known good query plan.

Reveal Solution Hide Solution

Question #54

SIMULATION

Task 9

You need to generate an email alert to [email protected] when CPU percentage utilization for db1 is higher than average.

Reveal Solution Hide Solution

Question #55

SIMULATION

Task 10

You need to protect all the databases on sql37006S95 from SQL injection attacks.

Reveal Solution Hide Solution

Question #56

SIMULATION

Task 11

You have a legacy application written for Microsoft SQL Server 2012. The application will be the only application that accesses db1 You need to ensure that db1 is compatible with all the features and syntax of SQL Server 2012.

Reveal Solution Hide Solution

Question #57

SIMULATION

Task 12

You need to configure high availability for dbl. The solution must tolerate the loss of an Azure

datacenter without data loss or the need to modify application connection strings.

Reveal Solution Hide Solution

Correct Answer: To configure high availability for dbl, you can use the failover groups feature of Azure SQL Database. Failover groups allow you to manage the replication and failover of a group of databases across different regions with the same connection strings1. You can choose all, or a subset of, user databases in a logical server to be replicated to another logical server in a different region. You can also specify the failover policy, such as manual or automatic, and the grace period for data loss. Here are the steps to create a failover group for dbl: Using the Azure portal:

Go to the Azure portal and select your Azure SQL Database server that hosts dbl.

Select Failover groups in the left menu and click on Add group.

Enter a name for the failover group and select a secondary region that is different from the primary region.

Click on Create a new server and enter the details for the secondary server, such as server name, admin login, password, and subscription.

Click on Select existing database(s) and choose dbl from the list of databases on the primary server. Click on Configure failover policy and select the failover mode, grace period, and read-write failover endpoint mode according to your preferences.

Click on Create to create the failover group and start the replication of dbl to the secondary server.

Using PowerShell commands:

Install the Azure PowerShell module and log in with your Azure account.

Run the following command to create a new server in the secondary region: New-AzSqlServer – ResourceGroupName <your-resource-group-name> -ServerName <your-secondary-server-name> – Location "<secondary-region-name>" -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "<your-admin-login>", $(ConvertTo-SecureString -String "<your-password>" -AsPlainText -Force))

Run the following command to create a new failover group with dbl: New-AzSqlDatabaseFailoverGroup -ResourceGroupName <your-resource-group-name> -ServerName <your-primary-server-name> -PartnerResourceGroupName <your-resource-group-name> – PartnerServerName <your-secondary-server-name> -FailoverGroupName <your-failover-group-name> -Database dbl -FailoverPolicy <manual-or-automatic> -GracePeriodWithDataLossHours <grace-period-in-hours> -ReadWriteFailoverEndpoint "<enabled-or-disabled>"

You can modify the parameters of the command according to your preferences, such as the failover policy, grace period, and read-write failover endpoint mode. These are the steps to create a failover group for dbl

Question #58

Topic 6, Misc. Questions

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?

A . page compression
B . row compression
C . columnstore compression
D . columnstore archival compression

Reveal Solution Hide Solution

Question #59

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?

A . distributed availability groups
B . database mirroring
C . log shipping
D . Database Migration Assistant

Reveal Solution Hide Solution

Question #60

HOTSPOT

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.