4

In a MS SQL Server 2014 AlwaysOn AG setup, I would like to schedule backup jobs for a given availability group. The ultimate goal is to have the regular backups run on the synchronized secondary and, most importantly, not bound to the availability of a particular secondary node.

The approach I have seen so far was to use the SQL Server scheduler, set up identical jobs on all running instances and introduce conditional logic into the scheduler steps in order to determine if the role is primary or secondary. This won't work for my use case for several reasons:

  1. I want to run the scheduled action on a secondary just once, but I have multiple secondaries
  2. I want to ensure that it is ultimately run, regardless if there are any secondaries left at all - if no secondaries are left, it should run on the primary

The backup job consists of running BACKUP LOG [...] WITH COMPRESSION, NOINIT, NOFORMAT every 15 minutes.

Right now, I am thinking about creating a clustered scheduled task bound to the respective AG's failover cluster role but wondered if there is a more easy and streamlined way to implement this.

|improve this question
  • What is preventing you from running the log backups on primary ? Every 15 mins, is a reasonable amount of time and the log backups will be small unless you have a really busy system with lots of changes happening ! – Kin Shah Jun 29 '16 at 14:17
  • @Kin it's additional I/O for the transaction log disks. Plus, it likely will evict data and index pages from cache. Basically, we just don't want the backup cycle to interfere with the system's performance, especially at times when it is taking high insert loads. – syneticon-dj Jun 29 '16 at 23:37
5
+100

In a MS SQL Server 2014 AlwaysOn AG setup, I would like to schedule backup jobs for a given availability group. The ultimate goal is to have the regular backups run on the synchronized secondary and, most importantly, not bound to the availability of a particular secondary node.

This can be accomplished without issue. Just keep in mind that your systems should be spec'd to be able to take the full load of the synchronization mechanics for AlwaysOn Availability Groups, plus the redo needed, plus the backups. The last thing that you want is to overwhelm your IO system and cause outages or a large redo queue. This is assuming it's not actively being used as a readable secondary - if it is, you'll want to factor that in and watch for blocked redo threads.

1.I want to run the scheduled action on a secondary just once, but I have multiple secondaries 2.I want to ensure that it is ultimately run, regardless if there are any secondaries left at all - if no secondaries are left, it should run on the primary

There is an Availability Group setting and a system function that can be used in order to accomplish this within your expected guidelines.

The first is the setting, per AG, called AUTOMATED_BACKUP_PREFERENCE which has four different options. The one you're describing is called SECONDARY which will prefer any secondary node over the primary node. The secondary node chosen is by the BACKUP_PRIORITY which is set per replica ranging from 0-100, where the higher the number the more weight it has. If there is a tie in weights for secondary replicas, the replica that is sorted first given the system collation will be chosen. If all secondary replicas should fail, the primary replica will be chosen.

The second part of the equation is the system function used to check if the replica that is running the job is the preferred replica based on the values in the previous paragraph. This system function is called sys.fn_hadr_backup_is_preferred_replica(). Given a database name (any database in the AG) a value of 0 will be returned if it is not the preferred replica and a value of 1 if it is preferred.

When creating the agent job to accomplish this, you'll want to wrap the backup logic in an IF conditional to check for the preferred replica. That's it. Put the identical agent job on all replicas.

Please be sure to test that this truly is what you want and expect.

|improve this answer
  • 1
    Optional to using the logic check, is just use maintenance plan to perform the backup...or Ola Hallengren's DatabaseBackup proceudre that is AG aware and would honor the backup preference configuration. – user507 Jun 29 '16 at 16:13
  • Both are options, but I would highly stay away from maintenance plans as only the backup option understands Availability Groups in 2012 and 2014. In 2016, this has changed to include the other backup centric tasks. – Sean Gallardy Jun 29 '16 at 17:04
  • Thanks, sounds like this will get me where I need to go. Although the entire procedure does feel rather ... manual. I kind of expected a clustered version of the SQL Server agent and some built-in logic for BACKUP DATABASE to be performed on the desired node automagically when connecting through a listener. – syneticon-dj Jun 29 '16 at 23:30
  • Yes @syneticon-dj, it is quite manual at the moment. If you believe that this should be more built in and automatic, please open a connect item at connect.microsoft.com and then please link it in your original post or a comment. – Sean Gallardy Jun 29 '16 at 23:41
0

I have very good experience with Tivoli Storage FlashCopy Manager (TSM DP for SQL Server). TSM DP allows backups from any node in the availability group. Redundant backups are avoided by storing the backups and logs in one TSM node.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.