NAV LS Retail Native DB - How to reduce (compress )data

smmurugan · 2011-11-02

Hi all,

Our client is using NAV 5.0 with LS retail. They are into supermarkt. so data volume is high.

Scenario:

HO (SQL database) -- > Store (Native database) --> POS (Native database)

All financial postings - Purchase Inoive, crdit memoetc. - are done in Head office only.

In store (Native DB) Purhase orders, Purchase returns, Transfer orders, Purchase worksheet, stock count worksheet, negative stock worksheet are created and posted for stock.

All purchase orders and returns data comes to Head Office through instore documents and invoiced.

Now the store DB has almost reached the limit of 65 GB. We neither want to for SQL nor to update/buy licence for more space.

There is huge data in Value entry (15 GB), Item Ledger Entry (9 GB), G/L Entry (2 GB) and other tables.

Is there any possibility to compress old data on these tables ? They don't want to carry the history at store level as the comple data is available in Head Office (through replication). Only opening balances of previous year is enought to maintain balances.

Any advice ?

- Murugan

kriki · 2011-11-02

With a native DB going over 10-20GB, it is best to go to SQL server.
Native DB has a max of 1 GB of DB-cache. SQL server has no limit (except the physical limit of the machine). Native can never compete with this.
SQL Server 2008 (R2) Enterprise Edition has the possibility to compress data without losing any data! There are also external tools that do this.

in native: If you want to save some space in native, check all of the keys in the big tables and see if they REALLY serve. If a key is used only for some reporting, rewrite the report and kill the key. You can also save some space by removing (or making smaller) fields that are not used, but this must be tested very well because I never did this and it might have unwelcome sideeffects.

David_Singleton · 2011-11-02

smmurugan wrote:

Hi all,

Our client is using NAV 5.0 with LS retail. They are into supermarkt. so data volume is high.

Scenario:

HO (SQL database) -- > Store (Native database) --> POS (Native database)

All financial postings - Purchase Inoive, crdit memoetc. - are done in Head office only.

In store (Native DB) Purhase orders, Purchase returns, Transfer orders, Purchase worksheet, stock count worksheet, negative stock worksheet are created and posted for stock.

All purchase orders and returns data comes to Head Office through instore documents and invoiced.

Now the store DB has almost reached the limit of 65 GB. We neither want to for SQL nor to update/buy licence for more space.

There is huge data in Value entry (15 GB), Item Ledger Entry (9 GB), G/L Entry (2 GB) and other tables.

Is there any possibility to compress old data on these tables ? They don't want to carry the history at store level as the comple data is available in Head Office (through replication). Only opening balances of previous year is enought to maintain balances.

Any advice ?

- Murugan

Hi Murugan,

this is not the way you should be using LS retail. The store data you should be regularly purging an rebuilding the data form head office. Generally the only reason for historical entries kept in Store databases in LS is if you have some complex returns requirements and generally then you customize replication so as to keep only the necessary data.

How long has the store been running now?

sudhakarsv · 2011-11-03

Hi,

I agree with the solution provided by David & Kriki.

Also you can delete the "Transactions Data" at store end, as the copy of the same is available at HO.If i am correct Transactions data would be about 10GB,if this is case you can delete the data pertaining to Trans.header,Trans.Sales Entires,Payment entries etc.

This will help you to reduce the DB Size.

NAV LS Retail Native DB - How to reduce (compress )data

Comments

Categories