SQL Server Contained Availability Groups are new, shiny feature that has great use cases and potential. With that said, you may want to hold off on using them if you haven’t taken the time to understand the impact and limitations. By next version of SQL Server, I see this getting closer to being the standard of how we deploy Availability Groups. For now, pay attention to when you use this because it could impact your scenario.
Now for some more depth. Refer to the documentation for the latest updates on this topic: What is a contained availability group? – SQL Server Always On | Microsoft Learn
First:
There are bugs. DO NOT set up a contained availability group before applying the most recent Cumulative Update for SQL Server. There are several bugs in the RTM version of SQL 2022 and in the earlier CUs. If you set up a Contained AG prior to applying the latest CUs, you may end up having to tear down the Contained AG you have created, Apply the CU, and set the AG back up again to get items to function correctly. Not a huge deal if you haven’t gone to production yet but a pain if you are in production. Here are the steps that Microsoft gives if you hit a bug that causes these types of scenarios:
- Script the server-level objects and SQL Server Agent objects.
- Drop the contained availability group.
- Apply the cumulative update.
- Recreate the contained availability group.
Second:
Understand the impact to items like Jobs, Logins and DB Mail. The core functionality of how Contained Availability Groups manage these objects is nice, but you might be surprised at how it does some things you don’t expect. For example, when you set up Database Mail, the profile is ONLY visible to the contained AG. You need to set up profiles in each contained AG. Logins, Jobs are the same. Once you figure out this pattern it becomes natural but it will change how you manage your environment quite drastically.
Third:
Pay attention to limitations. These may change over time but the feature is new so limitations are to be expected:
Not Supported at all:
- SQL Server Replications of any type (Transactional, Merge, Snapshot, etc).
- Distributed Availability Groups
- Log shipping where the target database is in the contained availability group. Log shipping with the source database in the contained availability group is supported.
Summary of Contained Availability Groups is they are a cool technology but not quite ready for Prime Time. Tread lightly into the world of Contained Availability Groups. Get comfortable with them in smaller environments and find a pattern that works for you company.
