SQL Server Contained Database

Contained Databases is a database isolated from other databases and the instance of SQL Server hosting the database.

SQL Server helps the user isolate the database in 4 ways.

1. Most of the metadata that defines a database is kept in the database

(in addition to or instead of preserving metadata in the master database.)

2. All metadata are defined using the same collation.

3. User authentication can be performed by the database, reducing the SQL Server instance’s dependency on login databases.

4. The SQL Server environment (DMVs, XEvents, etc.) reports and can act on coverage information.

The database feature, which looks nice in theory, becomes available in an “Always-On” architecture with SQL Server 2022.

So, what is the advantage of Contained Databases coming in SQL Server Always-On?

All SQL Server availability features (secondary replica/warm standby/mirror ) except Always On Failover Cluster Instances (FCI) have a problem.

This issue is when secondary takes over as new boss after failover, some items such as SQL Server Agent, login etc.

It is the status of processes such as staying on the primary server.

This process has always been a manual issue for us database administrators to handle, and this is a longstanding problem for those responsible for administering SQL Server.

In order to eliminate such a situation, Contained Availability Groups, which came with SQL Server 2022, solved this problem by synchronizing their master and msdb databases as part of the AG mechanism.

Considering the new features, I think it will be important to upgrade your current SQL Server version to the new SQL Server 2022 version.