navision sql database size

i want to know what sql database size people with navision attain have… currently our db size is about 70 GB any solution & tips to reduce the size…or increase the efficiency of database

Hi, Sorry i did’nt have a solution, but i have the same problem. The db size of my Navision is 150Go. I didn’t know if Sql server and navision as limit. Patrick

quote:

i want to know what sql database size people with navision attain have… currently our db size is about 70 GB any solution & tips to reduce the size…or increase the efficiency of database
Originally posted by masqazi - 2005 Nov 21 : 15:54:45

First of all: 70Gb is no problem with properly configured server for SQL. Most likely that’s Your problem. People seem to have a need to by to little HW [;)]. Otherwise the tool You have in Navision is to compress ledger entries, but I allmost never consider that an option. HW is to cheap in comparison to loosing detaild data. Do Your homework instead and search the HUGE material You will find on this site if you search for SQL performance and such.

Increase RAM on the server. Put the swap file on it’s own mirrored drive. Reconfigure the db drives. - If you have a SINGLE db file, use RAID-10 with LOTS of drives. You gain performance with more spindles. - If you are already using multiple files, check with your VAR to see how you can increase the number of files that Navision is using. And again, put these on their own mirrored drives. Each db file should be on it’s own mirrored drive. The idea is to reduce head contention. For RAID, use HARDWARE RAID controller(s), not the OS software RAID. gud luk Gary

Hi Gary! I don’t quite agree with your recommendations regarding to the harddrives. Your advice is 100% correct when talking about a native database, but with SQL Server that’s just the “half of the truth”. The “Transaction Log” is absolutely depending on optimal disk performance, thus the TLog MUST be placed on a dedicated RAID10 (please, no compromise here). With SQL Server, a fast disk subsystem for the database files of course will increase performance. But as long as SQL Server has enough cache memory and a reasonable disk I/O rate, the impact will be minor. The biggest benefit of having more files (and filegroups) here is, that SQL Server is able to process on each file with a dedicated thread - this speeds up the system if you have multiple CPU installed! (For that reason e.g. it could be wise to have different files/filegroups for data and indexes!!!). @masqazi: please follow Lars’ advice and search the forum for “SQL Performance” as we have discussed this issue often before!

And by the way: We have installations (3.70) in some european countries with database sizes between 60 and 120 GB on SQL Server 2000, running without problems (after learning a lot about performance troubleshooting [;)]).

Joerg You are correct, I was thinking that masqazi was using the native db. The the TLOG file is indeed critical. Although depending on the size of the TLOG and db writes, it could be on a dedicated mirrored drive rather than RAID-10. hmmm I wonder about the solid-state drives. This would be the ideal application for them, where speed is the primary goal. Have not heard about them for years. I remember when I speced out a server a few years ago… Same issues my mentors told me in about the 60s, just larger and faster. BTW Microsoft is running their corporate ERP on MS SQL Server. I talked to a couple companies with a MS SQL Svr db of a TeraByte, so I will scale quite well…if the application will scale. masqazi…just don’t do what one company did. They put their entire ERP db + log + os + swap file file on one physical RAID array, 2 logical drives. And they wondered why their performance was so BAD. Gary

bump for cristi…

Thanks David. This was the kind of info i was after.

Hi, Microsoft made test for 300 uses in 4.00 or 4.01 where they was able to create and post sales orders in less then 0.5 second[Wow!]. So the configuration was: 4 processors 8 GB ram 8 (or 16 i do not remember) hard drives SQL Enterprise Edition They had to optimize code and SQL server. As i understand they get big increase in performance when they move some KEY tables to different physical partitions. Half year ago they promiced to release official results soon[Yeah!].

Cristi, contact Hynek, he can help you.