Archiving Data

theredmiata

All,

We've had Navision 4.x running for over two years and the growth of the database has been interesting to say the at least. We are currently at about 280gb half data, half indexes. I'm currently going after the indexes, however trying to figure out if an index is being used is not necessarily trivial. So, I've been tasked with trying to extract out all 2006 data from the database such that by the start of 2008, only 2007 data will be available. Is this possible? Any documents available on this subject?

Regards,

Ian

ara3n

The developer toolkit can help you with finding where a key is used.
I'm guessing/hoping that you are disabling them from navision.


As far as deleting historical data. There are delete Reports (e.g. 1495) that will delete specific areas.

I would write custom report and hardcode the filters and delete the documents.

As far as GL and sub ledgers concerned. hmmm
I've never deleted them, only for go live.

David_Singleton

theredmiata wrote:

All,

We've had Navision 4.x running for over two years and the growth of the database has been interesting to say the at least. We are currently at about 280gb half data, half indexes. I'm currently going after the indexes, however trying to figure out if an index is being used is not necessarily trivial. So, I've been tasked with trying to extract out all 2006 data from the database such that by the start of 2008, only 2007 data will be available. Is this possible? Any documents available on this subject?

Regards,

Ian

Hi Ian,

Please consider very carefully before deleting any data in Navision. Even using the standard deletion batch process is dangerous, because they do not allow for any customizations to your database, and the compression routines suffer the same issue. I have done quite a few clean up jobs to fix systems where someone has done exactly what you are contemplating, and it can be a huge job to repair, generally involving restoring an old backup, and moving data from the old database back into the current one.

The path you are taking with the keys is the right one, and if you are on SQL (which I would hope with that size of DB) then you should be able to dramatically reduce database size, but still not as much as you probably want.

In cases with this sort of growth, its generally because of some small repetitive transactions in huge volumes, say for example like in Retail. In these types of cases you should look at a combination of two solutions.

The first is pre-compression. This is where instead of posting data immediately, you push the high volume records to a journal, then say at night, you compress where possible by similar fields the data, and then post a journal that may be say 20% the size of the raw data.

The second step is that you have a periodic process, say monthly that takes old data, and moves it to a second set of tables, whilst maintaining integrity in the existing database. In SQL you can even off load this "historic" information onto another database, or even another server.

Also in step one above, you need all the detail, then you post the compressed journal, but put the raw data into a different table that is then moved in step 2 to the other server say.

In Navision, the actual size of the database is never really an issue. The core issue is how many records you have in the active tables, and this is what you need to minimize.

But please if its the first time you have done this, then do a LOT of testing, and ask a LOT of questions.

nEWvision

Hi David,

I am having the same requirement. My client do not want to use compression as it delete the history data. Instead he is interested in Archiving the data.

Can you please suggest if there is any add on / Solution by third party available for Archiving?

Best Regards