When deploying a solution architecture with Microsoft Dynamics NAV as the ERP of your choice, you already know that the database will be a core feature for your entire architecture.
NAV backend is based on Microsoft SQL Server and when deploying NAV you’ve the choice to (we’re not considering here the SaaS proposition like Dynamics 365):
- Install SQL Server on-premise
- Install SQL server on an Azure VM
- Use Azure SQL Database
With the option 1 and 2, you’ve the responsibilities to manage the infrastructure (scalability, updates, patches and so on) and the database software too (installation, setup and licensing). Obviously, with option 1 you’ve also to manage the hardware, while option 2 is a complete IaaS.
With Azure SQL Database, you can use the “relational database as a service” feature provided by Azure (PaaS). Here, you have a SQL Server database fully managed with scaling capabilities and many other features and options natively offered by the platform like geo-replication or geo-redundancy.
The curious thing that many people don’t know is that Azure SQL Database IS NOT the same as SQL Server. Azure SQL has a different engine totally built and optimized for the cloud and hosted using Azure Service Fabric for the underlying managed infrastructure (are you familiar with concepts like microservices?).
With Azure SQL Database for your NAV deployment in the cloud:
- You don’t have to think to the underlying infrastructure of your database.
- You don’t have to license SQL Server separately (!!)
- You have a complete scalability.
- You have automatic index tuning.
Azure SQL Database is very cost-saving option for a classic NAV installation. As an example, I’ve played a bit with the Azure Pricing Calculator in order to define a configuration for the typical NAV installation I see every day on customers around the world:
- 20-30 users
- One single database instance for NAV
- No replication or load balancing
This is the price I can estimate for a configuration like this:
Azure VM for NAV Service Tier:
Azure Managed Disk for a persistent data disk (optional but maybe useful for handling some NAV tasks like file exchange and so on):
Azure SQL Database instance:
Standard Tier offers 99.9% SLA, 35 days backup retention, 1TB database maximum.
The total estimated monthly cost for a server active h 24 x 24 every day is:
Obviously, this is a starting point and from here we can plan more robust architectures with (for example) load balancing between VMs, geo-replication and so on, and all of these architectures will always be cheapest that organizing your on-premise IT infrastructure in order to satisfy all the same features.
For about 300$ you have a cloud infrastructure for your NAV that has 99.9% SLA and it’s completely scalable and fully managed.
Now I hope that Azure SQL will be more considered when planning for a new NAV installation on when transitioning an old installation from on-premise to a cloud environment.
Stefano Demiliani 
Reblogged this on and commented:
Good post here for indicative costs of hosting NAV on Azure platform…
LikeLike
Hi Demiliani,
We are proposing that scenario for our customers. With 3 Azure SQL databases for:
– Production
– Test
– Development
In this environment, we concluded that the Azure VM for the NAV Service Tier should have more than 7 GB RAM, at least 14GB. We use at least 1,5 GB per NAV Application Service (prod, test, dev) that makes 4.5 GB and some for the operating system. With 7 GB the memory is always near 100%.
Do you see any other way to do it?
Regards
LikeLike
An Azure VM D2 v2 is a minimum for efficiently handling the NAV Service tier and reducing cost. For more advanced scenarios (and more service tiers on the same machine) Azure VM D3 v2 with 14 GB is better.
LikeLike
I wonder about that “it is always cheaper”.
Building a server platform for NAV I’d be using certainly higher ‘RAM / CPU’ ratio. RAM is cheap these days, I would go for 64 or 128GB RAM, even more if hardware platform allows, but I’d stick with only 4 cores (it doesn’t make sense to go lower dues to SQL Server Licensing structure, 4 cores are the minimum they are charging for).
128GB server grade RAM is about ~$1400 (one-off cost)
To get close to 64GB of RAM we’re talking D13 v2, 56GB of RAM / 8 cores, and this is $630 per month. D14 v2 coming with 112GB of RAM and 16 cores is $1260 per month. for the virtual server alone.
Going with more RAM in my own server costs me just the RAM chips, while in Azure I have to pay for cores, which I don’t need.
How many companies in real life scenarios run production SQL Server on a on-premise hardware with 8GB of RAM only??
Subscription means paying xx per month forever, each and every month. If I want to play with something for a short period of time, then yes, it is obviously cheaper. But in a long period of time the constant monthly bill will certainly get to the point where the one-off purchase cost will be lower than the subscription.
LikeLike
Don’t pair exactly an Azure VM with a machine you’ve on premise. A D3v2 (or DS3v2) is enough to handle SQL Server for 30/40 NAV Users. DS4v2 has double power and costs double. The most important here for SQL is USE STORAGE PREMIUM.
I can agree that in the long time on-premise hardware cost will be lower, but in the cloud you pay also for the managing of all the entire infrastructure and SLA. It’s a choice 🙂
LikeLike
Thanks Stefano.
How about installation with fairly low user count but loads of data. Would D3 or DS3 suffice if you had a 50k+ active customers (invoices send every month) and 10m+ Customer ledger entries?
LikeLike
DS3v2 can handle a NAV installation with the described volumes. The important thing is to use Premium Storage for SQL data files (high performances).
Then, there are also other tips you could think, like:
1) Enable instant file initialization for data files. This should be done by granting “Perform Volume Maintenance Tasks” permission to service account for SQL Server. This is very useful when we try to restore a database which has huge database files.
2) Enable database page compression. This would help in reducing IO by reading more data in less IO.
3) Disable autogrow and autoshrink for the database. This means that a DBA needs to plan the database size in advance and grow it beforehand rather than letting SQL grow the file. In the same way, autoshrink is not advisable for database. This is true even for on-premises database server also.
4) Use Lock Pages in Memory. This setting would help in locking the memory and avoid paging-out the memory to pagefile.
5) C drive should not be used to store any database or SQL Errorlog or default trace file. Once SQL is installed, we might need to move all of them off the C drive to a drive which is part of Data disk.
LikeLike
Thanks again, Stefano.
I thought that Lock Pages in Memory was needed when SQL Server runs on 32bit OS using AWE. Does the DS3v2 run 32bit Windows OS?
LikeLike
No it’s 64 bit.
LikeLike
Hi,
Thanks for your post.
Could you please share your opinion about SQL Azure and NAV limitations ? No one in NAV world writes anything about limitations 🙂
For example security: The same user for several tasks: Accessing DB using Developer environment, NST services connections to Azure SQL. Till this moment I cannot find a way to create secondary user with “Dbowner” on Azure SQL. Did you find a way to workaround that ?
What about database conversion process in case of large databases. It is not so efficient to download the db to “full” SQL and upload converted database to Azure SQL.
thanks in advance!
LikeLike
With Azure SQL, you can create different users with different privileges, dbowner or not. You’ve to use SSMS for that. For NAV and big installations, I think that SQL on an Azure VM performs better than Azure SQL. DB Conversion: do you mean for uploading a NAV dB to Azure SQL?
LikeLike
Have you ever successfully create user with sufficient privileges on SQL Azure to work with NAV ? I tried some time ago with no success. Based on your experience I will try again. Do you have any procedure ?
LikeLike
I’ve used SSMS and T-SQL. I’ll share it when I’ll return to home 🙂
LikeLike
Ok, thanks in advance. I tried to do it again, but it doesn’t matter what SQL roles I assigned, user could not access the database. User can access database through SSMS but list of available database in NAV client is empty.
LikeLike