With SQL Server AlwaysOn Availability Groups, you can offload backups to a replica rather than running them on the primary. Here's how to do it:
1. Install Ola Hallengren's utility scripts on all of the replicas. During the install, it creates a laundry list of SQL Agent jobs, but doesn't set up schedules for any of them. More on that in a couple of steps.
When the DBA reports to you, ask yourself two questions:
"What would I fire the DBA for?"
"How can I prove that they're not doing that?"
A question came up on Twitter recently-- how you can you figure out where a rogue backup is coming from?
I've run into this question with clients before. In an enterprise environment where backup tools come and go, it's not unusual to find backups being run by some mysterious account.
If you've got indexed views, or are thinking of using them, this is important.
First, go read the blog post, "An Indexed View Bug with Scalar Aggregates" by Paul White. It will take a minute, but it's important to understand.
Every time someone tells me, "This database is mission critical - we can't have data loss or downtime," I just smile and shake my head. Technology is seriously difficult.
To illustrate, here's a collection of client stories from the last few years:
I've seen servers with thousands of databases on a single SQL Server, and it works. Sure, opening the databases list in SQL Server Management Studio is painful, and a lot of third party monitoring tools fall over, but it's not so bad once you know how to work around these issues.
But there's two issues you can't work around: our old archenemies, RPO and RTO. A quick reminder:
One of the many restore features in SQL Server is the ability to restore one or more pages of data. This can be very convenient in some narrow situations - for example, corruption occurs on one page or an oops update is made to one record. The page restore process is not straightforward, however, and,…
When you weren't looking, your databases went and grew up. Now your backup window has grown so large that you're about ready to open it and jump.
Time to make a choice.
The Native Way: Tuning SQL Server Backups
You can theoretically pull this off by using a combination of tactics:
DBCC CheckDB: Frequently Asked Questions
Q: Do I really need to run DBCC CheckDB? Yes. If you're even partially in charge of a SQL Server and you'd like to keep your job, you should make sure CheckDB is being run regularly. (Seriously!)
In this 5-minute video, Doug tells about the time he deleted the Reporting Services encryption key, and what it took to fix the damage. You'll learn about what the SSRS encryption key protects, as well when to:
Back up the encryption key
Restore the encryption key
Change the encryption key
Delete the encryption key
One of the questions we ask every new client is, "How much data is this application allowed to lose, and how long is it allowed to be down for?"
This leads to some really fun conversations.
Right now, your transaction log backups are set up to run every 15 minutes, every hour, or maybe every few hours.
It's time for a reality check. Figure out what internal customer owns the data inside the database, and walk into their office. Ask them how much data they're comfortable losing.
There will come a time when your database backups will not be as fast as you would like them to be. Over time, the volume of data you’re storing will grow, and backups will take longer – but your weekly or daily maintenance windows don’t grow as well. Backups can be resource-intensive, using I/O and network resources – and if they’re running when users are trying to work, the users may notice the performance impact.
In Michigan where I grew up, we pull the boats out of the water at the beginning of the fall. It's a bit of a sad time, realizing we're done having fun on the water, and the seasons are about to change.
To prepare a boat for a few months of storage, we drain some fluids, replace others, give it a good cleaning, do some basic maintenance, and put on a tight waterproof cover.
Taking care of your health is incredibly important (says the marathon runner). Eating right, exercising, and getting plenty of rest help you stay healthy and perform optimally. Your database server’s health is important to your company’s health. You need to take regular backups, check for consistency, and keep indexes tuned for optimal performance. Here are…
In our recent webcast on HA & DR basics for DBAs, we got a bunch of questions that couldn't be answered fast enough. Here you go:
In theory, before you introduce a new system - database server, load balancer, virtualization infrastructure, etc - you build a robust runbook that documents how you'll handle every conceivable scenario. When there's any kind of failure, you'll simply turn to chapter X and start going through a precise checklist that will guide you to the promised land of uptime.
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?
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.
