Backup design, restore testing, integrity checks, and corruption response.
292 associated posts117 primary posts
As your databases get bigger, you're going to run out of time to do nightly full backups. You're going to be tempted by the siren song of differential backups. The idea is that you'll do full backups on the weekends, and then differential backups each night of the week. Differential backups aren't just the sum…
I recently posed the question, “Who’s taking your backups?” This is of utmost importance, because your business’s data is the business. Protecting it and backing it up need to be priorities for DBAs.
Now, for my next question: who is responsible for restoring those backups?
It's crucial for database administrators to recognize signs of pending doom. Make sure you're able to quickly spot problems in SQL Server Management Studio by studying these screenshots.
What's that? You can't see the differences in the screenshots? That's because SQL Server Management Studio doesn't show you serious problems.
I recently finished helping the StackExchange team migrate their SQL Server 2008 infrastructure to SQL Server 2012. These servers power StackOverflow, DBA.StackExchange.com, the new AskPatents partnership with the US Patent and Trademark Office, and hundreds of other Q&A sites that together form one of the biggest web site networks. It's one of the most visible…
The more SQL Servers I see, the more scared I get. There are a staggering number of ways that your server can sneak around behind you and ... well, let's just say you shouldn't drop the soap in the datacenter.
As part of my health checks, I'm used to verifying that full backups are done regularly, but that's not enough.
This question came up when working with a client last week. The team mentioned they were planning to move some tables into a new filegroup. The new filegroup is intended to help quickly restore data in cases when a monthly process goes off the rails and leaves the data in some tables in a bad state.
Every now and then, I hear an experienced DBA say, "SQL Server maintenance plans for backups, indexes, and statistics? What, are you kidding me? Nobody uses maintenance plans because they suck."
They say maintenance plans are so inflexible because they insist on defragging or rebuilding every index, every time, regardless of how little the table has changed. Or maybe they complain about problems with scheduling, or how they saw somebody use a DBCC SHRINKDB maintenance plan once.
March 31st is World Backup Day, and while most of the press will be focusing on backing up your priceless lolcat photos, it's still a good time to talk with management about your database backups.
Your boss will have some simple questions, and you can arm yourself with a few easy queries.
Are We Backing Up Every Database?
With SQL Server 2012's new AlwaysOn Availability Groups, we have the ability to run queries, backups, and even DBCCs on database replicas. But should we? Paul Randal (Blog - @PaulRandal) covered this issue in today's SQLskills Insider newsletter, and he says: "It’s not checking the I/O subsystem of the log shipping primary database – only…
Backup strategies are like rashes: people tend to ignore them for as long as they can, then they ask a specialist if it's going to be OK. There’s a lot of good ways to back up databases. The challenge is finding best strategy for your data. To find out if your SQL Server backup strategy measures…
Right after the DBA says “Uh oh,” they call in the consultants. We get to see some really amazing acts of career suicide.
You wouldn’t believe some of the stuff we’ve seen, and some of these things are probably happening in your environment right now. In this session, we'll talk about some of the most common ways to shoot yourself in the foot, and we'll show you how to put the gun back in the holster.
I'm proud to say that I've only lost one job in my life. I was a grocery store bagger when the store came under pressure to increase their percentage of minority workers. They laid off all of the Caucasian baggers, but I'm not bitter. I actually loved bagging groceries, and if I hadn't been laid off, I'd still be the guy asking you "Paper or plastic?"
You need to poop. Maybe not right now, but in general, you need to breathe, eat, drink, sleep, and poop. If you can't do any of those things, then the rest of your needs will take a back seat until you can accomplish the basics.
Psychology professor Abraham Maslow designed Maslow's Hierarchy of Needs to illustrate how us meatbags prioritize our daily tasks. It's usually visualized with a pyramid:
Managing data is about managing risk, but no matter how we good we are at managing risks, they're still risks.
We've seen several high-profile data failures recently:
Every day, your servers do hundreds or thousands of operations that are all resource-intensive. You want these operations to finish as quickly as possible, because they're absolutely mission critical, and you'll lose your job if they're not done successfully. Whole companies have been lost when these operations fail. Yet do you really know how to make these operations happen faster?
StackOverflow's database servers are hosted in a colo facility. They pay by the byte for transfer in and out of their servers, so it's really crucial to compress the database backups as much as possible. The smaller the backups, the less they pay. They're already using backup compression, but they wanted to explore more ways to cut their backup sizes.
In the final part of my SQL University serries, I talk about the nightmares of storage: why things change when we're not looking and why virtualization makes it harder to keep an eye on things.
When I design a backup & recovery strategy for a database, I don't talk to the developers, database administrators, or systems administrators first. The first people I go to are the business managers, and I ask three questions:
How much money would you lose if you lost the data altogether?
How much money would you lose if you were down for X hours?
How much time & money can you afford to devote to backups?
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…
Are all of your servers set up to clean out their backup history periodically?
Are you sure?
