Red Gate forums :: View topic - SQL2012 - Availability Groups - backing up via replica
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

SQL2012 - Availability Groups - backing up via replica

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
dwaterson



Joined: 08 Jun 2013
Posts: 3

PostPosted: Sat Jun 08, 2013 11:08 am    Post subject: SQL2012 - Availability Groups - backing up via replica Reply with quote

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
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Mon Jun 10, 2013 6:09 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
dwaterson



Joined: 08 Jun 2013
Posts: 3

PostPosted: Mon Jun 10, 2013 7:56 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Mon Jun 10, 2013 8:02 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
dwaterson



Joined: 08 Jun 2013
Posts: 3

PostPosted: Mon Jun 10, 2013 10:32 pm    Post subject: Reply with quote

Thanks James, I'd appreciate that.

Kind regards, David.
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2306

PostPosted: Thu Sep 26, 2013 3:49 am    Post subject: Reply with quote

The error you are seeing:

Code:
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:
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
Back to top
View user's profile Send private message Send e-mail
brentm



Joined: 09 Feb 2011
Posts: 4
Location: Wellington, New Zealand

PostPosted: Fri Nov 22, 2013 5:20 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Yahoo Messenger MSN Messenger
eddie davis



Joined: 14 Jun 2006
Posts: 939
Location: Red Gate Software

PostPosted: Wed Jan 29, 2014 12:32 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
RobAlexander



Joined: 22 Nov 2005
Posts: 3

PostPosted: Wed Jan 29, 2014 4:54 pm    Post subject: Reply with quote

--> nevermind
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group