DP-300 Configure and manage automation of tasks — All Questions With Answers

Refer to the exhibit.

```
{
    "properties": {
        "startTime": "2024-05-01T00:00:00Z",
        "interval": "PT1H",
        "frequency": "Week",
        "daysOfWeek": [ "Sunday" ],
        "timeZone": "UTC",
        "retentionPolicy": {
            "days": 30
        }
    },
    "type": "Microsoft.Sql/servers/elasticPools/elasticJobAgents/jobs/executions"
}
```

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

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

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

Refer to the exhibit.

{
    "type": "Microsoft.Sql/servers/databases",
    "apiVersion": "2023-08-01-preview",
    "name": "[format('{0}/{1}', parameters('serverName'), parameters('databaseName'))]",
    "location": "[parameters('location')]",
    "properties": {
        "collation": "SQL_Latin1_General_CP1_CI_AS",
        "maxSizeBytes": 1073741824,
        "readScale": "Disabled",
        "zoneRedundant": false,
        "autoPauseDelay": 60
    },
    "sku": {
        "name": "GP_Gen5_2",
        "tier": "GeneralPurpose",
        "family": "Gen5",
        "capacity": 2
    }
}

Refer to the exhibit.

$params = @{
    ResourceGroupName = 'rg-sql'
    ServerName = 'sqlserver123'
    DatabaseName = 'mydb'
    Edition = 'Standard'
    RequestedServiceObjectiveName = 'S2'
    ElasticPoolName = 'pool1'
}
Set-AzSqlDatabase @params

Refer to the exhibit.

{
    "properties": {
        "displayName": "DailyBackup",
        "description": "Backup all databases",
        "intervalType": "Recurring",
        "interval": 1440,
        "intervalUnit": "Minutes",
        "schedule": {
            "startTime": "2025-01-01T00:00:00Z",
            "interval": "P1D"
        }
    }
}

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

Invoke-AzSqlDatabaseTransparentDataEncryptionProtector -ServerName "myserver" -ResourceGroupName "myrg" -ProtectorType "AzureKeyVault" -KeyVaultKeyId "https://myvault.vault.azure.net/keys/mykey/abc123"

{
  "type": "Microsoft.Sql/servers/databases/auditingSettings",
  "apiVersion": "2020-11-01-preview",
  "properties": {
    "state": "Enabled",
    "auditActionsAndGroups": [
      "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP",
      "FAILED_DATABASE_AUTHENTICATION_GROUP"
    ],
    "storageAccountAccessKey": "...",
    "retentionDays": 90
  }
}

Refer to the exhibit.

{
  "type": "Microsoft.Sql/servers/databases",
  "apiVersion": "2023-05-01-preview",
  "properties": {
    "collation": "SQL_Latin1_General_CP1_CI_AS",
    "maxSizeBytes": 5368709120,
    "requestedServiceObjectiveName": "S2",
    "zoneRedundant": false,
    "autoPauseDelay": 60
  }
}

Refer to the exhibit.

$resourceGroup = "rg-sql"
$databaseName = "sqldb1"
$elasticPoolName = "ep1"

Set-AzSqlDatabase -ResourceGroupName $resourceGroup `
  -DatabaseName $databaseName `
  -ElasticPoolName $elasticPoolName

Refer to the exhibit.

-- Kusto Query Language (KQL) query
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| where Category == "SQLSecurityAuditEvents"
| where action_id_s == "AUSC"
| project TimeGenerated, session_server_principal_name_s, statement_s

{
  "properties": {
    "targetGroup": {
      "type": "SqlDatabase",
      "membershipType": "Exclude",
      "members": [
        {
          "serverName": "prod-server",
          "databaseName": "db1"
        }
      ]
    },
    "schedule": {
      "type": "Once",
      "startTime": "2026-01-15T02:00:00Z"
    },
    "jobStep": {
      "type": "TSql",
      "commandText": "EXEC sp_cleanup @retention_days = 30"
    }
  }
}

{
  "properties": {
    "name": "WeeklyBackup",
    "description": "Weekly backup of all databases",
    "isEnabled": true,
    "storageAccountType": "Standard_LRS",
    "retentionDays": 7,
    "backupSchedule": {
      "frequencyInterval": 7,
      "frequencyUnit": "Day"
    },
    "databases": [
      {
        "databaseName": "SalesDB"
      }
    ]
  }
}

Refer to the exhibit.

{
  "type": "Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies",
  "apiVersion": "2023-05-01-preview",
  "properties": {
    "retentionDays": 28,
    "diffBackupIntervalInHours": 24,
    "weeklyRetention": "P4W",
    "monthlyRetention": "P12M",
    "yearlyRetention": "P7Y",
    "weekOfYear": 1
  }
}

Refer to the exhibit.

{
  "name": "start-stop-database",
  "type": "Microsoft.Logic/workflows",
  "location": "eastus",
  "properties": {
    "definition": {
      "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
      "actions": {
        "Pause_Database": {
          "type": "ApiConnection",
          "inputs": {
            "host": {
              "connection": {
                "name": "@parameters('$connections')['sql']['connectionId']"
              }
            },
            "method": "post",
            "path": "/databases/@{encodeURIComponent('AdventureWorks')}/pause",
            "queries": {
              "api-version": "2023-08-01-preview"
            }
          }
        }
      },
      "triggers": {
        "Recurrence": {
          "recurrence": {
            "frequency": "Week",
            "interval": 1,
            "schedule": {
              "hours": [20],
              "minutes": [0],
              "weekDays": ["Friday"]
            }
          },
          "type": "Recurrence"
        }
      }
    }
  }
}

Refer to the exhibit.

$params = @{
    ResourceGroupName = 'rg-sql-prod'
    ServerName = 'sql-prod-01'
    DatabaseName = 'SalesDB'
    Edition = 'Standard'
    RequestedServiceObjectiveName = 'S3'
    ElasticPoolName = 'pool-prod'
}

Set-AzSqlDatabase @params

Refer to the exhibit.

{ "type": "Microsoft.Sql/servers/databases", "apiVersion": "2022-05-01-preview", "properties": { "autoPauseDelay": 60, "minCapacity": 0.5, "zoneRedundant": false } }

Refer to the exhibit.

```
Invoke-SqlCmd -ServerInstance 'myserver.database.windows.net' -Database 'mydb' -Query "SELECT * FROM sys.dm_db_resource_stats" -Credential $cred | Out-GridView
```

Refer to the exhibit.

```
{
    "properties": {
        "targetGroups": [
            {
                "type": "SqlDatabase",
                "serverName": "myserver",
                "databaseName": "mydb"
            }
        ],
        "content": {
            "command": "ALTER INDEX ALL ON Sales.Orders REBUILD"
        }
    }
}
```

Refer to the exhibit.
```json
{
  "type": "Microsoft.Sql/servers/databases/securityAlertPolicies",
  "apiVersion": "2021-11-01",
  "name": "[concat(parameters('serverName'), '/', parameters('databaseName'), '/Default')]",
  "properties": {
    "state": "Enabled",
    "emailAccountAdmins": true,
    "emailAddresses": ["admin@contoso.com"],
    "disabledAlerts": ["Sql_Injection", "Access_Anomaly"],
    "retentionDays": 30,
    "storageAccountAccessKey": "...",
    "storageEndpoint": "https://stgaccount.blob.core.windows.net/"
  }
}
```