Reporting
from NY, East Coast, USA: In the wake of
hurricane Sandy, or as re-classified, post-tropical cyclone Sandy (whatever
that is), we slowly begin to recover from the devastated regions. My thoughts and well-wishes go out to all
those in my local area and other areas affected by this monster storm.
(Including another “post-tropical cyclone” in Southern India)
Listening to
all the news on the storm as it occurred; one New York hospital thought it was
well-prepared with backup generators.
Unfortunately, in the time of disaster those generators did not come
online, and failed. Therefore, they
needed to be evacuated to another facility.
Let me talk
business continuity for a moment. It is
a simple concept often overlooked, and entails procedures and processes to
ensure the continuance of business, usually in a disaster. The technology employed to achieve business
continuity encompasses a degree of both High Availability and Disaster
Recovery.
While I won’t
drill down on HA/DR in-depth in this blog, clearly there are a number of
options available in SQL Server 2012 (and earlier) to achieve this. Out of the box, you will find Mirroring,
Replication, Log Shipping and Availability Groups. Failover clustering, SAN-to-SAN replication
and Virtualization all are technologies that can be employed in your High
Availability/Disaster Recovery plan as well.
However, when
all else fails, remember backups are the last line of defense. Backup and Restore is the classic fallback to
any disaster recovery situation.
It’s easy to
create a backup maintenance plan, and never restore it – until – disaster
strikes. This is not an optimal position
to be in as a DBA, nor a very good career move.
Therefore, don’t just have a backup plan, have a restore plan as
well. The restore plan must be
incorporated into your overall backup strategy.
So, as
related to the SQL Server world, it’s not enough to have a backup of your
database(s), but you must test make sure they can be restored successfully. You also want to ensure that you database
backups are not corrupt.
Just because
your backup jobs are completing successfully, it doesn’t necessarily mean you
have a good backup. So, it is important
to remember, a completed backup, does not mean a backup that is free of
corruption. In a situation where you
must restore from backup, “oops, sorry the backup is corrupt” is not a valid
response to your manager, boss, or end-users.
Sadly, most
folks do not validate their backups. At
the recent Red Gate SQLIntheCity
event in New York, a city badly hit by super storm Sandy, I spoke about
business continuity and the need to ensure your backups can successfully be
brought online. This means not only
ensuring the integrity of your backups, but restoring them on a regular basis. Verification
is one of the most crucial steps in any DBA’s backup plan.
So, how do we
ensure that our backups are corruption free? As of version 2005, SQL Server introduced a
new improved option over torn page detection called page checksum. When
checksum is enabled on databases SQL Server computes the checksum of the page whenever
a page is read from disk to memory or written to disk from memory.
When SQL
Server reads the page, it re-computes the checksum comparing it with the page
header values. If the checksum value matches then it is assumes the page did
not get corrupted during the read/write process.
BACKUP
CHECKSUM will compute a combined CHECKSUM of all pages and stored on the the
backup media. This value can be used to re-compute the CHECKSUM before
restoring to make sure the backup itself is valid. It’s straightforward to tell
if the backup has any corruption during a restore, or using a restore verify
only. Backup checksum uses same
algorithm as used by page checksum. You
can choose to generate backup checksum during a backup which can then later be
used during restore to validate that the backup is not corrupt. Additionally,
the page checksum, if available, can be verified during backup and recovery.
However,
there is a caveat of using backup checksums that BOL (BooksOnLine) talks about:
“Due to the overhead verifying and generating backup checksums, using
backup checksums poses a potential performance impact. Both the
workload and the backup throughput may be affected. Therefore, using backup
checksums is optional. When deciding to generate checksums during a backup,
carefully monitor the CPU overhead incurred as well as the impact on any
concurrent workload on the system. ”
Without checksum, checking the integrity of
your database requires running DBCC CHECKDB.
This operation can also be quite I/O intensive with performance overhead
on your production databases. Therefore,
the recommendation is to create a process, whether automated or manual, to restore
your database, and run DBCC CHECKDB against it.
In an article I wrote on MSSQLTips.Com, I discuss specific ways to Minimize Performance Impact of SQL Server DBCC CheckDB. This includes discussion on backup and
restore, backup with checksum and other options.
Full backup
verification requires running a full restore + DBCC CHECKDB. This can be
time-consuming and resource-heavy, and is often easy to put off, despite the
potentially negative consequences.
There are
many 3rd party tools that can help automate verification. One that I worked with, RedGate’s SQL Backup Pro 7
streamlines the process of backup, restore, and verification. Offering step-by-step
scheduling wizards, the new features make verification a natural, easy step in
your normal backup procedure, which is fully integrated.
With SQLBackup Pro, you can quickly
and easily set up regular restores from SQL Backup that will automatically
restore the latest backup (to another server) which allows you to Offload resource-intensive DBCC CHECKDB.:
restore your backups to any SQL Server for verification, to take the load off
production.
Finally, if
you’re looking for a great resource on Backup and Restore, then you need to
check out and get a great new book by my friend Tim Radney (blog|twitter),
and a great addition to the Joes2Pros Series, SQL Backup and
Recovery Joes 2 Pros: Techniques for Backing up and Restoring Databases in SQL
Server. As per Tim, “Backups and restores are the core foundation of a
DBA’s job”, and this reference will provide great help DBAs everywhere. Available now on
Amazon, Tim’s book is one that all DBAs should have at their fingertips.
--------------------------------------------------------------------------------------------------------------------------------------
I’m SQL Server MVP, Robert Pearl, and ask
you to follow me on twitter @Pearlknows,
and keep watch for a very exciting campaign, no not the 2012 election, but one that
is designed to build awareness in the SQL Server community to keep your servers
humming. Please contact us at
rsp05@pearlknows.com, to learn how you can get your 15-point SQL Server HealthCheck report!
