Hi,

For the extraction part, for pure speed purposes, you could try a simple SSIS package that exports the required data from your source DB to a Raw File and then an import from the Raw File to the Archiving database. This can be better performance-wise than doing an export/import on the fly, mainly because you can control the level of transaction between the two operations I would say. But the other methods you mention would work too. Plus RAW Files Import/Export are pretty fast to process in SSIS (they often showcase SSIS' speed using RAW Files are their sources).

For the deletion part, based on how much data you're removing from the tables, you could build a new temp table, move the required data (using BULK INSERT in T-SQL or SSIS etc. or any other fast methods) and flush the old table/rename the new one to the old one. Otherwise, you can maybe export the PK (assuming your table is not a heap) to another table and perform a delete with a JOIN on the primary key you just selected. However, this will take much longer if you have a lot of rows to remove at a time.

These are more "maintenance-mode" kinda methods though, if it's something you need to schedule very often, then T-SQL might be plenty enough based on the volume of data to move. The thing is that with T-SQL, you'll have to deal with remote servers/queries if I understand it correcly. That's why the export to RAW file to a share accessible to both servers could be a good way to reduce the time needed to do it.

Hope this helps,

Greg