store "cold" data (history, logs) external - solutions?

MartinFK

Hi there,

We have a lot of data in historical tables (archive tables, tables for posted docs, ...).
Deletion is not an option at the moment (data is too young), also moving them to tables with less keys/indexes was not satisfactory.

I wondered if someone set up a solution, where data is spread over different devices with different access types.

E.g. Posted Invoices of:
current year --> primary data pool (fast)
last 2 yrs. --> secondary data pool (big but slower)
...

Do you have any experiences with solutions like that.
Glad for any input.

Regards,
Martin

jlandeen

Are you running on native NAV or SQL? If you're running on SQL server you may want to look into partioning off some of those archive tables into a seperate file group. I know file groups are supported on SQL 2005 and 2008 however I'm not 100% sure if the same features are available in older versions of SQL.

For more insight into filter groups check out:
http://www.mssqltips.com/tip.asp?tip=967
http://www.mssqltips.com/tip.asp?tip=1112
http://msdn.microsoft.com/en-us/library/ms179316.aspx

MartinFK

Hi Jeff,

thanks for your reply.
Yes we're running on SQL2005, so filegroups are available.

I was more thinking about a solution that moves data between two (or more) SQL servers, depending on rules (the aging of data).
For the user it should be like before, all data available.

Regards,
Martin

jlandeen

I haven't seen any solution (add on or something in the base product) that handles data in that fashion. So you may have to custom build or configure a solution that works for your specific situation.

I don't think NAV works very well if you were going to try and segment your data of onto different servers as NAV normally looks for all the data on 1 server within a database so you would have to find a solution that preserves the structure (at least as far as the NAV client is concerned).