包含的数据库Contained Databases

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

“包含数据库” 是独立于其他数据库以及承载数据库的 SQL ServerSQL Server 实例的一种数据库。A contained database is a database that is isolated from other databases and from the instance of SQL ServerSQL Server that hosts the database. SQL Server 2019 (15.x)SQL Server 2019 (15.x) 以 4 种方法帮助用户使其数据库独立于实例。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. (除此之外或代替在 master 数据库中维护元数据。)(In addition to, or instead of, maintaining metadata in the master database.)

  • 使用相同的排序规则定义所有元数据。All metadata are defined using the same collation.

  • 数据库可执行用户身份验证,因此减少了对 SQL ServerSQL Server实例的登录名的数据库依赖关系。User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL ServerSQL Server.

  • SQL ServerSQL Server 环境(DMV、XEvent 等)报告并可以执行包含信息。The SQL ServerSQL Server environment (DMV's, XEvents, etc.) reports and can act upon containment information.

部分包含的数据库的某些功能(例如将元数据存储在数据库中)适用于所有 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 数据库。Some features of partially contained databases, such as storing metadata in the database, apply to all SQL Server 2019 (15.x)SQL Server 2019 (15.x) databases. 部分包含的数据库的某些优点(例如数据库级别身份验证和目录排序规则)必须在可用后才能实现。Some benefits of partially contained databases, such as database level authentication and catalog collation, must be enabled before they are available. 使用 CREATE DATABASEALTER DATABASE 语句或者通过使用 SQL Server Management StudioSQL Server Management Studio可启用部分包含。Partial containment is enabled using the CREATE DATABASE and ALTER DATABASE statements or by using SQL Server Management StudioSQL Server Management Studio. 有关如何启用部分数据库包含的详细信息,请参阅 Migrate to a Partially Contained DatabaseFor more information about how to enable partial database containment, see Migrate to a Partially Contained Database.

部分包含的数据库概念Partially Contained Database Concepts

完全包含的数据库包括定义数据库所需的所有数据库设置和元数据,它与安装数据库的 SQL Server 数据库引擎SQL Server Database Engine 实例没有配置依赖关系。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 数据库引擎SQL Server Database Engine where the database is installed. 在以前的 SQL ServerSQL Server版本中,将一个数据库与 SQL ServerSQL Server 实例隔离开来可能会很耗时,并且要求该数据库与 SQL ServerSQL Server实例之间的关系的详细知识。In previous versions of SQL ServerSQL Server, separating a database from the instance of SQL ServerSQL Server could be time consuming and required detailed knowledge of the relationship between the database and the instance of SQL ServerSQL Server. 通过部分包含的数据库,可以轻松地将 SQL ServerSQL Server 实例上的数据库与其他数据库隔离开来。Partially contained databases make it easier to separate a database from the instance of SQL ServerSQL 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
数据库和 SQL ServerSQL Server实例之间的边界。The boundary between a database and the instance of SQL ServerSQL 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
具有设置为 NONE的包含的数据库。A database that has containment set to NONE. 版本早于 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 的所有数据库均属于非包含数据库。All databases in versions earlier than SQL Server 2012 (11.x)SQL Server 2012 (11.x) are non-contained. 默认情况下,所有 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 和更高版本数据库的包含都设置为 NONEBy default, all SQL Server 2012 (11.x)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 ServerSQL 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 主体Windows principals

    授权的 Windows 用户和授权的 Windows 组的成员可直接连接到数据库,而无需登录 master 数据库。Authorized Windows users and members of authorized Windows groups can connect directly to the database and do not need logins in the master database. 数据库信任 Windows身份验证。The database trusts the authentication by Windows.

基于 master 数据库中登录名的用户可被授予对包含数据库的访问权限,但将在 SQL ServerSQL Server 实例上创建依赖关系。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 ServerSQL Server instance. 因此,基于登录创建用户需要部分包含。Therefore, creating users based on logins requires partial containment.

重要

启用部分包含数据库会将对 SQL ServerSQL Server 的访问控制委托给该数据库的所有者。Enabling partially contained databases delegates control over access to the instance of SQL ServerSQL Server to the owners of the database. 有关详细信息,请参阅 Security Best Practices with Contained DatabasesFor 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. 位于数据库模型内部的实体示例包括:系统表(如 sys.tables)、具有密码的包含数据库用户,以及当前数据库中由特定名称(包含两部分)引用的用户表。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. 位于数据库边界之外的实体示例包括:系统表(如 sys.endpoints)、映射到登录名的用户,以及另一个数据库中由特定名称(包含三部分)引用的用户表。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:

  • 完全包含的用户实体(从不跨越数据库边界的用户实体),例如 sys.indexes。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.

  • 非包含用户实体(跨越数据库边界的用户实体),例如 sys.server_principals 或服务器主体(登录名)本身。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 DatabasePartially 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.

使用 sys.dm_db_uncontained_entitiessys.sql_modules (Transact-SQL) 视图可返回有关非包含对象或功能的信息。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.

重要

由于某些对象的默认包含项目设置的是 NONE,此视图可能返回误报的结果。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. 有关排序规则问题的详细信息,请参阅 Contained Database CollationsFor 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. 如果将非包含数据库从 SQL ServerSQL Server的一个实例移到另一个实例,则这些信息会留在原地。When you move a non-contained database from one instance to another instance of SQL ServerSQL Server, this information is left behind. 您必须标识缺失的信息,并将其与数据库一起移到新的 SQL ServerSQL Server实例中。You must identify the missing information and move it with your database to the new instance of SQL ServerSQL 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.

备注

部分包含数据库可提供文档,说明无法独立于实例的数据库所使用的那些功能。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.

使用 AlwaysOn 的包含的数据库用户的好处Benefit of Contained Database Users with Always On

通过减少与 SQL ServerSQL Server实例的关联,当您使用 Always On 可用性组Always On availability groups时,在故障转移过程中部分包含数据库可能会很有用。By reducing the ties to the instance of SQL ServerSQL Server, partially contained databases can be useful during failover when you use Always On 可用性组Always On availability groups.

通过创建包含用户,可使用户直接连接到包含数据库。Creating contained users enables the user to connect directly to the contained database. 这在高可用性和灾难恢复方案中(例如在 AlwaysOn 解决方案中)是非常重要的功能。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. 有关详细信息,请参阅 AlwaysOn 可用性组的概述 (SQL Server)针对 AlwaysOn 可用性组的先决条件、限制和建议 (SQL Server)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

通过在数据库中维护数据库设置,而非在 master 数据库中进行维护,使每个数据库所有者都可以更好地控制其数据库,而不必向数据库所有者授予 sysadmin 权限。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.

  • 部分包含数据库不能使用复制、更改数据捕获或更改跟踪。Partially contained databases cannot use 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.

  • 复制、变更数据捕获和更改跟踪。Replication, change data capture, and change tracking.

警告

目前允许临时存储过程。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. sys.dm_db_uncontained_entities (Transact-SQL) 是可以显示数据库中所有潜在的非包含实体的视图。The sys.dm_db_uncontained_entities (Transact-SQL) is a view that shows all the potentially uncontained entities in the database. 在运行时识别出任何实际的非包含实体时将 database_uncontained_usage 事件将发生。The database_uncontained_usage event occurs when any actual uncontained entity is identified at run time.

sys.dm_db_uncontained_entitiessys.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. 但是,由于直到运行时才能确定某些实体(例如那些使用动态 SQL 的实体)的包含关系,所以此视图中显示的某些实体实际上可能并不是非包含实体。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. 有关详细信息,请参阅 sys.dm_db_uncontained_entities (Transact-SQL)For more information, see sys.dm_db_uncontained_entities (Transact-SQL).

database_uncontained_usage 事件database_uncontained_usage event

在运行时标识出非包含实体时将发生此 XEvent。This XEvent occurs whenever an uncontained entity is identified at run time. 这包括源自客户端代码的实体。This includes entities originated in client code. 此 XEvent 仅针对实际的非包含实体发生。This XEvent will occur only for actual uncontained entities. 但是,该事件仅在运行时发生。However, the event only occurs at run time. 因此,此 XEvent 将不能识别您尚未运行的任何非包含用户实体。Therefore, any uncontained user entities you have not run will not be identified by this XEvent

另请参阅See Also

经过修改的功能(包含的数据库) Modified Features (Contained Database)
Contained Database Collations Contained Database Collations
Security Best Practices with Contained Databases Security Best Practices with Contained Databases
Migrate to a Partially Contained Database Migrate to a Partially Contained Database
包含的数据库用户 - 使你的数据库可移植Contained Database Users - Making Your Database Portable