Dynamics User Group

What are the experiences so far?

Is it really worth it?

============
Best regards,
Erik P. Ernst, webmaster
Navision Online User Group

Hi all.

My primary work field is Navision on SQL Server.

I guess we all need experience in this field, because to my knowledge no company is using Navision for SQL yet.

But still I have gathered a lot of info and practical Navision SQL experience myself. A little bit follows here...

1) Navision for SQL Server is much more HW demanding. Instead of running on single processor machine with 128 MB RAM, we are now talking a multi processor machine with at least ½ GB RAM and preferably more.

2) The difference between Navision on SQL Server and Navision on the nativ Navision server is not visible to the end user.
The Navision programmer is only affected if he or she want to experience with the new locking facilities in Navision for SQL.
The only change will be for the tecnical installer.

3) Security is a bigger issue in Navision for SQL and than the native version.

In native Navision user access from inside Navision AND user access from C/ODBC is controlled by Navision's user management, thereby focusing user management to one point.

In Navision for SQL user access from inside Navision is controlled by Navision's user management.
All other access to Navision data, that is from outside Navision, is exclusively controlled from SQL Server, and must therefore be managed from within a SQL Server client applikation like in example Enterprise Manager.

1) You need alot more HW. The order of importance IMO would be 1) Disk I/O(due to the way that SIFT is implemented) 2) Mem 3) Processors.


2)The client will need more I/S expertise, in order to manage a SQL database.


3) The database is very stable.


4) Commitment is much higher on both sides of the fence.


My opinion is that you should carefully weigh the need for SQL before choosing it over the standard database. Simply because it is "fashionable" and has name recognition is not a sufficient reason. There must be a need and additional expertise in order to have a sucessful SQL implementation. One FYI, there are currently companies using Navision SQL on live systems.



Mike Dertian
Programmer/IS
Aston-Frontline

In my personal opinion, as a minimum, the client must have at least one person who has passed the Microsoft certification exam for SQL7 server admin. If that's not possible, they really shouldn't be running such complex software, and they should use the Vanillavision database.



-jp

I have a note to Mike's posting.

Mike Dertian said that the order of importance when talking HW on Navision for SQL is : 1) Disk, 2) Memory, 3) Processors.

This was indeed the case with the Native version of Navision. Though I don't think this is the truth with Navision for SQL.

In my appinion the order of importance has changed to : 1) Memory, 2) Processors, 3) Disk I/O. SQL Server is very dependent on the ability to process its operations in memory, and as far as I've seen this is especially the case with Navision on SQL Server.

I've perform the following test - repeatedly :

40.000 records from a flat file written to a Navision ledger through a dataport on Navision native and on Navision for SQL.

The test was performed on a server with only 128 MB RAM, and showed an extreme difference in performance between Navision native and Navision for SQL.

More important, what I saw was that both versions of Navision performed equally well during the beginning of the dataport writing. Then suddenly after 10 procent completion Navision for SQL slowed down to virtually a standstill, while the native version of Navision continued its rapid pace throughout the dataport writing.

I'm allmost 100 procent certain that this performance decrease after 10 procent completion was due to the memory shortage. I'm currently in the tedious process of argumenting (begging) for more RAM, and when I succeed I'll perform the test again properbly with a much more positive result.

Once You have enough memory You'll see that You need as much processors as you can get. Disk I/O isn't the problem.

When performing the import, have You done a tablelock. Otherwise SQL Server will have much to do administrating the rowlocks.

You can also try to do the import in a codunit insted or a dataport or report. You'll probably se an improvement in performance then.

All of the problems I have had with speed and SQL have to do with either inefficient translation of C/AL code into transact SQL statments or maintaining and calculating Flowfields(maintaing the SIFT tables). Both are bottlenecked due to Disk I/O not memory or processors. Of course, I think it goes without saying that sufficient memory and processors are a requirement and can't imagine a decent sized SQL implementation witout at least 500M-1GB RAM and 2 500mhz processors.


To address Nordtorp's test, importing large files via dataport into SQL is slow and is a known issue(similar to the version 1.1/1.2 issue native nav had with dataports, although not as bad). My client brings in 50,000 records into their SQL database every day via DTS(logged) using an active X transformation script(to modify the data as it comes in) and it takes ~10 sec. to bring in these records.


In my experience with this product, if you have more than 2 processors your pretty much at a dimishing return. I currently have a client that is in the process of converting to SQL who will have a 50GB DB after a full conversion. I have a smaller 30GB database currently running on the client's server (4-PIII Xeon 500, 7-seperate RAID arrays(the one containing the large .NDF's(the .MDF is 20MB on a seperate drive) has 14 spindle's), 2GB RAM). I have adjusted the envrionment to restrict SQL from consuming 500M-2GB without much of a noticable diffrerence during intesive activity. On the same server during intensive tasks, perfmonitor is averaging ~70% 30% 5% 5% for processor useage.

The C/AL code translation into T-SQL is one place you are going to want to focus on, especially in smaller installations. Changes as small as the order and how (setfilter/setrange) that filters are set within the C/AL can make drastic differences in the performance of the product. Spend time with the SQL profiler while using the navision debugger and you can see what statements are time intensive.

Again, obviously if you starve the system of any one resource you will seriously impact performance. However, due to the disk intensive nature of this product(due to SIFT) I seriously recommend making your disk subsystem your prime concern (after meeting resonable requiements of other resources).





Mike Dertian
Programmer/IS
Aston-Frontline

Moved to SQL-Forum.