Microsoft Administering Relational Databases on Microsoft Azure (DP-300 Deutsch Version) - DP-300 Deutsch Exam Practice Test

Azure SQL elastic pool pausing feature
Aan elastic pool vCore provisioned server can be paused. You can do this by changing the compute tier of the elastic pool to Serverless. In the serverless compute tier, the database is automatically paused when it is inactive for a period of time that you specify. The database is automatically resumed when the next login or other activity occurs.
Part 1: Change to the serverless compute tier
To change the compute tier of an elastic pool, you can follow these steps:
Step 1: Go to the Azure portal and sign in to your account.
Step 2: Select the Azure SQL resource that contains the elastic pool.
Step 3: In the left navigation menu, select Elastic Pools.
Step 4: Select the elastic pool that you want to change the compute tier for.
Step 5: In the Settings section, select Compute Tier.
Step 6: Select Serverless.
Step 7: Click Save.
Part 2: Configure the Auto-pause setting.
Step 1: Select db1.
Once you have changed the compute tier to serverless, the database will be paused after the idle time that you specified. You can view the idle time in the Auto-pause delay setting.
Step 2: Open database settings.
Step 3: Change the Auto-pause delay setting to 1 hour.
Reference:
https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview
https://learn.microsoft.com/en-us/azure/azure-sql/database/single-database-create-quickstart

Explanation:
Box 1: max_worker_percent
Maximum concurrent workers (requests) in percentage of the limit of the database's service tier.
Box 2: dm_db_resource_stats:
This dynamic management view (DMV) in Azure SQL Database provides real-time resource utilization data.
The query SELECT avg_memory_usage_percent FROM sys. is used in Azure SQL Database to retrieve the average memory usage percentage of a database. This information is provided by the sys.dm_db_resource_stats dynamic management view (DMV). The avg_memory_usage_percent column represents the average memory usage as a percentage of the maximum allowed memory for the database's service tier and performance level.
Reference:
https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-resource-stats-azure-sql-database

Automatic tuning options
The automatic tuning options available in Azure SQL Database and Azure SQL Managed Instance are:
* FORCE LAST GOOD PLAN (automatic plan correction)
Identifies Azure SQL queries using an execution plan that is slower than the previous good plan, and forces queries to use the last known good plan instead of the regressed plan.
* Etc.
You can either manually apply tuning recommendations using the Azure portal, or you can let automatic tuning autonomously apply tuning recommendations for you.
Azure defaults are set to FORCE_LAST_GOOD_PLAN enabled.
We need to disable it.
With transact-SQL:
ALTER DATABASE SET options (Transact-SQL)
Parameters include:
<automatic_tuning_option> ::=
{
AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = { DEFAULT | ON | OFF } )
}
Solution with T-SQL
Stored procedure execution
You can use the SQL Server Management Studio (SSMS) user interface or Transact-SQL in an SSMS query window to execute a stored procedure. Always use the latest version of SSMS.
Use SQL Server Management Studio
Step 1: In Object Explorer, connect to an instance of SQL Server or Azure SQL Database, expand that instance, and then expand Databases.
Step 2: Expand the database that you want, expand Programmability, and then expand Stored Procedures.
Step 3: Right-click the stored procedure that you want to run [here: ALTER DATABASE SET] and select Execute Stored Procedure.
Step 4: Set the parameters: AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = { OFF } ) Step 5: Select OK to execute the stored procedure. If the stored procedure doesn't have any parameters, just select OK.
Reference:
https://learn.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-overview
https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options
https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/execute-a-stored-procedure

Explanation:
Step 1: Create an Azure Policy Initiative
The first step in enforcing compliance with Azure Policy is to assign a policy definition. A policy definition defines under what condition a policy is enforced and what effect to take.
With an initiative definition, you can group several policy definitions to achieve one overarching goal. An initiative evaluates resources within scope of the assignment for compliance to the included policies.
Step 2: Create an Azure Policy Initiative assignment
Assign the initiative definition you created in the previous step.
Step 3: Run Azure Policy remediation tasks
To apply the Policy Initiative to the existing SQL databases.
Reference:
https://docs.microsoft.com/en-us/azure/governance/policy/tutorials/create-and-manage

Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).

Explanation:
Box 1: Store the infrastructure logs in the Cool access tier the application logs in the Archive access tier Hot - Optimized for storing data that is accessed frequently.
Cool - Optimized for storing data that is infrequently accessed and stored for at least 30 days.
Archive - Optimized for storing data that is rarely accessed and stored for at least 180 days with flexible latency requirements, on the order of hours.
Box 2: Azure Blob storage lifecycle management rules
Blob storage lifecycle management offers a rich, rule-based policy that you can use to transition your data to the best access tier and to expire data at the end of its lifecycle.
Reference:
https://docs.microsoft.com/en-us/azure/storage/blobs/storage-blob-storage-tiers

Explanation:
Box 1: No
There is only one query plan available. Force has no effect.
Box 2: No
Adding an index will not increase IO usage.
Box 3: Yes
The performance would improve.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store