2

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.

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
2
  • 1
    I agree. We don't know about UPDATE (if those occurs) but DELETE doesn't happen. So this should be a pretty trivial thing as a do-it-yourself. Possibly complement it with a "high watermark" table (or would that be "low"?) where you record the highest copied row ID so you know where to pick up. Or just read the current max on the target.
    – Tibor Karaszi
    Jan 27, 2021 at 14:01
  • 1
    @TiborKaraszi Yea agreed. There's a number of equally well implementable approaches to this one with a do-it-yourself solution, which is why I didn't get into too many details, but personally I'd just reference the max ID value from the Target Table when doing my INSERT to minimize the amount of objects needed in the workflow. To each their own though.
    – J.D.
    Jan 27, 2021 at 17:40
2

One thing you can play with is Transactional Replication. The replication process (Agent; Log Reader) sniffs the transaction log and generates DML commands based on that and then apply those DML commands on the Subscriber. You can decide whether to "ship" DELETE, for instance when you configure your replication.

OTOH, one can have the opinion that replication "litters" your SQL server, and it also puts some limitations on the source database (regarding what DDL you can run). So make sure you play with it and make yourself comfortable with it in general before applying it on your production server(s).

Roll your own can be considered a "cleaner" solution. But you'd spend more time to develop that solution. Features in SQL Server that might assist you would be Change Tracking (which rows were modified since last time I checked) and Change Data Capture (what modifications were done ...).

Improve this answer
2
  • I forgot to mention, that offline solution is almost mandatory, as the target server is many times offline.
    – g.pickardou
    Jan 27, 2021 at 8:17
  • I see. As you say, any solution based on backup won't fly. I also think you can count out replication, considering the offline aspect. That leaves you with some homegrown solution, possibly aided by CDC or CT. And there might exist 3:rd party products in this space, of course.
    – Tibor Karaszi
    Jan 27, 2021 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.