SQL2012 - Availability Groups - backing up via replica
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
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
Comments
I'll add a note to the internal bug we have for this that you'd also like to see support however.
Redgate Software
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
Redgate Software
Kind regards, David.
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:
but at least you're able to back up the secondary replica database.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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
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
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com