Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Suggestion on how to implement a custom rollback Expand / Collapse
Author
Message
Posted Saturday, August 4, 2012 10:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:31 PM
Points: 151, Visits: 391
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
Post #1340236
Posted Sunday, August 5, 2012 1:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
you can create an archive table that keeps only the previous version data, it's good to have the history information in other table. Also it depends how your business requirement need to follow up .
like - how many versions do you need to keep ? how many revert back you would have to allow ?


~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1340241
Posted Sunday, August 5, 2012 1:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
if it's just one version , you can have the back end loader to dump the data in the new table and have front end configured to use new table when rolled back by user..
also , have a user and version table to check which user want to see the rolled back data .....


~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1340242
Posted Sunday, August 5, 2012 7:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:31 PM
Points: 151, Visits: 391
Demonfox,

Thank you for your suggestion, I think that is an excellent approach. I don't anticipate we keep every version of the data as it is very expensive to do so, but it is likely that user wants to have several versions of the data instead of just one, though it is unlikely user will go beyond the 1 version.

If you can think of other approach please do post again as i do have sometime to find the best approach I can find for this.

Again thank you for your suggestion
Regards
Post #1340271
Posted Sunday, August 5, 2012 10:39 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:31 AM
Points: 3,897, Visits: 7,138

(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.


I think option 1 is your best approach. I'd personally use SSIS within a SQL Agent job to archive the data, and then BCP in the data to the LIVE tables...

These archive tables will be identical to the live tables minus all the indexes and such.

If you go with option two, and non-clustered indexes you have on those tables will grow significantly as they will need to keep up with the "new" and "old" data

You haven't said how large all this data is...only that it was "expensive"...how much data are we talking about? Thousands or rows? Millions?


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1340279
Posted Sunday, August 5, 2012 12:31 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
haiao2000 (8/5/2012)
Demonfox,

I don't anticipate we keep every version of the data as it is very expensive to do so, but it is likely that user wants to have several versions of the data instead of just one, though it is unlikely user will go beyond the 1 version.

well, if there are several versions , then anyhow the data has to kept somewhere; for different version you may have to add an attribute,a column, to identify the data version ; have that column included in clustered key.
but the point is that who is going to own the data.
Does this version process has to be live ? In a live environment you can't move the records, when there is a lot,and a user is on the front.
If it's a request process then you can have job running for each request.

To save the back end loader time ,If it's millions of row , then check if SCD works for your business environment; this might save a lot , if it suits your business.and for newer versions you may have fewer data to go forth and may be a lot less to roll back.


~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1340283
Posted Monday, August 6, 2012 9:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:31 PM
Points: 151, Visits: 391
MyDoggieJessie,
I agree. what SSIS and BCP stand for?

Thanks!
Post #1340678
Posted Monday, August 6, 2012 9:14 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:31 AM
Points: 3,897, Visits: 7,138
SSIS - SQL Server Integration Services (similar to DTS in SQL versions prior to 2005)

BCP - Bulk Copy Program (http://msdn.microsoft.com/en-us/library/ms162802.aspx)


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1340682
Posted Monday, August 6, 2012 9:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:40 AM
Points: 2,692, Visits: 3,383
We handle this a bit differently, and it depends on your space allowance... We take a backup every night of said database, and then restore it to databaseName_dayOfWeek. We then have 7 days of backups that are "live." If we ever need to rollback to a specific day, we simply take a backup, rename the current prod database, and then rename the one we want restored. Of course, if there is other transactional data in the database that is used, this will not be beneficial to you. However, for us, the application only reads from the database and does not write to it. You also have to be allowed the seconds of downtime for the switch. Just another approach that may or may not work depending on your requirements.

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1340687
Posted Monday, August 6, 2012 9:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:31 PM
Points: 151, Visits: 391

well, if there are several versions , then anyhow the data has to kept somewhere; for different version you may have to add an attribute,a column, to identify the data version ; have that column included in clustered key.
but the point is that who is going to own the data.
Does this version process has to be live ? In a live environment you can't move the records, when there is a lot,and a user is on the front.
If it's a request process then you can have job running for each request.

To save the back end loader time ,If it's millions of row , then check if SCD works for your business environment; this might save a lot , if it suits your business.and for newer versions you may have fewer data to go forth and may be a lot less to roll back.


Archive versions do not need to be live. and I agree with your concern in regarding to moving records while user is connected. I will have to verify and see how the current process deals with the loader processing while user is on front ends. I bet the loader is normally scheduled to run at night time. So to minimize that risk, if user elects to rollback, the rollback will be to scheduled to run on another time, but that has another issue of its own if bad feeds are received, then the rollback may need to be done immediately.

I didnt understand what you mean by SCD, could you explain? I am terrible with Acronyms..

Regards,
Post #1340690
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse