1. Create New Audit Database
2. Activate Service Broker on New Audit Database and your production database
3. Create stored procedure to apply triggers on tables for auditing
This database will hold new and old values of columns changed on the production database. Create a new database as AuditDatabase. It will have following Tables;
SET ANSI_PADDING OFF
2. Activate Service Broker on New Audit Database and your own database
Next we will create stored procedures to apply triggers on the tables we need to Audit on the production database.