Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
AlwaysOn Availability Groups and SQL Server Jobs, Part 19: Availability Group Database Backups
One of the biggest strengths of AlwaysOn Availability Groups is that they allow DBAs to address both high availability and disaster recovery concerns from a single set of tooling or interfaces. But, this doesn’t mean that you won’t still need backups.
ITPro Today
March 6, 2015
4 Min Read
ROADMAP: In my last post I tackled some additional options for monitoring the health of SQL Server AlwaysOn Availability Groups, wrapping up Section II of this multi-post series on tackling jobs for SQL Server AlwaysOn Availability Groups. For the next seven to eight posts I’ll be switching gears to address concerns with backups. Then, once that’s completed, I’ve got a handful of follow-up posts that will provide summaries and guidance for options available when it comes to tackling batch jobs, backups and the like, as the primary purpose of most of my posts so far has been to walk through an overview of some of the many pitfalls you can run into when working with Availability Groups and Jobs, and to offer some rationale into some of the choices I’ve made in trying to address those problems.
Part III: Backups
I’ve long maintained that one of the biggest strengths of AlwaysOn Availability Groups is that they allow DBAs to address both high availability and disaster recovery concerns from a single set of tooling or interfaces. This, plus their scale-out capabilities, makes them a very powerful option for addressing both HA and DR scenarios.
But, it doesn’t mean that you won’t still need backups. Simply put: Backups are always a must.
Ironically, though, the deployment of AlwaysOn Availability Groups into your environment will actually complicate backups a bit. Granted, this isn't as big of a concern or problem as the issues you’ll face when trying to address how to ensure that batch jobs operate as needed, but there a couple of important concerns that you’ll need to address – including the following:
Fragmentation. Unfortunately, fragmentation is a bit of a loaded term – in technical circles it usually causes people to think of disk fragmentation – and in SQL Server circles, it usually causes people to think about index fragmentation. The core idea in both of those cases though, is a problem where needed data doesn’t end up being contiguous – or in close proximity. Which is something you wouldn’t want to have happen to your backup files. For example, when (not if) you need to perform an emergency restore of an Availability Group database, you’re already going to have to ‘tear down’ the Availability Group first – adding a bit of complexity and time to the process. As such, the last thing you want to do is to have to ‘hunt and peck’ for your backup files – in, say, a scenario where your last FULL backup and 15 subsequent T-Log backups are on SERVER1, but the next 7 T-Log Backups are on SERVER2, and the next 18 T-Log backups are back again over on SERVER2. Happily, preventing situations like this is borderline trivial – but it will require some pro-active planning on your part.
Execution. In a similar fashion, determining which of your Availability Group hosts will actually execute backup jobs isn’t exactly rocket-science, but it will take some additional planning and effort on your part to make sure this is done correctly. (This is also a good place to point out that while ‘offloading’ backups to one of your secondary replicas can make decent sense from a performance standpoint, it’s an expensive choice as this’ll require a new/dedicated set of Enterprise Edition licenses for compliance purposes.)
Again, neither of these concerns is very vexing or complex. But failure to address these concerns correctly is, well, tantamount to setting yourself up for failure. As such, in upcoming posts we’ll look at each of these issues in more detail (there’s much more complexity here than you might initially think), and look at some options for working around these concerns, establishing best practices, and addressing advanced scenarios as well.
Part 1: Introduction
Part 2: Putting AlwaysOn into Context
Part 3: Defining Batch Jobs
Part 4: Synchronizing Server-Level Details
Part 5: Setting Up Failover Alerts
Part 6: High-Level Options and Rationale for Handling Batch Jobs
Part 7: Detecting Primary Replica Ownership
Part 8: Dynamic Detection Gotcha #1
Part 9: Dynamic Detection Gotcha #2
Part 10: Dynamic Detection Gotcha #3
Part 11: A Fork in the Road
Part 12: Issues with Enabling and Disabling Batch Jobs
Part 13: Creating and Populating a Batch Job State Table
Part 14: Establishing Synchronization Checks
Part 15: Setting Up Linked Servers
Part 16: Job Synchronization Checkup Logic
Part 17: Creating Jobs to Check on Synchronization
Part 18: Health Checks for Availability Groups
About the Author
You May Also Like
.png?width=700&auto=webp&quality=80&disable=upscale)