Filegroups in SQL Server 2005 on an iSCSI SAN

valerio

We have a 1TB (approx.) database of Microsoft Business Solutions Navsion v4.0SP2 with LS Retail.
I have found through google search the document "Tuning Navision for better performance" by Patrice Dupont-Roc.
One of the optimisation he proposes is to split the database in 6 filegroups (allocating the tables to each filegroup depending on the data they carry) while stating that this does not apply to striped RAID strategies.

Though this is a fairly old, and probably outdated document, I would like to ask why does this not apply to striped RAID strategies (and taking it a step further I would say to iSCSI SAN configured for RAID 10)?
Also, does it make more sense to have more than one files for the data given the size of the database?

Another optimisation that is proposed (for SQL databases generally) is keeping the indexes (other than the clustered ones) on a separate filegroup. Does this work with Navision?

And one last thing, our iSCSI SAN is a Dell Equallogic (Actually two units) which optimises gradually by spreading the data across it's disks to increase performance. Wouldn't that on it's own cancel out any need
for using different filegroups?

Thank you in advance,

Valerio

kine

Do not use iSCSI for SQL. It is not working good. iSCSI is good for backups etc., when working with big files. SQL is making many small changes etc. and the iSCSI have too big overhead for small packets. We never had good experiences with iSCSI and SQL together, even when using good LAN connection. See this: http://support.microsoft.com/kb/833770/en-us

stryk

Have in mind that the document you are referring to was written in 2003, thus it's based on SQL 2000 and NAV 3.70 - hence this totally out of date and 100% (OK, let's be fair: 80%) irrelevant for today!

Splitting the DB into multiple parts is actually not necessary anymore, since the hardware e.g. the storage systems are much more powerful than 10 years ago. Thus, you should have a powerful disk-subsystem, plenty of spindels if using RAID10 (with SSD this might be different) and powerful FC controllers (with lots of caches). Also "high-end" SAN solutions might be feasible.

What actually is really required needs to be investigated and discussed thoroughly (what IS the current I/O, how long are the disk response times?); just the size of a DB is not the most important thing - it is the transaction volume you process every day! I'm pretty sure that your users are NOT moving 1 TB of data around the whole day ...

Marije_Brummel

I agree fully with the above statements.

Also keep in mind that 4.0SP2 in combination with SQL2005 without the right hotfixes whas probably the worst combination for NAV ever.

My suggestion would be to have an expert do a quick scan on you system.

valerio

Thank you all for your answers. We are planning an upgrade to 2009 next year in collaboration with our Navision support provider.
In the mean time I am always looking for more information on how I can get our current version in a better shape just enough to get us till the upgrade.