I’m looking for a few reference sites, case studies for the largest Navision Financials/Attain installation using MS SQL I terms of large I mean : Database Size : Over 60 Gigas Concurrent users : 100 users and more (these includes a few Citrix users) We may plan to move to SQL but we’re a bit scared regarding the performance issues, it might be obviously slower than the native engine, but how slow will it be, any benchmarking available on the web ?? Other questions : o how stable is Navision when running under SQL ? (knowing that when running under C/SIDE, its as solid as a rock!) o Will we require a full time SQL System Administrator to manage Navision in this “new” environment ?
With a system this size, you must have an internal SQL DBA. Or at least a very close relationship with a contractor that will give you availablitiy as required.
60GB range is ok, I have one on 69 GB (in a 97 GB SQL) with 77 users incl. Citrix and a smaller at ~55 GB (56GB cont. expanding) with 17 users only, but keep close note on the SIFT tables which tend to use lots of space - very important to clean those regularly (thus a tightly connected DBA is important as Mr Singleton noted above). In the big picture I’d still prefer fdb (use less disc for same amount data etc.) but some ppl tend to buy a porsche just to look nice.
Hi Navigera wrote : “but keep close note on the SIFT tables which tend to use lots of space - very important to clean those regularly (thus a tightly connected DBA is important as Mr Singleton noted above).” → How do you clean this SIFT Tables ?? Is this in Navision, or in SQL Entreprise manager ?
quote:
Originally posted by NicoV
H → How do you clean this SIFT Tables ?? Is this in Navision, or in SQL Entreprise manager ?
. . .SIFT does not exist within SQL Server, Navision has to create a new table for each of your Flow Fields, as an example this will result in over 2,100 tables in the demo database when converted to SQL Server.[V]
…SIFY does not exists, … Yes I known that’s it stored in different tables, but Navigera has problably some tools to clear/maintain this huge amount of tables ? Nico
quote:
Originally posted by NicoV
Yes I known that’s it stored in different tables, but Navigera has problably some tools to clear/maintain this huge amount of tables ? Nico
. . .Unaware. One thing that I tried, boldly, was to create a Database Diagram in SQL Server 2000 on the Navision SQL database and there were too many tables to work with. Granted a monster machine may have been able to perform this action but obviously not on a typical development machine, too many tables in the Database. Groetes [;)]
quote:
Originally posted by NicoV… but Navigera has problably some tools to clear/maintain this huge amount of tables ? Nico
Nope, I don’t but my beloved DBA warriors do, but I guess you can check this with any NSC employee with SQL specialty. As far as I remember they run something directly from SQL, but you may also do the “optimize” from Database - Info… - Tables button. Optimize do, among other stuff, clean those zero value SIFT records if I’m correctly informed. (they run it directly in SQL only for performance) [Edit]As you might see it’s very important to do these jobs on regular basis (pls. don’t ask me to specify “regular” [:)] )
We have a user with 40 Gb database on SQL with 200+ daily concurrent users. The site will expand to 350+ users on 1-January 2004. Database growth is 2-3 Gb per month. The key to an installation of this size is to quantify each and every transaction that a user may perform, test it, review the code that is run, optimize the code if necessary, and move forward. We had to change a lot of the posting code (codeunits 80 and 90, item tracking, etc.) in order to maintain excellent performance and eliminate deadlocks (we now get fewer than 5 total system-wide deadlocks per week). All in all, it was a fairly major project, but now has resulted in a high-performance, stable site.
Just regarding the SIFT - sorry this wasn’t the original topic. When doing an Optimize from Navision it does remove the 0 entries from the SIFT tables. It also rebuilds the base table and SIFT table indexes (SIFT tables mostly have just a clustered primary key but occasionally also a secondary index depending on the position of Date fields within the key). The rebuild is done with a CREATE INDEX … WITH DROP_EXISTING statement, but slightly better defragmentation can be had using DBCC REINDEX, which is why it might be done outside of Navision. Imteresting to read about these sites - they are pretty big systems in Navision terms.
We’re running a system with +350GB of data (mostly orders, invoices & posted entries) and approx. 40 concurrent users (Citrix users as well). We hardly use SIFT-indeces at all - we’ve gradually disabled them in order to keep (write)-performance acceptable. The Customer, of course, have their own DBA(s) - and the server is - need I say it - state of the art. Almost all dataprocessing (dataimport, validation & posting) is done outside normal office hours via batchjobs. Things are running quite smoothly and has been for more than 2 years - so don’t tell me Navision cant’t handle large installations [;)] Of Course the datagrowth (+75Gb per year) is an issue as well. We’re currently debating the best way to implement a database-split (production-db (6 months)/ statistics-db (6 months - 3 years)). Suggestions are welcome. BTW Axapta, I’m told, does a better job of handling large installations, datawise. Can anyone comment on this - better SQL interface or what?