> ## Documentation Index
> Fetch the complete documentation index at: https://cloudanix.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# 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

<Tabs>
  <Tab title="Remediation">
    ### Remediation

    <AccordionGroup>
      <Accordion title="Using Console" defaultOpen="true">
        To remediate the misconfiguration "Enable Automatic Tuning for SQL Database Servers" in Azure using the Azure console, follow these steps:

        1. Log in to the Azure portal ([https://portal.azure.com/](https://portal.azure.com/)).
        2. Navigate to the SQL databases page.
        3. Select the SQL database server that needs to be remediated.
        4. Click on the "Automatic tuning" option in the left-hand menu.
        5. Click on the "Configure" button to enable automatic tuning for the server.
        6. In the "Automatic tuning" pane, select the "Basic" option to enable automatic tuning for all databases on the server.
        7. Click on the "Apply" button to save the changes.

        Once you have completed these steps, automatic tuning will be enabled for the selected SQL database server, which will help optimize query performance and improve overall database performance.

        #
      </Accordion>

      <Accordion title="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.
      </Accordion>

      <Accordion title="Using Python">
        To remediate the misconfiguration of enabling automatic tuning for SQL database servers in Azure using Python, you can follow the below steps:

        1. Install the Azure SDK for Python using the following command:

           ```
           pip install azure-mgmt-sql
           ```

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

           ```python theme={null}
           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)
           ```

        3. Get the resource group and server name where the SQL database is located using the following code:

           ```python theme={null}
           resource_group_name = 'your_resource_group_name'
           server_name = 'your_server_name'
           ```

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

           ```python theme={null}
           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.

        5. Verify the automatic tuning is enabled for the SQL database server using the following code:

           ```python theme={null}
           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.
      </Accordion>
    </AccordionGroup>
  </Tab>
</Tabs>