Performance Navision SQL 300 users 250 comp

Help, Client ask me: They want to use Navision 2.60 SQL (later Attain) with 300 online users and 250 companies in one db. What about performance?? Could they work without any performace problems??? Had anybody experience about so many clients and SQL??? What could be hardware requirements???

Hi, answering to your question, we have only 50 users and 8 companies and the performance of the database is tottaly anucceptable, really i do not know how it would be if we had what you are planning to have, one thing i´m sure…we had already changed aplication! Br Jose

How is Your System Setup Jose? 50 users is no problem at all given that You have the correct setup of Your server. /Lars

i think users is not the problem. but companies is. i cannot even create more than 30 companies in navision, based on a 512MB memory SQL server.

We have 40 users on native engine and 50 on sql. Native is good with 1 gb ram, and sql version sux with 2gb. Planning to roll back to native engine.

Have you moved over with regard to looking at problem areas with your SQL solution, identifying problem queries, looking at keys and application code that is not suitable for SQL - or just with your fingers crossed? Because the latter will not work.

Gedas, We are “cranking” with Nav2.60f, 64 users, 3GB mem on SQL Server, 5 Citrix/NT4 servers with 2GB each across Novell network. Can’t complain at all about speed and intend (at some date) to take our user base much higher. By the way, we have another 120-150 hammering the citrix servers and Novell volumes via our old DOS/Clipper applications. One thing we’ll probably do though is write a method to purge transactions as I see this becoming a problem after several years worth of hammering the system. This could be quite a challenge as Navision documentation lacks clarity and we’ll have to hunt and peck through the data to figure out what to remove. Bill

Received answer from partnerguide. Recommend a newer c/side (min. 3.10.a) -300 users are a lot but it depends what workload they are putting on the system. any index or wrong key design will have bad effect to performance. -HW: 4cpu and >2gb memory will be needed.-

I think the recommandations you got are kind of weak. One thing you also have to take into consideration is the excpected size of you database, and don’t forget or underestimate the need for a proper and fast storage solution. We are running 100 users on SQL sever, we have 8 companies in our database and the database is now at 194 GB. Our server is equipt with 8 processors and 6 GB of memory and we could still wish we had more power in certain situations. But we think that our main bottleneck at the moment is the preformance of our storage systems, Navision is very I/O intensive and with the number of companies and user your planing on running you need to plan this very well and make sure you get the fastes storage your client can afford. Spread your databases over as many disks as possible. Many small disks are better then few big ones. Also remember to put your tempdb on a very fast disks. With the number of companies you are talking about i think i would seriously consider to split them on several serveres so that one servere dosn’t have to carry all this load. I am saying this without knowing the size of each company, but i think that no matter how small they are it would not be a good idea to put all theese comapnaies in one database. I am not afraid that it would work, but preformance and administration of a database with the numbers of tables and indexes you will have could be a problem. Each company will have over 1000 tables, and just navigating trough your list of tables from within SQL serveres Enterprise mananger could be a challange. I think our about 14 000 tables are more then enough when i am scrolling trough them to do index optimalizations or other operations where i need to work on single tables.

Thanks Ingeb. If we try to split companies into separate database, we must buy separate licence for every database, no matter if it is on the same SQL server?? Is it? But your experience is very usefull. Thank you.

Gedas, Quote Have you moved over with regard to looking at problem areas with your SQL solution, identifying problem queries, looking at keys and application code that is not suitable for SQL - or just with your fingers crossed? Because the latter will not work. I have to agree with Robert’s remarks (as stated above). I have heard of many SQL option installations from people who say the system is running slowly. But moving to the SQL option is not just a straight forward backup and restore. Sure, with a small database and small number of users, you could probably get away with it. It’s not even a solution to throw more hardware at your problem (ie: more disks, RAM and CPU’s). The Navision coding has been in place for near on 8 years and has structuring problems and needs to be investigated for optimization purposes. Another thing is keys (indexes). SQL Server works differently with keys and you may find turning off certain keys that aren’t used or restructuring code to use different (better equiped) keys, improve performance. Whether or not the No. of Companies is a problem, have you tried speaking to Navision?

Gedas, your license file applies to an entire SQL Server. You can create as many databases as you like on the server - but doing this in order to have separate companies may cause problems with your application processes, since each company will be essentially hidden when working in a particular database. (From version 3.10 you can have separate licenses per database). There is no problem with having many companies in normal day-to-day use. Having many companies in one database gives problems in terms of: 1. A Navision restore into a non-empty database is time consuming. 2. Of course, it increases the number of tables which means a larger database but that does not have a performance hit in itself. You will need memory to reflect the number of companies if many are being used simultanously, since you will want the SQL Server buffer manager to cache company data. 3. Re-design tasks on per-company tables. 4. Adminstration in SQL Server tools is more cumbersone because of the number of tables. Last, Navision makes little use of tempdb. tempdb comes into play for worktables in join queryes, subqueries etc, building temp sets for SQL operations, and of course for temp tables. Navision uses such queries for certain SIFT summing (actually they are UNIONs) but the impact on tempdb is low. Also, tasks like index creation and table redesign use tempdb, which of course Navision does do, but this is a development-time task and not day-to-day user operation. Navision does not use SQL Server temp tables except for very few security related tasks.

Hi Robert, You said very strange thing about licence. Our NTR replied, that according licence agreement, one licence is only for one database and no matter is it native server or SQL server. Can anybody canfirm me, can i make databases on one SQL server as more as i need without any legality problems?? We have licence with unlimited company granule.

Gedas, I guess your licence is for unlimited companies not unlimited databases. [:)]

Connull, There isn’t granule unlimited database and as i understand licence not checking database numbers in one sql server (in native server it isn’t possibility to have more than one database in one server). So problem is only agreement and legality.


Originally posted by Gedas
If we try to split companies into separate database, we must buy separate licence for every database, no matter if it is on the same SQL server?? Is it?

I am not sure what the license agreement states on the issuse of several databases on one server. But i would think that this is ok as long as you have the licence for an unlimited numbers of companies. But my best advice would be to discuss this issue with MBS and explain to them what setup your planning to use. I know that there are alot of the Navision code that is not optimal for the use of SQL server, but i really don’t think it is that bad either. The runtime we are using is version 2.60E but the application objects are actually 2.00 objects that have been modifyed when it comes to the TABLELOCK changes that where made in version 2.50 to when the SQL server first was an option. You can ofcourse do alot to the Navision code and indexes to tune the preformance when it runs on SQL server. But my expirience tells me that if your hardware and infrastructure is not solid enough you will not get the an acceptable preformance no matter how much time you spend on tweaking your code and your indexes at least if your database is of some size. SQL server is pretty fast you write a query that should sum some numbers. So on a small database you could probably run fine with most of the SIFT indexes switched off. But when it comes to a database of our size you can’t simply turn then off becuase the preformance you would get when running reports and using flowfields in forms simply would not be to live with. You can ofcourse turn of all the indexes and sum indexes that is never used by any of your users, but that is considerd good practise no matter what database your working with. I also think that you should plan your hardware and infrastructure not only by looking to what features of the SQL server that Navision is using today but look more to the general recommandations for setting up a database server with high preformance. There are several reasons for this. One is that we don’t know what features of the SQL server Navision will use in the comming versions and another is that if you want to take full advantage of the new features that will be avalible for you if you go to SQL server then you need a sql server with a proper configuration. SQL server gives you for example very good tools for writing reports both for web and for office (excel) with out having to write C/side code. And not to forget the oppertunities you get if you start to look into Analysis services that is an integrated part of SQL server. I think that in most of the cases where Navision in general is running slow on SQL server it is because the hardware and infrastructure is not planned and set up good enough. When your infrastructure is in place you can allways spend time on optimizing slow running queries or tweak your code.

Gedas, I stand corrected - there is no limit on databases! Sorry! [:(]

Gedas, I would follow up what you’ve been told from another source - perhaps Vedbæk. From the begining, since SQL Server was very open in terms of databases numners and sizes, the idea of limiting the number of databases allowable along with the maximum database size was abandoned for the SQL version - it didn’t work as it did for Navsion Server. The design made of having the license file stored in the master database on a SQL Server, that would apply to all databases you were using in that server was deliberate and therefore there was no physical checking made that you are using only one database - which is very easy to do (actually it IS done, from 3.60 onwards when using a per-database license!)

I’m not sure that there is no limit on databases unless for maintenance or development. It exists the granule N° 2020 wich allows to execute more than one databases on one SQL server. But to create or maintain data and account (don’t really understand what that means.)

Thanks Lessi, I didn’t notice new granule 2200 - per database licence in version 3.60. But how expalined our NTR this granule allow use different licenses for every database on same sql server. If we haven’t it, all databases will use one licence. But realy nothing stoped me from use more than one navision database on the same server excepting licence agreement[}:)]