Enable Automatic Tuning For SQL Database Servers

More Info:

Enable automatic tuning for Microsoft Azure SQL servers in order to monitor database queries and improve database workload performance. Automatic Tuning is a built-in intelligence feature that automatically tunes your Azure SQL databases to optimize their performance.

Risk Level

Medium

Address

Security

Compliance Standards

HITRUST, SOC2

Triage and Remediation

Remediation

Using Console

Using CLI

To remediate the misconfiguration “Enable Automatic Tuning For SQL Database Servers” for AZURE using AZURE CLI, you can follow the below steps:Step 1: Open the Azure CLI in your system or use the Azure Cloud Shell.Step 2: Run the following command to enable automatic tuning for a specific database server:

az sql server configuration set --name automaticTuning --resource-group <resource_group_name> --server <server_name> --value Enabled

Note: Replace <resource_group_name> with the name of the resource group where your database server is located and <server_name> with the name of your database server.Step 3: If you want to enable automatic tuning for all the database servers in your subscription, run the following command:

az sql server configuration set --name automaticTuning --value Enabled --subscription <subscription_id>

Note: Replace <subscription_id> with the ID of your subscription.Step 4: After running the above command, automatic tuning will be enabled for your SQL database servers, and Azure will automatically tune your database to improve its performance.That’s it! You have successfully remediated the misconfiguration “Enable Automatic Tuning For SQL Database Servers” for AZURE using AZURE CLI.

Using Python

To remediate the misconfiguration of enabling automatic tuning for SQL database servers in Azure using Python, you can follow the below steps:

Install the Azure SDK for Python using the following command:
```
pip install azure-mgmt-sql
```

Authenticate and create a client object to interact with the Azure SQL Database using the following code:

from azure.common.credentials import ServicePrincipalCredentials
from azure.mgmt.sql import SqlManagementClient

subscription_id = 'your_subscription_id'
credentials = ServicePrincipalCredentials(
    client_id='your_client_id',
    secret='your_client_secret',
    tenant='your_tenant_id'
)

sql_client = SqlManagementClient(credentials, subscription_id)

Get the resource group and server name where the SQL database is located using the following code:
```
resource_group_name = 'your_resource_group_name'
server_name = 'your_server_name'
```

Enable automatic tuning for the SQL database server using the following code:

from azure.mgmt.sql.models import ServerAutomaticTuningProperties

sql_client.servers.create_or_update(
    resource_group_name=resource_group_name,
    server_name=server_name,
    parameters={
        'properties': {
            'automatic_tuning': {
                'state': 'Enabled',
                'options': {
                    'createIndex': 'Auto',
                    'dropIndex': 'Auto',
                    'forceLastGoodPlan': 'Enabled'
                }
            }
        }
    }
)

In the above code, we are setting the state of automatic tuning to ‘Enabled’ and also setting the options for createIndex, dropIndex, and forceLastGoodPlan to ‘Auto’, ‘Auto’, and ‘Enabled’ respectively.

Verify the automatic tuning is enabled for the SQL database server using the following code:
```
server = sql_client.servers.get(resource_group_name, server_name)
print(server.properties.automatic_tuning.state)
```
This will print the current state of automatic tuning for the SQL database server.

By following the above steps, you can remediate the misconfiguration of enabling automatic tuning for SQL database servers in Azure using Python.

Azure Introduction

Azure Pricing

Azure Threats

Azure Misconfigurations

Enable Automatic Tuning For SQL Database Servers

More Info:

Risk Level

Address

Compliance Standards

Triage and Remediation

Remediation

Azure Introduction

Azure Pricing

Azure Threats

Azure Misconfigurations

​More Info:

​Risk Level

​Address

​Compliance Standards

​Triage and Remediation

​Remediation

More Info:

Risk Level

Address

Compliance Standards

Triage and Remediation

Remediation