How to ship new rows from the source to a target server? 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.
(edited: I forgot mentioning, the target server is mostly offline)
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.
It sounds like in this case, a simpler solution might be best for you, such as scheduling a daily SQL Agent job that INSERTS INTO the Target Database's Table the 10 million rows you need from your Source Table. If you're always deleting those 10 million rows from the Source Table after they get sent to the Target Table then it sounds like a pretty simple INSERT statement.
You'd have to remotely INSERT INTO the Target Table which can be accomplished via a number of means such as with a Linked Server and/or OPENQUERY.
Part of your SQL Job will need to manage exceptions for if the Target Server is offline, based on your own retry / abort logic.