RPO, RTO, failover procedures, service continuity, and DR readiness.
41 associated posts41 primary posts
In our Faux PaaS project, we need a backup plan - or rather, a restore plan.
On each SQL Server instance, clients can create as many databases as they want, anytime they want, with no human intervention. We need those databases covered by disaster recovery as quickly as practical.
This week, Brent and Richie discuss sp_Blitz Backups, change history tracking, exams and certifications, using Azure for DR-only, which version of SQL server to use for a new DB, adding additional data files, load balancing, trace flag 2371, dealing with reporting services, and much more.
Here’s the video on YouTube:
Background: I'm working with kCura to build a Faux PaaS: something akin to Microsoft's Azure SQL DB, but internally managed. In Part 1 of this series, I talked about why we're building it, and today, I'll talk about where.
The database team rarely gets to pick the cloud vendor.
Listen, I can explain. Really.
We had a client with a 5GB database, and they wanted it to be highly available. The data powered their web site, and that site needed to be up and running in short order even if they lost the server - or an entire data center - or a region of servers.
This week, Brent, Richie, Erik, and Tara get together in Round Rock, TX, for Dell DBA Days, to talk through your SQL questions and answers. They discuss what they are working on at Dell during the event, as well as Windows Kerberos and NTLM Authentication, Always On Availability Groups, Microsoft's Document DB, automating restore databases to other servers, why they are wearing "fun" hats, and much, much more!
One of the most popular things in our First Responder Kit is our HA/DR planning worksheet. Here's page one: In the past, we had three columns on this worksheet - HA, DR, and Oops Deletes. In this new version, we changed "Oops" Deletes to "Oops" Queries to make it clear that sometimes folks just update…
I've set up Mirroring about a billion times I'm not bragging about that. I'd rather say that I set up a billion AGs, and not one of them ever failed. But then I'd be lying to you; those things fail like government programs. One thing I'd never done, though, is set up Mirroring with a…
If you're a full time production database administrator, you need a staging environment.
This is where you test infrastructure tasks like failing over mirroring, doing a disaster recovery role swap, cluster quorum changes, log shipping configuration, and patching. You need to test this stuff repeatedly so that when you're under pressure in the production environment, you feel comfortable and confident. Bonus points if you use this environment to build checklists and screenshots for rarely-done tasks like recovering from corruption.
Most companies come to us saying, "The SQL Server isn't fast enough. Help us make it go faster."
They're kinda surprised when one of the first things we fill out together is a variation of our High Availability and Disaster Recovery Planning Worksheet:
But Kendra, it can't be that hard... after all, we have synchronous modes in Database Mirroring and Availability Groups, right?
Synchronous Commit doesn't mean "zero data loss"
When we think about limiting data loss, the first thing we think of is a technology that lets us reduce points of failure. If every transaction must be written to two separate storage systems, we have a pretty good chance to have no data loss, right?
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:
I'm a fan of SQL Server's transaction log shipping. It works in Standard Edition, it's relatively simple to set up, and you can even make your log shipping secondary readable using STANDBY mode.
I've worked with some pretty cool, complex log shipping environments over the years. In one case, we had multiple log shipping secondaries and a load balancer involved to support a full fledged reporting application. It worked pretty well-- with a lot of careful scripting and support.
Your first concern is licensing, and once you've got that under control, it's time to talk about backups, restores, and uptime.
The business begins by defining the RPO and RTO requirements for high availability, disaster recovery, and "oops" deletions. Use our High Availability Planning Worksheet and fill in the "Current" column on page 1:
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.
If SQL Server's Transactional Replication was an animal, it would be a cockroach. It's not pretty, but it's resourceful and strong. Once you get even a small amount of replication in your environment it starts spreading everywhere. And if it gets out of control-- well, in that case you've got no choice but to pretty…
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:
I've been working with and talking about SQL Server Log Shipping a lot lately!
I've gotten a lot of great questions about log shipping, so I've put together an FAQ.
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.
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?"
I don't wanna fly in a single-engine plane.
Call me chicken, call me scaredy-cat, but I'm not excited to get into an airplane that will kill me if an engine fails. The next step up is a twin-engine plane - but I don't get on all of those either.
