Please bare with me as I am not a SQL or hardware expert by any stretch. Hoping to learn as we go through this, though. We are currently on SQL2000 and Navision 3.7 but will be moving to SQL2008 and NAV2009SP1 very shortly. We have 125+ users and a 100GB+ database. Currently the data all resides on a single disk.
We have identified our most heavily hit tables. They are just as you would expect: G/L Entry, Vendor Ledger, Customer Ledger, Value Entry, etc.
We would like to split these tables over multiple drives in order to increase performance. We will have a RAID10 with four disks dedicated to NAV data. From reading other posts it seems as though this can be very beneficial: more drives = more spindles = more concurrent reads/writes.
Our DBA is worried that because of NAV's unique internal ID number for tables, copying the tables using a SQL script will mess with the mappings between the NAV code and the tables. I've seen some other posts about partitioning data (specifically by year) across drives, and I assume it's the same for tables. And I know it has to be done from the SQL side.
Anything we should watch out for? Any posts you can point me to that I might have missed or not understood? NAV is pretty particular when it comes to doing things, especially outside of the client window.
Thanks for your advice.