Maintaining a timely warehouse

[Joseph]

I want to try to use Transactional replication to build a warehouse of
our 90 databases....

I've got the warehouse shell set up, and publications that Keep
existing data when they sync. All databases are identical in schema,
and they now contain a Location column that is now used in the primary
key. So far so good: I have two databases pouring data into the
warehouse.

My question is...What happens when I need to drop and re-add the
subscription to one of the databases? Will I have to execute separate
queries to delete all of the old data out for that one location? That
would be one query per table, deleting everything with a specific
Location value.

It's doable, but is another maintain task to keep track of, not to
mention it may be expensive...

Thanks!

-Joseph

[Ben Thul]

You could get creative with the @creation_script parameter to
sp_addarticle to not only create the schema at the subscriber, but to
delete the data for that particular Location. You might also consider
partitioning by the Location so that such deletes are quick(er).
--
Ben