Has your SAN admin or CIO been telling you not to compress your SQL Server backups because they're doing it for you with a dedupe tool like EMC's Data Domain? I've been hearing from a lot of DBAs who've been getting this bad advice, and it's time to set some records straight. The Basics of…
We focus so much on the fine-grained details of exactly when and where we're backing up our servers. Sometimes it helps to take a quick step back and ask what we're trying to protect ourselves, because it'll help us do a better job of designing a recovery strategy.
From small problem to big disaster, we need to protect ourselves from:
Are all of your servers set up to clean out their backup history periodically?
Are you sure?
BACKUP LOG WITH TRUNCATE_ONLY is a dangerous command: it empties out the contents of your SQL Server's transaction log without really backing it up. Database administrators sometimes run this command right before shrinking their log file with a DBCC SHRINKFILE command, thereby freeing up drive space. Why 'truncate_only' is not a recognized backup option. When…
David Friend, the CEO of Carbonite, left a comment on my blog entry about Carbonite. I'd like to applaud his efforts for taking the time to do that, but the comment raised some ugly questions. It appears that they were putting data on 15-drive RAID 5 arrays. RAID 5 is the most cost effective array setup (other than RAID 0, which offers no data protection).
In a perfect world, my test/QA servers get restored nightly from production. Let's say every night at 9pm, the production full backups kick off, and they're finished by 10pm. At 11pm, the QA box kicks off a restore job that grabs the latest full backups off the file share and restores them. The production backups are written to a file share, never local storage, so there's no additional overhead on the production box for this restore process.
TechCrunch reports that Carbonite, an online backup company, lost customer data.
But wait, this is different: it's not their fault. They're suing Promise Technology, makers of popular storage gear, for selling them bogus equipment. Bogus equipment? You mean, like hard drives that fail? That's horrible! Who could expect something like that? Who could know about the dangers that lurk around every corner?
Bookmark sharing site Ma.gnolia just had a big problem (not quite clear from the site what it was) and user data was trashed. They needed to restore from backup.
Only one little problem - they didn't have one.
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)
Database administrators: are you backing up your SQL Servers to local drives? If so, you need to stop for a minute to think about a few possible side effects, and maybe think about backing up to a network share instead. Why? I'm glad you asked!
Get your data off the server faster
I spent some time last week digging into Microsoft Data Protection Manager 2007, Microsoft's solution for SQL Server backups, and I'm going to share some of my findings here with you, dear reader. "But You Work For Quest Software! You can't write a Microsoft DPM review!" Yeah, disclaimer time - I work for the people…
I've talked about how a SQL backup compression program can help database administrators, but now it's time to convince the boss. We have to sell management on why it's worth the money. First, a few words about my approach to database administration, because I think it'll help DBAs in general. Being a database administrator is…
In this issue of my multi-part series about SQL Server backup compression software, I'm going to talk about some features that database administrators might find useful, but don't yet exist in the current generation of backup software. In-Depth Backup & Restore Progress Monitoring On multi-terabyte data warehouses – especially those running new backup compression software…
In the last decade, Microsoft has brought SQL Server a long way - bringing in things like .NET code, data encryption, partitioning, and a darned slick management user interface. But one of the things that hasn't changed has been the process of backing up and restoring data. We still back up to a file, and…
In my last post, Why Native SQL Backups Suck, I talked about the weaknesses of native SQL Server backups. Today, I'm going to extend that a little by talking about one of the greatest surprises for DBAs in recent history: the advent of dirt-cheap multi-core processors that don't cost extra for SQL licensing. How SQL…
Before we start looking at SQL Server backup compression software, we need to spend a few minutes looking at the weaknesses of the native SQL Server backup process. In order to judge the fixes, we have to know what's broken. Native SQL Server backups take the same disk space as the data. When we back…
I've been backing up SQL Servers for almost a decade now, and it's time to share the lessons I've learned. All of this is my own opinion - your mileage may vary - but I'll try to explain the reasoning behind the choices I make. I won't address log shipping or snapshots this time around.…
