Free Practice Questions for Microsoft DP-300 Exam

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 strings 1 .  You can also use active geo-replication as an alternative, but you will need to update the connection strings manually after a failover 2 .

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 Azur e 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 -Typ eName 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 -FailoverPo licy 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.

To enable page compression on the PK_SalesOrderHeader_SalesOrderlD clustered index of th e 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 st orage 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 separate s 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 perf ormance 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.inde xes 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 f or 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 clu stered 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 ensure that all queries executed against dbl are captured in the Query Store, you need to enable the Query Store feature for the database and set the query capture mode to ALL.  The Query Store feature provides you with insight on query plan choice and performance for Azure SQL Database 1 .  The query capture mode controls whether all queries or only a subset of quer ies are tracked 2 .

Here are the steps to enable the Query Store and set the query capture mode to ALL for the database dbl:

Using the Azure portal:

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

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

Click on Configure Query Store and turn on the Query Store switch.

In the Query Capture Mode dropdown, select All and click on Save.

Using Transact-SQL statements:

Connect to the Azure SQL Database server and the database dbl using SQL Server Management Studio or Azure Data Studio.

Run the following command to enable the Query Store f or the database:  ALTER DATABASE dbl SET QUERY_STORE = ON;

Run the following command to set the query capture mode to ALL for the database:  ALTER DATABASE dbl SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL);

These are the steps to ensure that all queries execute d against dbl are captured in the Query Store.

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 dat a 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.