Single or Separate database for Audit

Question

Hi,

I would like to know if there is any advantage of having the Audit Trail on a separate database and not on the transaction database. I am not a DBA, so I am not familiar with resource pool or additional cost/space of having a database. 

Advantage/Disadvantage of having the Audit trail on Same/Separate database. Please help!

-Shreepal

Answer 1

I think like- create separate database not in the same database server rather than other server (this server should act like central management server) .

so that you can manage the growth,logging,purging ,recovery/backup, resource contention(like cpu,mem,i/o,accesses etc.. if you are going to logging more).

I am not a DBA, so I am not familiar with resource pool or additional cost/space of having a database. 

>>you just need to ensure capacity for database/growth & resource contention/utilization where you are going to create along with managing that database,ofcourse proper purging should be in place.

Also in SQL there are other audit(instance,database,login) related exists like Extended events,CDC,Logon audit,DDL,DML(triggers) etc..

---

Regards, S_NO "_"

Answer 2

Shreepal

The most obvious reason to have your audit in a different database is that you then separate the users that did the actions from the record of those actions and so you reduce the chance that a malicious user with UPDATE or DELETE permission on the audit tables can remove audit information and therefore rendering the audit useless.

Have the audit trail in a separate database and reduce all permissions to a minimum.  I hope that you restrict strongly the number of users with Instance level permissions such as sysadmin role membership?

---

Martin Cairney SQL Server MVP

Answer 3

Hi Martin,

I can still restrict the users by moving my Audit tables to Audit Schema. I would like to understand if there is any advantage of having it in a separate database or any disadvantage of having it in a separate database. 

Based on that I can justify the decision for Audit trail database design either same db or separate db.

-Shreepal

Answer 4

There is no technical difference between having the audit/history tables in the same or different database on the same server.  You would have all the same features and/or restrictions available.

The only thing is possibly easier maintenance on the audit database because you could setup a different backup strategy for the audit database vs the live database.  You could do the same thing, more difficultly, by doing file group backups in the same database.