Using Change Data Capture for Small Desktop Applications?

Question

A new client of mine has a small VB/Access database application written in 2002 that he wants rewritten to bring it more up to date and to support new features he has been wanting for some time. So, I'm going to convert it over to use C#.NET 2008 and SQL Server Express 2008 on the local machine with the ability to scale to using WCF and SQL Server 2008 on a remote server.

One of the new features he is interested in is maintaining and reporting on complete history of data changes over a period of time. In the past, I've done this through the use of triggers and stored procedures, and it is a pain in the @!#$.

I have had an itch lately to mess with the Change Data Capture features of SQL Server 2008. During my initial hour of playing with it, I realized that it creates a job in SQL Agent that by default runs every 5 seconds. It also seems to be a bit more of a pain when I need to change the schema of captured tables. Other than that, it seems much easier to implement than my original method. So, these are my questions:

1. Is this overkill for a small desktop application that might or might not eventually migrate over to a remote server?
2. What should I expect in terms of performance? As his database size increases, am I going to get more calls from him saying that his computer is running slow?
3. Are there any other gotchas with CDC that I should be aware of from anyone currently using it in production?
4. Does anyone have any links to their favorite ways of tracking changes over time that might be a better fit for a small desktop application?

Thanks,

Marc

Answer 1

CDC is only available in SQL Server Enterprise edition. so if you have express you can't use it and you'll have to remain with triggers.

Answer 2

While triggers are a pain to work with in many ways, you can use them layer on a transparent data audit history mechanism without any impact to the main codebase, by writing scripts that actually generate the triggers and history tables automatically. It's a fair amount of work, and I don't know of anyone who's done such a thing and open-sourced it, but it might be an interesting project. At least that way, you can write one generator procedure and then never have to mess with the individual triggers again.

Answer 3

I've used my own change-data-tracking system using an XML column in my change-table, makes it more flexible. Also makes the trigger rather generic.

Assuming you already have triggers to create audit rows and your source table has a column named "Version" of type ROWVERSION:

INSERT INTO [Changes].Sites
(
    SiteID,
    Operation,
    Version,
    ModifiedOn,
    DataChange
)
SELECT
    IsNull( I.SiteID, D.SiteID ),
    CASE
        WHEN D.[Version] IS NULL      AND I.[Version] IS NOT NULL  THEN 'I'
        WHEN D.[Version] IS NOT NULL  AND I.[Version] IS NOT NULL  THEN 'U'
        WHEN D.[Version] IS NOT NULL  AND I.[Version] IS NULL      THEN 'D'
        ELSE '?'
    END,
    IsNull( I.Version, D.Version ),
    SysDateTimeOffset(),
    (
        SELECT
            [Deleted] = ( SELECT * FROM deleted D1 WHERE D1.SiteID = D.SiteID FOR XML PATH(''), TYPE ),
            [Inserted] = ( SELECT * FROM inserted I1 WHERE I1.SiteID = I.SiteID FOR XML PATH(''), TYPE )
        FOR XML PATH('Changes')
    )
FROM deleted D
FULL JOIN inserted I
    ON D.SiteID = I.SiteID

The only thing in that query that's specific to my table is the primary key. Generating a template to create those queries would be rather simple (could even do it in SQL using sys.tables etc).