March 1, 2016 at 6:44 pm
Hi,
Need an advice on rolling back the ETL load if anything fails.
I have a one truncate-and-load ETL process that runs every night, it first truncates the fact tables and then extracts the data from multiple source systems and loads them in 2 different SQL server databases. There are times when the ETL fail for any reason and end-up in truncated fact tables.
To have a proper rollback method in place, should i use the transactions of SSIS and set the TransactionOption=REQUIRED for the Master ETL package (or sequence container) and the rest of the sub packages with TransactionOption=SUPPORTED, can SSIS manage the whole ETL process in one transaction without any issues?
OR
Take the backup of the database before ETL and if anything breaks then restore the database.
Which option is better / recommended?
The current ETL takes about an hour and loads 10million rows and takes 25GB of disk space.
Thanks.
March 1, 2016 at 7:04 pm
moid.hassan (3/1/2016)
Hi,Need an advice on rolling back the ETL load if anything fails.
I have a one truncate-and-load ETL process that runs every night, it first truncates the fact tables and then extracts the data from multiple source systems and loads them in 2 different SQL server databases. There are times when the ETL fail for any reason and end-up in truncated fact tables.
To have a proper rollback method in place, should i use the transactions of SSIS and set the TransactionOption=REQUIRED for the Master ETL package (or sequence container) and the rest of the sub packages with TransactionOption=SUPPORTED, can SSIS manage the whole ETL process in one transaction without any issues?
SSIS actually does not handle this. The transaction management is delegated to the MSDTC, which then coordinates the transactions with SQL Server. So, as long as your SQL Server can handle the transaction duration and volume, then yes, you can do this from an SSIS Package.
Now, handling the truncation and loading of 10 million rows occupying 25 GB of disk space inside an explicit transaction will generate a lot of transaction log activity. How does your loader work today, what is your batch size set to on your OLE DB Destination in SSIS? If it is the default of <max value of int> then you are already hammering your transaction log with 10 million rows being inserted in a single batch and so you know your server can handle it because the truncate table piece will be a minimal impact on the transaction log.
The only way to know is to try it. Leveraging MSDTC in SSIS is as simple as you described, namely setting the transaction property to Required on one of your containers however you have to have the MSDTC configured ahead of time.
OR
Take the backup of the database before ETL and if anything breaks then restore the database.
Which option is better / recommended?
The current ETL takes about an hour and loads 10million rows and takes 25GB of disk space.
Thanks.
The best guidance here is to test. All the techniques you mentioned are viable but best is subjective and will depend on your environment.
Recommended, well, again, it depends on your environment and there is not enough info here to make a comprehensive recommendation.
Try out the MSDTC approach in a non-prod environment.
As an alternate approach you could also try managing transactions in SSIS yourself with an explicit BEGIN TRAN and COMMIT TRAN in Execute SQL Tasks that bookend your truncate and load operations, however you will need to set the RetainSameConnection property to True on your Connection Manager and make sure you use the same CM for all relevant operations.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 1, 2016 at 7:57 pm
moid.hassan (3/1/2016)
Hi,Need an advice on rolling back the ETL load if anything fails.
I have a one truncate-and-load ETL process that runs every night, it first truncates the fact tables and then extracts the data from multiple source systems and loads them in 2 different SQL server databases. There are times when the ETL fail for any reason and end-up in truncated fact tables.
To have a proper rollback method in place, should i use the transactions of SSIS and set the TransactionOption=REQUIRED for the Master ETL package (or sequence container) and the rest of the sub packages with TransactionOption=SUPPORTED, can SSIS manage the whole ETL process in one transaction without any issues?
OR
Take the backup of the database before ETL and if anything breaks then restore the database.
Which option is better / recommended?
The current ETL takes about an hour and loads 10million rows and takes 25GB of disk space.
Thanks.
IMHO, neither option is better. 😉
25GB isn't much space anymore. As Orlando points out, though, transactions of that size are a bit much. Rollbacks are never a good thing especially since they can be avoided altogether and having the tables "out of service" for an hour might not be a problem during the night but it's not necessary at all. The "rollback prevention" method will make all that unnecessary.
So, what is the "Rollback Prevention" method? Identical parallel tables with pointers. Take the 25GB of disk space that you would have used for the log file and put it to much better use. Here's how.
1. Rename ALL of your Fact tables by appending either an "_1" or "_A" to their names. These tables will be referred to as the "First Set" of tables from here on.
2. Create synonyms (or "pass through" views) with the original table names that point to the newly renamed tables. These will be referred to as the "Normal Synonyms" for here on.
3. Create another set of tables named identically to the first set of tables except suffix them with either an "_2" or "_B'. These tables will be referred to as the "Second Set" of tables from here on.
4. Create synonyms (or "pass through" views) named exactly the same as the first set of synonyms but with the suffix of "_ETL". These will be referred to as the "ETL Synonyms" for here on.
5. Change your ETL jobs to point to the "ETL Synonyms" instead of tables.
At this point, everything is wonderful and the users/code know no difference from before.
Then, night descends and so does your ETL job. Here's what happens then...
1. Your ETL job truncates the tables being pointed at by the "_ETL" synonyms.
2. Your ETL job loads the tables being pointed at by the "_ETL" synonyms.
3a. If your ETL job is 100% successful, drop and rebuild the "ETL Synonyms" to point to the "First Set" of tables to take the tables offline and drop and rebuild the "Normal Synonyms" to point to the freshly loaded "Second Set" of tables. Total downtime will be measured in milliseconds and the system will also wait to drop a synonym until it's not being used so there will be no "mid-query" interruptions.
3b. If your ETL job has a problem, then do nothing. The "First Set" of tables continues to exist with the old data and the "Normal Synonyms" continue to point at them. You now have time to fix the bad job/data with no interruption to the users.
Next night occurs. Steps 1 and 2 are identical this night. Step 3a just reverses the synonyms again. Step 3b continues as before.
No rollback if something fails... "out of service" is measured in milliseconds instead of an hour... you have time to make a fix if the ETL fails... you might even be able to set your database to SIMPLE Recovery Model because you no longer need to depend on the transaction log and prefer to use as many "Minimally Logged" commands as possible to keep the size of the transaction log very low.
To summarize, one set of tables is online while you're loading the other set. When done, flop the synonyms to the other tables. If there's a failure, do nothing because you didn't kill the "current" tables. Nasty fast, super simple, no rollbacks or restores required.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2016 at 10:01 pm
Thanks a lot for your response. I will first try with setting the Transaction Option as required.
March 2, 2016 at 10:16 pm
moid.hassan (3/2/2016)
Thanks a lot for your response. I will first try with setting the Transaction Option as required.
Heh... WHY? Are you looking forward to an all-night rollback? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2016 at 2:00 am
moid.hassan (3/2/2016)
Thanks a lot for your response. I will first try with setting the Transaction Option as required.
Jeff is offering a scalable solution. Changing your Package to use a transaction is a five minute affair, I get it, but if you have the resources to re-architect the load you should take his advice.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply