Full SQL functionality?????

Hello Navision users: First of all let me state that I am very happy with my current Navision implementation, but that I am considering moving to the SQL version of Navision. However, I am a little concerned about how fully the SQL capabilities are utilized. I have heard rumours that some basic SQL capabilties are not utilized i.e. that the stored procedures features of SQL are not available as part of the functionality. Is there any truth to this? Any input you can provide would be greatly appreciated because I cannot get a straight answer out of my current NSC. Thanks Robert1

The SQL version of Navision Financials is running pretty well and has been installed at many sites already. Although you lose some speed, compared to native Navision, you gain improved server features, automated backups, distributed transactions and more flexibility in scaling. Also interfacing with other systems is easier, because for that you can/might use the stored procedures and scheduler features of SQL server. Don’t understand your concerns completely, I’m afraid. Why would you be concerned about the way Navision has implemented its functionality (stored procedures are used a lot for that, I can assure you), and what do you have in mind when you would want to use your own stored procedures outiside Navision? John

Hi John! How much User? We have a customer wtih a 30 GB database and 250 User and we have many problems in testing phase of change to SQL thx, Gayer Rene Gayer Rene Projektmanagment NAVIConsult AG


We have a customer wtih a 30 GB database and 250 User and we have many problems in testing phase of change to SQL

Can You give a brief description of Your configuration? 30Gb and 250 user should definitly run better on SQL. Edited by - Lars Westman on 2001 Mar 14 22:03:34

Rene, We don’t have customers (yet) with that kind of load, so I can’t tell from experience. But in your situation, there are few things that should be looked at carefully. Heavy duty servers, lots of physically separated harddisks, plenty of RAM, high speed network - to name a few things at the hardware side. Have the log file on a different disk than the data. Be sure to have the latest NF version (2.60 D) running on all workstations. Do all users have set cache size to the max? But just as important is the design of the database. Have you just copied over from a previous version, or did you go through all the code to modify i.e. all locking routines? Did you apply all service packs and hotfixes? Are all keys well defined and are all really needed? Due to the amount of traffic you will be generating, all (additional) code should be checked for optimal performance. If there are specific things you have troubles with, post some more details here. John

Hello all. Robert, let me assure you that there is nothing unique in the way that Navision has implemented the SQL version. Navision is using SQL server as a database shell. I’ve worked for three other companies that did exactly the same with their software. Why do it this way - the average user doesn’t have access to your code, you are not tied to that platform, and it allows you to have an additional level of security - just to think of a few reasons. As for performance, I agree with John and Lars that there are many system (hardware, networking, and software configuration) issues that can affect performance. Therefore, this would be the first place that I would recommend looking to help performance. Michael

Hi all! Many features, many erros :slight_smile: Our customer switched for 2 years from SAP to Navision. At this day the database is near the size limit. The option is SQL. Oh well, it’s running … but how. - we have lost much performance (ok is known) - problems with the date format(is known) - we have test a periodic Invoice Job, in CSide the this job create e.g 200-300 MB in SQL 15 GB!!! thats not the end problem, the same job fot the same period wtih the same date at second time create only 500 MB!? Nobody knows why. We have specialist form NSAT(Navision Austria), specialist form MS developer and specialist form MS austria. The problem with the 15 GB is not solved… - the most errors surely small one but … I think SQL and Navision will be the future, but not with the actually release. Microsoft means that the communcation between NF and SQL ist not the best way to communicate with SQL. However, its not even a good way, its seemed to be a bad way. Please do not missunderstand me, my mail is not against SQL or any other issues. I have also a SQL project which is running(3 User, 500 MB DB), but if you have the chance to wait for the next release … wait :slight_smile: Please believe me our customer has not a good hardware he has a very good hardware , but no hardware all over the world will bring so much perfomance that the the communcation between SQL an NF is not a important factor Best Regards, Gayer Rene Projektmanagement NAVIConsult AG

i’ve forgotten. Tommorow we wil change the to SQL for one day from C Side to SQL. All User, real Buisness,… I’ll tell you the result next weak Gayer Rene Projektmanagement NAVIConsult AG

Rene, Are you sure to be running the latest version? Going from 2.60C to the current 2.60D gave us a boost in performance. To give an example: at one customer searching the Item table (containing some 700.000 items!) took forever before, now we have “find as you type” switched on again… Oh, about those local MS people - tell them that there has been a large team of Microsoft Developers (some 40-50 people at times, I was told) that assisted Navision during development. Don’t know how many are still working at Navision HQ now, but the continued research and development (and feedback from the field, not to forget) has improved a lot in the past year. Bit premature to blame Navision, I believe. Again, with such a heavy load the database design should be optimal. I have the feeling you just copied the C/SIDE version, right? John

Hi John! if it sounds like an attack from me to you i’m sorry. I dont want that. I also dont want to blame navision. I’m a fanatic fan of it - really!!! all what i want to say is: if i go to buy a new car and the the shop assistant says that in few month the new series is coming and he will prefer me this one because it is the better one, and i the customer know there is no time limit for me and i will take advantage of the new series, why should i take the actually (i think its not a blame for the assistant) Greetings of a fin fan :slight_smile: Gayer Rene Projektmanagement NAVIConsult AG

Hello all! For few minutes i get a short report of the change to sql from the customer i told you another employee of our company told me that the customer today worked with SQL Server The first have some perfomance problems but as the server has the data in cache it seems to be a “good” working. some little problems but the will try on the full next week … :-))) i think then i know more about this i’ll tell you nice greetings Gayer Rene Projektmanagement NAVIConsult AG

Rene, didn’t feel attacked by you in any way, don’t worry. In contrary, my intention was to keep you sharp on finding the cause of the problems. Too often, “techies” are focussing on correcting the outcome and forget to look where the problems are originating from. Perhaps the database design was not optimal before also, but has it been hidden by the performance of native Navision till now. Something I would start searching for is, for instance, an excessive use of flowfields. These powerful fields are putting more strain on the database under SQL than they do under C/SIDE, so -as example- use of flowfields in filter settings should be carefully analyzed. Or I would examine the indexing. Are the keys properly organized, no inefficient or rarely used keys in the most used tables? The customer (and you?) should realize that you’re doing more than just an update to a newer version of the program. You’re switching to another platform which requires to look (again) to the strengths and weaknesses. John