Microsoft DP-300 Dumps

To prevent db1 from reverting to the last known good query plan, you need to disable the automatic plan correction feature for the database. This feature is enabled by default and allows the Query Store to detect and fix plan performance regressions by forcing the last good plan1. However, if you want to test the performance of different plans without interference from the Query Store, you can turn off this feature by using the ALTER DATABASE SCOPED CONFIGURATION statement2.

Here are the steps to disable the automatic plan correction feature for db1:

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 SCOPED CONFIGURATION SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = OFF); GO

This command will disable the automatic plan correction feature for db1 and allow the Query Optimizer to choose the best plan based on the current statistics and parameters3.

To verify that the automatic plan correction feature is disabled for db1, you can query the sys.database_scoped_configurations catalog view. The value of the force_last_good_plan column should be 0 for db1.

These are the steps to disable the automatic plan correction feature for db1.

To enable page compression on the PK_SalesOrderHeader_SalesOrderlD clustered index of the SalesLT.SalesOrderHeader table in db1, you can use the following Transact-SQL script:

-- Connect to the Azure SQL database named db1

USE db1;

GO

-- Enable page compression on the clustered index

ALTER INDEX PK_SalesOrderHeader_SalesOrderlD ON SalesLT.SalesOrderHeader

REBUILD WITH (DATA_COMPRESSION = PAGE);

GO

This script will rebuild the clustered index with page compression, which can reduce the storage space and improve the query performance

The script solution consists of three parts:

The first part is USE db1; GO. This part connects to the Azure SQL database named db1, where the SalesLT.SalesOrderHeader table is located. The GO command separates the batches of Transact-SQL statements and sends them to the server.

The second part is ALTER INDEX PK_SalesOrderHeader_SalesOrderlD ON SalesLT.SalesOrderHeader REBUILD WITH (DATA_COMPRESSION = PAGE); GO. This part enables page compression on the clustered index named PK_SalesOrderHeader_SalesOrderlD, which is defined on the SalesLT.SalesOrderHeader table. The ALTER INDEX statement modifies the properties of an existing index. The REBUILD option rebuilds the index from scratch, which is required to change the compression setting. The DATA_COMPRESSION = PAGE option specifies that page compression is applied to the index, which means that both row and prefix compression are used. Page compression can reduce the storage space and improve the query performance by compressing the data at the page level. The GO command ends the batch of statements.

The third part is optional, but it can be useful to verify the compression status of the index. It is SELECT name, index_id, data_compression_desc FROM sys.indexes WHERE object_id = OBJECT_ID('SalesLT.SalesOrderHeader');. This part queries the sys.indexes catalog view, which contains information about the indexes in the database. The SELECT statement returns the name, index_id, and data_compression_desc columns for the indexes that belong to the SalesLT.SalesOrderHeader table. The OBJECT_ID function returns the object identification number for the table name. The data_compression_desc column shows the compression type of the index, which should be PAGE for the clustered index after the script is executed.

These are the steps of the script solution for enabling page compression on the clustered index of the SalesLT.SalesOrderHeader table in db1.

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 -ServerName -Location "" -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "", $(ConvertTo-SecureString -String "" -AsPlainText -Force))

Run the following command to create a new failover group with dbl: New-AzSqlDatabaseFailoverGroup -ResourceGroupName -ServerName -PartnerResourceGroupName -PartnerServerName -FailoverGroupName -Database dbl -FailoverPolicy -GracePeriodWithDataLossHours -ReadWriteFailoverEndpoint ""

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