SQL2012 - Availability Groups - backing up via replica

dwaterson

Hi,

We have beeen using RegGate Backup for some time and recently migrated to SQL2012 and implemented AllwaysOn Availability Groups. Until now we have been backing up directly on the "primary" read/write node however we now need to move this to one of the secondary nodes.

We have RedGate Backup 7.3 installed on each node however when we try and backup on the secondary we get:

08/06/2013 10:51:40: Backing up AManagement (full database) to:
08/06/2013 10:51:40: G:\RedGateBackups\FULL_(local)_AManagement_20130608_105140.sqb

08/06/2013 10:51:40: Error 880: BACKUP DATABASE permission denied in database: (AManagement)
08/06/2013 10:51:40: SQL error 978: The target database ('AManagement') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.

I'm not sure how to make RedGate use the ApplicationIntent=ReadOnly option, or whether there is something wrong in my current setup?

On the secondary node I've connected to both the listener IP and the local IP with the same results. The AG is setup to prefer secondary for backups.

Can you point me in the right direction?

Thanks
David

James B

To the best of my knowledge, SQL Backup isn't currently compatible with availability groups in 2012, and I don't have any ETA for when this is likely to be added.

I'll add a note to the internal bug we have for this that you'd also like to see support however.

dwaterson

Hi James,

Thanks for the reply, disappointed though that availability groups aren't supported at this time, I did contact support back in September when we purchased the licences and was advised to purchase a Backup licence for each node and told "this really shouldn't present you with any problems" (after having described our setup) ..... which I took to mean it was supported.

It does mean we may have to move away from RedGate Backup at the moment as we can't have the backups running on our primary node any longer, we have to backup from one of the other replicas as this is part of the reason we implemented the solution.

I will look out for the feature being available in a later update/release.

Regards, David

James B

I understand your frustration - i'll feed it back to our development team and hopefully we can get it sorted out sooner rather than later.

dwaterson

Thanks James, I'd appreciate that.

Kind regards, David.

petey

The error you are seeing:

08/06/2013 10:51:40: Error 880: BACKUP DATABASE permission denied in database: (AManagement) 
08/06/2013 10:51:40: SQL error 978: The target database ('AManagement') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.

was raised because SQL Backup is attempting to access the database to check if you have rights to back up that database. You can disable that check altogether, by creating a DWORD registry entry, SkipChecks, with the value 1. This entry needs to be created in the

HKEY_LOCAL_MACHINE\Software\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>

registry node. However, this means that all users with permission to run the sqlbackup extended stored procedure will be able to back up and restore any database on that instance.

This workaround isn't perfect in that SQL Backup will still try to access the database to get its size but fail with the following warning:

Warning 167: Failed to get database size from server.
SQL error 978: The target database (xxx') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.

but at least you're able to back up the secondary replica database.

brentm

Modify the error checking in the command to this:

IF (@exitcode >= 500) OR (@sqlerrorcode <> 0 and @sqlerrorcode <> 976)

so that the SQL agent job will succeed and ignore the 976 error and report success

Eddie D

Further information, there is a patch for SQL Backup that will allow a backup of replica database that is a member of an always-on availability group and allow the ERASEFILES and COPYTO processes to run.

The patch suppresses SQL Error 976 that is generated so that the ERASEFILES and COPYTO processes will run.

There is still the need to create the SkipChecks registry key by creating a DWORD registry entry, SkipChecks, with the value 1. This entry needs to be created in the

HKEY_LOCAL_MACHINE\Software\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>

registry node.

Please contact the support team, [support@red-gate.com] will provide details of where to download the patch and instructions on how to install.

PLEASE NOTE: The Backup of a replica database will report successful with warnings. The backup process will still generate a warning, Warning 167: Failed to get database size from server.

Many Thanks
Eddie

RobAlexander

--> nevermind

Colin Millerchip

FYI, SQL Backup Pro v7.7 provides some improved support for AlwaysOn Availability Groups, as documented at http://documentation.red-gate.com/display/SBU7/AlwaysOn+Availability+Groups. Thanks - Colin.