As and end user, I have been tempted to switch to SQL server, but haven’t been able to justify the cost. So, here is my scenario for you:
If you are selling Microsoft Dynamics Nav 4.0 to a new company, they are sold on the benefits and you have priced out the granules for their complete installation, You have based it on the Native database. They say, “ I see it doesn’t cost any thing different to get the SQL option, why don’t we go with that” and you explain the Microsoft Dynamics Nav 4.0 SQL license option is only the right to run it on SQL server, you still need to purchase SQL server and SQL user licenses on top of the Microsoft Dynamics Nav 4.0 licenses and the Microsoft Dynamics Nav 4.0 professional user licenses you are already purchasing. What benefits do you present to them, for paying the money for SQL server on top of the Microsoft Dynamics Nav 4.0 you are selling them?
I was working on a blog about this, so I’ll finish it today.
But in general, this is a very hot topic here since the days of NOLUG first thing to do is to take a look at the old posts: go here http://www.mbsonline.org/forum/search.asp and enter native sql there are 28 pages [:D]
Basically though, if Native works for you then why change?
My base idea for SQL-server is.
If you already have it, you can opt to use it. If you need to buy/install it, do it only under certain circomstances.
-You want an uptime of 99.99%. This is almost impossible with a Navision-DB in case of a server-crash.
-You have a DB of a lot of GB’s and backing it up a Navision-DB is a problem.
-You have a lot of users and all working in the same tables in the same company.
-You want at all costs use other products that can directly connect to SQL.
Biggest negatives of SQL:
-You need better hardware than with a Navision-DB.
-Navision-DB is install-and-forget. SQL isn’t. You have to maintain it. So in the company where they use, they better have someone who knows SQL.
-You have to finetune the indexes/SIFTindexes used in SQL to get acceptable performance. Also prepare to have some “record locked by another user, retry again”-problems. This takes a lot of time to fix all (depends, of course, how much functionality of Navision is used).
And basically, like David wrote: If it works, don’t fix it!
Another point I would make is, although the native database is extremely efficient and the program was designed around the native database, no one can be sure how long it will survive and be supported.
SQL server has higher costs involved and like my learned collegue said previously, you will need a SQL guru analysing the installation periodically.
I usually suggest smaller installations to stick with native database.
Worth also asking the question “is there a person in the company that is an Excel or Access superuser, who is also great at producing reports”?
If so, the SQL option can be a dream for them if they are hooking up data from several sources as well as Navision.
The SQL support need not be needed that much with the latest version, there is a lot of info available via user forums similar to this, so small companies can still gain the benefits.
Overall though, unless there is a really good reason to use SQL, stay with the Native
Another thing to consider is your report requirements as Colin said but not only “Excel reports”.
Navision native reports are rather simple and users needs to login to Navision to view the reports. SQL server offers Reporting Services, a reporting tool very different from the reporting tool within Navision.
In Reporting Services you can created interactive reports (e.g. drill down from one report to view more detailed information), users can subscribe to reports (e.g. the report will be delivered each week via email to the user), reports can be exported in variously formats (e.g. Excel and PDF), users do not have to login to Navision to run the report and thereby use a Navision session and you can use chart components.
Now that sounds pretty interesting, as it stands now, any time someone needs a report, they ask me and I find what they need and print it out. More automated reporting and interactive reports sound very interesting.
Microsoft SQL Reporting Services is a pretty interesting reporting tool but it is not a specific Navision reporting tool; it is a reporting tool for Microsoft SQL Server.
All Navision reports are currently developed in the internal Navision reporting tool, so the backside of Reporting Services is that you will have to develop / redevelop the reports you want to use with Reporting Services. However Microsoft has developed a few Navision reports for Reporting Services (and for and Axapta as well) and as far as I know Reporting Services will be the reporting tool for Navision version 5. I haven’t looked at these reports yet so I can’t comment on them. You can find them here if you are interested.