What is the best practices way to archive / shrink Dynamics GP company database

(0) Share

Report

Posted on by Jack Harper

470

Our live company database is very large. We have considered purchasing the Company Data Archive (CDA) 3rd party product to archive older data. Is anyone currently recommending a best practice of any kind that helps archive older data 1-4 times during each year? Before we purchase another tool for this, I'm interested with the success and practices others have implemented using any of the archiving tools that are built-in to the GP Dynamics product. Thanks.

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Community Member on at

Like (0)

Report

Just as an FYI, CDA does not work if you're using replication

Was this reply helpful? Yes No
sandipdjadhav 18,306 on at

Like (0)

Report

Carl,

Few days before I saw demo of CDA and I believe it is possible to do archive older data more than 1 time.

Please correct me if I am wrong.

Thanks

Sandip

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

With Company Data Archive you generally run it once per year as you push the oldest year in the production company to the archive company.

Was this reply helpful? Yes No
Donnette Spencer 445 on at

Like (0)

Report

We required an enhancement for CDA to leave open sales orders and purchase orders in the parent company. CDA has a best practices document which they will give you. I strongly suggest running arcive in a test enviroment first. Here's the design change we asked for:

Required Functionality & Suggested Design

Change the SOP archiving to have the addition criteria of checking the RM open and RM History tables for invoices associated with the SOP Order as well as the SOP Invoice. If the SOP Order has an invoice in the RM Open table, it will not be archived. If the SOP Order does not have a matching SOP Invoice in the SOP History table, it will not be archived.

Change the POP archiving process to only archive documents that are cancelled or closed. Receipts for open purchase orders should never be archived.

Was this reply helpful? Yes No
Jack Harper 470 on at

Like (0)

Report

Wow. I would have thought CDA would already do that as-is. Thank you for taking the time to respond!

Was this reply helpful? Yes No
Donnette Spencer 445 on at

Like (0)

Report

You're welcome. The enhancment is currently in test. We have not rolled it to production.

Was this reply helpful? Yes No
John Lowther 5,122 on at

Like (0)

Report

Hey Jack,

Out of curiosity, how large is your database? Also, what are the sizes of the MDF and LDF files for that dtabase?

Was this reply helpful? Yes No
Jack Harper 470 on at

Like (0)

Report

VALL database Size : 80819.75 MB

VALL MDF File Size : 68006592 KB

VALL LDF File Size : 14752832 KB

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Standard CDA uses the same rules as GP does when removing PO’s and receipts from history. That doesn’t always make it right, but it is the way GP does it.

The explanation of the SOP makes it sound like CDA doesn’t check RM at all as part of the standard SOP archiving, but it does. This case needed additional restrictions because they have business that has additional requirements.

Sometimes companies think about doing a SQL backup/restore of live to create an archive company and then removing history in live as an alternative to CDA. This seems to be popular concept, but it isn’t a sustainable process. If you have live with years 1 to 7 in it, duplicate it, and then remove years 1 to 3 in the live that fine for now. Next year, what do you do? You have years 1 to 7 in archive, and now years 4 to 8 in live. If you do it again, you end up with 1 to 7, 4 to 8, and 5 to 8 across three databases. You’ve got all kinds of duplication/waste (years 4 to 7 are stored three times). It sounds like a good alternative, but really only works once, unless you have tons of disk space, and don’t mind looking in multiple companies to find old data.

Was this reply helpful? Yes No