When talking about Microsoft Dynamics NAV installed on Azure, we have mainly two choices: use an Azure VM with SQL Server installed or use Azure SQL.
In the Azure-based NAV deployments I see every day, it seems that using SQL Server on an Azure VM is the choice where everyone are going or prefer. However, too often I see that the installation is done like an on-premise SQL or without considering some important best practices and SQL performances are poor.
Here are few tips that I would like to share in order to improve your SQL Server performances when it runs on an Azure virtual machine. They come from my personal experience, so feel free to discuss them or add something new based on your experience 🙂
Recommendation 1: The VM machine that fits the best with SQL Server on Azure for me are D or F series (forget the A series). I recommend to go for the DS or FS series because that machines use Premium storage (we’ll talk about that in next recommendation). In my experience, DS2v2 is ok for starting with SQL Server Standard Edition,while DS3v2 is the starting point for SQL Server Enterprise Edition. I don’t suggest to create a SQL Server VM by using the standard Azure template for SQL Server.
Recommendation 2: use Premium storage!! In my opinion, this is the most important aspect regarding SQL Server performance on Azure. In Azure you can have two kind of storages, Standard and Premium. Standard storage is based on magnetic drives, it has low cost but also poor performances for applications that frequently needs to access data. Premium storage is based on SSD drives and it has the best performances (lowest latency). When installing SQL Server on Azure, please avoid Standard storage (use VMs that supports premium storage like the DS of FS series). The storage account and the SQL Server VM must be in the same region.
My recommendation is to add to your VM at least 2 premium disks, 1 for log files and 1 for data files and TempDB.
Recommendation 3: never store any database or log files on the C drive of your VM but for these files use a premium storage disk mounted in your VM. Place TempDB on data disks instead of the temporary disk of your VM. Leave all the data disks attached to a single virtual machine in the same storage account (recovery operations will be more performant).
Recommendation 4: disable autogrow and autoshrink for the database.
Recommendation 5: enable database page compression. This helps on reducing the I/O operations when reading data.
Recommendation 6: When creating a data disk, NTFS volumes are created with a default cluster size of 4 KB. Changing the default cluster size to 64 KB during volume creation for both single disk and multiple disks (storage spaces) volumes can help to increase performances, at least for deletion operations. Enable read caching on the disks hosting the data files and TempDB and disable caching on disks hosting the log file.
With at least these recommendations applied, I think that your SQL Server on Azure VM (and your NAV database) could satisfy your performance requirements.