Dynamics 365 Business Central on-premise and SQL on Azure VM: reducing storage costs

If you search on my blog, I’we written in the past a lot of articles with suggestions and best practices for using Dynamics 365 Business Central (and Dynamics NAV too) with SQL Server installed on an Azure Virtual Machine or also with Azure SQL Database.

Onmy personal recommendations, I’ve always emphasized the importance of using Premium Storage with SQL Server on a production environment. Please always avoid Standard storage, and in particular:

• For the data drive, only use premium P15 to P80 disks to ensure the availability of cache support
• For the log drive plan for capacity and test performance versus cost while evaluating the premium P10 to P80 disks.
  • If submillisecond storage latency is required, use Azure ultra disks for the transaction log.
  • For M-series virtual machine deployments consider Write Accelerator over using Azure ultra disks.
• Place tempdb on the local SSD  D: drive for most SQL Server workloads after choosing the optimal VM size.

Some days ago an IT manager contacted me saying that they have followed my recommendations on installing Dynamics 365 Business Central with SQL Server on an Azure VM, but they actually have an high storage cost and they would like to reduce costs of the SQL Server machine. They stopped the VM during non working hours and turned it on before the start of the working hour, but costs are high also with this trick.

You have always to remember that when an Azure VM is stopped you won’t be charged for the resource usage (CPU, RAM, Dynamic IP etc.), but however you will be charged for the Azure Storage account since the disk file image can’t be released.

So, what to do in this case if I need Premium disks for having an high throughput formy database but the costs are too high?

One of the trick that I’ve used in the past is reducing the tier of the disks from Premium to Standard storage when the VM is deallocated, and then increase it to Premium when the VM is turned on.

The Powershell scripts that stops the VM and reduces the tier or the disks (from Premium to Standard) is the following:

$ResourceGroupName = "YOUR_VM_RESOURCE_GROUP"

$vmnames = get-AzVM -ResourceGroupName $ResourceGroupName -status
foreach ($vmname in $vmnames) 
{
    $vms = ((Get-AzVM -ResourceGroupName $ResourceGroupName -VMName $vmname.name -Status).Statuses[1]).Code
    if ($vms -eq 'PowerState/running')
    {
        { 
            stop-AzVM -ResourceGroupName $ResourceGroupName -Name $vmname.name 
        }

        $vdisks = $vmname.StorageProfile.DataDisks

        foreach ($vdisk in $vdisks) {
        $d = Get-AzDisk -DiskName $vdisk.Name
            if ($d.sku.tier -eq "premium") {
                $storageType = 'Standard_LRS'   
                $d.Sku = [Microsoft.Azure.Management.Compute.Models.DiskSku]::new($storageType)
                $d | Update-AzDisk
            }
        }
    }
}

The script that does the opposite (moving the disks tier from Standard to Premium and turning on the VM) is the following:

$ResourceGroupName = "YOUR_VM_RESOURCE_GROUP"
$VM = "YOUR_VM_NAME"

$v=get-AzVM -ResourceGroupName $ResourceGroupName -VMName $VM 

$vms=((Get-AzVM -ResourceGroupName $ResourceGroupName -VMName $VM -Status).Statuses[1]).Code
 

$vdisks=$v.StorageProfile.DataDisks

foreach ($vdisk in $vdisks)
{
    $d=Get-AzDisk -DiskName $vdisk.Name
   
    if ($d.sku.tier -eq "standard")
    {
        $storageType = 'Premium_LRS'   
        $d.Sku = [Microsoft.Azure.Management.Compute.Models.DiskSku]::new($storageType)
        $d | Update-AzDisk
    }
}

if ($vms -ne 'PowerState/running')
{
    start-AzVM -ResourceGroupName $ResourceGroupName -Name $VM
}

With this trick, you can save a lot of money!

The script is available on my Github if needed.

As a final note, I want to mention also that Azure VM price depends also on the region where the VM is created, but don’t place an Azure VM in another Region with a lower price different that from your Azure Region. Remember that users located in a different region that need to move data between the resources will lose any potential cost savings because they have an additional cost for data transfer between those resources on the different regions (inbound data transfers are free and outbound data transfer price is instead based on the Billing Zones).