From the way I see it, you have two pretty good options here:
- Log Shipping
If you setup transactional replication you could have your data modifications replicated to a subscriber database (or databases).
With log shipping it essentially would be transferring transaction log backups (note, you'd need to have your database in the full recovery model to take transaction log backups). Then you could have your secondary database in standby mode, allowing for read-only traffic.
The way I would choose between these two options is the level in which you want reporting and data movement to your secondary server. Are you reporting on the entire database? In that case, I'd opt for log shipping. But if you're primary reporting on a single table or just a handful of tables and you don't necessarily want the entire database's footprint living on the secondary server, then replication would be a better option.
As per your comments below, it looks like you don't want to transmit
DELETE operations to your reporting server. In this case, Log Shipping wouldn't meet the requirements because all logged operations would be on the reporting server, including your
What you can do with replication is set it up so that it only replicates
See the below screenshot. Basically when you create your publication, you can set many properties for each article. One of the options is what action to take for each operation:
DELETE. All you need to do is specify to not replicate