John Morehouse takes us through contained availability groups:
Availability Groups have been one of the best high availability and disaster recovery options in SQL Server for a long time. They let us move a group of user databases together, keep replicas synchronized, offload some read-only workloads, and give applications a listener instead of a single server name.
That works great until the application needs more than just the user databases.
What about the logins? SQL Agent jobs? Permissions? Objects that live in
masterormsdb? In a traditional Availability Group, those objects are still tied to each SQL Server instance. That means DBAs have to keep them synchronized manually across replicas. Miss one login, job, credential, or permission and the failover might technically succeed while the application still has a very bad day.
Read on to see how contained availability groups can help resolve these problems.