Hi There,

I have 2 SQL server 2014 configured as AlwaysOn cluster.

I've tried to configure SQL maintenance plan on the secondary replica in order to perform daily tasks of DB full backup and transaction logs backup in order to limit the size of the transaction log (according to best practices I've tracked on the web it is recommended to perform it on one of the secondary replicas).

I've been trying to configure it based on resources that I found on the web. But on the Backup task of the maintenance plan I receive a notice that the full backup is not supported if running on the secondary replica (Although I configure the secondary replica as preferred backup target on the AG level).

I've found that full backups are not supported on the secondary replica just read only backups.

I have several questions regarding the issue described:

1) Does backup the secondary replica is indeed recommended?
2) If it is than what I need to do if I want to perform the backup task via the SQL maintenance plan available in the SQL studio administration tool.
3) if it's not possible to accomplish via the SQL studio what is the recommended way to do that.
4) Since the backup (the DB and transaction log) is conducted on the secondary replica and not on the primary one what I need to do If I want to limit the transaction log size on the primary one.
If I want to

http://blogs.msdn.com/b/sqlgardner/archive/2012/07/21/sql-2012-alwayson-and-backups-part-2-configuring-backup-preferences-and-automating-backups.aspx

http://sqlsailor.com/2012/05/15/backups-on-secondary-replicas-always-on-availability-groups/

To run a full backup on secondary you have to run it with the "COPY_ONLY" option, then it works perfectly fine. (You can't do differential backups based on that though...) For Log backups I would suggest using Log Shipping without a receiver. It's fairly simple to configure (I can send you a script if you like) and runs on secondary if the prefernce is set for it. We do that over here for pretty much all our DBs...

"Since the backup (the DB and transaction log) is conducted on the secondary replica and not on the primary one what I need to do If I want to limit the transaction log size on the primary one."

It just works.

The secondary will inform the primary of the transaction log backup, and the transaction log space will be reused.

David

The logmark (that leads to the transaction log growing until a backup is done) is transferred from the secondary to the primary once the backup is finished. So for the sakes of the Transaction log size doing a log backup on the secondary is just the same as doing it on the primary.