1

I have one Primary and one secondary replica on WSFC and AAG configuration. Both nodes are physical machines and sync with automatic fail over.

The secondary replica is readable and we run maintenance job on it but now we want to keep the "Readable Secondary" setting to "NO" instead of "Readable" and move the maintenance jobs to Primary replica so the secondary replica becomes pure passive node instead of active which saves licensing cost.

My question is - Does changing the readable secondary replica setting to "NO" will make it passive node please?

2

You have to do two things

  1. ALTER AVAILABILITY GROUP group_name SECONDARY_ROLE ALLOW_CONNECTIONS = NO

The above will not allow connections to secondary. So your secondary will be TRUE standby and will be used when you do a failover.

  1. ALTER AVAILABILITY GROUP group_name AUTOMATED_BACKUP_PREFERENCE = 'PRIMARY'

The above will indicate that your maintenance especially, backups will always happen on PRIMARY replica.

Since we are talking about maintenance, its worth mentioning the Recommendations for Index Maintenance with AlwaysOn Availability Groups

  • 1
    1. ALTER AVAILABILITY GROUP group_name Modify Replica ON N'node_name' With (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)) 2. ALTER AVAILABILITY GROUP group_name Set (AUTOMATED_BACKUP_PREFERENCE = PRIMARY) These works for me - – Jared Mar 31 '16 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.