Free SQL Help for Devs, DBAs, and the Curious

Welcome to a new blog post from SQLSupport.org. Today, we will dive into SQL Server 2022 and explore the concept of Contained Availability Groups. Contained Availability Groups, an exciting feature introduced in SQL Server 2022, provide an enhanced level of database availability and disaster recovery. This feature comes as an upgrade to the traditional Availability Groups introduced in SQL Server 2019.

Contained Availability Groups enable database-level failover, without the dependency of an instance-level failover that was a requirement in traditional Availability Groups. This feature brings the ability to have separate failover policies for each database, providing a high level of flexibility and control for administrators. This is a significant leap forward from SQL Server 2019 where the failover unit was the entire Availability Group.

Let's have a look at a simple T-SQL example for creating a Contained Availability Group in SQL Server 2022:

-- Creating a Contained Availability Group

CREATE AVAILABILITY GROUP [TestAG]

WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)

FOR DATABASE [TestDB]

REPLICA ON

    N'ServerA' WITH (ENDPOINT_URL = N'TCP://ServerA:5022',

    FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT),

    N'ServerB' WITH (ENDPOINT_URL = N'TCP://ServerB:5022',

    FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

Contained Availability Groups also bring in the concept of autonomous databases, a feature that is already popular in other database technologies like MySQL 8.0 and DB2 11.5. Autonomous databases provide self-managing and self-tuning capabilities, reducing the need for manual database administration tasks. This feature also brings SQL Server 2022 a step closer to the capabilities of Azure SQL and Azure Synapse, which already provide these features.

Another benefit of Contained Availability Groups is that they simplify the database migration process. In SQL Server 2019, migrating an Availability Group to a new server required a significant amount of manual intervention. With Contained Availability Groups, the migration process has been streamlined as each database can be moved independently. This is similar to the migration capabilities provided by Azure SQL and Azure Synapse.

Contained Availability Groups also provide enhanced security features. Each database in a Contained Availability Group has its own set of users and permissions, creating a secure and isolated environment. This is similar to the security features provided by MySQL 8.0 and DB2 11.5. Here's a T-SQL example on how to create a new user in a Contained Availability Group:

-- Creating a new user in a contained database

USE [TestDB];

CREATE USER [TestUser] WITH PASSWORD=N'TestPassword';

In conclusion, the introduction of Contained Availability Groups in SQL Server 2022 is a significant enhancement in the world of database management and administration. It provides high flexibility, enhanced security, and ease of migration, bringing SQL Server 2022 on par with other popular database technologies like MySQL 8.0, DB2 11.5, Azure SQL, and Azure Synapse. Stay tuned for more exciting features and updates from SQL Server 2022 in our upcoming posts.