Having been called in many times when an enterprise has lost all its data I can assure you that on arrival it is a dismal scene. Blame goes all around but it is usually the DBA who ends up getting most of it. A colleague of mine in the SQL Consulting business calls this “a resume-generating event”.
Somebody (guess who) might get fired. I have even seen statistics that say when an enterprise has suffered a significant loss of data, the company might be out of business sometime in the following few months and every one employed there will be out of work.
Protecting your data is the number one job a database admin (DBA) has to do, and he\she needs to do it right.
There are many ways you can do it right. Unfortunately there are also a lot of ways to do it wrong.
One sure way to do it wrong is to leave decisions about your recovery plan to the technicians. This is all about your ability to continue in business after a failure of your database platform. It is a management decision to be decided by a well informed IT manager or even the CEO.
The first thing I do when I get a new client is to look at their recovery plan. Then I ask top management a simple question. “How much data can you afford to lose?” They of course reply “None”. Then I explain that there is no such thing as “no chance of data loss” and that 99.9% surety will cost them orders of magnitude more money than 95% surety. In this way we get down to realities.
Count on it. There will always be failures that threaten your data. There are hardware failures, power failures, hackers, fire, theft, earthquakes, wars etc. All we can do is reduce the risk.
That sometimes requires overlapping recovery resources. Fortunately there are many applications now that can back up your data in various ways, but all have limits. Here are my thoughts on the matter.
Legacy SQL Server Backup and Recovery
This is the place to start. However I have looked over a countless number of backup and recovery plans and have seen many plans that just don’t work. With these plans you will lose data after certain types of failures. This is especially true in smaller companies with accidental DBA’s, untrained persons who are in charge of creating backup and recovery plans.
I have been working so long with SQL Server that it puzzles me why it is so difficult to understand recovery modes, but I see the same mistakes repeated over and over in many different SQL instances. For example:
- Databases in Full Recovery mode with no transaction log backups to keep the log file from uncontrolled runaway growth.
- Misunderstanding the recovery limitations of databases in SINGLE recovery mode.
- Misunderstanding the role that the transaction logs play in the recovery process.
- Shrinking the database at every opportunity in an attempt to control log growth, not knowing that when you shrink the data file it fragments all the indexes that you probably have just defragged.
Snapshot Backups
There are many 3rd Party Applications that will backup your database by a series of snapshots at configurable intervals. In the past I have been disappointed by some of them but I think now that the kinks have been worked out for this fairly new technology they are a viable option, at least as a secondary defense for database recovery.
Making Data Safety Someone Else’s Problem
This is certainly the easiest way to deal with data safety but given the failures of several huge cloud providers to protect their client’s data, this is a hard call to make. There is also the question of cost. Is it affordable?
If your database is in SQL Azure I think it is very safe in regard to device failures. You will have three copies of your database spread into three different data centers and they will be updated with changes in almost real time. However moving an existing on premises database to SQL Azure is a big project and there are still a number of limitations that might disqualify Azure as a platform for your database.
These are the possibilities regarding backup and recovery options, but I really have no recommendations. One size does not fit all. Every database and every enterprise is different and must be evaluated differently by a trained and experienced DBA.
Another Way to Lose Your Data
How about the built-in SA login account? Hackers love it. they all know the username and once they get in through SQL Injection or by some other method, all they need to do is hook up a password cracker and they have the keys to your kingdom.
You need to disable the SA account after you create an admin level account with a forgettable username and a REALLY hard password.
A Word to The Wise
Don’t think you know everything about SQL Injection unless you know everything about SQL Injection.There are many ways into your database other than simple query stacking.
Food For Thought
I know I have not been very helpful in recommending a path to data safety but I hope I have brought the subject to mind for you to consider.