Apply Azure Hybrid Benefits at Scale for Azure SQL

[HEADING=1]Introduction[/HEADING]

In the world of cloud computing, cost optimization is a top priority. One way to achieve this in Microsoft Azure is by utilizing Azure Hybrid Benefits (AHB). This blog post will walk you through a PowerShell script that applies AHB to your Azure SQL Databases and SQL Managed Instances. AHB can be applied at database creation time and can be changed via the Azure Portal. However, there are use cases where many databases or instances may have been created without AHB as part of an at scale migration and there is a need to apply AHB to the entire environment.

[HEADING=1]What are Azure Hybrid Benefits?[/HEADING]

Azure Hybrid Benefit (AHB) allows you to exchange your existing licenses for discounted rates on Azure SQL Database and Azure SQL Managed Instance. You can save up to 30 percent or more on SQL Database and SQL Managed Instance by using your Software Assurance-enabled SQL Server licenses on Azure. With AHB, you only pay for the underlying Azure infrastructure costs by leveraging your existing SQL Server licenses. Keep in mind that AHB on Azure SQL Database are only applicable when using the provisioned compute tier of the vCore-based purchasing model. AHB does not apply to DTU-based purchasing models or the serverless compute tier.

[HEADING=1]Apply AHB at Scale[/HEADING]

At a high level, this Powershell script will allow a user to pass subscription, resource group, and service type information. Then it will loop through any databases or instances that are missing Azure Hybrid benefits and apply AHB to those resources.

Please ensure that before using this script to apply AHB that you or your organization has the appropriate SQL Licenses in place.

#login into azure
Connect-AzAccount

#prompt user to select their azure subscription
$subscription = Get-AzSubscription | Out-GridView -Title "Select an Azure Subscription" -PassThru

#set subscription context
Set-AzContext -Subscription $subscription.Id

#prompt the user for their resource group name using a GUI
$resourceGroup = Get-AzResourceGroup | Out-GridView -Title "Select a Resource Group" -PassThru

#prompt the user for the service type
$serviceTypes = ("Azure SQL Server", "Azure SQL Managed Instance")
$service = $serviceTypes | Out-GridView -Title "Select a Service" -PassThru

switch ($service) {
   'Azure SQL Server' {
       #run Azure Resource Graph query to find Azure SQL Server without azure hybrid benefit enabled in selected resource group
       $ahbSQLDatabases = Search-AzGraph -Query "resources | where type =~ 'microsoft.sql/servers/databases' 
       | where resourceGroup =~ '$($resourceGroup.ResourceGroupName)' 
       | where name != 'master'
       | where kind contains 'vcore'
       | where kind !contains 'serverless'
       | where not(properties.licenseType == 'BasePrice') 
       | project name, id"
           
           foreach ($db in $ahbSQLDatabases) {

                   #split the server name out the resource ID
                   $serverName = $db.id.Split("/")[8]

                   Write-Host "Applying AHB Benefits to" $db.name "on" $serverName
                   #set azure hybrid benefit to base price and show no output
                   Set-AzSqlDatabase -LicenseType BasePrice -ResourceGroupName $resourceGroup.ResourceGroupName -ServerName $serverName -DatabaseName $db.name > $null
           } 
       }
      
   'Azure SQL Managed Instance' {
       #run Azure Resource Graph query to find MIs without azure hybrid benefit enabled in selected resource group
       $ahbMIs = Search-AzGraph -Query "resources | where type =~ 'Microsoft.Sql/managedInstances' 
       | where resourceGroup =~ '$($resourceGroup.ResourceGroupName)' 
       | where not(properties.licenseType == 'BasePrice') 
       | project name, id"

       foreach ($instance in $ahbMIs) {
           #display the azure sql managed instance name
           Write-Host "Applying AHB Benefits to instance" $instance.name
           
           #apply azure hybrid benefit to azure sql managed instance
           Set-AzSqlInstance -LicenseType BasePrice -ResourceGroupName $resourceGroup.ResourceGroupName -InstanceName $instance.name > $null
       }
   }
   default {
       Write-Host "Invalid selection"
   }
}

[HEADING=1] [/HEADING]

[HEADING=1]Feedback and suggestions [/HEADING]

If you have feedback or suggestions for improving this data migration asset, please contact the Azure Databases SQL Customer Success Engineering Team. Thanks for your support!

Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.

Continue reading...