SQL Server Availability Groups – Notification When New Database is Added

... 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.

What we would like to also see in the email is WHY and/or WHO.

Then you have quite a good bit of development work ahead of you. Seriously.

Everyone asks this same question, but it is not an easy one to answer with any more resolution than "It failed over at this time." Things such as virtualization make this even harder as the tools and the hypervisor itself may do things that are outside the purview of your sandbox.

However, to get you started, places to scrub for data:

1. SQL Server Errorlog
2. Application Event Log
3. System Event Log
4. Cluster Log/Clustering Event Log
5. Hypervisor Logs/Database
6. Internal Lists of Downtimes/Upgrades/Patches/Etc.

Best of luck.

We have other teams in there that restart, test and delete things all the time and we would like to know if this is someone working on something vs something wrong.

That's an organizational information sharing/Administration issue and not one you're going to have much help in dealing with through a simple SQL Server Alert. In fact, to do this properly, you'll want to take SQL Server out of the picture!

Most likely you're going to need to write a service that constantly consumes event logs, traces, error logs, etc., and then takes action when issues occur.