Introduction

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 (Pacemaker cluster assumed), 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'10.2.20.50', 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 5: Retry Database Creation (Success)

CREATE DATABASE TestCAGDB;

Result: Database created successfully within the contained AG context.

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

Step 6: Backup the database.

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

Step 7: Add database to the CAG

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

**Automation: **To automate Steps 4 through 7, execute the following stored procedure while connected through the CAG Listener context.

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

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.

• What Is a Contained Availability Group
• Deploy a Pacemaker Cluster for SQL Server on Linux