Alternative to Delete Trigger for tracking deleted records

.net provisioning sqlbulkcopy sql-server tsql

Question

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.

Any suggestions?

Accepted Answer

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?


Popular Answer

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



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why