Sql-server – Overlap in LSN’s backed up

Wouldn't you want to ensure the secondary logs are restored to the secondary server at certain intervals? With log shipping, I thought you ship the logs over to your secondary DB and then restore from those to keep the secondary up-to-date as close as possible to your primary.

Do you see any reason why you would not want to automate the log shipping restore on your secondary DB in the log shipping configuration?

Once those log files are applied to secondary, then the job will purge the logs that are not needed any longer since those are reflected in the secondary DB at that time.

Test it out and see how it goes.

EDIT: @nulldotzero

Okay, since you cannot run restore operations on primary or secondary in an AlwaysOn configuration, and because you are also copying the TRN log files to the seconary as well as the DR for redundancy, then you could just setup a SQL Agent job on the secondary instance to do the below for whatever hours you feel need to be purged.

--This will set the date time stamp to pass onto the Stored Proc for 72 hours from the current date when it runs
--The stored proc will recursively delete all TRN files from the parent level specified all the way down
declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(hh, -72, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

--5th argument below "0 - don't delete recursively (default)" and  "1 - delete files in sub directories"
EXECUTE master.dbo.xp_delete_file 0,N'S:\MSSQL\Backup\TransactionLog\',N'trn', @DeleteDate,1

Since the log backup files are purged from the locations which the LSRestore for DR runs and grabs those, then that job will take care of those once the restore job completes successfully.

I will answer your second question.

If I do my backups in my secondary replica, the log files in my primary replica will continue to grow big since it doesn't get truncated coz the log is not backed up in the primary replica

NO, this is where Availability Groups are so nice. No matter on which secondary replica you take transaction log backup it would work. What happens is when you take backup on secondary replica and after it finishes the secondary replica will give all information related to log backup to primary replica like LSN and VLF's that can be marked as truncated and accordingly primary replica does the changes. Bottom line is you can take log backup on any replica and the changes would be reflected on primary.

I would suggest you read Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups). From the BOL I quote

A consistent log chain is ensured across log backups taken on any of the replicas (primary or secondary), irrespective of their availability mode (synchronous-commit or asynchronous-commit).

So you can see you can take transaction log backup on any replica no matter whether it is synchronous or asynchronous replica.

Is it possible to do the full backups on the secondary replica and log backups on the primary replica to avoid the log in the primary replica growing too big? Will it work if we restore the database?

Yes you can take full backup on secondary replica but you have to make sure that they are COPY_ONLY full backups. As already pointed in BOL article shared above.

Backup Types Supported on Secondary Replicas

•BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it is executed on secondary replicas. Note that copy-only backups do not impact the log chain or clear the differential bitmap.

•Differential backups are not supported on secondary replicas.

Please note that the backup history is kept in the msdb database of the replica the backup got executed on. This implies that backup history and chain cannot be retrieved out of one instance only.

Differential backup can only be done on instance which is primary node.

Restore will work as normal but I must tell you cannot just restore database which is part of availability group you need to first evict it out of AG and then perform the restore.

At last I suggest you to read MSDN blog series on Availability Groups backup and restore by SQLGardner for more details

If you are backing up lot of big databases it does create lot of I/O so your idea to configure backup when load is relatively very less is correct. Normally during midnight or during maintenance windows load is relatively very less you can physically look at server to see the load and configure backup at that time.