August 4, 2012 at 10:22 pm
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
August 5, 2012 at 1:41 am
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 :ermm:
August 5, 2012 at 1:46 am
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 :ermm:
August 5, 2012 at 7:48 am
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
August 5, 2012 at 10:39 am
(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; Theyll drag you down to their level and beat you with experience
August 5, 2012 at 12:31 pm
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 :ermm:
August 6, 2012 at 9:11 am
MyDoggieJessie,
I agree. what SSIS and BCP stand for?
Thanks!
August 6, 2012 at 9:14 am
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; Theyll drag you down to their level and beat you with experience
August 6, 2012 at 9:20 am
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.
Jared
CE - Microsoft
August 6, 2012 at 9:24 am
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,
August 6, 2012 at 9:33 am
It's an integration services control "- Slowly Changing Dimension"
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 6, 2012 at 9:53 am
When you saying "feed" do you mean files? Does the loader loads from actual files located on the server or network path?
--Vadim R.
August 6, 2012 at 10:35 am
SQLKnowItAll (8/6/2012)
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.
I see. our application do write to the database, restore the entire database is not suitable option for us as there is a number of complications, we only need restore certain set of tables.
-Thanks for the input.
August 6, 2012 at 10:36 am
MyDoggieJessie (8/6/2012)
SSIS - SQL Server Integration Services (similar to DTS in SQL versions prior to 2005)BCP - Bul Copy Program (http://msdn.microsoft.com/en-us/library/ms162802.aspx)
Got you. Thanks!
August 6, 2012 at 8:43 pm
Yes feed means files. I think location is probably be configurable. I would predict each client config differently.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply