With Auditing configured in the system, Dynamics 365 field level changes can be written to the AuditBase table in MSSQL. The challenge is how to maintain audit history without creating an unmanageable database size over time.
With SQL Enterprise, the AuditBase table is managed in partitions. A separate audit partition is created quarterly by the system. With appropriate user security privileges, Dynamics 365 provides functionality to delete the quarterly audit partitions, oldest first. However, deleting a partition removes the record level audit data for the related time period. Audited details for the time period represented by the audit partition will no longer be available in Dynamics 365.
As the AuditBase table doesn’t have any constraints that would be violated by moving data from the table, an audit partition’s records can be copied into an audit archive database, set to the same collation as the Dynamics 365 production database.
As the data in the AuditBase table contains all GUID and INT based values, a join between the archive database and the production database will support query-based access and reporting.
Over time, as audit data is archived before each audit partition deletion, the join between the audit and production databases continues to provide reporting.
What is Auditing and what is being Audited?
Designed to meet compliance, security and governance requirements, Dynamics 365 auditing can be configured to capture the following:
- When a user accesses a record and what changes are made
- Create, update, deactivate, and delete operation details
- Original attribute value versus new attribute value
- Changes to sharing privileges
- Record relationship changes
With the appropriate user security setting, system auditing configuration is accessible under Auditing in the Settings section of Dynamics 365. Entity and attribute configuration is made in the Customization section where custom fields and entities are created and added to the system.
Over auditing can impact database size significantly. To validate and adapt audit configuration, use XRMToolBox’s AuditCenter plugin. AuditCenter bypasses the cumbersome process of using system customization to review and edit system auditing configuration; editing Case Stage in this example.
Creating an Audit Archive Database in MSSQL Enterprise
Use MSSQL Management Studio to create a new database in which to archive Dynamics 365 audit records prior to purging audit partitions in Dynamics 365. Ensure that the new audit record archive database uses the same Collation as the D365 production database that will be the source of the archived audit records.
“Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.”
The Collation rules between the D365 production database and the audit record archive database must be identical to insure data consistency when running queries and reports against both datasets concurrently.
An organizational database administrator (DBA) should create the audit archive database as they are in the best position to duplicate the schema, determine collation and ensure any organizationally specific configuration and security is in place.
Archive Audit Records Prior to Purge
Although Dynamics 365 will create a new audit partition quarterly, it may not be necessary to archive audit records to the audit archive database quarterly. With analysis of how much audit data is being generated over time, it may be appropriate to archive and purge audit data biannually or yearly. Depending on analysis, it may be determined that maintaining two years of audit data in Dynamics 365 while maintaining older audit data in the audit archive database is the preferred approach. In such a circumstance, an annual audit data archive / purge procedure may be run at each year end. Regardless of regularity, the key is a documented procedure.
As there are multiple methods of copying a Dynamics 365 audit partition’s records from the production database to the audit archive database, an organizational database administrator (DBA) should identify and document the preferred procedure. MS SQL Management Studio menu commands and sql scripts are two distinct approaches. The PowerObjects infrastructure team is available to consult and write sql scripts to insure a repeatable procedure that ensures ongoing data integrity.
Create a Join Between Audit Archive and Dynamics 365 Production Databases
The critical factor in maintaining continuity and data integrity between the Dynamics 365 record and their related archived audit records, is a join between the Dynamics 365 and audit archive databases.
Each record in a relational database has a unique identifier, that is not associated with any other record. In MSSQL, the unique identifier is referred to as a GUID or Globally Unique IDentifier. No two records in Dynamics 365 will share a GUID. By extension, the GUID of the archived audit record will also not be associated with any other record in Dynamics 365.
The GUID is often referred to as the record’s primary key. In a relational database, when a record (a contact) is in relation to another record (a case), the GUID of the contact record can be thought of as the primary key and the GUID of the related case record can be thought of as the secondary key. A primary key pointing to secondary keys creates the relationship; in this a case a one to many relationship or one contact related to many cases.
As noted above, Dynamics 365 audit records include the GUID of the record on which the changes were made. If a contact’s email address is changed, the audit record (which will have its own GUID), will also reference the GUID of the contact on which the email address was edited.
A join will reference GUIDs between the archived audit record and the Dynamics 365 record on which the audited change was made. From an external reporting tool, all the archived audit records related to the GUID of a specific Dynamics 365 record can be returned by query and reported on.
A database administrator (DBA) will be able to create the join to leverage reporting tools used within the data warehouse strategy of the organization.
Purge Dynamics 365 Audit Partitions
Once the Dynamics 365 production audit data has be validated as successfully archived, the final step is to delete the archived audit partition or partitions.
With appropriate security privileges, a Dynamics 365 administrator can navigate to the Auditing section of the Settings area in Dynamics 365 where Audit Log Management is an available option.
As previously noted, Dynamics 365 creates a unique audit log partition for each quarter and the oldest partition must be deleted first. Partition deletion is completed by clicking on the oldest partition (a checkmark will show in the left column to indicate the partition selected) and clicking the ‘Delete Logs’ button.
There you have it! For more Dynamics 365 tips and tricks, subscribe to our blog!
Happy Dynamics 365’ing!