Microsoft DP-300 - Administering Relational Databases on Microsoft Azure

Prepare a secondary database for an Always On availability group requires two steps:

1. Restore a recent database backup of the primary database and subsequent log backups onto each server

instance that hosts the secondary replica, using RESTORE WITH NORECOVERY

2. Join the restored database to the availability group.

Instead deploy an Azure SQL database that uses the Business Critical service tier and Availability Zones.

Note: Premium and Business Critical service tiers leverage the Premium availability model, which integrates compute resources (sqlservr.exe process) and storage (local ly attached SSD) on a single node. High availability is achieved by replicating both compute and storage to additional nodes creating a three to four-node cluster.

By default, the cluster of nodes for the premium availability model is created in the same datacenter. With the introduction of Azure Availability Zones, SQL Database can place different replicas of the Business Critical database to different availability zones in the same region. To eliminate a single point of failure, the control ring is also duplicated across multiple zones as three gateway rings (GW).

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 plan 1 .  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 statement 2 .

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 parameters 3 .

To ve rify 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 corr ection feature for db1.

To generate an email alert to admin@contoso.com when CPU percentage utilization for db1 is higher than average, you can use the Azure portal to create an alert rule based on the CPU percentage metric. Here are the steps to do that:

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

Select  Alerts  in the Monitoring section and click on  New alert rule .

In the Condition section, click  Add  and select the  CPU percentage  metric.

In the Configure signal logic page, set the threshold type to  Dynamic .  This will compare the current metric value to the historical average and trigger the alert when it deviates signific antly 1 .

Set the operator to  Greater than , the aggregation type to  Average , the aggregation granularity to  1 minute , and the frequency of evaluation to  5 minutes .

Click  Done  to save the condition.

In the Action group section, click  Create  and enter a name and a short name for the action group.

In the Notifications section, click  Add  and select  Email/SMS message/Push/Voice .

Enter  admin@contoso.com  in the Email field and click  OK .

Click  OK  to save the action group.

In the Alert rule details section, enter a name and a description for the alert rule, choose a severity level, and make sure the rule is enabled.

Click  Create alert rule   to create the alert rule.

This alert rule will send an email to admin@contoso.com when the CPU percentage utilization for db1 is higher than average.  You can also add other actions to the alert rule, such as calling a webhook or running an automation script

To ensure that any enhancements made to the Query Optimizer through patches are available to dbl and db2 on sql37006895, you need to enable the query optimizer hotfixes option f or each database.  This option allows you to use the latest query optimization improvements that are not enabled by default 1 .  You can enable this option by using the ALTER DATABASE SCOPED CONFIGURATION statement 2 .

Here are the steps to enable the query optimizer hotfixes option for dbl and db2 on sql37006895:

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

Open a new query window and run t he following commands for each database:

-- Switch to the database context

USE dbl;

GO

-- Enable the query optimizer hotfixes option

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;

GO

Repeat the same commands for db2, replacing dbl with db2 in the USE statement.

To verify that the query optimizer hotfixes option is enabled for each database, you can query the sys.database_scoped_configurations catalog view. The value of the query_optimizer_hotfixes column should be 1 for both databa ses.

These are the steps to enable the query optimizer hotfixes option for dbl and db2 on sql37006895.

Scenario: Business Requirements

Litware identifies business requirements include: meet an SLA of 99.99% availability for all Azure deployments.

Box 1: Cloud witness

If you have a Failover Cluster deployment, where all nodes can reach the internet (by extension of Azure), it is recommended that you configure a Cloud Witness as your quorum witness resource.

Box 2: Azure Basic Load Balancer

Microsoft guarantees that a Load Balanced Endpoint using Azure Standard Load Balancer, serving two or more Healthy Virtual Machine Instances, will be available 99.99% of the time.

Note: There are two main options for setting up your listener: external (public) or internal. The external (public) listener uses an internet facing load balancer and is associated with a public Virtual IP (VIP) that is accessible over the internet. An internal listener uses an internal load balancer and only supports clients within the same Virtual Network.