January 12, 2022 at 8:50 am
Although GDPR has been in place for some time, my organisation has had a dispensation to essentially ignore it up until now. The dispensation was because the contract from the government we were on was an interim basis while a new provider got their system online. The thinking was we'd be turning off our system at any time and handing over. Fast-forward seven years and the new provider hasn't done what was asked of them so we're staying put.
We've now been told by the Ministry to bring ourselves into GDPR compliance and one of the issues being raised is about backups. My understanding is that for day to day business, simply deleting the backup file when it ages out is enough to be compliant. More through cleansing is possibly needed when disposing of hardware but no extra measures are required for business-as-usual backup maintenance. However, there's some questions being asked about whether we need to completely erase any deleted files from the disk. This complete erasure apparently uses third-party software to totally remove the file and render it unrecoverable.
Does anybody have any experience of this? I appreciate that everybody's situation is different and government will have the ultimate say about how we do it but I'm interested in whether it's now the norm to delete backups as thoroughly as it's suggested.
To be clear, I'm not looking for technical advice here. Whatever we do is highly likely to be imposed on us by the government ministry to which we're contracted. I'm after a 'sense of the room' perhaps. How are others handling this situation, what's the general feeling about it?
If that's what people have always done and we've never been doing it right, I'll take that too.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 12, 2022 at 3:03 pm
One, please, always, despite this being a technical question, talk to someone in legal at your organization after you decide on a path forward.
Two, I've been studying this for years, and the fact is, no one agrees 100% on the requirements around backups. However, you can make a few inferences. You're supposed to take advantage of the technology available and put reasonable protections in place using that technology. So, encryption at rest is a big one for backups. Encrypt those suckers. They're already a vector for problems, so reduce that overhead now. Next, have a way to deal with "right to be forgotten" requests at the point of database restore. 'Cause, yeah, you have to do that. Finally, do you have to have a perfect scrub? Since the technology currently exists, and it's not an impossible task for you to implement it, yeah, I'd add that to the list.
The biggest key on this is whether or not current technology exists to support you. Let's take the right to be forgotten as an example. In theory, as soon as you get one of these, you should delete all their data from your system that you're legally required to expurge, except the stuff you're required to keep. THEN, you should delete it everywhere, not just your OLTP system, but your data warehouse, interim processing tables, third party vendors, all of that... including backups. However, there's currently not a technological fix that will work on backups. Restore the backup, delete the data, take a new backup, overwrite the old one, hope you got it right? Nah. Just delete stuff as part of the restore process. That's how it's being done currently in law offices & governments in the US.
Here's a blog post with a bunch of my research on backups.
BTW, let me reiterate, these are technical considerations to a legal problem. This isn't legal advice and you should absolutely bounce all of this off your own legal counsel.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 12, 2022 at 3:16 pm
I'll echo what Grant noted. In my experience (PCI/SOX, not GDPR) as a technical worker, the interpretation of these regulations is up to the government and/or auditor. I find their view is often not what we feel inside the company, or even what we agree to do. Usually if we provide a rational, the auditor will accept that, as long as we show some logic and documentation.
For the backups, what I might do is have a job that automates deletion (rolling, xxx days/weeks/months). I would log something as part of this job and ensure those log files are then captured elsewhere (whole other archive/delete issue). Now, I'd also then perhaps have a job or a person with a calendar reminder to go into the recycle bin once a month (or quarter) and remove all files older than xxx days. I'd screenshot this and drop in the log folder somewhere as well. This provides some proof that we've removed older files. It's not 100% perfect, but most of these regulations aren't asking you to be perfect, but that you have a process.
If it were easy/cheap to rewrite the file location with 0s, a secure delete, I'd do that instead of a person, but I haven't always found these products to be cheap, easy, or programmatic.
January 12, 2022 at 3:22 pm
One, please, always, despite this being a technical question, talk to someone in legal at your organization after you decide on a path forward.
Two, I've been studying this for years, and the fact is, no one agrees 100% on the requirements around backups. However, you can make a few inferences. You're supposed to take advantage of the technology available and put reasonable protections in place using that technology. So, encryption at rest is a big one for backups. Encrypt those suckers. They're already a vector for problems, so reduce that overhead now. Next, have a way to deal with "right to be forgotten" requests at the point of database restore. 'Cause, yeah, you have to do that. Finally, do you have to have a perfect scrub? Since the technology currently exists, and it's not an impossible task for you to implement it, yeah, I'd add that to the list.
The biggest key on this is whether or not current technology exists to support you. Let's take the right to be forgotten as an example. In theory, as soon as you get one of these, you should delete all their data from your system that you're legally required to expurge, except the stuff you're required to keep. THEN, you should delete it everywhere, not just your OLTP system, but your data warehouse, interim processing tables, third party vendors, all of that... including backups. However, there's currently not a technological fix that will work on backups. Restore the backup, delete the data, take a new backup, overwrite the old one, hope you got it right? Nah. Just delete stuff as part of the restore process. That's how it's being done currently in law offices & governments in the US.
Here's a blog post with a bunch of my research on backups.
BTW, let me reiterate, these are technical considerations to a legal problem. This isn't legal advice and you should absolutely bounce all of this off your own legal counsel.
Thanks Grant, I've clarified my original post to, hopefully, give a better intent of the question.
A very interesting point is raised towards the end of your post, and in a meeting I have literally just had, about making sure that you thoroughly delete your data but also making sure you're able to put it back. I'm quite glad that I'm only tangentially involved in this project and any questions I have are essentially for my own understanding.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 17, 2022 at 10:34 am
As said above, involve your legal department at each stage.
If there is any thought of doing a 'secure erase' by repeatedly overwriting your backup file, you need to look at how it is stored.
If you have any form of vector array storage (just about everywhere nowadays on enterprise kit) then be aware that individual blocks are only ever written and read, they are never updated. In a his situation, writing to a file 2 times will a) process each block individually, b) release the current block for re-use, c) get a block from the free-space queue, d) write your data to the new block, e) Repeat for the next block of data until the entire file is processed. The end result will be a new vector of blocks for your file, with all the old blocks released for re-use. The recycle algorithm in your storage system may overwite the block with a fixed pattern or it may just mark it as empty in the free-space queue. Some storage arrays can do a secure erase of anything put into the free-space queue, you need to check this. Note that all this applies regardless of if you use NTFS or RAID, it is a characteristic of your storage system.
If you have an old-style storage system where the data at rest can be physically updated then a 'secure erase' may be of benefit.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply