Contained Databases

Artikel
02/28/2023

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server helps user to isolate their database from the instance in 4 ways.

Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the master database.)
All metadata are defined using the same collation.
User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.
The SQL Server environment (DMV's, XEvents, etc.) reports and can act upon containment information.

Some features of partially contained databases, such as storing metadata in the database, apply to all SQL Server databases. Some benefits of partially contained databases, such as database level authentication and catalog collation, must be enabled before they are available. Partial containment is enabled using the CREATE DATABASE and ALTER DATABASE statements or by using SQL Server Management Studio. For more information about how to enable partial database containment, see Migrate to a Partially Contained Database.

Partially Contained Database Concepts

A fully contained database includes all the settings and metadata required to define the database and has no configuration dependencies on the instance of the SQL Server Database Engine where the database is installed. In previous versions of SQL Server, separating a database from the instance of SQL Server could be time consuming and required detailed knowledge of the relationship between the database and the instance of SQL Server. Partially contained databases make it easier to separate a database from the instance of SQL Server and other databases.

The contained database considers features with regard to containment. Any user-defined entity that relies only on functions that reside in the database is considered fully contained. Any user-defined entity that relies on functions that reside outside the database is considered uncontained. (For more information, see the Containment section later in this topic.)

The following terms apply to the contained database model.

Database boundary
The boundary between a database and the instance of SQL Server. The boundary between a database and other databases.

Contained
An element that exists entirely in the database boundary.

Uncontained
An element that crosses the database boundary.

Non-contained database
A database that has containment set to NONE. All databases in versions earlier than SQL Server 2012 (11.x) are non-contained. By default, all SQL Server 2012 (11.x) and later databases have a containment set to NONE.

Partially contained database
A partially contained database is a contained database that can allow some features that cross the database boundary. SQL Server includes the ability to determine when the containment boundary is crossed.

Contained user
There are two types of users for contained databases.

Contained database user with password

Contained database users with passwords are authenticated by the database. For more information, see Contained Database Users - Making Your Database Portable.
Windows principals

Authorized Windows users and members of authorized Windows groups can connect directly to the database and do not need logins in the master database. The database trusts the authentication by Windows.

Users based on logins in the master database can be granted access to a contained database, but that would create a dependency on the SQL Server instance. Therefore, creating users based on logins requires partial containment.

Important

Enabling partially contained databases delegates control over access to the instance of SQL Server to the owners of the database. For more information, see Security Best Practices with Contained Databases.

Database Boundary
Because partially contained databases separate the database functionality from those of the instance, there is a clearly defined line between these two elements called the database boundary.

Inside of the database boundary is the database model, where the databases are developed and managed. Examples of entities located inside of the database include, system tables like sys.tables, contained database users with passwords, and user tables in the current database referenced by a two-part name.

Outside of the database boundary is the management model, which pertains to instance-level functions and management. Examples of entities located outside of the database boundary include, system tables like sys.endpoints, users mapped to logins, and user tables in another database referenced by a three-part-name.

Containment

User entities that reside entirely within the database are considered contained. Any entities that reside outside of the database, or rely on interaction with functions outside of the database, are considered uncontained.

In general, user entities fall into the following categories of containment:

Fully contained user entities (those that never cross the database boundary), for example sys.indexes. Any code that uses these features or any object that references only these entities is also fully contained.
Uncontained user entities (those that cross the database boundary), for example sys.server_principals or a server principal (login) itself. Any code that uses these entities or any functions that references these entities are uncontained.

Partially Contained Database

The contained database feature is currently available only in a partially contained state. A partially contained database is a contained database that allows the use of uncontained features.

Use the sys.dm_db_uncontained_entities and sys.sql_modules (Transact-SQL) view to return information about uncontained objects or features. By determining the containment status of the elements of your database, you can discover what objects or features must be replaced or altered to promote containment.

Important

Because certain objects have a default containment setting of NONE, this view can return false positives.

The behavior of partially contained databases differs most distinctly from that of non-contained databases with regard to collation. For more information about collation issues, see Contained Database Collations.

Benefits of using Partially Contained Databases

There are issues and complications associated with the non-contained databases that can be resolved by using a partially contained database.

Database Movement

One of the problems that occurs when moving databases, is that some important information can be unavailable when a database is moved from one instance to another. For example, login information is stored within the instance instead of in the database. When you move a non-contained database from one instance to another instance of SQL Server, this information is left behind. You must identify the missing information and move it with your database to the new instance of SQL Server. This process can be difficult and time-consuming.

The partially contained database can store important information in the database so the database still has the information after it is moved.

Note

A partially contained database can provide documentation describing those features that are used by a database that cannot be separated from the instance. This includes a list of other interrelated databases, system settings that the database requires but cannot be contained, and so on.

Benefit of Contained Database Users with Always On

By reducing the ties to the instance of SQL Server, partially contained databases can be useful during failover when you use Always On availability groups.

Creating contained users enables the user to connect directly to the contained database. This is a very significant feature in high availability and disaster recovery scenarios such as in an Always On solution. If the users are contained users, in case of failover, people would be able to connect to the secondary without creating logins on the instance hosting the secondary. This provides an immediate benefit. For more information, see Overview of Always On Availability Groups (SQL Server) and Prerequisites, Restrictions, and Recommendations for Always On Availability Groups (SQL Server).

Initial Database Development

Because a developer may not know where a new database will be deployed, limiting the deployed environmental impacts on the database lessens the work and concern for the developer. In the non-contained model, the developer must consider possible environmental impacts on the new database and program accordingly. However, by using partially contained databases, developers can detect instance-level impacts on the database and instance-level concerns for the developer.

Database Administration

Maintaining database settings in the database, instead of in the master database, lets each database owner have more control over their database, without giving the database owner sysadmin permission.

Limitations

Partially contained databases do not allow the following features.

Replication, change data capture, or change tracking.
Numbered procedures
Schema-bound objects that depend on built-in functions with collation changes
Binding change resulting from collation changes, including references to objects, columns, symbols, or types.

Warning

Temporary stored procedures are currently permitted. Because temporary stored procedures breach containment, they are not expected to be supported in future versions of contained database.

Identifying Database Containment

There are two tools to help identify the containment status of the database. The sys.dm_db_uncontained_entities (Transact-SQL) is a view that shows all the potentially uncontained entities in the database. The database_uncontained_usage event occurs when any actual uncontained entity is identified at run time.

sys.dm_db_uncontained_entities

This view shows any entities in the database that have the potential to be uncontained, such as those that cross-the database boundary. This includes those user entities that may use objects outside the database model. However, because the containment of some entities (for example, those using dynamic SQL) cannot be determined until run time, the view may show some entities that are not actually uncontained. For more information, see sys.dm_db_uncontained_entities (Transact-SQL).

database_uncontained_usage event

This XEvent occurs whenever an uncontained entity is identified at run time. This includes entities originated in client code. This XEvent will occur only for actual uncontained entities. However, the event only occurs at run time. Therefore, any uncontained user entities you have not run will not be identified by this XEvent

Dela via