Context
We have a SQL Server ‘source’ where daily 10M rows inserted to a table. No updates. I would like to ship the new rows to a ‘target’ server, then delete those (sooner or later) on the source where disk space is limited. PK is monoton increasing identity column.
Question
Besides of programmed export import which based on administering which PK ranges which were shipped, is there any more efficient solution?. I have very limited DBA knowledge, my first idea was differential backup with recovery model simple, then some log shipping with recovery model full, but I think both cases the deletes on the ‘source’ will by applied on the ‘target’ which I do not want to happen.
