Suggestion on how to implement a custom rollback

Question

Suggestion on how to implement a custom rollback

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

demonfox SSCertifiable Points: 6289 More actions · Answer 1

MyDoggieJessie (8/6/2012)
It's an integration services control "- Slowly Changing Dimension"

Apart from ssis control , It's a theory and can be implemented out of SSIS .

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

To add to what DemonFox has already stated, take a look at the following article. My favorite SCD (Slowly changing dimension) is Type 2 and it can be implemented, in most cases, with a very high speed trigger so you can "set it and forget it".

http://en.wikipedia.org/wiki/Slowly_changing_dimension

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

demonfox SSCertifiable Points: 6289 More actions · Answer 3

haiao2000 (8/6/2012)
but that has another issue of its own if bad feeds are received, then the rollback may need to be done immediately.
Regards,

when you say bad feeds , it means files. I didn't get your point. are you referreing the feed to a back end loader or feed generated through user rollback request ?

user rollback request could be handled in the database as you would be having the data stored, i prefer in the database , not in files. If somehow the backend loader fails then the process stops there itself.as no newer version is avaliable to mark the previous one a step lower ; you may have to think upon the purging deprecated data as per business.

Please correct me if I am assuming something unrelated to your requirement.

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

rVadim Hall of Fame Points: 3969 More actions · Answer 4

haiao2000 (8/6/2012)
Yes feed means files. I think location is probably be configurable. I would predict each client config differently.

Then another way is to implement this as a "File Management" solution.

1. Don't discard files after loading, instead save them to a predefined archive location.

2. Create a table that holds file locations along with version/date.

3. When user chooses version to restore copy appropriate files to loading folder and execute your current loader.

I don't know if this can be implemented in your environment, but just as an idea...

--Vadim R.

demonfox SSCertifiable Points: 6289 More actions · Answer 5

rVadim (8/7/2012)[hrThen another way is to implement this as a "File Management" solution.
1. Don't discard files after loading, instead save them to a predefined archive location.
2. Create a table that holds file locations along with version/date.
3. When user chooses version to restore copy appropriate files to loading folder and execute your current loader.
I don't know if this can be implemented in your environment, but just as an idea...

using the file again for dataload; I would rather prefer the cleansed data from the database.

--above all, I have another solution for you ; this can be aceived by creating a view on different versions saved in an archived database.

*do check out SCD's ..

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

haiao2000 Hall of Fame Points: 3734 More actions · Answer 6

rVadim (8/7/2012)
haiao2000 (8/6/2012)
Yes feed means files. I think location is probably be configurable. I would predict each client config differently.
Then another way is to implement this as a "File Management" solution.
1. Don't discard files after loading, instead save them to a predefined archive location.
2. Create a table that holds file locations along with version/date.
3. When user chooses version to restore copy appropriate files to loading folder and execute your current loader.
I don't know if this can be implemented in your environment, but just as an idea...

I thought about this solution, but it doesn't solve our problem, the issue is one of the critical parts that our application does is it tracks changes made to the data. that being said, reverse last import files would loose all changes user may have made after the previous import. take a look at the senarios below:

-on 8/1/2012 files being imported by backend job

-on 8/2/2012-8/3/2012 user make tons of change to the data via front end

-on 8/4/2012 updated files being imported

if we reverse back to 8/1/2012 state. changes made on 8/2-8/3 would be lost. so what I plan to do on 8/4/2012 is as follow:

-backup certain tables before importing

-import updated files

-after 8/4 if user find out there is some thing wrong due to the import. he can restore data back to previous state. There will be a UI on front end that allows user to restore to data to the previous state as was on 8/3.

Another this works just like windows 7 restore point.

Thanks!

haiao2000 Hall of Fame Points: 3734 More actions · Answer 7

demonfox (8/7/2012)
rVadim (8/7/2012)[hrThen another way is to implement this as a "File Management" solution.
1. Don't discard files after loading, instead save them to a predefined archive location.
2. Create a table that holds file locations along with version/date.
3. When user chooses version to restore copy appropriate files to loading folder and execute your current loader.
I don't know if this can be implemented in your environment, but just as an idea...
using the file again for dataload; I would rather prefer the cleansed data from the database.
--above all, I have another solution for you ; this can be aceived by creating a view on different versions saved in an archived database.
*do check out SCD's ..

Sure I will check this out.

Thank you!

haiao2000 Hall of Fame Points: 3734 More actions · Answer 8

haiao2000 (8/4/2012)
Hello Experts,
I am trying to come up with a better solution for our intranet web application (running on SQL Server). this process currently works as follow:
-a back end loader process that imports data from feed to multiple tables in a database on a weekly basis. Currently, the way this loader works is..... it deletes old data then import new ones each time it runs. The current loader process tracks the date/time import occurred but does not save the old data before re-importing new one.
Going forward, I have a requirement to modify the front end interfaces that allows user to roll back imported data to the previous version if user chooses to.
Below are couple solutions I could think of:
(1)-add code to save current data to a set tables (archive) before importing, if user decides to roll back, this set of archive tables set can be used for rollback purpose
(2)-add new fields in affected tables called "load date", do not delete data historical data every time new data is imported, that way if user need to roll back, all I need to do is removed new data.
Either of the solutions above seems to be expensive. Could someone shed some light on which would be the best way handling this?
Thank you in advance!
-Bradley

Ooop! Now I got where you guys got false impression from.... I just realized that I unintentionally said... "it deletes old data then import new ones each time it runs". It doesn't delete old data, it updates existing data with new one, some data may be added or deleted

Sorry

Jared SSC Guru Points: 61793 More actions · Answer 9

-on 8/1/2012 files being imported by backend job
-on 8/2/2012-8/3/2012 user make tons of change to the data via front end
-on 8/4/2012 updated files being imported
if we reverse back to 8/1/2012 state. changes made on 8/2-8/3 would be lost. so what I plan to do on 8/4/2012 is as follow:

Ok, now I am really confused... What is the point of reverting back to 8/1/2012 state if you didn't want to change the changes in-between? I mean, what exactly are you restoring if you don't alter the data changed in-between 8/1 and 8/4?

Also, now that we realize your process is not overwriting, but merging data... Just take a backup before every merge, restore as needed. Which still doesn't make sense to me.

So, if data is merged on day 1, changes happen on days 2 and 3, data is merged on day 4... Then you want to go back to day 1... How on earth could you go back to day 1 while keeping day 2 and 3?

Jared
CE - Microsoft

demonfox SSCertifiable Points: 6289 More actions · Answer 10

SQLKnowItAll (8/9/2012)
-on 8/1/2012 files being imported by backend job
-on 8/2/2012-8/3/2012 user make tons of change to the data via front end
-on 8/4/2012 updated files being imported
if we reverse back to 8/1/2012 state. changes made on 8/2-8/3 would be lost. so what I plan to do on 8/4/2012 is as follow:
Ok, now I am really confused... What is the point of reverting back to 8/1/2012 state if you didn't want to change the changes in-between? I mean, what exactly are you restoring if you don't alter the data changed in-between 8/1 and 8/4?

I am confused too ; how many users are you talking about ?

what update could be wrong , the backend loader update or the user transactional update ? (as you said now that backend loader update the existing record)

your scenario is like the database refresh from a mirrored database...

I am not sure ,but the way you want to follow could be done by Insert only model ,though i don't think it would be feasible for transactional table that is subjected to heavy updates..

otherwise backup sounds like a nice idea...

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: