CODEWITHSUNDEEP
sql-serverasp.net-mvclogging
Niklas
Niklas

Reputation: 13155

How do I log every data change that has been made in SQL Server?

I have a profile page (less than 10k users) and I need to track every change and update that is made through out the application, by the users. Whether a user updates the profile picture or adds an extra space in a "comments" field, I need to store the previous data.
In short: I need to store everything.

Is there some sort of "tracking-history-and-changes-in-sql-server" software or do I have to implement it myself?

If I have to implement it myself I can think of three ways to do it:

  1. I keep everything in the same table but I add a key column that specifies which row is active and which is old.

  2. I add a new table called history where I store the column name that had the change, when it was changed and what the old data was.

  3. I add a history table for each table in the database. It looks the same but only keep track of each tables history.

Has anyone had a similar problem and how did you solve it?

This was built using mvc 4 and it's a normal website.

EDIT
I'm mostly interested in existing solutions/software, but If there are none I would have to do it myself.
Has anyone used SQL Data Compare?

Upvotes: 4

Views: 4672

Answers (4)

Gambit
Gambit

Reputation: 223

Microsoft offers Change Tracking and Change Data Capture for awhile now. These technically offer the tracking of all your changes in your database, which suits your purpose. Just note that CT is available in most versions, whereas CDC used to be only available in Enterprise until SQL Server 2016, where they made it available for Standard too.

ApexSQL Log does pretty much the same as well, but if you're using SQL Server then it's integrated in your software already.

Upvotes: 2

Dragan
Dragan

Reputation: 21

have you considered enabling full transaction logging on your database and then using some of the log reading tools to monitor data changes. ApexSQL Log is by far the best log reader on the market but there are other solutions out there. SQL Log Rescue from Red Gate is free but it's only for sql server 2000.

Using this approach you dont need to make any other changes in your database or in your application since every transaction is automatically logged when database is in full recovery mode.

Hope this helps.

Upvotes: 0

Amir Ismail
Amir Ismail

Reputation: 3883

I used the third approach to do that but didn't create a history table for all tables of my DB but history tables for most important tables. You can use triggers to do that , create trigger for Update. You can read more about Triggers here and here

Upvotes: 1

Tom Squires
Tom Squires

Reputation: 9296

Where I worked last everything had to be logged fully. (working with goverment organisations). We never updated or deleted data.

What you would do is have a start date and an end date on each row. To do an update you would update the old data to have an enddate then insert a new row in the table. To do a delete you would put an enddate on the row with a null enddate. We also had an "updated by" column to put the userid

Upvotes: 5

Related Questions