I have to build a service for huge data replication that will provide our data warehouse. other servers host the source databases.
So far, I've been able to utilize the SqlBulkCopy class and the TSql Excepts statement in combination to implement the Update and Insert items into the warehouse.
I'm having trouble right now with record deletion. The only solution I could come up with for dealing with deletions was to build a custom trigger on the table, add removed data to a temporary table that my service could access, then remove from the warehouse.
Although there are several data replication options available, the business needs a tailored internal service.
Some of the tables that will be provided contain more than 100 million records. note
A Delete trigger wouldn't be all that horrible, in my opinion. 100 million records is indeed a huge number, however because the trigger is being executed on the server, SQL may optimize the execution route.
If you utilize another client-side method, there will be a cost associated with retrieving the data from the server and sending delete orders to the warehouse.
What specifically about a trigger bothers you?
Change tracking is already incorporated into SQL Server (at least as of 2008 R2, but I'm not sure when this capability was first made available). Continue reading at http://msdn.microsoft.com/en-us/library/cc280462.aspx.