I'm developing an application which requires multiple replicas and uses syncrhonization to keep replicas up to date with each other.
To do this, I'm taking advantage of SQL Server 2008 change tracking feature.
The one hole in this strategy appears when tracking who made a particular change in a replica. SQL Sever 2008 provides for this through something called a "change context". The trouble is that making this context available to a set of DML statements requires that the SQL be prepended with a custom clause ("WITH CHANGE_TRACKING_CONTEXT"). I'm using NHibernate and am looking for a way to inject this clause. So far, I can't find one other than native SQL.
I've looked at interceptors, events and custom dialects. I can't find how to do this with these approaches, however, and it's not clear to me if I'm just missing something or these methods really are inadequate for my need.
Any suggestions?