SQL Server 2014包含的数据库

最新推荐文章于 2023-08-14 23:48:44 发布

culuo4781

最新推荐文章于 2023-08-14 23:48:44 发布

阅读量690

点赞数

文章标签：数据库 python mysql java linux

原文链接：https://www.sqlshack.com/sql-server-2014-contained-databases/

版权

SQL Server 2012引入了包含数据库的概念，以减少对服务器级别的依赖。数据库包含可以是完全、部分或无，其中部分包含允许在不创建服务器登录的情况下进行用户身份验证。当移动数据库时，包含的数据库可以解决孤立用户问题，简化迁移过程。此外，包含的数据库在故障转移和Always On可用性组中提供优势，减少跨环境部署的复杂性。

摘要由CSDN通过智能技术生成

SQL Server provides two ways to authenticate users; SQL Server Authentication, which requires a predefined username and password to connect to the SQL Server, and Windows Authentication, in which SQL Server trusts the windows integrated user. The server level user that is authenticated to connect to the SQL Server is called a Server Login. This login should be mapped to a database user and granted permissions at the database level in order to access the database and be able to perform the authorized tasks. The relationship between the Server login and the database user in addition to the database metadata stored in the master system database draw up the dependencies between the SQL Server databases the server-level resources.

SQL Server提供了两种验证用户身份的方法： SQL Server身份验证（需要预定义的用户名和密码才能连接到SQL Server）和Windows身份验证（其中SQL Server信任Windows集成用户）。经过身份验证以连接到SQL Server的服务器级别用户称为“服务器登录名”。该登录名应映射到数据库用户并在数据库级别授予权限，以便访问数据库并能够执行授权的任务。服务器登录名和数据库用户之间的关系以及存储在主系统数据库中的数据库元数据之外，还绘制了SQL Server数据库与服务器级资源之间的依赖关系。

The dependency of a SQL Server database on the server-level resources could cause problems especially when you manage to copy, move or restore a database to another SQL instance, where the entities that are external to the database, such as the logins, are not there. So you must define these logins on the new instance, otherwise, the mapping will be broken and these users will not be able to connect to the database, and these database users will be considered orphaned users.

SQL Server数据库对服务器级资源的依赖性可能会导致问题，尤其是当您设法将数据库复制，移动或还原到另一个SQL实例时，其中数据库外部的实体（例如登录名）不存在那里。因此，您必须在新实例上定义这些登录名，否则，映射将被破坏并且这些用户将无法连接到数据库，并且这些数据库用户将被视为孤立用户。

In SQL Server 2012, Microsoft introduced a new concept that defined the database borders, which includes the database settings and metadata, this concept is called Boundaries. Specifying the boundaries make it easy to isolate the database from the external server-level dependencies, including the logins. This isolation feature is called Containment.

在SQL Server 2012中，Microsoft引入了一个定义数据库边界的新概念，其中包括数据库设置和元数据，该概念称为Boundaries 。指定边界可以轻松地将数据库与外部服务器级别的依赖关系（包括登录名）隔离开。这种隔离功能称为“ 遏制” 。

A contained database is a database that is isolated from the SQL Server instance hosting that database and other databases located in the same instance. Database containment can be Full, Partial or None. Only Partial containment and None containment types are supported in SQL Server. A fully contained database has no dependencies on the SQL Server instance that hosts it, as the database contains its metadata and settings. The partially contained database contains only the possible objects that can be managed by the database. None containment is the default setting for the databases, in which all entities are managed by the SQL instance.

包含的数据库是与托管该数据库SQL Server实例以及位于同一实例中的其他数据库隔离的数据库。数据库包含可以是完全，部分或无。 SQL Server仅支持部分包含和无包含类型。完全包含的数据库不依赖于承载它SQL Server实例，因为该数据库包含其元数据和设置。部分包含的数据库仅包含可以由数据库管理的可能对象。没有包含是数据库的默认设置，其中所有实体均由SQL实例管理。

As a benefit from the contained database, user authentication can be performed at the database level, without the need to have a mapped server login. This will overcome the errors related to the orphaned users. Contained database users can be Windows authenticated or SQL Server authenticated users.

受益于所包含的数据库，可以在数据库级别执行用户身份验证，而无需具有映射的服务器登录名。这将克服与孤立用户有关的错误。包含的数据库用户可以是Windows身份验证的用户或SQL Server身份验证的用户。

Contained entities are user entities that are located inside the database boundaries. Entities located outside the database or interact with external entities are considered uncontained entities. Contained database users are able to access only database objects. The partial containment allows the user to use uncontained entities too. The user that is created inside the partially contained database will have access to the master and TempDB system databases as a guest, and will be accessing it if the guest user is enabled on it.

包含的实体是位于数据库边界内的用户实体。位于数据库外部或与外部实体进行交互的实体被视为未包含的实体。包含的数据库用户只能访问数据库对象。部分包含允许用户也使用不包含的实体。在部分包含的数据库中创建的用户将以来宾身份访问master数据库和TempDB系统数据库，并且如果在其上启用了来宾用户，也将对其进行访问。

To support SQL Server contained databases, ALTER DATABASE and CREATE DATABASE statements are modified in SQL Server 2012 to include the CONTAINMENT argument to change the database containment status between None and Partial containments. Also, the ALTER DATABASE statement includes the CURRENT argument in order to make changes on the current database directly without specifying the database name.

为了支持SQL Server包含的数据库，SQL Server 2012中对ALTER DATABASE和CREATE DATABASE语句进行了修改，以包含CONTAINMENT参数，以在无和部分包含之间更改数据库包含状态。另外，ALTER DATABASE语句包含CURRENT参数，以便直接在当前数据库上进行更改，而无需指定数据库名称。

Enabling the SQL Server containment feature at the server level, you can create a database or ALTER the current database to be able to store its settings and metadata by itself, and create database users without the need to create server logins and map it to that database users.

在服务器级别启用SQL Server包含功能，您可以创建一个数据库或更改当前数据库以能够自己存储其设置和元数据，并且无需创建服务器登录名并将其映射到该数据库即可创建数据库用户。用户。

Another benefit from the contained database, when your database collation is different from the hosting server’s collation, temporary database objects will be created using the contained database collation rather than the TempDB collation in a non-contained databases situation, in which you have to use the COLLATE statement to resolve collation compatibility issues.

包含数据库的另一个好处是，当您的数据库排序规则与托管服务器的排序规则不同时，将在不包含数据库的情况下使用包含的数据库排序规则而不是TempDB排序规则来创建临时数据库对象。 COLLATE语句可解决排序规则兼容性问题。

The SQL Server contained database feature is useful in resolving many problems you may face with non-contained databases. The first problem is when you copy or move databases from one SQL Server instance to another one, where the login information which is stored in the source SQL instance will not be available in the new instance. To overcome this issue with non-contained databases, you need to create these missing logins in the new instance, which requires time and effort. This issue is completely resolved in the case of contained databases, where the partially contained database can store its information after moving the database to the new server, without consuming time in defining the server logins. The contained database feature is not enabled in the SQL Server instance level by default. So you need to make sure that you enable it in the instance where you moved or copied the contained database.

SQL Server包含的数据库功能对于解决非包含数据库可能遇到的许多问题很有用。第一个问题是将数据库从一个SQL Server实例复制或移动到另一个SQL Server实例时，存储在源SQL实例中的登录信息在新实例中将不可用。要解决非包含数据库的问题，您需要在新实例中创建这些丢失的登录名，这需要时间和精力。在包含数据库的情况下，此问题已完全解决，其中部分包含的数据库可以在将数据库移至新服务器后存储其信息，而无需花费时间来定义服务器登录名。默认情况下，SQL Server实例级别未启用包含的数据库功能。因此，您需要确保在移动或复制所包含数据库的实例中启用它。

Reducing the level of database dependency on the SQL Server instance by using a contained database is useful when you configure the SQL Server Always On Availability Groups. In the case of uncontained databases, server logins on the primary server should be created in the secondary replicas to make sure that the users can connect during the failover. If you use contained databases, the users will be able to connect during the failover without the need to create logins on all replicas and check the mapping.

当配置SQL Server Always On可用性组时，通过使用包含的数据库来降低对SQL Server实例的数据库依赖级别是很有用的。对于不包含数据库的数据库，应在辅助副本中创建主服务器上的服务器登录名，以确保用户可以在故障转移期间进行连接。如果使用包含的数据库，则用户将能够在故障转移期间进行连接，而无需在所有副本上创建登录名并检查映射。

Contained database are useful also for database developers who have no idea where the database will be deployed in the production environment, which differs from the development environment. So, the developer can concentrate on his development tasks without considering the impact of the database deployment.

对于不知道将在生产环境中部署数据库的位置的数据库开发人员而言，包含的数据库也很有用。因此，开发人员可以专注于其开发任务，而无需考虑数据库部署的影响。

Enabling database containment will allow each database owner to manage his/her own database, without having sysadmin permissions at the server level, as the contained database settings are maintained in the database itself, not the master system database.

启用数据库包含将允许每个数据库所有者管理自己的数据库，而无需在服务器级别具有sysadmin权限，因为所包含的数据库设置是在数据库本身（而不是主系统数据库）中维护的。

The sys.dm_db_uncontained_entities system view is a useful tool to identify the status of the contained database. From the name of the view, it shows all uncontained database entities due to dependencies of these entities on the server-level entities, which you need to consider when moving the contained database to another SQL Server instance.

sys.dm_db_uncontained_entities系统视图是确定所包含数据库状态的有用工具。从视图的名称来看，由于这些实体对服务器级实体的依赖关系，它显示了所有未包含的数据库实体，将包含的数据库移动到另一个SQL Server实例时需要考虑这些依赖关系。

The below simple query is used to show uncontained entities within the SQLShackDemo contained database:

下面的简单查询用于显示SQLShackDemo包含的数据库中不包含实体的实体：

 
USE SQLShackDemo 
GO
SELECT class, class_desc ,major_id ,feature_name ,feature_type_name  
FROM sys.dm_db_uncontained_entities

You can easily join the previous query with the corresponding system table to get the object name. For example, the previous query can be joined with the SQLShackDemo.sys.database_principals system table to get the principals that has dependencies with other databases in the same server as follows:

您可以轻松地将上一个查询与相应的系统表结合起来以获取对象名称。例如，上一个查询可以与SQLShackDemo .sys.database_principals系统表结合在一起，以获取与同一服务器中其他数据库具有依赖性的主体，如下所示：

 
USE SQLShackDemo 
GO
SELECT class, class_desc ,major_id ,feature_name ,feature_type_name , SP.name  
FROM sys.dm_db_uncontained_entities UE
JOIN SQLShackDemo.sys.database_principals SP
on UE.major_id =SP.principal_id

SQL Server partially contained databases have few limitations. Any database configured with partial containment can’t use the SQL Server Replication, SQL Server Change Data Capture (CDC) or SQL Server Change Tracking (CT) features.

SQL Server部分包含的数据库没有什么限制。配置了部分包含的任何数据库都不能使用SQL Server复制，SQL Server更改数据捕获（CDC）或SQL Server更改跟踪（CT）功能。

Let’s start with configuring the contained database. To use the contained databases, we should enable the containment feature at the SQL Server Instance level first. This can be easily achieved by using the sp_configure stored procedure as below:

让我们从配置包含的数据库开始。要使用包含的数据库，我们应该首先在SQL Server实例级别启用包含功能。使用sp_configure存储过程可以很容易地实现这一点，如下所示：

 
USE master
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'CONTAINED DATABASE AUTHENTICATION', 1
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0 
GO
RECONFIGURE
GO

You can also enable containment using SQL Server Management Studio from the Advanced page of the Server Properties window, by setting the Enable Contained Databases option to True as follows:

您还可以通过将“ 启用包含的数据库”选项设置为“ True” ，从“ 服务器属性”窗口的“ 高级”页面使用SQL Server Management Studio 启用包含 ，如下所示：

Once the containment feature is enabled at the SQL instance level, we can enable it at the database level. What is required here is to change the database containment option from None to Partial. You can perform this operation using the ALTER DATABASE T-SQL statement below:

一旦在SQL实例级别启用了包含功能，我们就可以在数据库级别启用它。这里需要将数据库包含选项从None更改为Partial 。您可以使用下面的ALTER DATABASE T-SQL语句执行此操作：

 
USE [master]
GO
ALTER DATABASE SQLShackDemo SET CONTAINMENT = PARTIAL
GO

Again, you can perform the same process using SQL Server Management Studio from the Options page of the Database Properties window by setting the Containment type to Partial as follows:

同样，您可以通过以下方式将SQL Server Management Studio从“ 数据库属性”窗口的“ 选项”页面中执行相同的过程，方法是将“包含”类型设置为“部分”，如下所示：

Now the SQLShackDemo is configured as a partially contained database. To have a full demo, we will create a SQL authentication contained user, which is created at the database level without mapping it to a server login. The user can be created using the Create User T-SQL statement as below:

现在，SQLShackDemo已配置为部分包含的数据库。为了获得完整的演示，我们将创建一个包含SQL身份验证的用户，该用户是在数据库级别创建的，而没有将其映射到服务器登录名。可以使用创建用户T-SQL语句创建用户，如下所示：

 
USE [SQLShackDemo]
GO
CREATE USER [SQLShackDemoUser] WITH PASSWORD=N'SQLSCKdem_123456'
GO
USE [SQLShackDemo]
GO
ALTER ROLE [db_owner] ADD MEMBER [SQLShackDemoUser]
GO

Using the SQL Server Management Studio, expand the database Security node and right-click on the Users node to choose New User option:

使用SQL Server Management Studio，展开数据库“ 安全性”节点，然后右键单击“ 用户”节点以选择“ 新用户”选项：

From the Database User – New page, simply fill the user name and the password fields and grant the user the required permissions from the Membership tab then click OK.

在“ 数据库用户-新建”页面上，只需填写用户名和密码字段，然后从“ 成员资格”选项卡中为用户授予所需的权限，然后单击“ 确定” 。

To check that this user is a contained user and has no mapped server login, we can query the sys.server_principals and the sys.database_principals system tables for that user as below:

要检查此用户是否是包含的用户并且没有映射的服务器登录名，我们可以查询该用户的sys.server_principals和sys.database_principals系统表，如下所示：

 
SELECT name,type,type_desc from master.sys.server_principals WHERE name='SQLShackDemoUser'
GO
SELECT name,type,type_desc from SQLShackDemo.sys.database_principals WHERE name='SQLShackDemoUser'

It is clear from the following result that the user is only created at the database level without mapping it to a login at the server level:

从以下结果可以明显看出，仅在数据库级别创建用户，而没有将其映射到服务器级别的登录名：

Another useful option is to change the already created SQL authentication or Windows authentication users to be contained database users using the sp_migrate_user_to_contained stored procedure, with the ability to change the user’s name and disable or keep the mapped login at the server level as follows:

另一个有用的选项是使用sp_migrate_user_to_contained存储过程将已创建SQL身份验证或Windows身份验证用户更改为要包含在数据库用户中，并具有更改用户名并在服务器级别禁用或保留映射的登录名的功能，如下所示：

 
USE [SQLShackDemo]
GO
sp_migrate_user_to_contained
    @username = N'suheir',
    @rename = N'keep_name',
    @disablelogin = N'do_not_disable_login' ;
GO

If you try to connect to the SQL instance using the contained user created previously you will get the Login failed error below:

如果您尝试使用先前创建的包含的用户连接到SQL实例，则会出现以下“登录失败”错误：

What is missing here is that you need to specify the contained database name that the user has access on in the Connect to Database option from the Connection Properties window, after expanding the Options button in the Connect to Server window below:

这里缺少的是，在展开下面的“ 连接到服务器”窗口中的“ 选项”按钮之后，需要在“ 连接属性”窗口的“ 连接到数据库”选项中指定用户有权访问的包含的数据库名称：

Another way to achieve this, by specifying the contained database name as Initial Catalog in the Additional Connection Parameters page in the Connect to Server window as follows:

实现此目的的另一种方法是，在“ 连接到服务器”窗口的“ 其他连接参数”页面中，将包含的数据库名称指定为“ 初始目录” ，如下所示：

Click on the Connect button after specifying the database name, the user will connect to the SQL instance successfully and will be able to browse the SQLShackDemo database only as below:

指定数据库名称后，单击“ 连接”按钮，用户将成功连接到SQL实例，并且只能浏览SQLShackDemo数据库，如下所示：

If that contained user try to access any entities outside the SQLShackDemo database boundaries, he will not be able to access, getting the permission error below:

如果该包含的用户尝试访问SQLShackDemo数据库边界之外的任何实体，则他将无法访问，并显示以下权限错误：

 
SELECT * FROM AdventureWorks2012.dbo.ErrorLog

结论： (Conclusion:)

SQL Server 2012 introduced the contained database concept, where the database has no dependencies on the SQL Server instance that hosts that database. This dependency between the database and the instance hosting that database is useful when you manage to move the database between SQL instances or use the database in a SQL Server Availability Group site without requiring time managing factors located outside the database boundaries.

SQL Server 2012引入了包含的数据库概念，其中数据库与托管该数据库SQL Server实例没有依赖关系。当您设法在SQL实例之间移动数据库或在SQL Server可用性组站点中使用数据库而无需花费时间来管理位于数据库边界之外的因素时，数据库与承载该数据库的实例之间的这种依赖关系非常有用。