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:
- Does backup the secondary replica is indeed recommended?
- 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.
- if it's not possible to accomplish via the SQL studio what is the recommended way to do that.
- 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