Question: We are in the early stages of building a large C# MVC2 app (we also employ Sharp architecture and Nhibernate as part of the ecosystem) on SQL 2008 R2, and one of the requirements is that all database row versions are accessible for a given period of history.

We have toyed with the idea of a layout similar to:

id (PK)  

recordId  

versionId

and having each edit to a record result in a new record being created with the same recordId and an incremented versionId. Record display would then be done with something along the lines of SELECT ... WHERE recordId = X AND versionId = MAX(versionId)

Snapshotting on every transaction would not work (too many? and not accessible from within the application easily).

But we are curious as to what other implementations have been tried with success, or potential problems with our proposal.

Answer: 
Rather then versionId which requires you to do self-join to get max version, I would introduce the validFrom - validTo pair. This requires you to update (end) current record's validTo when inserting new version of row, but allows to easily select current data with `where @now >= validFrom and @now < validTo` or the data in any historical time.

You can either have these historical records in a separate table or not. If you want to have only one table which includes all versions of row, which is better maintainable and usable, you may want to have a look at SQL Server Partitioning (Partitioned Tables) which allow you to separate recent history from old history and optimize searching on it.
My enterprise also uses a trigger based approach for both auditing and history. In addition to the core table in the enterprise warehouse, every significant table has an audit table in a separate database. This audit table has 1 row for every transaction. Some of our tables also have a historical version in a third database. The Audit database is purely for after-the-fact trouble shooting and non repudiation -- but it is difficult and non-performant to query for data analysis. Our history database is optimised to answer point-in-time queries very efficiently. All of this is 100% scripted by a .net tool I wrote, so when we alter the schema or add new tables to history, we just re-script the affected triggers.