Give this article a Google +1!
Issue 7 Number 4 April 2017Tweet This Article.
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.
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:
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.
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.
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.
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.
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.
I hope this information has been helpful to you. I would appreciate any feedback you can give me about this article or recommend future articles you would like to see. If you enjoyed reading this article, you might like to receive this monthly newsletter via Twitter. If so, follow me.
As always, if you have questions or comments give me a call toll-free at 1.877.201.3428 or send me email.
Kurt Survance, SQL Consulting, Inc.
On this page:
Elsewhere on this site: