Maintaining reports is challenging for dba’s most of the time. The requirements are unique and specific across various environments when it comes to keeping the transactional data and reporting data in the same database. In order to keep the transactional databases slim and to enhance their performance often dba’s get requests to separate the reporting database. We had a similar situation. I have identified the tables required for reporting purpose. Data retention for this database was 1 year and it was only a week for the transactional database. So the question was if I implement transactional replication then how would I prevent the deletes in the reporting database. Fortunately, there is a way to prevent deletes and replicate only the inserts and updates in the reports database.
Below are the steps:
1) Expand the replication folder in SSMS and expand the local publications.
2) Right click on the publication name. Go to properties and select articles on the left hand side.
3) As shown in the below screen shot check on “show only checked articles in the list”. Click on the “Article properties” and select “Set properties of all table articles”
4) Now scroll down to the statement delivery section and in the delete delivery format select “Do not replicate DELETE statements” as shown below and hit ok. This requires subscriptions to be reinitialized. Click on “Mark for reinitialization”
5) Now to ensure that the relationships exists between the subscriber database tables in the “Copy objects and settings to subscriber” area change the “copy foreign key constraints” value to True as shown below:
Now only inserts and updates are replicated but not deletes.
Hope it is useful