Sql-server – Audit SQL Server database transactions

Don't bother tracking audit data on a per-field basis. It's much easier to have an audit table with the same structure as the main table and timestamp and user information tracked on the table.

This architecture has a few advantages:

• You can easily create a view across the main table and audit table to show a complete history includint current versions.
• It is relatively simple to implement the audit triggers - in fact if you hae a large number of tables you can write a utility to generate them from the system data dictionary.
• The audit tables can be put on a separate disk to reduce the I/O load on the disk with the main tables.
• It's very easy to reconstruct an as-at version of the record, rather than having to apply a list of field-level changes in reverse.

Audit information should include at least: user who created/changed the record, date/time of the creation/change, nature of change (insert, update, delete). You may want to use logical deletion (i.e. a 'Deleted' flag) if you have the option of doing so. Otherwise you need to capture the user and date/time from the session and put these on the deletion record, probably along with the last state of the record. If this is not available from the connection (often the case on N-tier apps where the user is not being impersonated at the database level) then you need to find some other way to get it.

The transaction log is a important method to restore your database in a specific time. If you have a large database > 500 GB and if you must restore your database from a full backup, this will cost very much time. Also if you full backup your database everytime, think about how long this backup could take.

A very easy concept for SQL Server can be: Set Recover Model of your Database Full

Create a Maintenance Plan(1) in SQL Server:

• Do FullBackup every Week maybe in D:\yourbackup\FullDBBackup.bak
• Do Differential Backup every two days in D:\yourbackup\DiffBackup.bak
• Do every 2 houers Backup your Transaction log in D:\Yourbackup\Tranlogbackup.trn

Create a Maintenance Plan(2) in SQL Server:

• Delete all older Files 8 days from D:\yourBackup*.bak
• Delete all older Files 3 Days from D:\yourBackup*.trn

In this case, you are able to recover your Database in a specifc time, very fast very easy. SQL Server will automaticly manage your "Backup" files, older files will be deleted after your specific time range.

I would suggest that you read about SQL Server Transaction Log here:

http://www.sqlservercentral.com/articles/Design+and+Theory/63350/

For using the Maintenance Plans in SQL Server just ask BING / google :D

you should build a small test db and test this before you go in production