Summary
A client was running a Contained Availability Group in SQL Server 2022, but wasn’t using the AG Listener for their application connections. This negated most of the benefits the Contained AG was designed to provide. They also had some security misunderstandings and missteps, as this was built for them without any real knowledge transfer – one of the reasons they reached out to us for help. After review, it became clear that there was no need for a contained AG here, so we helped them migrate to a Basic Availability Group (SQL Server Availability Group in SQL Server Standard Edition) while preserving their database configurations and minimizing downtime.
Context
The customer originally implemented a Contained Availability Group to simplify credential rotation and reduce SQL Security overhead. Contained Availability Groups were introduced to centralize configurations such as logins, SQL Agent jobs, and other metadata that typically require manual synchronization across AG replicas.
Think of a Contained AG like a shipping container: everything you need is packaged together so you can move it from one ship to another without repacking. The contained AG maintains its own master and msdb system databases that are separate from the instance-level system databases, and these sync automatically between replicas.
The problem? The client’s applications connected directly to individual nodes rather than through the AG Listener. That’s like having a shipping container but carrying each item by hand anyway -you’re not getting the benefits of the container. Also got a bit messy because of some confusion from their team cleaning up some access before we came in – but there was confusion about “which master DB” they were in.
The Investigation
When connections bypass the Listener and go directly to individual nodes, you lose most of what makes a Contained AG valuable:
- Logins and credentials created in the Contained AG’s context aren’t accessible when connecting directly to a node
- Automatic failover for applications becomes manual
- The credential synchronization benefits don’t apply to direct-node connections
Since they weren’t leveraging the Listener, the Contained AG was adding complexity without providing its intended benefits. The right move was to migrate to a non-contained Availability Group.
The Migration
I approached this like a side-by-side migration, with one key difference: I didn’t need to restore the databases since they already existed on both nodes.
Preparation
- Documented everything: I scripted all AG configurations using the GUI and captured screenshots of every option. When you’re about to delete your AG configuration, you want a backup of your backup.
- Copied logins using dbaTools: Here’s the critical part—the source connection was the Contained AG Listener (which is the only way to access the contained system databases), and the destination was all nodes that would participate in the Standard AG. This ensured the logins, SIDs, and hashed passwords were identical across all nodes.
Copy-DbaLogin -Source "ContainedAGListener" -Destination "Node1", "Node2"
Cutover Day
- Took a full backup several hours before the maintenance window
- Waited for the log backup to complete on Node1
- Verified no data loss in the AG Dashboard
- Paused log backups (this is important—you’ll see why)
- Deleted the Contained AG: right-click, delete, gone
- Created the new Standard AG through the GUI, using my documented settings
- During creation, set the AG to Join Only rather than attempting to add the database
Because Node2’s databases were still in RESTORING state (from the original Contained AG replication) and I had paused log backups, I was able to re-add the databases to the new AG without re-seeding. This saved 2-3 hours of downtime that would have been required for auto-seeding to complete.
Key Takeaways
Use dbaTools to copy logins with SIDs intact. If you use other methods that don’t preserve the hashed password and SID, you can end up with authentication issues—especially for readable secondary operations.
Backup your configurations immediately before cutover. I ran Copy-DbaLogin about 10 minutes before the maintenance window to ensure I had the absolute latest login information from the Contained AG. This saved me when one login wasn’t working correctly on the secondary after the migration.
Watch the default database setting. One login was failing on the readable secondary because its default database wasn’t accessible (it was in read-only mode). The login attempt failed before the application could switch to the intended database. Copying the login from Node1 (which had the correct default database configured) resolved the issue.
Final Thoughts
Contained Availability Groups aren’t a bad choice—they just need to be the right fit for your environment and how your applications connect. If you’re using the Listener for all connections and need simplified credential management, a Contained AG can be valuable. If your applications connect directly to nodes, you’re not getting the benefits.
We care deeply about security in your SQL Server environment. Check out our community tool sp_CheckSecurity (among the others we have available) to get a jumpstart on identifying security concerns in your environment.
If you’re trying to figure out whether a Contained AG, regular AG, if you can get by with SQL Server Standard and meet your needs, or another high availability solution is right for your needs, we’d love to help. Reach out to us and let’s talk about managing your SQL Server environment.
This post is part of our Case of the Week series—real SQL Server issues and lessons from the field.