Roll back schema sync if data sync fails?

StuM

Hi,

I'm trying to find a full proof way to handle failures in a data sync. I am trying to sync the schema but then only sync the data of certain tables/columns between two databases.

In some scenarios I am expecting the data sync to fail due to constraint errors, etc. which rolls back the sync however the schema is already synced by that point.

So, does anyone know of any way to combine the two sync actions so that a roll back will roll back schema changes also? Or a way to roll back a schema sync once it has completed?

The best option I can come up with outside of the SDK is to backup/package the database beforehand and restoring it if necessary but I want to avoid that if possible.

Another option I can consider is to perform the syncs myself after using the SDK to do the comparisons and generate the SQL then I can combine the two sets of SQL into a single transaction. I'd much rather let the API handle running the SQL though.

Thanks

James B

Hi,

The two processes (data and schema) run completely separately, so you can't get the schema change to just roll-back ad-hoc should the data fail.

Rather than backing up the whole DB, you could look at creating a snapshot which contains only schema. Do this as your first operation (refer to the SDK Sample "LoadAndSaveASnapshotExample.cs" for details) then, should the data sync fail, do a schema compare from Snapshot > Database.

StuM

Hi James,

Thanks a lot for the tip, I will look into snapshots. In my case I can't snapshot just the schema because the sync would be dropping columns/tables as well as creating them so data could potentially be lost from the target database in the initial schema sync that would not be restored in the roll back.

James B

Yeah, if you're in that scenario and need to roll back the data that was dropped; then you're looking more into backup files (or maybe schema and data script folders which the SDK should be able to automate too)