What's the best way to allow queries/views to see how tables looked in the past based on a datetime stamp in SQL Server 2008?

Question

Scenario: We have a great deal of server environmental information (names, IPs, roles, firewall ports, etc.). Currently it's stored in a big Excel workbook on a SharePoint, which trivially allows for side-by-side comparisons of past versions of the data with current, for example.

we're planning to move it into a SQL Server 2008 database to make it easier for tools/automation to tap into as well as for better reporting. However, as you'd expect, one of the requirements given was that the admins would like to be able to see how an environment looked at some point in the past. Some piece of magic like: sp_getEnvironmentsAsOf('PERF1', '2009-11-14 00:00:00') and suddenly all the data that was current as of 11/14/09 is returned.

I'm looking into SQL Server 2008 Change Tracking and Change Data Capture, but all of the scenarios and examples don't see to relate to the specific requirement of seeing the data in the tables as they were at some arbitrary point in the past.

Is CT/CDC apropos? And what are the other options, beyond rolling my own solution out of ticky-tacky and hope?

Answer 1

What comes to my mind is 'snapshot' feature (snapshot shows you a state of you database at the time snapshot is created). However, snapshot looks like a different database (so you will query something like 'MyDBSnapshot_DATE' instead of 'MyDB' and it definitely takes resources [to track changes].

Other option is ... do it yourself.

Answer 2

CDC is appropriate but you might also want to look at AutoAudit on CodePlex.

Answer 3

You should design your schema to track this changes instead of relying on a dbms feature. Something like:

Devices
  Id
  Description
  Serial number
  Some immutable properties

Properties
  Id
  Description

Device-Properties
  DeviceId
  PropertyId
  Value
  TimeStamp

You never update or delete Device-Properties, you only add rows with a new timestamp.

Sample Data:

Devices
1,Server A,1123123
2,Server B,1323454

Properties
1,IP Address
2,Location
3,Rol

Devices-Properties
1,1,192.168.0.10,2010-02-12
1,2,Rack D,2010-02-12
1,3,Proxy,2010-02-12
2,1,192.168.0.105,2010-02-12
2,2,Rack C,2010-02-12
2,3,Mail server,2010-02-12
1,1,192.168.0.11,2010-02-15

In the sample data, Server A IP address was changed from 192.168.0.10 to 192.168.0.11 on 2010-02-15

You can construct views or stored procedures to join and filter the data as needed.