I was wondering if there was a way to include a verification step before a backup on a secondary replica under asynchronous commit mode in availability groups to verify that the secondary replica is up to date with the primary. We intend to run backups on secondary replicas only to keep load off of our primary. However, we do not want to sacrifice performance by using synchronous commit so we must stick with asynchronous.
Current setup is a FCI as the primary, and 2 additional instances setup as secondary replicas. All configured as asynchronous commit mode. One secondary is read-intent only, another is a readable secondary.
Is there any solution that you know of that could essentially give us the best of both worlds?
Here's a query that I use to check AG latency. I grabbed it from somewhere on the Internet and then changed it a bit.
WITH PrimaryReplica (group_id, name) AS ( SELECT ag.group_id ,ag.name FROM master.sys.dm_hadr_availability_replica_states ars JOIN master.sys.availability_groups ag ON ars.group_id = ag.group_id WHERE ars.role_desc = 'PRIMARY' ), PrimaryHardenedLsn (group_id, group_database_id, last_hardened_lsn) AS ( SELECT PrimaryReplica.group_id ,dm_hadr_database_replica_states.group_database_id ,dm_hadr_database_replica_states.last_hardened_lsn FROM master.sys.dm_hadr_database_replica_states JOIN PrimaryReplica ON dm_hadr_database_replica_states.group_id = PrimaryReplica.group_id JOIN master.sys.availability_replicas ON dm_hadr_database_replica_states.replica_id = availability_replicas.replica_id WHERE availability_replicas.replica_server_name = @@SERVERNAME ) SELECT [Server] = availability_replicas.replica_server_name ,DatabaseName = dm_hadr_database_replica_cluster_states.database_name ,AvailabilityGroupName = PrimaryReplica.name ,AvailabilityGroupListenerName = agl.dns_name ,AvailabilityMode = availability_replicas.availability_mode_desc ,SynchronizationState = dm_hadr_database_replica_states.synchronization_state_desc ,LatencySeconds = CASE WHEN dm_hadr_database_replica_states.last_hardened_lsn = PrimaryHardenedLsn.last_hardened_lsn THEN 0 WHEN dm_hadr_database_replica_states.last_commit_time IS NULL THEN 0 ELSE CASE WHEN DATEDIFF(s, dm_hadr_database_replica_states.last_commit_time, GETDATE()) < 0 THEN 0 ELSE DATEDIFF(s, dm_hadr_database_replica_states.last_commit_time, GETDATE()) END END ,RecoveryLsn = dm_hadr_database_replica_states.recovery_lsn ,TruncationLsn = dm_hadr_database_replica_states.truncation_lsn ,LastSentLsn = dm_hadr_database_replica_states.last_sent_lsn ,LastSentTime = dm_hadr_database_replica_states.last_sent_time ,LastReceivedLsn = dm_hadr_database_replica_states.last_received_lsn ,LastReceivedTime = dm_hadr_database_replica_states.last_received_time ,LastHardenedLsn = dm_hadr_database_replica_states.last_hardened_lsn ,LastHardenedTime = dm_hadr_database_replica_states.last_hardened_time ,LastRedoneLsn = dm_hadr_database_replica_states.last_redone_lsn ,LastRedoneTime = dm_hadr_database_replica_states.last_redone_time ,LogSendQueueSize = dm_hadr_database_replica_states.log_send_queue_size ,LogSendRate = dm_hadr_database_replica_states.log_send_rate ,RedoQueueSize = dm_hadr_database_replica_states.redo_queue_size ,RedoRate = dm_hadr_database_replica_states.redo_rate ,EndOfLogLsn = dm_hadr_database_replica_states.end_of_log_lsn ,LastCommitLsn = dm_hadr_database_replica_states.last_commit_lsn ,LastCommitTime = dm_hadr_database_replica_states.last_commit_time ,CreatedDate = SYSDATETIME() ,CreatedBy = SUSER_SNAME() FROM master.sys.dm_hadr_database_replica_states JOIN PrimaryReplica ON dm_hadr_database_replica_states.group_id = PrimaryReplica.group_id JOIN master.sys.availability_replicas ON dm_hadr_database_replica_states.replica_id = availability_replicas.replica_id JOIN master.sys.dm_hadr_database_replica_cluster_states ON dm_hadr_database_replica_cluster_states.group_database_id = dm_hadr_database_replica_states.group_database_id AND dm_hadr_database_replica_states.replica_id = dm_hadr_database_replica_cluster_states.replica_id JOIN PrimaryHardenedLsn ON dm_hadr_database_replica_states.group_id = PrimaryHardenedLsn.group_id AND dm_hadr_database_replica_states.group_database_id = PrimaryHardenedLsn.group_database_id LEFT JOIN master.sys.availability_group_listeners agl ON PrimaryReplica.group_id = agl.group_id ORDER BY dm_hadr_database_replica_cluster_states.database_name, availability_replicas.replica_server_name
This is fantastic! Thank you! Now I just need to configure it to go ahead and run the full backup IF the LastHardenedLsn on the secondary replica matches the LastHardenedLsn on the primary replica
Is your system not that busy? We'd never get backups on busy systems if we had to wait until the two were equal between a primary replica and an async replica.
But is the backup load really adding any kind of load to the primary? I've never bothered with offloading them to a secondary replica.
Our nightly full backups run around 12am or 1am when business has been closed for a while. Our databases don't get beat up much at all outside of business hours.
Do you run index rebuilds or any other big transactions at night? That'll greatly impact the AG latency.