---

Introduction

With the release of SQL Server 2022, Microsoft introduced several enhancements to its database engine, one of the most notable being the Contained Availability Groups. This feature represents a significant advancement in high availability and disaster recovery solutions. Contained AGs simplify database management and migration by minimizing the dependency on the SQL Server instance level objects, making them a critical feature for database administrators and developers.

Quick Summary For Dummies:

Imagine you have a favorite coffee shop where you always order your coffee through an app. One day, the coffee shop decides to open a new branch across town. To make your life easier, they ensure that your app login and order history are instantly available at the new location, just like at the original shop. This is similar to how Contained Availability Groups work in SQL Server 2022.

Contained Availability Groups are like having multiple coffee shops (servers) where your coffee order (database) can be seamlessly served, no matter which location you visit. If one shop has to close temporarily (a server failure), you can go to the other location and find everything just as you left it—your order preferences, points, and even your “usual” ready to be made. This setup ensures that your coffee experience (database access) is uninterrupted, highly available, and easily managed, no matter what happens behind the scenes.

What are Contained Availability Groups?

Contained Availability Groups extend the traditional AG functionality by encapsulating database users and their permissions within the database, rather than storing them at the instance level. This encapsulation makes the database more portable and simplifies the management of users and permissions, especially in high availability (HA) and disaster recovery (DR) scenarios.

Benefits of Using Contained Availability Groups

• Simplified Management: Eases the management of databases, especially when moving databases between servers or instances.
• Improved Portability: Enhances the portability of databases across different environments, facilitating easier disaster recovery and replication setups.
• User Isolation: Isolates database users from the instance, reducing the dependency on instance-level security settings.

Setting Up a Contained Availability Group

Prerequisites

• SQL Server 2022 installed on all nodes participating in the AG.
• A Windows Server Failover Clustering (WSFC) cluster configured with all nodes.
• Sufficient permissions to create AGs and manage databases.

Step 1: Enable Contained Databases

First, enable the contained database authentication at the SQL Server instance level.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'contained database authentication', 1;
RECONFIGURE;

Step 2: Create a Contained Database

Create a new database or alter an existing database to be contained.

CREATE DATABASE [YourDatabaseName]
CONTAINMENT = PARTIAL;
GO

Or for an existing database:

ALTER DATABASE [YourExistingDatabaseName]
SET CONTAINMENT = PARTIAL;
GO

Step 3: Create Availability Group

Create the AG specifying the databases to include. Ensure the AG is set with the correct options for your environment.

CREATE AVAILABILITY GROUP [YourAGName]
WITH (DB_FAILOVER = ON, CLUSTER_TYPE = WSFC)
FOR DATABASE [YourDatabaseName]
REPLICA ON 'Node1' WITH (ENDPOINT_URL = 'TCP://Node1.domain.com:5022', 
                        FAILOVER_MODE = AUTOMATIC, 
                        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
                        BACKUP_PRIORITY = 50, 
                        SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
            'Node2' WITH (ENDPOINT_URL = 'TCP://Node2.domain.com:5022', 
                        FAILOVER_MODE = AUTOMATIC, 
                        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
                        BACKUP_PRIORITY = 50, 
                        SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));

Step 4: Join the Database to the Availability Group

Lastly, add your database to the newly created AG.

ALTER DATABASE [YourDatabaseName] SET HADR AVAILABILITY GROUP = [YourAGName];
GO

Monitoring and Maintenance

Monitoring the health and performance of your Contained AGs is crucial. Use the following T-SQL query to check the status of your AGs:

SELECT ag.name AS AGName, 
       ar.replica_server_name, 
       drs.database_id, 
       drs.is_primary_replica, 
       drs.synchronization_health_desc
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.group_id = ar.group_id
JOIN sys.availability_groups ag ON ar.group_id = ag.group_id
WHERE ag.name = 'YourAGName';

Conclusion

Contained Availability Groups in SQL Server 2022 offer a powerful solution for managing high availability and disaster recovery in a more streamlined and efficient way. By following the steps outlined in this guide and utilizing the provided T-SQL code examples, you can set up and manage your Contained AGs, ensuring your databases are always available and easily manageable.