October 5, 2011 at 3:23 pm
We’ll use worker’s compensation claims as an example. A worker is injured on the job, a claim is generated, services are provided for the worker and paid for by the company, hopefully eventually the claim is closed. Lots of tables involved.
The data is in SQL Server. The company has a policy (perhaps it’s a state or federal requirement) that the data must be maintained for seven years after the claim is closed then all data related to the claim is purged.
I spent an hour discussing this with our IT security officer. Backups are archived, but you can’t edit a backup. You’d have to restore the database, delete the data, then back it up again. And then it’s no longer a backup of that date 7 years ago. And how many tapes would have to be edited like this?
The only thing that I can think of off-hand is that you maintain backups short-term for crash recovery, but your archive would have to be some sort of text file output, perhaps BCP, so that the data could actually be purged. That BCP data is not directly a backup, you can’t easily restore from it, and not only would it require intimate knowledge of the data to make sure you got it all, you’ll have to do this for every stinkin’ table in the database that’s potentially related!
Another thing that occurs to me (at a very meta, unimplemented level) would be to have delete triggers on the tables that would copy the data to a partitioned file before deleting from the main table. Back up that database separately, preferably to a separate tape. You would need program logic and pre-created databases to automatically handle the advancing calendar forcing database name changes. After the retention period has expired, this database and it's backups are destroyed.
Just wondering if anyone else had implemented anything remotely resembling this. This is very theoretical because we still need to maintain ACID. There’s a whole ‘nother layer of mess piled on top of this that I have zero control over, I’ll just have to get a bigger shovel when it comes to that part.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 5, 2011 at 3:32 pm
How strictly is 'purge' defined? If I can take a hex editor, read the disk raw and find the data there is it a violation?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 5, 2011 at 3:36 pm
GilaMonster (10/5/2011)
How strictly is 'purge' defined? If I can take a hex editor, read the disk raw and find the data there is it a violation?
Theoretically when the retention period is up, the physical file is destroyed and all electronic record of the claim should be erased. Yes, theoretically you could go in with a hex editor, but I don't think that's practical from a human resource POV, especially since these backups will pretty much be guaranteed to be encrypted (I didn't think that was a critical detail to include).
EDIT: I misunderstood your post initially, I thought you were talking about hex editing the backup to purge data. I don't think what you posted would be a problem, it's probably a case of readily retrievable through a file or database search would be an issue.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 5, 2011 at 3:45 pm
Wayne West (10/5/2011)
EDIT: I misunderstood your post initially, I thought you were talking about hex editing the backup to purge data.
No, that would be a good way of ensuring the backup would never restore.
I don't think what you posted would be a problem, it's probably a case of readily retrievable through a file or database search would be an issue.
That's good, cause trying to guarantee that data is gone to that degree is near-impossible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 5, 2011 at 11:29 pm
Actually, I would not worry about purging the data from the database backups. Doing so simply is beyond the scope of the corporate/state/federal requirements. Purging the data from the current active database should be all that is required. This would include test and development databases as well if you are using production data in those databases.
October 6, 2011 at 12:01 am
Uh, silly question, why not just degauss your backup tapes 7 years from when they were taken?
The retention policies generally say how long you must retain the data for, it doesn't say that you can't retain it for 1 day or week longer. As an example, I've never seen a law that says X data must be retained for exactly 1000 days but no longer. The usual position is either min or max. And even in max cases usually the rule is that the data can't be used for decisions after X time, think care insurance, after three years that ticket drops off and can't be used to increase your rate.
CEWII
October 6, 2011 at 10:57 am
@Lynn: thanks, I'll look in to that. I don't know HIPPA rules, and this is definitely HIPPA-sensitive data.
@Elliot: I don't think we use tape anymore per se. We're in the process of implementing Data Protection Manager, and I'm not sure if individual files are accessible at the tape level outside of the system framework. In any case, this system is actually hosted, so I have no direct control over their back process or tapes.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply