Post navigation

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 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.

10 Comments

Great article, but why do you recommend disabling Autogrow? I agree you should always try to expand the files outside office hours, but I do not see the problem in having Autogrow enabled as a backup plan.

Because Autogrow is an overhead of resources on Azure. I prefer disable it and pre-grow the file using the Size switch, but obviously this is not a mantra 🙂 Obviously, if it’s enabled, check that your database does not perform an autogrow every day. In these cases, set the grow of your database manually to an appropriate size.

My recommendation for NAV 2018 with 1 to 6 users: Create an Azure VM with SQL Server and NAV Service Tier on the same machine, use VM with size like DS2 v2 or DS3 v2 .(Premium Storage).
Azure SQL is an option for low users but costs is quite predictable (it depends a lot on the transactions).