DP-300 Administering Relational Databases on Microsoft Azure Questions and Answers

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

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

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 configure sq1370O6895 to support the creation of new database users from Azure AD identities, you need to do the following steps:

Set up a Microsoft Entra tenant and associate it with your Azure subscription.  You can use the Microsoft Entra portal or the Azure portal to create and manage your Microsoft Entra users and groups 1 2 .

Configure a Microsoft Entra admin for sq1370O6895.  You can use the Azure portal or the Azure CLI to set a Microsoft Entra user as the ad min for the server 3 4 .  The Microsoft Entra admin can create other database users from Microsoft Entra identities 5 .

Connect to db1 using the Microsoft Entra admin account and run the following Transact-SQL statement to create a new database user from a Microsoft Entra identity:  CREATE USER [Microsoft Entra user name] FROM EXTERNAL PROVIDER; 6  You can replace the Microsoft Entra user name with the name of the user or group that you want to create in the database.

Grant the appropriate permissions to the new database user by adding them to a database role or granting them specific privileges. For example, you can run the following Transact-SQL statement to add the new user to the db_datareader role:  ALTER ROLE db_datareader ADD MEMBER [Microsoft Entra user name];

These are the steps to configure sq1370O6895 to support the creation of new database users from Azure AD identities.

To enable change data capture (CDC) for db1, you need to run the stored procedure sys.sp_cdc_enable_db in the database context.  CDC is a feature that records activity on a datab ase when tables and rows have been modified 1 .  CDC can be used for various scenarios, such as data synchronization, auditing, or ETL processes 2 .

Here are the steps to enable CDC 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 q uery window and run the following command:  EXEC sys.sp_cdc_enable_db; GO

This command will enable CDC for the database and create the cdc schema, cdc user, metadata tables, and other system objects for the database 3 .

To verify that CDC is enabled for db1, you can query the is_cdc_enabled column in the sys.databases catalog view. The value should be 1 for db1.

These are the steps to enable CDC for db1

To configure your user account as the Azure AD admin for the server named sql3700689S, you can use the Azure portal or the Azure CLI. Here are the steps for both methods:

Using the Azure portal:

Go to the Azure portal and select  SQL Server – Azure Arc .

Select the server named  sql3700689S  and click on  Active Directory admin .

Click on  Set admin  and choose your user account from the list of Azure AD users.

Click on  Select  and then  Save  to confirm the change.

You can verify the Azure AD admin by clicking on  Active Directory admin  again and checking the current admin.

Using the Azure CLI:

Install the Azure CLI and log in with your Azure account.

Run the following command to get the object ID of your user account:  az ad user show --id < your-user-name > --query objectId -o tsv

Run the following command to set your user account as the Azure AD admin for the server:  az sql server ad -admin create --server sql3700689S --object-id < your-object-id > --display-name < your-user-name >

You can verify the Azure AD admin by running the following command:  az sql server ad-admin show --server sql3700689S

These are the steps to configure your user account as the Azure AD admin for the server named sql3700689S.

To configure high availability for dbl, you can use the failover groups feature of Azure SQL Database.  Failover groups allow you to manage the repl ication and failover of a group of databases across different regions with the same connection strings 1 . 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 Azu re 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 Pow erShell 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 > " -AsPlainTe xt -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 > -Partner ServerName < 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 modif y 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

SQL injection attacks are a type of cyberattack that exploit a vulnerability in the application code that interacts with the database.  An attacker can inject malicious SQL statements into the user input, such as a form field or a URL parameter, and execute them on the databas e server, resulting in data theft, corruption, or unauthorized access 1 .

To protect all the databases on sql37006S95 from SQL injection atta cks, you need to follow some best practices for securing your application and database layers. Here are some of the recommended steps:

Use parameterized queries or stored procedures to separate the SQL code from the user input.  This will prevent the user input from being interpreted as part of the SQL statement and avoid SQL injection 2 3 .

Validate and sanitize the user input before passing it to the database.  This will ensure that the input conforms to the expected format and type, and remove any potentia lly harmful characters or keywords 4 .

Implement least privilege access for the database users and roles.  This will limit the permissions and actions that the application can perform on the database, and reduce the impact of a successful SQL injection attack 5 .

Enable Advanced Threat Protection for Azure SQL Database. This is a feature that detects and alerts you of anomalous activities and potential threats on yo ur database, such as SQL injection, brute force attacks, or unusual access patterns. You can configure the alert settings and notifications using the Azure portal or PowerShell.

These are some of the steps to protect all the databases on sql37006S95 from S QL injection attacks.

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 S erver 2012 1 .  The compatibility level affects the behavior of certain Transact-SQL statements and features, and determines how the database engine interprets the SQL code 2 .

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 follow ing 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 react to increased workloads and increase streaming units, consider setting an alert of 80% on the SU Utilization metric. Also, you can use watermark delay and backlogged events metrics to see if there is an impact.

Note: Backlogged Input Events: Number of input ev ents that are backlogged. A non-zero value for this metric implies that your job isn ' t able to keep up with the number of incoming events. If this value is slowly increasing or consistently non-zero, you should scale out your job, by increasing the SUs.

https://www.mssqltips.com/sqlservertip/3685/live-query-statistics-in-sql-server-2016/

Follow these same steps to create striped virtual disk:

Create Log Storage Pool.

Create Virtual Disk

Create Volume

Box 1: a storage pool

Box 2: a virtual disk that uses stripe layout

Disk Striping: Use multiple disks and stripe them together to get a combined higher IOPS and Throughput l imit. The combined limit per VM should be higher than the combined limits of attached premium disks.

Box 3: a volume

The lifecycle management policy lets you:

Delete blobs, blob versions, and blob snapshots at the end of their lifecycles

Scenario: Contoso requirements for the sales transaction dataset include:

Implement a surrogate key to account for changes to the retail store addresses.

A surrogate key on a table is a column with a unique identifier for each row. The key is not generated from the

table data. Data modelers like to create surrogate keys on their tables when they design data warehouse models. You can use the IDENTITY property to achieve this goal simply and effectively without affecting load performance.

Box 1: Hash

Scenario:

Ensure that queries joining and filtering sales transaction records based on product ID complete as quickly as possible.

A hash distributed table can deliver the highest query performance for joins and aggregations on large tables.

Box 2: Round-robin

Scenario:

You plan to create a promotional table that will contain a promotion ID. The promotion ID will be associated to a specific product. The pro duct will be identified by a product ID. The table will be approximately 5 GB.

A round-robin table is the most straightforward table to create and delivers fast performance when used as a staging table for loads. These are some scenarios where you should choose Round robin distribution:

When you cannot identify a single key to distribute your data.

If your data doesn’t frequently join with data from other tables.

When there are no obvious keys to join.

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.

Automating Azure SQL DB index and statistics maintenance using Azure Automation:

1. Create Azure automation account (Step 1)

2. Import SQLServer module (Step 2)

3. Add Credentials to access SQL DB

This will use secure way to hold login name and password that will be used to access Azure SQL DB

4. Add a runbook to run the maintenance (Step 3)

Steps:

1. Click on " runbooks " at the left panel and then click " add a runbook "

2. Choose " create a new runbook " and then give it a name and choose " Powershell " as the type of the runbook and then click on " create "

5. Schedule task (Step 4)

Steps:

1. Click on Schedules

2. Click on " Add a schedule " and follow the instructions to choose existing schedule or create a new schedule.

Azure Database Migration service

Box 1: Premium 4-VCore

Scenario: Migrate the SERVER1 databases to the Azure SQL Database platform.

Minimize downtime during the migration of the SERVER1 databases.

Premimum 4-vCore is for large or business critical workloads. It supports online migrations, offline migrations, and faster migration speeds.

Scenario: Authenticate databas e users by using Active Directory credentials.

(Create a new Azure SQL database named ResearchDB1 on a logical server named ResearchSrv01.)

Authenticate the user in SQL Database or SQL Data Warehouse based on an Azure Active Directory user:

CREATE USER [Fr itz@contoso.com] FROM EXTERNAL PROVIDER;

Box 1: DTU

Scenario:

The 30 new databases must scale automatically.

Once all requirements are met, minimize costs whenever possible.

You can configure resources for the pool based either on the DTU-based purchasing model or the vCore-based purchasing model.

In short, for simplicity, the DTU model has an advantage. Plus, if you’re just getting started with Azure SQL Database, the DTU model offers more options at the lower end of performance, so you can get started at a lower price point than with vCore.

Box 2: An Azure SQL database elastic pool

Az ure SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single server and share a set number of resources at a set price. Elastic pools in Azure SQL Database enable SaaS developers to optimize the price performance for a group of databases within a prescribed budget while delivering performance elasticity for each database.

Scenario: Authenticate database users by using Active Directory credentials.

The configuration steps include the following procedures to configure and use Azure Active Directory authentication.

Create and populate Azure AD.

Optional: Associ ate or change the active directory that is currently associated with your Azure Subscription.

Create an Azure Active Directory administrator. (Step 1)

Configure your client computers.

Create contained database users in your database mapped to Azure AD identities. (Step 2)

Connect to your database by using Azure AD identities. (Step 3)

Scenario: SalesSQLDb1 uses database firewall rules and contained database users.

SalesSQLDb1 experiences performance issues that are likely due to out-of-date statistics and frequent blocking queries.

A: Use sys.dm_pdw_nodes_tran_locks instead of sys.dm_tran_locks from Azure Synapse Analytics (SQL Data Warehouse) or Parallel Data Warehouse.

E: Example:

The following query will show blocking information.

SELECT

t1.resource_type,

t1.resource_database_id,

t1.resource_associated_entity_id,

t1.request_mode,

t1.request_session_id,

t2.blocking_session_id

FROM sys.dm_tran_locks as t1

INNER JOIN sys.dm_os_waiting_tasks as t2

ON t1.lock_owner_address = t2.resource_address;

Note: Depending on the system you’re working with you can access these wait statistics from one of three locations:

sys.dm_os_wait_stats: for SQL Server

sys.dm_db_wait_stats: for Azure SQL Database

sys.dm_p dw_nodes_os_wait_stats: for Azure SQL Data Warehouse

Box 1: The server, the elastic pool, and the database

Senario:

SalesSQLDb1 is in an elastic pool named SalesSQLDb1Pool.

Litware technical requirements include: all SQL Server and Azure SQL Database metrics related to CPU and storage usage and limits must be analyzed by using Azure built-in functionality.

Box 2: Azure Event hubs

Scenario: Migrate ManufacturingSQLDb1 to the Azure virtual machine platform.

Event hubs are able to handle custom metrics.

Box 1: Yes

DBAGroup1 is member of the Contributor role.

The Contributor role grants full access to manage all resources, but does not allow you to assign roles in Azure RBAC, manage assignments in Azure Blueprints, or share image galleries.

Box 2: No

Box 3: Yes

DBAGroup2 is member of the SQL DB Contributor role.

The SQL DB Contributor role lets you manage SQL databases, but not access to them. Also, you can ' t manage their security-related policie s or their parent SQL servers. As a member of this role you can create and manage SQL databases.

Ex planation:

Scenario: Use a single dashboard to review security and audit data for all the PaaS databases.

With dashboards can bring together operational data that is most important to IT across all your Azure resources, including telemetry from Azure Log Analytics.

Note: Auditing for Azure SQL Database and Azure Synapse Analytics tracks database events and writes them to an audit log in your Azure storage account, Log Analytics workspace, or Event Hubs.

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.