Blog

Early versions of SQL Server option for Microsoft Dynamics NAV (then called Navision) didn’t scale up as high as one would expect. Although SQL Server itself could scale up to thousands and tens of thousands of concurrent users, running a production environment of an ERP system is way different from running generic lab tests.

SQL Server 2000 brought many improvements, and finally outperformed Navision Database Server at any level of user concurrency. SQL was gaining momentum, and became the platform of choice for new implementations of Navision. When SQL Server 2005 came out, and when support for it was included in Microsoft Dynamics NAV 4.0, it could outperform the native database platform as much as 35%.

What are the main reasons why SQL Server generally does better than the native platform?

There are some significant differences in hardware support. While SQL Server can support many processors, native database server can make use of only one. This is bad, and in long term this limitation will be its demise. As processor speeds are approaching their limits imposed by the physics and Mother Nature, the only way to make processors do more work is to integrate multiple cores onto a single chip. In the market for server hardware, finding a single-core processor becomes an increasingly hopeless chore. Quad-core processors are almost the norm, so from this perspective alone, any comparison of the server performance itself would be pretty darn unfair.

Another reason is RAM. While amount of RAM on SQL Server is limited only by the choice of 32 or 64-bit architecture, and operating system edition, with native Database Server, situation is far from ideal. Even if you can stick as much memory as you fancy into the server box, Microsoft Dynamics NAV Database Server can address only 1 GB of it.

While it is true that operating system will still be able to take advantage of extra memory or multiple processors to improve overall performance, any performance improvement due to the operating system having more room to breathe won’t contribute but a tiny fraction to the overall performance of a machine running Microsoft Dynamics NAV Database Server.

However, on comparable hardware, especially with older versions, Microsoft Dynamics NAV Database Server can do same as, or even better than SQL Server.

There are some important differences between the way how these two handle the underlying databases. Native Database Server employs a concept called ISAM, or Indexed Sequential Access Method, which is based on sequential storage of fixed-length rows. This allows for very fast sequential reads, and native Database Server is optimized to leverage on this. Sequential reads are all over the architecture of Microsoft Dynamics NAV, and when it comes to sequential data reading, it can easily outperform SQL Server, which is not optimized for this kind of access. To simulate sequential access SQL Server needs cursors, which are a resource expensive alternative, and generally considered bad in SQL Server community. NAV Database Server also doesn’t do much processing, it is essentially used to marshall requests made by clients, and all of the processing is done on the client side. SQL Server, which could do server-side processing, does none, because in order to leverage on its server-side processing capabilities, Microsoft Dynamics NAV would need to be rearchitected completely, which would render it incompatible with native Database Server. As long as there is a significant install base of native Database Server, it can be expected that this trade-off between performance and compatibility will be maintained for a few more years.

Besides fast sequential access, ISAM is good for another thing which makes for a good native Database Server performance: lightweight indexes. Not that SQL Server indexes are especially heavyweight compared to those of NAV Database Server, in fact they are not, but what NAV Database Server can do, and SQL Server can’t, is fusing numeric data directly into the very structure of indexes. This technology is called SIFT, or Sum Index Flow Technology, and allows for calculating of totals instantly, with very little resource cost. This doesn’t adversely impact the index performance, providing at the same time significant performance benefits. Again, SQL Server can’t do this natively, so it has to simulate this functionality with extra tables, which simply can’t achieve the same performance scale native Database Server does. Dave has recently written a good post about SIFT on his blog.

But the case for NAV Database Server pretty much ends here. The whole C/SIDE development environment, C/AL programming language and the underlying concepts of Microsoft Dynamics NAV are constructed in the way to leverage on the architectural principles of the native database functionality, which is something that SQL Server can’t do natively, and can only simulate. But the fact is that native Database Server pushes the ISAM concept to its limits, and in reality cannot perform any faster than it already does, while SQL Server does exceptionally well in simulating a concept which is totally the opposite to the way true relational database management systems work, and if relieved of those ties, SQL Server option for Microsoft Dynamics NAV could grow significantly in the terms of both performance and scalability.

Another fact in favor of SQL Server is that there is really not much one can do about native Database Server when it starts performing poorly, because there are no management tools to let one do so. At the same time, SQL Server comes with tons of features, allowing fine tuning of the system in many areas, which can bring SQL Server far above the performance levels of native Database Server.

Last, but not least, while growth of a SQL Server database is limited only by the physical capacity of the storage subsystem, native database can grow only up to 128 GB (in current version, in older versions even less). This basically means that sooner or later, anyone running the native Database Server and growing rapidly, is about to hit the wall.

Besides performance and scalability, there are almost no functional differences between the two versions, which can make the decision making process an easy one. However, there is a significant feature which can really make the whole case for SQL Server, and it is called security filters. For SQL Server, it is possible to filter data by roles. This means that the system can be configured to limit access not only by table, but also by row, resulting in much better granularity.

So, are there any reasons whatsoever why anyone would want to run their implementation of Microsoft Dynamics NAV in the native environment? I can think of these:

1. Company size: If your company is a small one, and you have only a small number of users in the system (up to about 20), native Database Server will do quite well. If you also don’t expect to grow your head-count at a significant rate, you won’t be needing a highly scalable system.
2. Basic maintenance needs: If you don’t need any advanced management and maintenance capabilities (such as scheduled backups, log shipping, data integration with other system), you are a good candidate for native Database Server.
3. Low budget: If your budget is tight, choice of native Database Server can save you a few dollars. SQL Server requires purchase of licenses, which can be a costly sport, especially when user count is big, or server has many processors. SQL Server licenses may be a considerable one-off cost plus maintenance fee in case you want to get decent support for it. This puts extra pressure on total cost of ownership, and may be totally unnecessary, if there are no real needs for a SQL Server, and if native Database Server can offer the same performance and scalability (see 1 and 2 above).
4. Low transaction count: If you are going to post only a handful of documents per day, you are probably not going to exceed the physical native database size limit. You should try to calculate how much information your business will really generate per year, add to it your estimated growth, and you can easily see whether the system will support you long enough. If you generate only 5 GB of information and grow 10% per year, native Database Server will support you for good 13 years before 128 GB limit really becomes an issue. If you generate 30 GB per year, and expect a 25% year-over-year growth, it won’t be long before you start looking for more real estate.
5. Simple security needs: If you don’t need to have your data secured on a row-by-row basis (for example, limiting certain users from seeing certain customers, or certain items, etc.), then SQL Server might not be necessary, however this is the least of criteria which should influence your decision, at least if you are about to rule-out the need for SQL Server.

If you fit in all of the categories above, go for native Database Server, because you won’t get any benefits from having SQL Server manage your database. However, if you don’t see yourself in one or more of the categories above, you should seriously consider SQL Server, especially if you are expect a rapid growth or need a highly scalable system. Never trade scalability off for a lower price, because costs of unproductive time can be much higher than SQL Server licence costs, and it can hurt a lot in long run.

I hope to write a post about SQL Server optimization techniques which can really make a good case for choice of SQL Server database option, but also break a fairly common myth that Microsoft Dynamics NAV is not a good system for “big” companies.

Tomorrow I plan to publish something potentially big, and important, and having big impact on anyone doing a lot of development work on Microsoft Dynamics NAV, especially in team environment, which is also going to give one more reason to run SQL Server, so come back later. Bye!