2

I'm very sorry if this is the wrong place to ask this question.

I have been tasked with implementing a system to allow administrators to make changes to data in a database, and save these changes into "states" or "plans". The states need to be compare-able, with a final state chosen to become the "primary state" and be used for all future queries.

Normally, for things like this, I would duplicate the database and make changes there, but the database I have been entrusted with is extremely large and impossible to duplicate quickly. I'm using MS SQL Server, and interfacing with Entity Framework in .NET.

Do any of these tools provide inclusive solutions to this problem? How could this be done realistically? A coworker has suggested version control, which sounds promising, but I'm wondering if there is a better way.

8

SQL Server provides various ways to maintain states of the table + you can design your own:

  1. Change data capture : CDC is use to capture insert, update or delete operation on a table. But remember, it does not support computed columns.
  2. Change tracking: This also capture DML statements like CDC but does not hold historical data.
  3. SCD Type 2: By creating your own historical data change table by using timestamp and mark the latest record as active.
  4. Microsoft Sync Framework : You can use this if you want to have a replication of database. I used it when I had to do the sync between offline SQL Server to online. However, this may cost effort in implementation and also it will not provide you all the features you are looking for.

For future releases (i.e. SQL Server 2016 and above)

  1. Temporal Tables: I did use that in my experiments on SQL Server 2016 CTP 2 and found it promising. In a way you can consider them as SCD type 2.

If I have to choose at your place than I would go for CDC.

  • This is some great info, thank you very much! – Scott Sep 11 '15 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.