Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
PowerShell function to configure Azure SQL Autotuning on an Azure SQL Server or database
#Requires -Modules 'AzureRM.Profile'
Configure Azure SQL Autotuning on an Azure SQL server
or database.
This function will retrieve a current access token from
the Azure RM PowerShell context and use it to make a direct
request to the Azure management portal endpoint for the
SQL Server or database. It will configure the Autotuning
parameters for the server.
Requires AzureRM PowerShell Modules 5.1.1 or above*.
* May work on lower versions but untested
.PARAMETER SubscriptionId
The Azure subscription Id of the subscription containing
SQL Server or database.
.PARAMETER ResourceGroupName
The name of the resource grou containing SQL Server or
The name of the Azure SQL Server to set the autotuning
options on.
.PARAMETER DatabaseName
The name of the Azure SQL Database to set the autotuning
options on.
This defines where the settings for the Autotuning are
obtained from.
Inherit is only valid if the DatabaseName is specified.
.PARAMETER CreateIndex
Enable autotuning for creating an index.
Enable autotuning for dropping an index.
.PARAMETER ForceLastGoodPlan
Enable autotuning for forcing last good plan.
param (
[Parameter(Mandatory = $true)]
[Parameter(Mandatory = $true)]
[Parameter(Mandatory = $true)]
[ValidateSet('Auto', 'Custom', 'Inherit')]
$Mode = 'Auto',
[ValidateSet('On', 'Off', 'Default')]
$CreateIndex = 'Default',
[ValidateSet('On', 'Off', 'Default')]
$DropIndex = 'Default',
[ValidateSet('On', 'Off', 'Default')]
$ForceLastGoodPlan = 'Default'
# Get an access token from the Auzre RM PowerShell token cache for accessing the Azure Management Portal
$context = Get-AzureRmContext
$cache = $context.TokenCache
if (-not $cache)
# Use an older method of accessing the Token Cache (for old versions of AzureRM.Profile)
$cache = [Microsoft.IdentityModel.Clients.ActiveDirectory.TokenCache]::DefaultShared
$cacheItems = $cache.ReadItems()
$cacheItem = $cacheItems |
Where-Object -FilterScript { $_.TenantId -eq $context.Tenant.TenantId } |
Select-Object -First 1
if (-not $cacheItem)
Throw ('A current access token could not be found for the tenant Id {0}.' -f $context.Tenant.TenantId)
$accessToken = $cacheItem.AccessToken
# Generate the Body of the request
$body = @{
properties = @{
desiredState = $Mode
options = @{
createIndex = @{
desiredState = $CreateIndex
dropIndex = @{
desiredState = $DropIndex
forceLastGoodPlan = @{
desiredState = $ForceLastGoodPlan
# Generate the URI to the endpoint
$uri = ('{0}/resourceGroups/{1}/providers/Microsoft.Sql/servers/{2}' -f $SubscriptionId, $ResourceGroupName, $ServerName )
if ($PSBoundParameters.ContainsKey('DatabaseName'))
$uri = ('{0}/databases/{1}' -f $uri, $DatabaseName)
if ($Mode -eq 'Inherit')
Throw 'Inherit mode is only valid for a SQL database. Either use a different not or specify a database name.'
$uri = ('{0}/automaticTuning/current?api-version=2017-03-01-preview' -f $uri)
$bodyText = ConvertTo-Json -InputObject $body -Depth 10
$headers = @{
'Authorization' = ('Bearer {0}' -f $accessToken)
'Cache-Control' = 'no-cache'
$invokeRestMethodParameters = @{
Uri = $Uri
Method = 'PATCH'
Headers = $headers
ContentType = 'application/json'
Body = $bodyText
return Invoke-RestMethod @invokeRestMethodParameters
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment