This article is contributed. See the original author and article here.
A well-defined data maintenance strategy improves the quality and performance of your database and reduces storage costs. In part one of this series, we covered the roles and responsibilities of your data strategy team, tools for reviewing storage usage, and data management features in Dynamics 365 finance and operations apps that your strategy should include. We recommended that you start your planning by decommissioning unneeded sandbox environments in your tenant. In this post, we focus on creating a data retention strategy for tables as part of your overall storage maintenance strategy.
Create a data retention strategy for tables
After sandbox environments, tables have the greatest impact on total storage volume. Your data maintenance strategy should include a plan for how long to retain the data in specific tables, especially the largest ones—but don’t overlook smaller, easily manageable tables.
Review table storage by data category
In the Power Platform admin center capacity report for the production environment, drill down to the table details.
Identify the largest tables in your production environment. For each one, determine the members of your data strategy team who should be involved and an action based on the table’s data category. The following table provides an example analysis.
| Data category and examples | Strategy | Team members |
|---|---|---|
| Log and temporary data with standard cleanup routines
SALESPARMLINE, USERLOG, BATCHHISTORY, *STAGING |
This category of data is temporary by design unless it’s affected by a customization or used in a report. Run standard cleanup after testing in a sandbox. Note: If reports are built on temporary data, consider revisiting this design decision. |
• System admin • Customization partner or team if customized • BI and reporting team |
| Log and temporary data with retention settings
DOCUHISTORY, SYSEMAILHISTORY |
This data is temporary by design but has an automatically scheduled cleanup. Most automatic jobs have a retention setting. Review retention parameters and update after testing in a sandbox. | • System admin • Customization partner or team if customized |
| Log data used for auditing purposes
SYSDATABASELOG |
Establish which department uses the log data and discuss acceptable retention parameters and cleanup routines. | • System admin • Business users • Controllers and auditors |
| Workbook data with standard cleanup routines
SALESLINE, LEDGERJOURNALTRANS |
Data isn’t temporary by design, but is duplicated when posted as financial. Discuss with relevant department how long workbook data is required in the system, then consider cleanup or archiving data in closed periods. | • System admin • Business users related to the workbook module • BI and reporting team for operational and financial reports |
| Columns with tokens or large data formats
CREDITCARDAUTHTRANS |
Some features have in-application compression routines to reduce the size of data. Review the compression documentation and determine what data is suitable for compression. | • System admin • Business users |
| Financial data in closed periods
GENERALJOURNALACCOUNTENTRY |
Eventually you can remove even financial data from the system. Confirm with controlling team or auditors when data can be permanently purged or archived outside of Dynamics 365. | • System admin • Controllers and auditors • Financial business unit • BI and reporting team for financial reports |
| Log or workbook data in ISV or custom tables
Should start with the ISV’s three-letter moniker |
Discuss ISV or custom code tables with their developers. | • System admin • Customization partner or team • ISV • BI and reporting team, depending on the customization |
Consider whether table data needs to be stored
For each large table, continue your analysis with the following considerations:
- Current business use: Is the data used at all? For instance, was database logging turned on by accident or for a test that’s been completed?
- Retention per environment: Evaluate how long data should be in Dynamics 365 per environment. For instance, your admin might use 30 days of batch history in the production environment to look for trends but would be content with 7 days in a sandbox.
- Data life cycle after Dynamics 365: Can the data be purged? Should it be archived or moved to long-term storage?
With the results of your analysis, your data strategy team can determine a retention strategy for each table.
Implement your data retention strategy
With your data retention strategy in place, you can start implementing the actions you decided on—running standard cleanups, updating retention settings, configuring archive functions, or reaching out to your ISV or customization partner.
Keep in mind that implementing an effective strategy takes time. You need to test the effect of each action in a sandbox environment and coordinate with multiple stakeholders.
As you implement your strategy, here are some best practices to follow:
- Delete or archive data only after all stakeholders have confirmed that it’s no longer required.
- Consider the impact of the data life cycle on customizations, integrations, and reports.
- Choose the date range or the amount of data to target in each cleanup or archive iteration based on the expected duration and performance of the cleanup or archiving routine, as determined by testing in a sandbox.
Need more help?
Creating a data maintenance strategy for Dynamics 365 finance and operations apps is a complex and ongoing task. It requires a thorough analysis and collaboration among different roles and departments. For help or guidance, contact your Microsoft representative for a Dynamics 365 finance and operations storage capacity assessment.
Learn more
Not yet a Dynamics 365 customer? Take a tour and start a free trial.
The post Create a data maintenance strategy for Dynamics 365 finance and operations data (part two) appeared first on Microsoft Dynamics 365 Blog.
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments