This article is contributed. See the original author and article here.

How to reduce the cost of running SQL Server on Azure

Azure offers a unique combination of powerful options that allow you to substantially reduce the cost of running SQL Server in the cloud. These include:

1. Using Azure Hybrid Benefit for SQL Server to bring your SQL Server license with Software Assurance to Azure SQL resources such as SQL managed instance, SQL database, SQL Server in Azure VMs, and others.


2. Deploying your disaster recovery SQL Server replica on Azure at no cost for the SQL Server license using the High availability and disaster recovery benefits for SQL Server.


3. Using SQL Server Developer Edition running in Azure Virtual machines at no cost for the SQL Server license for development or testing workloads, and leveraging Azure Dev/Test pricing to get discounts on VMs, dev tools and many other Azure service to support your ongoing development and testing.

Steps for Managing SQL Server license

The key question many customers ask is how I can get the maximum savings by leveraging Azure Hybrid Benefit while staying compliant with the existing number of SQL Server core licenses I have. To answer that question, you need to know several things.

First, you need to know how many SQL Server resources your company deployed to Azure that are not taking advantage of Azure Hybrid Benefit and are billed using the Pay-as-you-go prices (PAYG).

Second, you need to know how many on premises licenses with SA your company assigned to SQL Resources in Azure using Azure Hybrid Benefit.  

Finally, you also need to know how many SQL Server resources on Azure do not need a license at all. These include:

• SQL Server instances that are secondary passive HA or DR replicas on Azure and covered by the SA benefit


• SQL Server instances running Developer or Express editions


• SQL Server instances deployed to the Dev/Test subscriptions

Introducing a simple solution to help you manage SQL server license usage

Obtaining the above information involves querying different SQL resources, understanding which category they belong to, checking their size in vCores, and then calculating the total usage in each category. This task needs to be repeated to track the usage at the Azure account level and over time. To make sure you can easily obtain this information, we published a PowerShell script that automates all the above steps and provides you with this information today. The script and the detailed instructions can be found in the Azure Hybrid Benefits folder of Azure Data Sample Repository on GitHub .

The script will scan each subscription in the account and calculate the license usage for every resource that generally requires a SQL Server license to run. Those are:

• Azure SQL databases (vCore-based purchasing model only*)


• Azure SQL elastic pools (vCore-based purchasing model only*)


• Azure SQL managed instances


• Azure SQL instance pools


• Azure Data Factory SSIS integration runtimes


• SQL Servers in Azure virtual machines


• SQL Servers in Azure virtual machines hosted in Azure dedicated host

* The DTU-based resources are not eligible for Azure Hybrid Benefit or HADR benefit.

But, as we discussed earlier, in some cases the license is not required, and when it is required, you can use Azure Hybrid benefit, or leverage  PAYG license.

The script allows you to specify a single subscription to scan or multiple subscriptions. If not specified, it scans all the subscriptions in your account and produces a SQL Server license usage report for each subscription. The report will look like this:

By default, the script saves the report as a .CSV file but you can also specify a SQL database and the script will save the results in a table called Usage-per-subscription (it will be created if doesn’t exist).

These numbers are a snapshot of the SQL license usage in each subscription at the time of the script execution. That moment is represented by the Date and Time columns. But, these numbers often are very dynamic due to the new resource deployments, cancellations of test runs, spinning down developers’ VMs, scaling the resources up or down, etc. Therefore, a single snapshot like this is of limited value. The good news is that the script is designed for periodic snapshots.

Each new scan will add the results to the same file or table. This way you can easily visualize the license usage over time. For example, you can open the .CSV file in Excel and create a pivot chart that shows the consumption trends. If you store the results in a SQL database, you also can use Power Pivot or create a PowerBI view. The following is an example of a daily trend of the number of consumed vCores that are covered by Azure Hybrid Benefit and the number of consumed vCores that are billed at PAYG price.

NOTE: the script does not calculate the usage of Server/CAL licenses by the SQL VMs as that information is not yet exposed through the ARM API.

Running the script

To run the script, you can use a PowerShell CLI version 7.1 or higher on your local machine connected to Azure. Alternatively, you can use any Web browser and launch the Azure Cloud Shell. Could Shell is guaranteed to run the latest version. For detailed instructions of running the script, see the Azure Hybrid Benefits folder of Azure Data Sample Repository on GitHub.

Automating the process

The next logical question is what if I want to automate this process and eliminate the manual steps? You can do that by using an Azure Automation account with a PowerShell runbook containing a copy of this script. The Runbook tutorial will provide you with the details of how to create a PowerShell runbook. Note, because the script accesses the resources across multiple subscriptions, the runbook must be able to authenticate using the Run As account that was automatically created when you created your Automation account. For your convenience, the logic required for Runbooks is already part of the script and controlled by a UseInRunbooks command line parameter. You can learn more about this additional logic in the Add authentication section of the Runbook tutorial.

Staying compliant

When you analyze the license utilization results, you should engage your procurement and/or software asset management departments to confirm license availability before taking action to commit more licenses to Azure Hybrid Benefit. For example, if all your licenses are in use on-premises, you should not double utilize them on Azure unless you are in the process of migrating SQL server instances to Azure. In that case you have 180 days to use licenses in both places.

Next steps

Learn how to use the tool and generate SQL license usage data.

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.