Refer to the Exhibit Practice Questions

Refer to the exhibit.

Exhibit:
```
{
    "properties": {
        "collation": "SQL_Latin1_General_CP1_CI_AS",
        "maxSizeBytes": 214748364800,
        "sku": {
            "name": "GP_Gen5_4",
            "tier": "GeneralPurpose",
            "capacity": 4
        },
        "storageAccountType": "GRS",
        "zoneRedundant": false
    },
    "location": "eastus"
}
```

Refer to the exhibit.
```json
{
  "properties": {
    "description": "Automated backup retention policy",
    "policyType": "Default",
    "retentionDays": 35,
    "backupIntervalInHours": 12,
    "geoRedundantBackup": "Enabled"
  }
}
```

Refer to the exhibit.

```
-- Current state of the availability group
SELECT ag.name AS ag_name, 
       replica_server_name, 
       role_desc, 
       operational_state_desc, 
       connected_state_desc, 
       synchronization_health_desc, 
       last_connect_error_number, 
       last_connect_error_time
FROM sys.dm_hadr_availability_replica_states rs
JOIN sys.availability_groups ag ON rs.group_id = ag.group_id
WHERE ag.name = 'AG1';
```

Output:
ag_name | replica_server_name | role_desc | operational_state_desc | connected_state_desc | synchronization_health_desc | last_connect_error_number | last_connect_error_time
AG1     | SQLVM-Primary        | PRIMARY   | PENDING_FAILOVER       | CONNECTED            | HEALTHY                     | 0                         | NULL
AG1     | SQLVM-Secondary      | SECONDARY | ONLINE                 | DISCONNECTED         | NOT_HEALTHY                 | 35202                     | 2024-03-15 10:30:00.000

Refer to the exhibit.

Exhibit:
```
Event ID: 4140
Severity: 16
Message: Could not allocate space for object 'sys.sysrscols' in database 'mydb' because the 'PRIMARY' filegroup is full.
```

Extended Events session trace:

name: system_health
session_id: 5
total_events: 12450
event_name: sql_statement_completed
cpu_time: 2500 ms
logical_reads: 45000
duration: 3000 ms

name: system_health
session_id: 5
total_events: 12451
event_name: sql_statement_completed
cpu_time: 3200 ms
logical_reads: 62000
duration: 4000 ms

Wait statistics from sys.dm_os_wait_stats:

wait_type: PAGEIOLATCH_SH
waiting_tasks_count: 500000
wait_time_ms: 120000

wait_type: LCK_M_S
waiting_tasks_count: 200
wait_time_ms: 500

Azure Monitor metric for Azure SQL Database:

Metric: dtu_consumption_percent
Time range: Past hour
Average: 95%
Max: 100%
Min: 30%

Metric: cpu_percent
Average: 80%
Max: 95%

Metric: physical_data_read_percent
Average: 90%
Max: 100%

Metric: log_write_percent
Average: 20%

Query Store top resource consumers:

Query ID: 1234
Execution count: 5000
Total logical reads: 2.5 million
Total duration: 10,000 ms

Query ID: 5678
Execution count: 100
Total logical reads: 50,000
Total duration: 500 ms

Azure CLI output from 'az sql db list-editions -o table':

Edition       | SLO        | DTU | MaxStorageGB
GeneralPurpose | GP_Gen5_2  | 100 | 512
GeneralPurpose | GP_Gen5_4  | 200 | 512
GeneralPurpose | GP_Gen5_8  | 400 | 1024
BusinessCritical | BC_Gen5_2 | 100 | 512
BusinessCritical | BC_Gen5_4 | 200 | 512

Current database properties:

Name: SalesDB
Edition: GeneralPurpose
ServiceObjective: GP_Gen5_2
Storage used: 480 GB
Max storage: 512 GB

Query Store shows:

Total log write rate: 5 MB/s
Log IO percentage: 80%

sys.dm_db_log_space_usage:

Total log space: 100 GB
Used log space: 95 GB

Azure SQL Database automatic tuning recommendations:

Recommendation ID: 1
Type: CREATE_INDEX
Reason: Improve query performance
Score: 80
State: Active
Impacted queries: 3

Recommendation ID: 2
Type: DROP_INDEX
Reason: Index not used
Score: 90
State: Pending verification
Impacted queries: 0

Recommendation ID: 3
Type: FORCE_LAST_GOOD_PLAN
Reason: Regression detected
Score: 100
State: Active
Impacted queries: 1

Current query performance metrics for impacted query of recommendation 3:

Before plan change: avg CPU 500 ms, avg duration 1000 ms
After plan change: avg CPU 1000 ms, avg duration 2000 ms

Refer to the exhibit.

{
  "properties": {
    "serverKeyType": "AzureKeyVault",
    "uri": "https://myvault.vault.azure.net/keys/mykey/123456",
    "serverKeyName": "mykey",
    "kind": "azurekeyvault"
  },
  "id": "/subscriptions/sub-id/resourceGroups/rg/providers/Microsoft.Sql/servers/myserver/encryptionProtector/current",
  "name": "current",
  "type": "Microsoft.Sql/servers/encryptionProtector"
}

{
  "type": "Microsoft.Sql/servers/databases/backupLongTermRetentionPolicies",
  "apiVersion": "2021-02-01-preview",
  "properties": {
    "weeklyRetention": "P4W",
    "monthlyRetention": "P12M",
    "yearlyRetention": "P5Y",
    "weekOfYear": 1
  }
}

Refer to the exhibit.

```
-- Output from sys.dm_db_index_physical_stats
OBJECT_NAME = 'Orders'
index_type_desc = 'CLUSTERED'
avg_fragmentation_in_percent = 65
page_count = 25000
```

Refer to the exhibit.

```powershell
$params = @{
    ResourceGroupName = 'rg-sql'
    ServerName = 'sqlserver1'
    DatabaseName = 'db1'
    Edition = 'GeneralPurpose'
    ComputeGeneration = 'Gen5'
    VCores = 4
    MaxVCores = 8
    MinVCores = 0.5
    AutoPauseDelayInMinutes = 60
}
New-AzSqlDatabase @params
```

Refer to the exhibit.

```json
{
  "properties": {
    "storageAccountUri": "https://mystorageaccount.blob.core.windows.net/backups",
    "retentionDays": 30,
    "storageAccountAccessKey": "..."
  }
}
```

Refer to the exhibit.

```sql
CREATE USER [user@contoso.com] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [user@contoso.com];
```