Maintaining a timely warehouse
in [Replication]
|
Prev: Unable to synchronize the row because of inadequate permissions
Next: transaction replication failing
From: Joseph on 30 Mar 2010 13:01 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
From: Ben Thul on 30 Mar 2010 14:02
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 On Mar 30, 12:01 pm, Joseph <josephshepp...(a)gmail.com> wrote: > 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 |