To SQL or not to SQL ?

Hi everyone, Not sure if this is better asked here or in the Developers forum, but here goes. We’ve been using Navision for over 5 years and are on version 3.60. We have also gone through mutliple license upgrades as our database size keeps expanding. Now we are at the point were we are considering if we should just go the SQL route for a backend database. We are already a SQL database shop (and Sybase and Oracle) for our other applications and systems, so we have inhouse DB experience, as well as Navision developer license and experience. I’ve create a test SQL database and restored the Cronus backup database and connected to it with the Navision client. It seems to run smoothly and pretty quick. Are there any general concerns with switching from Navision native file database to SQL database? Is performance generally acceptable, or better? Our current license does not let me log onto SQL for anything other than the Cronus test company, so I cannot run some testing against a backup copy of our actual company database. Does the SQL license have size limiation like our current file based license? Is it typically cost effective to switch to a Navision SQL license? We are at about 70% of our current license size and therefore will have to compare costing of getting a larger file based license to a SQL license. Appreciate any comments and suggestions. Cheers, Eric


We are at about 70% of our current license size and therefore will have to compare costing of getting a larger file based license to a SQL license.
Originally posted by ECarmody - 2005 Nov 10 : 18:30:11

Aren’t these database-size-limit-upgrades free?

I don’t know for sure if it is free now, but it might be that the initial maximum database size is a lot bigger. I would get in touch with your solution center and see if you would even have to pay for that. As far as the cost involved in Navision on SQL, that also depends on your SQL license. Depending on your Navision database size you’re looking at possibly extra hardware and things like that. It really depends on your whole setup what your choice should be. Have you discussed this route with your solution center?

Since Navision 2.5, with the introduction of the SQL option, the need to purchase database expansions does not exist anymore, you should be able to obtain a 65Gb database size license at no cost for the native db - lately I have even seen limits of up to 128Gb, this reflects the maximum database size that can handle the native db engine. For me, as native db fan, there are in fact only a few reason to switch to SQL - more than 40 to 50 concurrent users, the need for more than 65Gb (or 128Gb) database size or integration with other SQL base applications - otherwise, I would stick to the native db - especially if Navision is running fine now. In your case, having the expertise in house, having the SQL licenses and the necessary machine power already available, and other applications that might integrate with Navision, it might be an idea to switch to SQL, but there are a lot of factors that should be considered. Saludos Nils

It’s only a question about time, IMHO, before Microsoft stops supporting the native database, so you might as well take the plunge now.

I know for at fact that you can get a 256 GB permission if you say please. Even read somewhere that a dutch company has 512 GB.

If you have the hardware and the expertise, might as well go for it. In 4.0, there is a granule called Business Analytics that requires you to have SQL Server. I’m not sure if this is going to be the trend with Navision in the future on having granules offered only for SQL Server, but if it is, then you’re one step ahead.

Thanks folks, for your feedback. I talked to our local Navision consultant group (where we got our license from) and they gave me a new license file for connecting to SQL, so were fine there. Also, they said 64gb was the maximun license file size, and that’s already what we have. We have allocated 100% of the 64gb, and are at 73% capacity of that total size; even with weekly data purge. Unfortunately, our use of Navision is slightly unorthadox in that we’re not really doing the financial aspect, therefore we cannot compress the data. We bring in lots of EDI data (orders) daily, that’s why we are building update size in the database. Started a restore of our company data to the SQL database for testing; woe is me, now I have to deal with invalid DATE values in the data, so I need to write some routines to clean it. Anyways, thanks again for your inputs. Cheers, Eric

The database limit is not 64gb, its 128gb and there is no cost associated with the database size any longer. Your license can be updated to allow expansion of the database, but it has to be requested (it isn’t done automatically). Reading Allans response above, it seems that even 128 isn’t the maximum size, which I personally didn’t know (which is why I keep reading this forum, you always learn something new :slight_smile: Regards Daniel

Eric, are you using Lanham’s EDI module? If you are, then there is a table that you can probably purge. I don’t remember exactly which, but if you look at table information, it will probably be the largest one you have (that was at least the case for one of my clients). This table contains the information that the EDI file contained, so this data isn’t needed after the EDI order has been created. Its just information that could be useful to look at if something goes wrong and you are trying to figure out why. But after a couple of months or so, you could probably purge the data safely (which is what my client did, and it freed up 25% of their database size). Regards Daniel

You can handle the date problem using the migrate.fob file that is on the product CD (under a folder called Upgrade or similar) rather than writing these routines yourself. There are other posts on this forum that discuss the use of this .fob.

If you decide to move to SQL, upgrade navision before you do. The client executables after version 3.6 have had a number of changes to improve performance under SQL. The native db is limited in its ability to take advantage of the ystem resources available with today’s modern servers. It has no multi-processor support and only accesses <1 GB of memory. Once a databse grows past a certain point the only thing is to add disk. SQL does not have these limitations. However SQL is not without it’s issues. Since you are running other SQL databases, I am sure you are familiar with the maintence requirements of SQL, such as index rebuilding. (Actually something any database needs). Navision SQL relies heavily on its non-clustered indexes, so rebuilding becomes important for frequently used tables. Also process that run fine under native may behave differently under SQL. Consider piloting a typical transaction load before moving production systems.