1

Is it possible to view a history of when the preferred replica in Availability Group changed?

E.g.

<datetime> Preferred replica changed from InstanceA to InstanceB

Not expecting it to be in any specific format, just trying to be clear as to what I am referring to.

1 Answer 1

2

... the preferred replica in Availability Group changed?

Availability Groups don't have preferred replica. They have primary and secondary replicas, but not preferred. Windows Server Failover Clustering has preferred owners. Are you looking for who was the primary replica or who was a preferred owner?

If it's the WSFC preferred owner you are looking for, then you'll have to scrape the cluster log.

If it's the primary and secondary replicas, then nothing exists out of the box - you'll need to write your own. This data can be gathered from a few different sources:

  1. AlwaysOn Health extended event session
  2. SP_Server_Diagnostics output
  3. Polling the DMVs and recording changes
  4. Scraping the cluster log

You don't need all of those, it's a mix and match sort of thing. Basically you'll want to find out who was primary at the moment the logs started and then look for replica state changes. You'll need all of the logs from all of the replicas to get the most complete picture - but having a single log from a single replica can give you all the data needed as well, just takes a bit more data manipulation to get there.

2
  • Thanks Sean, this is a great explanation. The reason I thought the preferred replica terminology I was using was correct, was because I viewed it with sys.fn_hadr_backup_is_preferred_replica ( 'dbname' ) Commented Mar 10, 2017 at 10:20
  • I am looking into all the options you suggested, thank you for the great answer Commented Mar 10, 2017 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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