Microsoft DP-300 - Administering Relational Databases on Microsoft Azure

To ensure that db1 is compatible with all the features and syntax of SQL Server 2012, you need to set the compatibility level of the database to 110, which is the compatibility level for SQL Server 20121. The compatibility level affects the behavior of certain Transact-SQL statements and features, and determines how the database engine interprets the SQL code2.

You can set the compatibility level of db1 by using the Azure portal or Transact-SQL statements. Here are the steps for both methods:

Using the Azure portal:

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

Select the database db1 and click on Query Performance Insight in the left menu.

Click on Configure Query Store and select 110 from the Compatibility level dropdown list.

Click on Save to apply the change.

Using Transact-SQL statements:

Connect to db1 using SQL Server Management Studio, Azure Data Studio, or any other tool that supports Transact-SQL statements.

Open a new query window and run the following command: ALTER DATABASE db1 SET COMPATIBILITY_LEVEL = 110; GO

This command will set the compatibility level of db1 to 110, which is equivalent to SQL Server 2012.

These are the steps to set the compatibility level of db1 to 110. 

To configure a disaster recovery solution for db1, 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 also use active geo-replication as an alternative, but you will need to update the connection strings manually after a failover2.

Here are the steps to create a failover group for db1 with the secondary server in the West US 3 region:

Using the Azure portal:

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

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

Enter a name for the failover group and select West US 3 as the secondary 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 db1 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 db1 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 West US 3 region: New-AzSqlServer -ResourceGroupName < your-resource-group-name > -ServerName < your-secondary-server-name > -Location " West US 3 " -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 db1: 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 db1 -FailoverPolicy Manual -GracePeriodWithDataLossHours 1 -ReadWriteFailoverEndpoint " Enabled "

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 db1 with the secondary server in the West US 3 region.

Requirement: Configure differential backups for Azure SQL Database db1 to run once a day instead of twice a day.

Correct setting: Change Differential backup frequency from 12 Hours to 24 Hours.

Azure SQL Database supports differential backup frequency of either 12 hours or 24 hours. A 12-hour frequency means twice per day; a 24-hour frequency means once per day. Microsoft also notes that 24-hour differential backup frequency can increase restore time compared with 12-hour frequency.

Method 1 — Azure Portal Method

This is the best method for the simulation.

Step 1: Open the Azure SQL logical server

Sign in to the Azure portal.

Search for SQL servers.

Open the logical SQL server that hosts database db1.

Do not start from SQL Server Management Studio for this task. The differential backup frequency is an Azure SQL backup policy setting, not a normal T-SQL database setting.

Step 2: Open the Backups page

In the SQL server left menu, select Backups.

Select the Retention policies tab.

Microsoft’s documented portal path is to go to the logical SQL server, select Backups, then select the Retention policies tab.

Step 3: Select database db1

In the list of databases, locate db1.

Select the checkbox next to db1.

Select Configure policies from the action bar.

Step 4: Change the differential backup frequency

In the policy configuration pane:

Find Differential backup frequency.

Change it from:

12 Hours

to:

24 Hours

Leave the PITR retention period unchanged unless the task specifically tells you to change it.

Select Apply or Save.

Microsoft’s documented option is exactly 12 Hours or 24 hours under Differential backup frequency.

Method 2 — Azure CLI Method

Use this if the simulation provides Cloud Shell.

az sql db str-policy set \

--resource-group < resource-group-name > \

--server < server-name > \

--name db1 \

--retention-days < current-retention-days > \

--diffbackup-hours 24

Example:

az sql db str-policy set \

--resource-group RG1 \

--server sql60152867 \

--name db1 \

--retention-days 7 \

--diffbackup-hours 24

Microsoft documents az sql db str-policy set with --diffbackup-hours 24 for changing active database differential backup frequency. Valid values are 12 or 24 hours.

Be careful: do not guess the retention days blindly in a real environment. In the exam lab, use the existing retention value shown in the portal unless the task also asks you to change retention.

Method 3 — PowerShell Method

Use this if Azure PowerShell is available.

Set-AzSqlDatabaseBackupShortTermRetentionPolicy `

-ResourceGroupName " < resource-group-name > " `

-ServerName " < server-name > " `

-DatabaseName " db1 " `

-RetentionDays < current-retention-days > `

-DiffBackupIntervalInHours 24

Microsoft documents Set-AzSqlDatabaseBackupShortTermRetentionPolicy with -DiffBackupIntervalInHours 24 for setting Azure SQL Database differential backup frequency.

SSMS / T-SQL Clarification

For this task, SSMS is not the right tool to change the setting.

There is no normal ALTER DATABASE T-SQL command in Azure SQL Database to change automated differential backup frequency. Microsoft documents this change through:

Azure portal

Azure CLI

PowerShell

REST API

not SSMS/T-SQL.

You may use SSMS only to confirm the database exists and is accessible, but the backup frequency setting must be changed from Azure management tools.

Basic Concept: This question tests high availability and disaster recovery design for Azure SQL, SQL Server on Azure VMs, and regional failure scenarios.

Why the selected answer is Correct: The selected values fit this scenario because they apply the required Azure SQL configuration at the correct scope and sequence: You need to recommend which service and target endpoint to use when migrating the databases from SVR1 to Instance1.

Why the alternate choices are Wrong: The alternate choices do not satisfy the stated availability design because quorum and listener resources must match Azure failover-cluster behavior and the required SLA.

Basic Concept: This question tests choosing the correct Azure migration approach by matching the source platform, target service, downtime tolerance, and administrative effort.

Why the selected answer is Correct: The selected values fit this scenario because they apply the required Azure SQL configuration at the correct scope and sequence: You need to recommend an authentication solution for App1 access to DB1 and DB2 after their migration to Instance1.

Why the alternate choices are Wrong: The alternate selections would either use a migration tier that does not match the downtime requirement, choose a network resource that does not support the migration path, or add an unnecessary deployment component.

Basic Concept: This question tests high availability and disaster recovery design for Azure SQL, SQL Server on Azure VMs, and regional failure scenarios.

Why D is Correct: Configure connection strings that reference the read-write listener. is correct because it is the feature whose normal purpose matches the stated requirement. Configure connection strings that reference the read-write listener. is part of the availability or recovery design space, but the correct choice must satisfy the specified failover, restore, quorum, RPO, or RTO requirement. The scenario wording points to that specific behavior: You need to recommend which configuration to perform twice to enable access to the primary and secondary replicas of DB3.

Why A is Wrong: A service endpoint secures traffic to an Azure service over the Azure backbone but still targets the service public endpoint rather than giving it a private IP in the VNet. It is not wrong technology in general, but it is the wrong HA/DR control for this scenario ' s failure model.

Why B is Wrong: Database-level firewall rules restrict access to a specific Azure SQL database and are more granular than server-level firewall rules. It does not meet the failover, restore, quorum, or cross-region continuity target stated in the question, even if it is valid in a different availability design.

Why C is Wrong: Create database-scoped credentials. is part of the availability or recovery design space, but the correct choice must satisfy the specified failover, restore, quorum, RPO, or RTO requirement. It handles a different resilience pattern and would not deliver the failover or recovery behavior required here.

Basic Concept: This question tests high availability and disaster recovery design for Azure SQL, SQL Server on Azure VMs, and regional failure scenarios.

Why the selected answer is Correct: The selected values fit this scenario because they apply the required Azure SQL configuration at the correct scope and sequence: You need to specify the licenseType and storagenedundancy parameters.

Why the alternate choices are Wrong: The alternate choices do not satisfy the stated availability design because quorum and listener resources must match Azure failover-cluster behavior and the required SLA.

Scenario: The sales data, including the documents in JSON format, must be gathered as it arrives and analyzed online by using Azure Stream Analytics. The analytics process will perform aggregations that must be done continuously, without gaps, and without overlapping.

Tumbling window functions are used to segment a data stream into distinct time segments and perform a function against them, such as the example below. The key differentiators of a Tumbling window are that they repeat, do not overlap, and an event cannot belong to more than one tumbling window.

Scenario: Real-time processing must be monitored to ensure that workloads are sized properly based on actual usage patterns.

To monitor the performance of a database in Azure SQL Database and Azure SQL Managed Instance, start by monitoring the CPU and IO resources used by your workload relative to the level of database performance you chose in selecting a particular service tier and performance level.

Basic Concept: This question tests choosing the correct Azure migration approach by matching the source platform, target service, downtime tolerance, and administrative effort.

Why B is Correct: an Azure Blob Storage container matches the expected DP-300 administration action. an Azure Blob Storage container is an Azure data-platform feature, but it must match the workload model, service tier, migration path, or deployment constraint described in the scenario. The question is not asking for a general Azure capability; it is asking for the feature that produces this result: You need to identify the event_flle target for monitonng DB3 after the migration to Azure SQL Database.

Why A is Wrong: an Azure SQL database is an Azure data-platform feature, but it must match the workload model, service tier, migration path, or deployment constraint described in the scenario. It mismatches the target platform, migration method, compute tier, or deployment model described in the scenario, so it would not satisfy the workload as stated.

Why C is Wrong: a SQL Server filegroup is an Azure data-platform feature, but it must match the workload model, service tier, migration path, or deployment constraint described in the scenario. It would solve a neighboring problem, but not the deployment, sizing, or migration requirement in this item.

Why D is Wrong: an Azure Files share is an Azure data-platform feature, but it must match the workload model, service tier, migration path, or deployment constraint described in the scenario. It mismatches the target platform, migration method, compute tier, or deployment model described in the scenario, so it would not satisfy the workload as stated.