Question Storing Auditing Data in Production Databases

KMoody

Member
OpenEdge documentation recommends archiving auditing data separately from production databases. I'd like to get some clarification on the reasons why.

(Context: Our production databases are 4.8 GB, and our archive database is 10 GB. We are not legally bound to use auditing; for us, it serves as a log. Once a week, we archive auditing from production into a separate audit storage database on the same server. Once a month, we archive data older than three years from the audit storage database.)

Source: "It is, however, your choice whether or not to move the audit data. If the database size is not an issue, you might not want to move the data; however, the longer the audit data resides in a database along with the application data, the higher the risk of data corruption."
Why is audit data in a production database at a high risk of data corruption over time? How does moving auditing data to a separate database prevent corruption?

Source: "To keep your production database from growing too large too quickly, and for greater reporting capabilities (remember, Progress recommends only minimal indexing of the audit tables in your production database), you are going to want to archive your audit data to a long-term storage database "
Let's say we left auditing data in the production databases and activated all audit table indexes. If audit table indexes were in their own storage area, would auditing reports or the production databases' performance suffer?

Finally, given the sizes of our databases, why might we want to keep auditing separate from production?

Thanks for your help!
 

TomBascom

Curmudgeon
The corruption commentary seems specious. I have no idea why that is there other than that a bigger target is more like to be hit by a random event. Which seems kind of "meh" as a reason to split it into another db.

Having mostly static data account for most of your database seems awkward. Ok, it's only 15GB total so maybe it isn't a big deal in your case but your backups (and restores) are taking 3 times longer than they need to. For instance. Dump & load will take 3x longer (if you d&l the auditing data). Etc.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Let's say we left auditing data in the production databases and activated all audit table indexes. If audit table indexes were in their own storage area, would auditing reports or the production databases' performance suffer?
The point about deactivating non-essential audit indexes in production (there is a KB article about this that explains which ones are essential) is to minimize the performance overhead of auditing on the OLTP performance of the production application. When auditing is enabled, every audited event results in writes to the audit tables and indexes, in addition to any writes that may otherwise occur as a result of the event itself. If all audit indexes are active, as opposed to only some, then there are more index key inserts and thus more performance overhead due to auditing.

The basic principle is to leave enabled only those indexes that are required to do auditing, i.e. to write the audit data and permit archiving of it. Then, in the audit archive database into which the data is loaded, you enabled all the indexes to permit audit reporting. And you can even define new indexes if you like, depending on the data you write and how you wish to report on it.

In answer to your question, if the audit indexes are in a dedicated audit index area, as opposed to an audit area (tables and indexes), you're still going to have additional overhead if you have more indexes active as opposed to fewer.

Other points about archiving audit data to a separate archive database as opposed to leaving in the database: first, if you are auditing multiple databases, you can consolidate their audits into a single archive database, as opposed to having the audit data spread across multiple databases. Second, you can enforce separation of concerns by allowing application users logical access only to the application database and no access to the audit database, and do the opposite for audit users (auditors), who should never be application users. Third, over time the size of audit data can become much larger than the actual application data, especially if there isn't a well-defined and enforced audit data retention policy. I've seen it happen. Keeping audit data in a separate database keeps this growth from happening in the prod database, preventing you from having to do extra space-management work there if the audit tables grow large, and preventing your prod backups from growing larger and larger over time, as Tom indicated.

Some of the points above may not be relevant/important to you if you are using OE Auditing more as a logging feature than as a true audit trail.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
KB: What audit-related indexes can be deactivated?
Knowledge Article

_aud-audit-data, _AppContext-Id
_aud-audit-data, _Connection-id
_aud-audit-data, _Event-context
_aud-audit-data, _Event-group
_aud-audit-data, _EventId
_aud-audit-data, _Userid
_aud-audit-data-value, _Field-name
 
Top