4

I know it is possible to get the changes to a single table in SQL Server 2008 using change tracking. However I have not been able to find an acceptable way to query all the changes in the entire database in a single query.

Is this possible?

What are your thoughts on writing some dynamic SQL and using exec to get this done? Are there any example of this you can point me to?

Are there any other alternatives to monitoring specific column/row data changes in SQL Server 2008 which require lesser effort than writing and maintaining triggers? Our DB has more than 1500 tables and this is not an option.

Thanks

SharePoint Newbie
  • 5,974
  • 12
  • 62
  • 103

1 Answers1

2

When I started using CDC this URL helped me to get started: http://www.mssqltips.com/sqlservertip/1819/using-change-tracking-in-sql-server-2008/

The disadvantage with CDC is that you can't see who changed the data, only that the data has changed and what the previous data was. CDC is only available in Enterprise, Developer and Evaltion editions.

In cases where I have to audit specific tables for data changes you can also use the Audit functionality that's available since SQL Server 2008. Audit can be enabled for a database and you can see which command was executed by who. The disadvantage is that you can only see the command executed and not what data was present before the command was executed.

This website helps you decide which functionality is better for your situation. It describes the different methods and their pros and cons: http://solutioncenter.apexsql.com/tag/methods-for-auditing-sql-server/

I always avoid using triggers because these slow the processes down for inserting, updating and deleting of records.

I hope this helps you out a bit

Sander Stad
  • 246
  • 4
  • 8