Addressing recommendations in Azure Advisor

Today we are going to look at the Azure Advisor to see where we can improve our implementations. This will help us ensure we are more secure, save money, maximize performances, and ensure we have best practices for high availability. Going forward, we will try to pay more attention to the recommendations as we deploy new resources. We won’t dive into all of the recommendations, but we will get a taste as we resolve most of the recommendations today

Using Azure Advisor to optimize our SQL Database and storage

We start by reviewing the Azure Advisor recommendations, reaching these by browsing to our Azure Portal and clicking on “Advisor” on the left hand menu. On the overview page that displays, you can see 12 recommendations, impacting 33 resources, arranged by four categories. These recommendations include a variety of 33 issues sorted into four buckets for all of our resources, but we will start by diving into the cost recommendation category first.

Cost advisor

The cost recommendation has detected that we have selected a larger database than we need. We are using the “S1”, or “Standard” edition of SQL Server with 10DTU’s, and 250 GB of disk space. This costs $14.99US a month per database.

When we dive into the details, we can actually see that our database is just a couple hundred MB and we can downgrade to a “Basic” edition database with 1GB of disk space. It’s important to double check the features before we downgrade, as sometimes the “Basic” edition has less features than the standard or premium editions, but in this case, we aren’t losing any functionality, and the “Basic” edition is $4.99US a month, saving us nearly $30 a month (as we have Dev, QA and production databases).

As we are using infrastructure as code, we load up our project in Visual Studio project, create a branch, and get to work updating our database to the Basic edition. Initially, this looked to be as easy as adding a new entry for “Basic”, but as we dived into the details, we discovered there was a newer recommended method of defining the database edition.

Using this documentation, we set the new “apiVersion” to “2017-10-01-preview”, and setup the new sku area with the new values we have defined in the parameters.

We commit our code and push it and note that after the deployment, that our dev and QA databases are still being set to the database SKU of “S1”. After further investigation, we discover that in our ImportBacPac PowerShell script, we specify the database SKU as part of the import, so we correct this to import the database as a “Basic” type, adding these items as parameters to the script and specifying the databases size as part of the deployment.

Security advisor

Next we move to the details of the security advisor, there are 26 ‘unhealthy’ items. As we mentioned earlier, we are just going to focus on the Azure Storage and SQL Server items today, which we’ve highlighted red.

Resolving “Provision an Azure AD Administrator for SQL server”

To resolve the “Provision an Azure AD Administrator for SQL server” item, we need to browse to the SQL Server and add an AD administrator. First we learn how to complete this task in the portal – browsing to the SQL server and adding the administrator.

Then we export the resource group. In the resulting ARM template, we can compare what we have to add to use this setting. With that resource, we can then see the change and easily find the right section of the documentation for the ARM template.

We are able to code up our infrastructure as code and resolve the issue, adding the resource below to our ARM template. We deploy it to our Dev environment a few times to ensure that it resolves the issue in the security advisor – but this does take a lot of trial and error. It can take some time before the advisor will pick up the change.

Resolving “Require secure transfer to storage account”

To resolve the “Require secure transfer to storage account” item, need to configure the Azure Storage account to only allow HTTPS. Again, once we’ve made the recommended change to our “Dev” environment and exported the template, we are able to discover the correct documentation to correctly configure our storage account, and update the Azure Storage template below:

Resolving “Enable auditing on SQL Server”

Finally, to resolve the “Enable auditing on SQL Server” item, we need to browse to the SQL server and configure logging to our Azure Storage account.

The exported ARM template here is complicated, it uses an old API version and requires a template for each database server and each database. Fortunately, there is an updated API version that rolls this together into one resource – this template will apply to the server and each database will inherit these settings. Here in the properties, we enable the auditing, specify the storage blob endpoint, retrieve the storage account access key, set the retention to 180 days, set the storage subscription id, and finally, set the actions and groups we want to audit, using the default settings to audit database successful and failed database authentication, and to audit all queries on our database.

SQL Auto tuning

When working through one of our previous sprints, we noticed that occasionally, one of our integration tests was taking one and a half minutes to run. The solution is simple, we needed an index, but we also knew there is a feature built into Azure SQL Server to automatically handle this. By browsing to our SQL server, and selecting the “Automatic tuning” menu item, we can enable three settings to allow our database to automatically force query plans, create indexes and drop indexes. These settings aren’t for everyone, eventually large databases with high volumes need a dedicated database administrator to maintain, but this is perfect for a new and relatively small database.

As we want to codify everything and turn it into infrastructure as code, below is the ARM template for our advisors. It’s fairly straight forward, each of the three sections has the advisor type and enables the advisor to run. We set the advisor settings on the server, and the databases will inherit the settings. It’s also possible to override settings on specific databases, but we don’t need that today.

In our database, we can now browse to the “Performance recommendations”, and see our recommendations – we don’t have any today, but we will continue to monitor this.

Wrapup

Today we addressed most of the recommendations, and added SQL auto tuning. The result of these recommendations have increased our security, reduced our cost, and increased database performance. Today we resolved 6 recommendations, affecting 12 resources .

References

• SQL Optimizer: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning-enable
• Service Advisors documentation: https://docs.microsoft.com/en-us/rest/api/sql/serveradvisors/get#definitions
• Enabled SQL auto tuning with ARM templates: https://stackoverflow.com/questions/46497300/enabling-azure-sql-database-automatic-tuning-via-arm
• Enabled SQL auto tuning with ARM templates:https://stackoverflow.com/questions/54031421/azure-arm-template-sql-server-auto-tuning-deployment-error
• Setting Azure Admin to sql server: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure
• ARM templates documentation – Storage accounts: https://docs.microsoft.com/en-us/azure/templates/microsoft.storage/2018-11-01/storageaccounts
• ARM templates documentation- Active Directory administrators:
  https://docs.microsoft.com/en-us/azure/templates/microsoft.sql/2014-04-01/servers/administrators