CODEWITHSUNDEEP
.netsqlsql-server-2008entity-framework-4change-tracking
Dylan Vester
Dylan Vester

Reputation: 2726

Ways to Maintain Data History in SQL Server 2008 Database

For a long time, we've wanted to create a case management system where no history is ever lost. When a change is made, we want to record that change, but have the ability to go back to any point in time and see what the record looked like. I wanted to pose this question to the Stack Overflow community to see what are some ways of doing this, is there technology already in place to achieve this?

Upvotes: 9

Views: 5181

Answers (2)

utexaspunk
utexaspunk

Reputation: 390

I'm not sure how a temporal database like marc_s mentioned works, but if you're using SQL Server 2008 or later, you can take advantage of its built-in Change Data Capture (CDC) functionality:

Enabling CDC uses the replication transaction log to store the inserts, updates, and deletes for a table and creates table-valued functions which allow you to retrieve the rows as of a given date/time, or to retrieve just the changes.

You can't rely on CDC alone, though, because your transaction log will become unmanageably big and slow. So what you do is:

  • enable CDC,
  • create a history table using the same schema as the original table, but adding a couple more columns for storing row version information (much like a slowly-changing dimension in a relational OLAP database), and
  • create a job which will periodically polls the CDC functions for changes since its last load and pushes them to the history table

Then you can then use the history table in your queries, joining to it as you normally would, but with an additional predicate(s) to get the record "as-of" whatever date you want.

Upvotes: 3

marc_s
marc_s

Reputation: 755321

Yes, that technology definitely exists - it's a bit of an effort to implement it and do so correctly.

What you're looking for is called temporal databases - see some resources:

Upvotes: 8

Related Questions