Possible Solution for Big Size Dynamics NAV Database (Performation Issue)

Hello Friends,

Everyone once in a while, we will get a support call from a customer about archiving their historical data.

Dynamics NAV (formerly Navision) has been in the US since 1996 and for customers that were first to adopt Dynamics NAV, they have kept those data with them through the years.

Some will argue that with the cost of storage in decline, why is it necessary to archive old data. While I tend to agree with that statement, but trying to manage a 700 GB database backup, if nothing else, very time consuming.

There are a couple of ways to “shrink” the size of your database by eliminating the data within Dynamics NAV.

Data Compression

The date compression processes will consolidate multiple entries in the table in question into one entry.

The problem with date compression process is that it takes a loooong time to run. It takes so long to run that we usually just stop the process. The design of the Date Compression process seem to want companies to run it periodically when they start using NAV instead of running it when you’re database is 700GB. This is never the case.

Most Dynamics NAV consultants out there will never recommend their clients to use Date Compression (me included). One of the main reasons is because in the prior versions of Navision caused data problems when you did date compression when you try to upgrade.

Removing Data That Adds No Value

There are data in the database that one can consider as low priority value. You’re keeping the historical data, but the historical data is more “nice to have” instead of regulatory compliance or critical to running the business. These tables include, but are not limited to:

• Change Log
• Sales Document Archive
• Purchase Document Archive
• E.D.I. Receive Documents
• E.D.I. Send Document
• Posted Sales/Purchase Documents
• Posted Warehouse Documents
• Warehouse Registered Documents
• Any posted documents

This is not to say you should delete all of the data, but you can certainly delete those data not required by the tax or audit authorities.

Re-Structure SQL Database

Create blank new database with only objects and create the company by powershell command. Now transfer all the data from old live database to new database using SQL Scripts ( we have done it successfully many times). It will reduce 50 to 70% database size. SQL Full backup and Sequential backup take less time and you will notice performation improvement in NAV. This process has re-structure database in disk and page size become smaller.

Replace the HDD disk drive with SSD disk drive

Upgrade you server computer disk with new SSD Drive with RAID 10 configuration. It will give you around 1000 to 1500 MBPS speed on disk. It will increase your NAV Big database performation to 20 to 50%.

Re-implementation

This is the nuclear option. Basically, start fresh in a new Dynamics NAV database with only your setup data, master data, and opening balances. This option is popular with companies that have been using Dynamics NAV for a long time. It gives an opportunity to eliminate a lot of bad data, in addition, to modifications that are no longer needed.

The historical information is basically kept at the old database environment. If an old data is needed, the user basically goes to the old NAV database to retrieve the information.

But re-implementation is really overkill to specifically address the database size issue.

Conclusion

With the performance and capacity of storage ever increasing and the cost of storage ever declining, these types of question does not come up as often. I suspect as time progresses, these questions will come up once in a very long while.

I typically would recommend companies remove the data that adds little or no value first before attempting to do anything drastic. Usually holding those data takes a ton of storage in the Dynamics NAV database.

There was a company I visited that had 10 GB worth of Change Log entries. Worst is that the customer didn’t even know what the change log is…

Thank you reading.
Please give me your feedback on this.