0

We have a shop floor database OPERATION that replicates selected data to a database BUSINESS that is used for reporting. The data in OPERATION is deleted daily by the third-party shop floor application so in order to retain the data on BUSINESS I've set the Article Property for DELETE delivery format to be Do not replicate DELETE statements.

This works well, but occasionally somebody wants something extra/different to be replicated. Depending on the nature of the change to the Publication it may prompt for Reinitialization of the snapshot which would of course blow away the database on BUSINESS (as I sadly did one day).

What's the best way around this?

Wayne Ivory
  • 321
  • 2
  • 8

1 Answers1

1

I would suggest you implement an ETL process instead of replication.

You can use SSIS to extract data out of OPERATION database and copy it to BUSINESS database. In the SSIS package you have full control over the logic. For example, you can append the data to existing data in BUSINESS. You can use MERGE, to insert new records and modify existing ones (this way it would be safe to run it repeatedly as the unchanged data would not be overwritten).

If someone requests additional data, you would just wrote a new SSIS package to transfer additional data without affecting your main process.

SSIS can be scheduled to run from a SQL agent job (use dtexec for example).

under
  • 2,519
  • 1
  • 21
  • 40
  • There are a couple of reasons I particularly want to stick with replication. 1. Its impact on the Publisher is low which is important for our realtime operation. – Wayne Ivory Dec 06 '16 at 07:10
  • 2. The time of day at which the third-party application deletes data from the OPERATION database is not necessarily consistent so in the scenario you propose I could potentially miss capturing some records. – Wayne Ivory Dec 06 '16 at 07:16
  • What I'd suggest then is to replicate everything to a "staging" database, and then use that as a source for your ETL process to a reporting database. This way you would have best of both worlds. Replication that seldom changes (only when there are changes to OPERATION database), and full control over what is transferred for to BUSINESS database. – under Dec 06 '16 at 08:55