We plan to install Navision Attain on an SQL 2000 database. I read several different posts i want to know wich is the best configuration RAID 10 or RAID 5 for the database, and for the log file ? Is there a specific size for the log file according for the database size ? thanks for some hints.
Give me You e-mail and I’ll send a document for You which describes it all. It’s written fror 2.60, but it still holds. In short it says: OS on RAID-1, Log on another RAID-1 (big stripe size) and database on RAID10 (and use as many disks as You need for performance). And don’t forget: Ton’s of CPU and RAM… //Lars
Kayser, Lars is correct RAID 1+0 offers the best performance for a transaction processing database, such as Navision. RAID 5 is expensive and provides greater redundancy, but is not optimal for read / write activity Here are the keys to a successful SQL install, on Navision or other: As Lars said TONS of CPU AND RAM Make sure the Log Files and the Data Files are on separate physical disks Plenty of disk space. If you have those pre-requistes it will only take a small amount of DBA work to keep Navision tuned on SQL. As far as the log size, it will change and grow and shrink along with the activity. By default Navision creates a log file size of 100MB with unlimited growth at 10 % (SQL Defaults). As long as you have plenty of disk space for the log file, this will never be a problem. Just back up the log a couple of times a day. If you have a small database, you could even do full backups throughout the day, and set the database recovery options to Simple on SQL. Then, the log is not retained after checkpoint and its size will never be a problem. etc.etc. I am droning on, but, take a look at the SQL documentation from Navision for setup, but I found it a little weak for Administration. Use online SQL forums and a good SQL admin book to help you set your administration parameters. I would be willing to help as I have been using SQL Option for over a year and have experienced both the benefits and pitfalls
With respect to separating the log and data files. NEVER except the default file locations Navision(or SQL!!) provides when creating a database, as by default it throws the data and log files in the same directory!!! I often wonder how many people who complain of SQL or Navision SQL performance have created databases using the default file locations
Never use RAID 5. Drives are cheap enough now so that you can do full RAID 1+0 without great expense. Have tons of RAM, as stated before. You should try to get it so that at the very minimum, your most used tables could actually fit in the available RAM. SQL will swallow up whatever you give it, so if money is no object, more is better. It also helps that if you stripe your disks, that you stripe them over as many as you can afford and fit reasonably. I’ve suggested this before in previous discussions about SQL sizing, but I would recommend an array of 10-12 disks (of course really dependent on the database size and load you expect). 6 for the database, 4 for the log, and 2 for the OS and applications. All with RAID 1+0 for striping and mirroring. Also, make sure you use hardware solutions for RAID, not a software based solution. And, make sure that your SCSI controller is of high quality, and that it itself has a lot of RAM, and high performance. 15,000 RPM drives are now a lot more prevalent, so try those as well.
Devin, I agree. The defaults are intended for a quick OK of the dialog for test or CRONUS databases - not for production databases. The same applies for the Options and Collation settings - not just the files. Anyone using the defaults for a live user database should not be using the Database New window at all.