Microsoft Dynamics NAV on Azure: SQL Server on Azure VM best practices

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.

SQL-Server-on-Azure-Virtual-Machine

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.

12 Comments

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

    Like

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

      Like

      1. We suggest to have at 1 as a default, but please experiment with higher settings (or zero), as mileage vary (customer data distributions and usage patterns are different)

        Like

    1. Yes E-series are another option but we don’t have observer so much performance gains on using them respect to DS, and the costs are higher. But I agree, they’re in the list 😉

      Like

  2. If price is an issue, what is minimum server to start on for a 1 to 6 users 2018 installation, and what is the cost?
    Is Azure SQL a recommended option, and how will that affect price?
    Thanks.

    Like

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

      Like

  3. How about this kind of setup with users connecting from different regions across the globe? US, EU, Asia…. Is an Azure solution sufficient enough for this? And I do mean users connecting with the windows client… not web client.

    Like

    1. Maybe I’ve just answered to you in the MS Forum? 🙂 I’ve suggested you a configuration (N service tier). If you use Azure VMs, you need to carefully check the latency and use a VNET.
      I report here my answer in the forum:
      In this scenario (single database, N companies in different locations), using N separated service tiers for the different companies is the best way to do (you can decouple users, tasks on a company don’t disturb tasks on another company and so on). This is absolutely the recommended way to do.

      Regarding service tier location, it depends 🙂 My suggestion is to leave service tier as close as possible to your database. So, you could have a scenario where you have NAV database (SQL Server) on a server A and Service Tier 1,2,3…N on separated servers on the same network as the database server. Windows Client from different companies will connect to the respective service tier, maybe using a VPN. This is the scenario with best performances. Obviously, you need to check the network latency of the different companies.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.