I have a large table in MS SQL that I am having issues reading from while a third party application writes to it. My plan to resolve the problem is to move all of the data from one database and delete the data in the source database periodically. The problem arises when the table has too much data ( hundreds of millions of rows), but I need to be able to read all of the data for a rows which could top a billion rows. I don't have the ability to modify the source database because it is third party.
I'm curious what some options might be to help facilitate this. Is it possible to replicate a table in a way that when I delete the data in the source table, it's not deleted in the destination table?