Creating a Contained Availability Group and Enabling Database Creation via CAG Listener

Microsoft

Jan 20, 2026

A Contained Availability Group (CAG) is designed to simplify high availability and disaster recovery by encapsulating system databases (master, msdb) within the availability group itself. This means that logins, jobs, credentials, and other metadata are automatically replicated across replicas, eliminating the need for manual synchronization and reducing operational complexity.

Starting with SQL Server 2025 CU1, you can create or restore databases directly through the CAG listener - without connecting to the physical instance - by enabling a session context key or by using the automated stored procedure [msdb].[dbo].sp_sysutility_cag_create_db.

Why Contained AGs Matter

Self-contained HA unit: Each CAG has its own copy of master and msdb, making it independent of the physical SQL instance.
Simplified failover: When the AG fails over, all associated metadata moves with it, ensuring applications continue to function without manual intervention.
Improved automation: Supports scenarios where direct access to the SQL instance is restricted, enabling operations through the AG listener.
Enhanced security: Reduces exposure by limiting instance-level access; operations can be scoped to the AG context.
Streamlined management: Eliminates the need for login and job replication scripts across replicas.

Step 1: Prepare the Database for Availability Group

This example uses SQL Server on Linux; however, the steps are identical for SQL Server on Windows.

In this walkthrough, an existing database named CAGDB is added to a Contained Availability Group (CAG). Before adding the database to the CAG, verify that it is configured for FULL recovery mode and perform a full database backup.

ALTER DATABASE CAGDB SET RECOVERY FULL; 
GO 
BACKUP DATABASE CAGDB TO DISK = N'/var/opt/mssql/backups/CAGDB.bak' WITH INIT, COMPRESSION; 
GO

Note: On Linux, ensure that the backup target directory exists and is owned by the mssql user.

sudo mkdir -p /var/opt/mssql/backups 
sudo chown mssql:mssql /var/opt/mssql/backups

Step 2: Create the Contained Availability Group

On your Linux SQL Server nodes, run:

CREATE AVAILABILITY GROUP [CAGDemo] WITH ( CLUSTER_TYPE = EXTERNAL, CONTAINED ) FOR DATABASE [CAGDB] REPLICA ON N'node1' WITH ( ENDPOINT_URL = N'tcp://node1:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = EXTERNAL, SEEDING_MODE = AUTOMATIC ), N'node2' WITH ( ENDPOINT_URL = N'tcp://node2:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = EXTERNAL, SEEDING_MODE = AUTOMATIC ); GO #Connect to secondary replicas and join the AG: ALTER AVAILABILITY GROUP [CAGDemo] JOIN WITH (CLUSTER_TYPE = EXTERNAL); ALTER AVAILABILITY GROUP [CAGDemo] GRANT CREATE ANY DATABASE;

Step 3: Configure Listener and Connect

Create a listener and connect using SSMS or sqlcmd:

ALTER AVAILABILITY GROUP [CAGDemo] ADD LISTENER N'CAGDemoListener' ( WITH IP ( (N'*.*.*.*', N'255.255.255.0') ), PORT = 1453 ); 
GO

Step 4: Connect to CAGDemoListener and attempt Database Creation (Failure)

When connected through the listener:

CREATE DATABASE TestCAGDB;

Result:\ Msg 262, Level 14, State 1: CREATE DATABASE is not allowed in this context.\ This happens because database creation is blocked by default in a contained AG session.

Step 5: Enable Database Creation in CAG Session or Instance master

EXEC sp_set_session_context = N'allow_cag_create_db', @value = 1;

This enables database creation for your current session.

Step 6: Retry Database Creation (Success)

CREATE DATABASE TestCAGDB;

Result: Database created successfully within the contained AG context.

Users with dbcreator role in the CAG context can perform this action.

Step 7: Backup the database.

ALTER DATABASE TestCAGDB SET RECOVERY FULL; 
BACKUP DATABASE TestCAGDB TO DISK = N'/var/opt/mssql/data/backups/TestCAGDB.bak';

Step 8: Add database to the CAG

ALTER AVAILABILITY GROUP [CAGDemo] ADD DATABASE [TestCAGDB];

Optional: To automate Steps 4 through 8, you can use the stored procedure [msdb].[dbo].[sp_sysutility_cag_create_db] while connected through the Contained Availability Group (CAG) listener context.

At a high level, this procedure simplifies database onboarding into a CAG by orchestrating the following actions as a single workflow:

Creating (or restoring) the database
Setting the recovery model to FULL
Taking the initial backup required for availability group seeding
Adding the database to the target Contained Availability Group

Example: Create a database

Execute the following command to create NewTestDB and add it to the target CAG.

EXEC [msdb].[dbo].sp_sysutility_cag_create_db @database_name = [NewTestDB];

Example: Restore a database

The stored procedure also supports an optional parameter, @createdb_sql, which allows you to provide a custom SQL statement to create or restore a database (for example, restoring from a backup). Once the database backup file exists and is accessible to SQL Server, you can use this parameter to perform the operation.

Important: When @createdb_sql is used, the procedure executes that SQL statement directly. Ensure the SQL statement is from a secured and trusted source.

DECLARE @restoreSql NVARCHAR(MAX);
SET @restoreSql = N'
RESTORE DATABASE AdventureWorks2022
  FROM DISK = ''/var/opt/mssql/backups/AdventureWorks2022.bak''
  WITH
      MOVE ''AdventureWorks2022''
           TO ''/var/opt/mssql/data/AdventureWorks2022.mdf'',
      MOVE ''AdventureWorks2022_log''
           TO ''/var/opt/mssql/data/AdventureWorks2022_log.ldf'',
      RECOVERY;
';
EXEC msdb.dbo.sp_sysutility_cag_create_db
     @database_name = N'AdventureWorks2022',
     @createdb_sql  = @restoreSql;

Conclusion

Contained Availability Groups represent a major step forward in simplifying high availability for SQL Server. By encapsulating system databases within the AG context, they eliminate the complexity of synchronizing logins, jobs, and credentials across replicas.

With the new capability in SQL Server 2025 CU1 to create or restore databases directly through the AG listener using sp_set_session_context, organizations can streamline automation and reduce operational overhead.