In this modern age of technology DBA’s face an ever
increasing demand from businesses; our databases must perform, be secure,
highly available and scalable and equally as important recoverable. There is an obsession with the 5 9’s within
the industry, and rightly so, but just how resilient is your recovery plan? Can you honestly say hand on heart you have
covered everything? I can’t, but it
isn’t through want of trying.
Performance and
Security
Let’s say then that you are happy with the performance of
your databases, you have normalised to 3rd normal form in the vast
majority of cases without compromising performance. All the right security measures are in place,
Active Directory Groups with access available only for production DBA’s, you
also have a controlled staged release procedure through Development, UAT and
Live. You monitor a wide variety of
metrics and analyse them on a weekly basis for auditing and capacity
planning. You have a solid index
strategy to squeeze out every possible ounce of performance. Performance and Security you have covered
pretty well but being a DBA you are always looking for ways to make things
better.
Highly Available
You have a 2 node active passive Windows Failover Cluster,
have tested all the possible failover scenarios and are happy that failovers
and failbacks occur successfully. Again
you are happy with the high availability of your environment.
Scalability
You have plenty of room for “growth” both in size and
load on the environment, there is also a contingency for additional resources
(for ITIL people I mean storage, CPU and memory ;) if required. Performance and utilisation is monitored and
reviewed weekly as part of your standards.
You are comfortable that the environment scales.
Recoverable
You have configured Database Mirroring in high safety
with manual failover to prevent false failovers as the DR site in a different
country. Failover has been tested and
the databases are served to the applications successfully failback also works a
treat. You take full backups on a Sunday
differentials every night and transaction log backups every 15 minutes. The backups are then backed up to tape and
sent to an offsite storage facility.
Good Times (GT’s), so far so good you have ticked all the
required boxes up to this point. Now
let’s throw in some scenarios and see how this environment would work.
Performance problems
reported
Your
solid release process highlighted a potential risk and the affected processes
are rolled back to guarantee performance while the issues are being addressed.
Potential Security
Breach
This
was found proactively as during your weekly review you noticed login failures
and raised the issue with your security team to resolve.
Running low on
disk space
Again this is proactive, based
on current growth rates you predict that a volume will run out of space in 6
months, a change request is made and the volume expanded.
A Server in the
cluster fails
No
problem here, failover is automatic you resolve the error with the problematic
node and add it back into the cluster.
Entire cluster /
primary site failure
Again
no problem you manually failover from the principal to the failover partner,
the application and operation teams follow their DR plans and hey presto with
little downtime your environment is up and serving customers again.
Data Loss
At 12:00 reports of errors
in the application reach your team, Investigation finds that a disgruntled DBA
who was working their notice period deleted 100,000 records from the order
table at 11:05 on a Friday morning. A
decision is made to recover from backup, the backups are kept on disk for one
week so you look at restoring the affected database. This is where the problem arises.
Your backup script consists of restoring the full backup
from Sunday The differential from Thursday night and then all transaction logs
from Friday with a STOP AT command at the last transaction before the
delete. The full backup restores fine
but the differential backup from Thursday night fails as it is incomplete. You investigate further and have to result to
restoring the full backup from Sunday the differential backup from Wednesday
and all transaction logs from Thursday up until 23:45 which is the last transaction
log before the incomplete differential.
Yes that’s right you have lost just over 12 hours’ worth
of data! Things didn’t have to be this way
though. The solution is to verify your backups;
if you would have verified the consistency of your backups this could have been
avoided.
Summary
I know from experience trying to justify the cost of a
server can be a painstaking task but justifying one that does not serve clients
is even more difficult. The spin I always
use is;
“How much would it cost the company if we lost a day’s
worth of data?”
As a techie it is a no brainer, but persuading someone to
part with their money that they see no day to day benefit from having is a
different matter. From their standpoint it
is not revenue generating and the risk is worth taking.
In an ideal world all backups would be verified by
restoring them but this is simply not practical for everyone as the budget may
simply not available. Take into
consideration also that most environments I have worked in have had 40+ SQL
Servers running different versions; this would potentially require more than
one server to verify the backups.
Recommendations
What I would recommend is;
1 – Get buy in from other team members and line managers,
the more people you have that agree with you on this the stronger the case will
be when it is presented to the person with the cheque book!
2 - Prioritise your SQL Servers and verify the backups of
the most critical databases as often as possible. If you lose data in some databases you can
get by, for example IT Internal databases (SQLSentry, Solarwinds).
Believe me it may one day save you and quite possibly
your job!
