Alternative to Delete Trigger for tracking deleted records

I've got a task to create a large data replic开发者_开发问答ation 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?

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?

From what I'm reading, you're trying to re-invent replication (http://msdn.microsoft.com/en-us/library/ms151198.aspx). Does that about sum it up? If so, my suggestion would be not to.

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

I might not understand entirely what you are doing but in SQL Server you can capture the rows that are deleted with the output clause. Perhaps this is something you can use.

-- Table to delete from
declare @T table (id int, name varchar(50))

-- Table to capture the deleted rows
declare @DeletedRows table (id int, name varchar(50))

-- Dummy data
insert into @T values
(1, 'Name1'),
(2, 'Name2'),
(3, 'Name3'),
(4, 'Name4'),
(5, 'Name5')

-- Delete every other row
delete from @T
output deleted.id, deleted.name into @DeletedRows
where id % 2 = 0

select *
from @DeletedRows

Result - Deleted rows

id          name
----------- --------------------------------------------------
2           Name2
4           Name4

I've had good experience using triggers to insert record IDs into staging tables. We then created a set of SSIS packages and SQL Agent jobs poll the staging tables and take appropriate action based on the contents of the staging table. This made it possible to implement custom. Since we were moving data between an OLTP system and a warehouse, it made a lot of sense - records don't always line up row for row.