Roll back schema sync if data sync fails?

Automate and integrate using the SQL comparison API

Moderators: Chris Auckland, David Atkinson, Michelle Taylor, chengvoon.tong

Roll back schema sync if data sync fails?

Postby StuM » Wed Oct 10, 2012 4:14 pm

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
StuM
 
Posts: 5
Joined: Thu Sep 13, 2012 2:17 pm

Postby james.billings » Thu Oct 11, 2012 5:21 pm

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.
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby StuM » Fri Oct 12, 2012 10:17 am

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.
StuM
 
Posts: 5
Joined: Thu Sep 13, 2012 2:17 pm

Postby james.billings » Fri Oct 12, 2012 10:24 am

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)
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.


Return to SQL Comparison SDK 10

Who is online

Users browsing this forum: No registered users and 0 guests