SQL AlwaysOn Availability Group Backup Tip

When running mission-critical systems such as Microsoft Dynamics CRM, it is critical keep to make sure that the on-premise SQL Server is highly available.  A good way to achieve this is with AlwaysOn Availability Groups.  One awesome advantage of this redundancy configuration is that you can use that secondary replica performance tasks such as backups without affecting the performance of the primary node.  When configuring backups however you will want your scripts to be configured on both the primary and secondary (that way no matter which server is available the backups still happen).  This is where the function sys.fn_hadr_backup_is_preferred_replica comes in handy.  It can tell your script, based on the “preference” you configure, if a server is the “preferred” replica to perform a backup.
SELECT [master].sys.fn_hadr_backup_is_preferred_replica(‘Crelate_PROD’)
One gotcha with this function however is that the name parameter is case-sensitive (even if your server is not configured to be case-sensitive). Not only that, the default return value of this function is 1 (“is preferred”); even if the name you pass in is not found! In my case, because of a simply case-sensitivity issue, both my primary and secondary thought they were the “preferred” and thus I was doing more backups than I wanted and my primary was being used for backups, when I only wanted my secondary to be. Hopefully this tip saves you some trouble.

Let's talk

If you want to get a free consultation without any obligations, fill in the form below and we'll get in touch with you.