Welcome to IDERA Virtual Education. In this video, we are going to talk about the automated backup of databases that reside within Always On availability groups (AGs). There are a couple of key things to think about when you are backing up a database that has part of an AG. When we do our scheduled backups or our automated backups within SQL Safe Backup, those are done through what we call policies. A policy itself is really more than just a schedule. When you look at an individual policy, you can have multiple objects that make up the policy. Right so, in this particular environment, we are talking about a two-node AG. So AG1 and AG2. And in this example, I have only included one particular database. That being said, I could have included any database that was part of that AG. Now it is important to understand that you need to have both or all nodes that are associated with the available availability group within the policy. So if I decide to add another server to this particular environment, the AG environment that is A3, for example. I would need to make sure that we add that to this policy when that new server is created. And the reason why that has important is the policy is going to rely on the settings within the availability group properties to understand which replicas or which replicas or the secondary of the primary should be backed up. Leveraging the settings that are built into the AG properties themselves. So, in this case, it is going to prefer the secondary. I could say secondary only. I could say primary or any replicas. And then I could put a weight around that as well. But in my environment, we prefer the secondary replicas. So SQL Safe Backup itself is going to identify that, in this case, AG2 to be the one to be backed up. So it is not terribly complicated. The trick to scheduling backups within availability groups is to make sure that both or all nodes in this case both nodes of the AG are part of that policy. And then, as you move forward in the policy, it is relatively straightforward. Just like any other policy create. You would specify the different compression settings that you are interested in. If you wanted to encrypt, you would specify the encryption level as well as your password. Then we would get into the accounts that we are going to be leveraging to talk to the target location. Which is to find down below. You can look at your grooming policy. You know how many days or hours or minutes or weeks or months do you want to pertain these backup files for. And then ultimately set up your schedules. So I am going to back up this database every night at midnight. The tool SQL Safe Backup is going to identify for me leveraging the properties within the AG to figure out exactly which server. In this case, it will run on AG2. Relatively simple. The last steps of creating a schedule really are talking about how do you want to be notified. Obviously, most people do not want to be notified on successes. You certainly could if you would like. But there are lots of other outcomes that could have been taking place on a backup that you would want to be notified on. And then, ultimately, when you hit finish what it is going to do is it is going to reach out to those particular servers, and it will create SQL jobs for you right. So the SQL agent jobs that reside on the individual nodes, in this case, are the ones that actually do the backup. So they will talk to each other. They will figure out which one is the primary replicas and which one is the secondary replicas. And then, based on the settings that you have configured within the AG, it will choose the appropriate node. So it is not a very difficult thing for SQL Safe Backup to deal with backup and recovery of an AG. But there are a few things that you need to be aware of. In this case, when you are doing scheduling, making sure that each node within the AG is part of the existing policy. So that has how you do a backup of an AG database within SQL Safe Backup. Thank you for watching.

Topics : Database Backup,

Products : SQL Safe Backup,

How to Configure SQL Safe Backup Policies with Availability Replicas?

IDERA SQL Safe Backup provides a high-performance backup and recovery solution for SQL server databases. It reduces database backup time by up to 50% over native SQL backups and reduces backup disk space requirements by up to 95% through its state-of-the-art compression and encryption technologies.

SQL Safe Backup allows DBAs to develop backup policies for individual or multiple servers and apply them throughout the SQL environment. It supports four standard databases backup types: Full, Differential, File, and Transaction Log backups. The SQL Safe Restore wizard allows DBAs to simultaneously restore individual databases or multiple databases on the same or different SQL Server instances.So save time and space with IDERA SQL Safe Backup. Learn More →

Contact IDERA: