I've got a task to create a large data replication service that will provision our data warehouse. the source databases reside on other servers.
So far, I've been able to implement the Update and Insert items into the warehouse using the conjunction of the SqlBulkCopy class, and the TSql Excepts statement.
my problem now is handling record deletion. the only thing I can think of for handling deletions was to create a custom trigger on the table, to insert deleted records into a temp table that my service could read, and remove from the warehouse.
I know there are a lot of data replication tools out there, but the company wants a custom in house service.
note some of the tables that will be provisioned are over 100 million records.
I don't think a Delete trigger would be all that bad. 100 million records is definitely a good chunk, but you're running the trigger on the server, in a way that SQL can optimize the execution path.
If you do something else client side, you're going to incur overhead for fetching the records from the server and then issuing delete commands to the warehouse.
What is it about a trigger that is bothering you?
SQL Server has built-in change tracking (at least in 2008 R2, I'm not sure when exactly this feature was introduced). Read more here: http://msdn.microsoft.com/en-us/library/cc280462.aspx