Is it possible to setup partitioning on a SQL Server 2012 publisher table, so that older data can be removed without affecting the same table on a subscriber table?

Table exists on Database A (SQL Server 2012 - app DB) to store a varchar(max) field that is populated by a web app, and never referenced again. Table is replicated to Database B (SQL Server 2012 - data mining DB) where it will be used for reporting/data mining.

Would like to partition table on both DBs by quarter and only have data from current quarter on Database A (Database B will have historical quarters).

  • Transactional Replication – user2966445 Jun 22 '15 at 14:24

You can override the way DELETE commands are replicated to the subscriber by using the @del_cmd parameter for sp_addarticle. You can specify a custom stored procedure that takes care of the archive logic or ignore DELETE commands altogether by setting it to NONE.

However, the rows kept at the subscriber won't be replicated any more if for some reason you re-initialize the subscription, which I suppose is not exactly desirable.

I don't think that replication is the right way to skin this cat. Probably a custom SSIS package would do the trick more cleanly.

| improve this answer | |
  • Thanks. I had wondered about the SSIS approach as well. Was thinking a nightly job that merges the Database A table partition into a staging table, where Database B picks up the data and truncates the staging table. Then, Database A merges partition with new date for next day's data. – user2966445 Jun 22 '15 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.