1

My system is importing data from a number of external sources on a nightly basis into staging tables in my local DB. The process backs up the relevant table (by copying all data to a replica table with a special name to indicate it's a backup table), clears the table, then imports data from the sources. If the import job fails, it just restores the data from the backup table.

The reason for backing up the staging tables is because if the job fails, we would rather see data that is a day or so older than having no data until the reason for the failure is fixed. Only one backup table exists per critical staging table, and with the next backup, the previous backup is wiped.

The problem with this approach is that the number of "backup tables" are growing quite significantly as the system expands. I've been thinking of deleting the backup table after the job succeeded, but is this good practice? (deleting and recreating every night)

Another reason for keeping the backup table would be, if the job succeeded, but the data got misaligned somehow, something that will only be picked up once the business day starts the next morning, the backup table can give you the opportunity to quickly revert to the version before the corrupted import.

This whole approach just doesn't feel quite right, and I am wondering if there is better approaches to this design.

The system is using MS SQL Server 2012, but we are afforded very little in terms of server admin tools and tasks.

Improve this question

4 Answers 4

Reset to default
3

Yes, there is a better approach: have you considered using transactions? Just begin a new transaction, delete everything from the table, add the data. If adding the data fails at any point of time, you just roll back the transaction.

The drawback is that if you didn't detect the failed data import during the import time but only after committing, it may be too late to roll back the transaction. So, you could use a number (e.g. 10) of backup tables, e.g. backup0 ... backup9, and then you calculate date modulo 10, and use that as the index of the backup table. So, in this case, you clear the correct backup table, add the data to the correct backup table, and delete it from the main table. This way, your count of backup tables stays constant.

Some databases also support point-in-time recovery, so be sure to check if your database does. If it does, you can recover a situation N days ago without having to have backup tables.

Improve this answer
1
  • This depends on the amount being inserted. One might need to check / extend rollback space for this to work, because the DB will have to contain both new and old copies until the transaction completes.
    – 9000
    Jul 28, 2017 at 20:58
1

It does sound like using transactions would benefit. If your concern is that the data import will be bad or bad data, then you can start a transaction before clearing the table, import the table, then only commit the transaction after the import is completed and rollback otherwise. Just be careful that your transaction log in the database engine is right sized.

Also consider creating a table that holds historical. This aids in maintaining good data and also there is typically a need to build reports showing historical metrics, trends, etc. I like to create a corresponding "snapshot" table which has the same structure but adds a datetime field, and nightly dump your table(s) into the snapshot table(s) and set the datetime field so that all records for that date have the same timestamp. This allows you to easily query for the data by day, etc. and it also provides a nice way of recovering from a bad data import or other problem. Of course, put an index on that timestamp field.

Then I have nightly jobs that prune these snapshots to keep each day for a month, one per week after a month, and one per month after a year, etc.

I have found this scheme to work well and be manageable and scalable. It seems clean because you don't end up with a ton of tables, and the data is archived in a way that you can get to any of the snapshots quite easily.

Improve this answer
0

You only need two copies of each table - one for "yesterday's" data and one for "today's". the "trick" is to add a View on top [of one] of these tables and change your application(s) to use that: 

create table table_from_elsewhere_a ( ... ) ; 
create table table_from_elsewhere_b ( ... ) ; 

create view table_from_elsewhere as select * from copied_table_a ;

Your load process for each table now becomes:

  • Find out which base table the view is currently using.
  • Empty and load the other table.

At some point when you're happy with things,

  • Rebuild the view to use the table that you just loaded.
Improve this answer
0

Why are you using "backup tables"? SQL Server 2012 is completely competent in backing up a database - assuming your staging tables are in one database, to a .bak file, compressing it and saving it off to storage.

Are you using IIS for some of the data imports? If so, then it is just a matter of adding a maintenance step to the SSIS package.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.