SQL2012 - Availability Groups - backing up via replica

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: JonathanWatts, Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

SQL2012 - Availability Groups - backing up via replica

Postby dwaterson » Sat Jun 08, 2013 11:08 am

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
dwaterson
 
Posts: 3
Joined: Sat Jun 08, 2013 10:57 am

Postby james.billings » Mon Jun 10, 2013 6:09 pm

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.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby dwaterson » Mon Jun 10, 2013 7:56 pm

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
dwaterson
 
Posts: 3
Joined: Sat Jun 08, 2013 10:57 am

Postby james.billings » Mon Jun 10, 2013 8:02 pm

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.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby dwaterson » Mon Jun 10, 2013 10:32 pm

Thanks James, I'd appreciate that.

Kind regards, David.
dwaterson
 
Posts: 3
Joined: Sat Jun 08, 2013 10:57 am

Postby petey » Thu Sep 26, 2013 3:49 am

The error you are seeing:

Code: Select all
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:

Code: Select all
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.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Postby brentm » Fri Nov 22, 2013 5:20 am

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
brentm
 
Posts: 4
Joined: Wed Feb 09, 2011 8:25 pm
Location: Wellington, New Zealand

Postby eddie davis » Wed Jan 29, 2014 12:32 pm

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
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 943
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software

Postby RobAlexander » Wed Jan 29, 2014 4:54 pm

--> nevermind
RobAlexander
 
Posts: 3
Joined: Tue Nov 22, 2005 8:48 pm


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 1 guest