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.