Track SQL Server 2005 meta changes?

Question

I know SQL Server 2008 can do this, but essentially I need a way to log all the changes made to a database. I don't need to log selects, and I don't need to log the user, the only important data is what has been added or changed, both with regard to data and structural changes like columns, tables, and indices.

What are my options?

Answer 1

Event Notifications can be deployed to monitor all schema changes at the database and even entire isntance level.

Global gata changes is not possible to monitor. You can select specific tables to monitor and deploy a trigger based monitoring. There are also low-impact log bassed solutions, but not out-of-the-box, they all need third party tools.

Answer 2

I've used AutoAudit quite a bit, you simply apply it to whatever tables you wish to audit.

Main drawback is that it requires a single column PK. But most of my tables have surrogate identity PKs, so it's fine for that design philosophy.