Are you using any of these technologies as a method of database protection?
- Database mirroring (keeping two database servers synchronized with the same data via SQL 2005/2008)
- Replication (copying records between databases)
- Log shipping (copying log files to another server and restoring them immediately)
- SAN-based mirroring (storing two copies of your database on two different sets of disks)
Do you think you’re completely protected by using these technologies?
If so, you’re dangerously wrong.
Take this all-to-common scenario: a developer screws up and deletes or updates a large number of records. Being a developer, he doesn’t fess up to the problem right away, because he’s going to try to figure out another way around the problem. Hours later, when he finally confesses to his manager and they track you down, the data changes have already replicated to your other systems, and the data is gone.
Or heaven forbid, a less common but even scarier scenario: an employee decides he’s had enough, and wants to screw you before he quits. He writes a delete statement to run on Friday night at 5pm, walks out the door, and it’s a long weekend before you find out what happened. Toss in “hacker” instead of employee, and it gets even worse.
There is no substitute for regular backups.
These backups need to be located offline, somewhere that a malicious (or incompetent) DBA can’t access.
My favorite scenario is to use a file share that the SQL Server service account can write to, but can’t change. The DBAs don’t get write access to that share either – they can read, but not write or delete. The network or backup team is responsible for getting those file backups off to tape, and then out of the building, as soon as possible. Retain these backups as long as possible.
For more tips, check out my best practices for SQL Server backups.
Yes, I'm going to be nitpicky, but I'm a security guy and since you're pointing out a security shortfall (integrity and availability on the C-I-A triad) II think you need to better define "database protection." You are using it in a narrow scope, as in, "protect data loss or malicious data change." None of these solutions provide database protection from a confidentiality attack using the C-I-A triad. Something as simple as "net stop mssqlserver" and then copy off the .mdf, .ldf, and .ndf files.
That's absolutely true. In order to get that level of protection, you also have to watch the SAN, because the SAN admin doesn't even need to stop SQL Server in order to take a snapshot at the disk level. Granted, the database might be corrupt, but he can try it a few times to get a good copy and you'll never know the difference.
Too bad the guys at JournalSpace didn't consult with you 6 months ago.