0

Our database is getting really big, and I was thinking about inserting some auditbase data into a different table, let's say, auditbase_recover.

This way we can continue to delete old logs, but if it's necessary, we can still search some old info that's not in the auditbase anymore, and it could be in this new table.

We would insert data daily in this auditbase_recover table.

I already did a job to log less information which help us A LOT.

and I don't want to restore the entire database, to retrieve a simple info of 3 years ago.

(I'm the DBA here, and I'm trying to learn more about CRM. Sorry if I'm talking nonsense).

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Racer SQL
  • 207
  • 1
  • 3
  • 14
  • I was messing with the table and I think this is not going to work, but it will be good to know if we can do that ( change the table that the CRM retrieves a LOG). – Racer SQL Dec 01 '20 at 12:49

1 Answers1

1

OOB audit cannot be directed away from auditbase table. That being said, you have to fine tune the entities, attributes being audited unnecessarily. Classify the business sensitive and long-duration CRM UI needed audit data vs blame-game audit data and drive the solution.

Then alternately you can do custom audit like you’re doing or purge the audit data into an on-premise SQL for reporting purpose using ETL or replication.

You can even log data into Azure AppInsights or similar kinda logs for cheaper blob storage.

Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
  • Thanks Arun. We are currently using CRM on premises in a SQL Server database on premises too. `purge the audit data into an on-premise SQL for reporting purpose using ETL`. Is there a way to "decrypt" that data inside the changeValue column ? and filter by date? Thanks.( is this what you mean ?) – Racer SQL Dec 01 '20 at 17:50
  • @RacerSQL if you want a good SQL data structure from this delimited values structure - refer my answer: https://stackoverflow.com/a/45492608/7920473 – Arun Vinoth-Precog Tech - MVP Dec 01 '20 at 17:55