Contained Always On Availability Groups in SQL Server 2022: Setup and Best Practices

Managing high availability in enterprise database environments requires keeping configuration data in sync. SQL Server 2022 introduced Contained Availability Groups, a feature designed to simplify replica synchronization by automatically keeping logins, SQL Agent jobs, and other server-level objects in sync across all replicas. This eliminates the need to manually recreate these objects on secondary nodes after a failover.

What is an Availability Group?

An Availability Group (AG) is a SQL Server High Availability and Disaster Recovery feature. It maintains one or more user databases in sync across multiple SQL Server instances, which are referred to as replicas.

In this setup, one replica acts as the primary database server handling active workloads, while one or more secondary replicas maintain copy databases in near real-time. If the primary instance becomes unavailable, a secondary replica takes over the active workload, a process known as a failover.

Essentially, an AG behaves like a live backup of your database running on an alternate server. If the primary system fails, the secondary replica assumes the primary role, allowing applications to remain operational.

The Limitation of Traditional Availability Groups

In a traditional Availability Group, replication is limited strictly to user databases. However, user databases rely on server-level objects that reside outside of those individual user databases. These external dependencies include:

• SQL Server Logins: Server-level authentication details that control who can connect to the SQL Server instance.
• SQL Server Agent Jobs: Scheduled background tasks, such as ETL processes, backups, and routine database maintenance.
• Permissions and Logins mappings: Explicit server-level permissions mapped to specific database-level users.

Because traditional AGs do not replicate server-level metadata, database administrators (DBAs) must manually recreate logins, credentials, and jobs on every secondary replica. If a failover occurs and these objects are missing or out of sync on the new primary replica, application connections fail and background tasks do not execute.

Introducing Contained Availability Groups

To address the limitations of traditional AGs, SQL Server 2022 introduced Contained Availability Groups. A Contained AG features its own dedicated system databases—a private master and a private msdb—contained within the Availability Group itself.

These private system databases are named using the following naming convention:

• AG Master Database (<AGName>_Master): Stores the logins, credentials, and server-level permissions belonging to the AG context.
• AG MSDB Database (<AGName>_MSDB): Stores the SQL Server Agent jobs, schedules, and alerts associated with the AG.

Because these system databases reside inside the AG, SQL Server replicates them to all secondary replicas using the same underlying replication technology used for user databases.

A Simple Analogy

A traditional AG is comparable to moving to a new home but leaving the house keys and furniture behind. In contrast, a Contained AG bundles the home, the keys, and the furniture together, moving them as a single package.

The Boundary Rule

Objects created within the scope of a Contained AG are isolated to that specific AG boundary. Only logins and jobs created under the active context of the Contained AG will reside in those private system databases. Standard server-level logins and jobs created outside the AG context will not automatically sync.

Prerequisites for Setup

Before implementing a Contained AG, ensure your environment meets the standard prerequisites:

1. Windows Server Failover Cluster (WSFC): A properly configured WSFC must exist across the target nodes.
2. Always On High Availability: This feature must be enabled within the SQL Server Configuration Manager on all instances.
3. SQL Server 2022: Because Contained AGs are a SQL Server 2022 feature, all participating replicas must run SQL Server 2022[1]. Mixed-version clusters are not supported for Contained AGs.

Step-by-Step Guide: Creating a Contained AG Using the SSMS Wizard

Creating a Contained AG follows the standard creation wizard in SQL Server Management Studio (SSMS), with a few additional configurations:

• In SSMS Object Explorer, expand Always On High Availability → Availability Groups. Right-click and select New Availability Group Wizard
• Type the Availability Group Name. You will see a new Contained checkbox — check this box. This is what makes it a Contained AG instead of a traditional one.
• The Reuse System Databases checkbox is only needed if you previously had a Contained AG with the same name and want to reuse its system databases. Leave it unchecked for a new setup. Click Next.
• In the Select Databases page, select the databases you want to include in this AG and click Next.
• In the Specify Replicas page, click Add Replica and configure:
  • Automatic Failover — check this (supports up to 5 replicas)
  • Availability Mode — set to Synchronous Commit
  • Readable Secondary — enable if you want read queries on secondary
• In the Endpoints tab, verify the port number is 5022.
• In the Listener tab, select Create an Availability Group Listener. Enter the Listener DNS name, port number, select Static IP in Network Mode, and add the virtual IP address. Click Next.
• In the Select Initial Data Synchronization page, choose Automatic Seeding for smaller databases. Click Next.
• In the Validation page, verify all checks pass. In the Summary page, confirm settings and click Finish.

Post-Creation: Managing the AG Listener

Once the setup process completes, two system databases are created and visible in SSMS:

<AGName>_Master
<AGName>_MSDB

These databases are deployed to every replica participating in the group.

Important: Use the Listener Name for Administrative Tasks

To create logins or SQL Agent jobs that replicate across all replicas, you must connect to SQL Server using the AG Listener Name. Connecting directly to an individual node's server name places those objects on that specific instance's local master or msdb database, preventing them from replicating to the other nodes.

Traditional AG vs. Contained AG: Feature Comparison

‍Note on Conversions: Once an AG is established as Contained or Traditional, you cannot modify its type. Converting between types requires deleting the AG and recreating it, which introduces planned downtime. If you must switch, a database migration strategy (such as Log Shipping or backup/restore operations) is required.

Managing Logins in a Contained AG

Logins are not automatically copied to the <AGName>_Master database when a Contained AG is created. Their replication behavior depends on where and how they were created:

Key Rule for Logins

• Only logins with sysadmin privileges at the time of AG creation are copied to the contained master database automatically.
• All other non-sysadmin logins must be created while connected directly through the AG Listener.

Best Practices for Domain Logins

• Rather than adding individual Windows domain accounts, create an Active Directory (AD) Domain Security Group, map that group as a single login in SQL Server, and manage individual permissions through Active Directory.
• Ensure that the SQL Server Service Account or the Cluster Computer Object has appropriate permissions to read the Active Directory group.

Managing SQL Server Agent Jobs

Unlike logins, existing SQL Server Agent jobs are not copied to the <AGName>_MSDB database during initialization—including jobs created by sysadmins. Every job that needs to run across the AG must be manually recreated while connected through the AG Listener.

• Jobs created via the AG Listener: Are stored in <AGName>_MSDB and replicate to all replicas.
• Jobs created by connecting directly to an instance: Are stored in the local server instance's msdb and will not replicate.

‍Common Mistake: If a DBA creates a job by connecting directly to the primary node's instance name rather than the listener, that job remains local to that specific physical node. Following a failover, the job will not exist on the new primary instance.

Understanding Connections and Failover Behavior

Your connection path determines which system context you access:

1. Connection via Server Name: Connects you to the physical server instance's local master and msdb databases. Objects created here do not replicate or fail over.
2. Connection via AG Listener: Connects you directly to the AG's private master (<AGName>_Master) and MSDB (<AGName>_MSDB) databases. Objects created under this context replicate across replicas and persist through failovers.

Important Architectural Considerations

Keep the following considerations in mind when planning your deployment:

• Version Parity: All replica instances must run SQL Server 2022 or later.
• Two Sets of System Databases: Understand whether you are connected to the instance-level system databases or the AG-level system databases.
• Document the Setup: If you host multiple Contained AGs on a single SQL Server instance, track which user databases belong to which contained group to avoid administration errors.
• WSFC Requirement: Contained AGs still require an underlying Windows Server Failover Cluster (WSFC) infrastructure.

Key Summary Points

1. Traditional Limitations: Traditional AGs replicate databases but leave logins, Agent jobs, and permissions behind, requiring manual recreation on secondary instances.
2. Contained System Databases: Contained AGs feature private, replica-synchronized versions of the master and msdb databases named <AGName>_Master and <AGName>_MSDB.
3. Always Connect via the Listener: You must connect using the AG Listener name to ensure logins and Agent jobs are written to the contained system databases and replicated.
4. Creation Step Copying: Only the sysadmin logins of the AG creator are copied during setup. All other logins and Agent jobs must be recreated through the listener.
5. No Direct Type Conversions: You cannot convert an existing traditional AG into a contained AG or vice-versa without dropping and recreating the group.

Contained Availability Groups in SQL Server 2022 resolve a long-standing high availability challenge. By removing the need for manual synchronization scripts and secondary login tracking, this feature helps database administrators ensure consistent failover behavior and reduce recovery time objectives (RTO).

Need Help Optimizing Your High Availability Architecture?

Ensuring database uptime and seamless failovers requires careful planning and robust configuration. If you are planning an upgrade to SQL Server 2022 or looking to migrate and optimize your database clusters, Mydbops can help.

Our team provides end-to-end database management, proactive performance tuning, and robust replication setups tailored to your business needs.