Pseudo-replication from read-only cloud SQL database to local SQL copy

Question

A vendor has provided us with read-only access to a cloud SQL (2014) database for reporting purposes.

We originally built some local reports off this database directly, but that proved too slow and inflexible.

Then we wrote some PowerShell scripts to drop and re-import a dozen or so needed tables to a local SQL 2019 server, and that definitely gives us the reporting performance and flexibility we need, but now has also proven too slow, as it tries to re-import million-row tables several times per day.

We are now discussing a new version of our process that uses the MODIFY_DATE column of the large tables to only import new rows, but will also have to handle modified rows. Not too hard, but also not trivial.

Am I on the right track here? If these were both under my control, I'd have set up transactional replication from the very beginning, but I don't have any ability to modify the configuration of the vendor's source database.

None of the fancier replication options would work here, would they? (Pull replication, Merge replication, etc.)

Are there clean options I'm missing? Does SSIS have easy ways to do this kind of "upsert" logic for scenarios like this?

Answer 1

None of the fancier replication options would work here, would they? (Pull replication, Merge replication, etc.)

You don't really have many options if it's a read-only database and you don't have access to the server. Replication (and most instance features) will require making changes to the source the database. You basically have to develop something that's external from the source database, whether that be in SSIS, a raw SQL process scheduled with an Agent Job for example, or via application code.

We are now discussing a new version of our process that uses the MODIFY_DATE column of the large tables to only import new rows, but will also have to handle modified rows. Not too hard, but also not trivial.

Cool, that works. You should just treat all rows with a MODIFY_DATE greater than the last time you synced as an INSERT and can attempt to DELETE said row by its key in your destination prior to that INSERT. This would handle UPDATEs for you besides handling new rows.