使用英语阅读

通过


SQL Server 2012: 有时只包含一部分会更好

SQL Server 2012 将支持部分包含的数据库,这将缓解某些迁移与合并问题。

Denny Cherry

部分包含的数据库解决了自发布 SQL Server 4.2 版以来围绕 SQL Server 数据库的一些主要合并和迁移问题。 令人欣慰的是,它将成为 SQL Server 2012 中引入的多项新功能之一。

部分包含的数据库解决的第一个问题是,将数据库从一个 SQL Server 实例移动到另一个 SQL Server 实例而无需标识该数据库使用的 SQL Server 登录名。 这还涉及在目标数据库实例上创建这些登录名。

SQL Server 2012 使用包含的 SQL Server 登录名来实现这一点。 这是在包含的数据库中创建的 SQL Server 用户,该用户并不具有匹配的 SQL Server 登录名。 包含的数据库中的这个包含的用户在包含的数据库中存储有一个密码,以便身份验证按预期执行。

解决的第二个问题是,有效排除了包含的数据库中的表与临时表之间的排序规则冲突。 SQL Server 会在整理时在包含的数据库范围内自动创建临时表。 这将使得同一个 SQL Server 实例可以承载具有不同排序规则的数据库,而无需修改 CREATE TABLE 语句来指定排序规则或无需在 JOIN 语句的语法中指定 COLLATE 语句。

与某些要求将数据库兼容性级别设置为最新值的新功能不同,部分包含的数据库当前能够向后兼容至 SQL Server 2005。另一方面,包含的数据库这一功能还不完善。 它应会在 SQL Server 2012 CTP3 发行版与 2012 年初或年中发布产品并投入生产之间的某个时间得到完善。

设置部分包含的数据库

您必须先使用 sp_configure 系统存储过程更改服务器设置,然后才能将数据库从不包含更改为部分包含。 使用 sp_configure 系统存储过程将“contained database authentication”设置从 0 更改为 1,然后使用 RECONFIGURE 语句激活新设置,如下所示:

EXEC sp_configure 'contained database authentication', 1 RECONFIGURE GO

在启用“contained database authentication”设置之后,可将特定数据库更改为部分包含。 可使用 SQL Server Management Studio(如图 1 所示)、ALTER DATABASE 语句或通过创建新的数据库作为包含的数据库:

ALTER DATABASE: USE [master] GO ALTER DATABASE [Cont] SET CONTAINMENT=PARTIAL GO CREATE DATABASE: CREATE DATABASE [Cont1] CONTAINMENT=PARTIAL GO

若要创建新的部分包含的数据库或将数据库更改为部分包含的数据库,请在对象资源管理器中连接到 SQL Server 实例。 右键单击以创建新的数据库或选择现有数据库。 选择属性(取决于您是创建新的数据库还是更改现有数据库)。 在任一情况下,都选择“选项”选项卡并将“包含类型”下拉列表从“无”更改为“部分”。

The Database Properties window lets you change the database containment setting

图 1 利用“数据库属性”窗口,可以更改数据库的包含设置。

若要将现有数据库从“不包含”更改为“部分包含”,请使用 T/SQL 和 ALTER DATABASE 语句。 若要成功更改数据库包含设置,ALTER DATABASE 语句必须能够对整个数据库进行独占锁定。 因此,您必须为使用此特定数据库的业务单元安排短暂中断。

避免临时表排序规则错误

新的部分包含的数据库允许同一个 SQL Server 实例上存在多个不同的排序规则。 您在联接临时表时无需担心排序规则问题。 您可以通过选择一个 Microsoft SQL Server 实例并创建一个具有不同排序规则的部分包含的数据库来对此进行测试。 然后,在部分包含的数据库中创建一个物理表和一个临时表。 将数据加载到这两个表中并尝试联接它们。

创建一个使用 Albanian_100_CI_AI_KS_WS 数据库排序规则的数据库,而实例使用的是 SQL_Latin1_General_CP1_CI_AS 排序规则,如图 2 中所示。 它会在新数据库中创建一个名为 dbo.Employee 的表并向表中加载三个行。 还会创建一个名为 #emp 的临时表并向表中插入一个行。 若在查询结束时联接这些表,则会返回一个行。 相反,在未将包含的数据库设置为部分包含的情况下,将返回一个错误。

图 2 创建和使用部分包含的数据库。

use master GO CREATE DATABASE [Cont] CONTAINMENT = PARTIAL ONPRIMARY (NAME = N'Cont', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Cont.mdf', SIZE = 4096KB, FILEGROWTH= 1024KB) LOGON (NAME = N'Cont_log',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Cont_log.ldf', SIZE = 1024KB, FILEGROWTH = 10%) COLLATE Albanian_100_CI_AI_KS_WS GO Use Cont GO Create table Employee (EmployeeId INTPRIMARYKEY, LastName nvarchar(100), FirstName nvarchar(100)) GO Insert into Employee (EmployeeId,LastName,FirstName) values (1,'last1','first1'), (2,'last2','first2'),(3,'last3','first3') GO Create table #emp (LastName nvarchar(100)) GO Insert into #emp (LastName) values ('last1') GO select* from Employee join #emp on Employee.LastName = #emp.LastName

包含的用户

包含的用户类似于传统 SQL Server 登录名,只不过它们不具有与包含的数据库用户对应的登录名。 使用 SQL Server Management Studio 或 CREATE USER T/SQL 语句创建一个包含的用户。

若要使用 SQL Server Management Studio,请在对象资源管理器中连接到承载包含的数据库的实例。 导航到“数据库”|“{您的包含的数据库}”|“安全性”|“用户”。 右键单击“用户”文件夹,并从上下文菜单中选择“新建用户”。 在“用户类型”下拉列表中,选择“带密码的 SQL 用户”。填写用户名和密码字段(见图 3),以及您需要配置的默认架构和任何数据库角色成员身份。

Create a contained SQL user with SQL Server Management Studio

图 3 使用 SQL Server Management Studio 创建包含的 SQL 用户。

创建包含的用户后,您可以查看包含的数据库中的 sys.database_principals 目录视图。 通过查询目录视图返回的记录集将显示出,名为 authentication_type 的新列设置为值 2。它还显示出,authentication_type_desc 列的值在用户为包含的用户时设置为“DATABASE”。

通过将 T/SQL 与 CREATE USER 语句结合使用来创建包含的数据库用户。 通过指定 WITH PASSWORD 语句,SQL Server 可知道您创建的是包含的用户而不是与实例级别的登录名对应的用户,如下所示:

CREATE USER MyContainedUser WITHPASSWORD = 'MyContainedUserPassword' GO

您可以创建包含的 SQL 用户以及包含的 Windows 登录名。 将“用户类型”下拉列表(见图 4)更改为“Windows 用户”,并输入用户名而不选择登录名。

Create a contained Windows Login using SQL Server Management Studio

图 4 使用 SQL Server Management Studio 创建包含的 Windows 登录名。

您还可以使用 CREATE USER T/SQL 语句创建包含的 Windows 用户,如下所示:

CREATE USER [CAPT-MAL\test] WITH DEFAULT_SCHEMA = [dbo] GO

像传统(非包含)用户那样,使用包含的登录名登录到包含的数据库。 所不同的是,使用包含的用户进行连接时,必须在连接字符串中指定数据库名称。 如果您不这样做,SQL Server 将认为是一个传统 SQL Server 登录名正在尝试建立连接。 如果所使用的登录名与实例级别定义的用户名和密码不匹配,则登录将失败。

在 SQL Server Management Studio 连接对话框中,通过单击登录窗口底部的“选项”按钮可以执行此操作。 选择“连接属性”选项卡。 在“连接属性”选项卡上的“连接到数据库”下拉列表中,可输入包含的数据库的数据库名称(见图 5)。

您无法查询 SQL Server 实例上承载的数据库列表,因为在“登录”选项卡上指定了包含的用户。 如果该包含的用户没有首先针对保存有其用户名和密码的包含的数据库进行身份验证,那么该用户就对 master 数据库没有任何权限。 为此,您必须知道数据库名称并将其手动键入框中。

The “Connection Properties” tab of the SQL Server Management Studio connection dialog window

图 5 SQL Server Management Studio 连接对话框窗口的“连接属性”选项卡。

从这两项功能可以看出,在 SQL Server 2012 实例上合并数据库实例要轻松得多。 只需花费少量时间并执行少量测试即可确保您的数据库应用程序与包含的数据库功能完全兼容。 但是,一旦您启用了包含的数据库设置,将来的数据库迁移和合并也将变得更加顺畅,而这才是我们的真正目的。

Denny Cherry

Denny Cherry是一名 MVP,他是具有 10 多年 Microsoft SQL Server、Hyper-V、vSphere 和企业存储解决方案使用经验的独立顾问。 他目前拥有与 SQL Server 2000 版到 2008 版相关的多个 Microsoft 认证,包括 Microsoft 认证大师。他还编著了有关 SQL Server 管理的多本书籍并发表了几十篇技术文章。

相关内容