包含数据库的真正好处是什么
在 SQL Server 2012 中，他们引入了包含数据库。该功能的真正目的是什么？它修复了以前版本的哪些缺点？
In SQL Server 2012, they have introduce the Contained Database. What is the real purpose of this feature? What drawbacks of previous versions has it fixed?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
它们的开发是为了使系统之间的数据库迁移更加容易（您的数据库以及 SQL Azure 上的数据库，它们需要移动以平衡资源）。任何在数据库之外有依赖关系的东西都被认为是风险，因为它是必须与数据库配合使用的额外脚手架 - 容易忘记、容易出错、容易不同步。
例如，在 Denali 中，这些问题得到了解决：
今天，当您将数据库移动到另一台服务器时，您还必须迁移服务器级别的所有 SQL 登录 - 这可能会很痛苦，尤其是当 SID 不同步时。对于包含的数据库，与 SQL Server 登录无关的数据库级用户只需在数据库进行备份、分离、镜像、复制等操作时就可以进行操作。非常方便。
如果您的数据库的排序规则与服务器排序规则不同，则当您对 #temp 表进行联接或执行其他操作时，您可能会发现排序规则发生冲突，因为创建的 #temp 表将继承服务器排序规则，而不是调用数据库。虽然您可以通过在每个列引用上指定 COLLATE 子句来解决此问题，但对于包含的数据库，#tempdb 会继承调用数据库的排序规则，从而覆盖服务器排序规则。
THROW() 几乎也可以属于这一类 - 因为您不再需要使用 sys.messages 来存储自定义消息。这并不像上述两个问题那么常见，但如果不需要保持 sys.messages 同步，它确实可以使迁移到新服务器的效果更好。这并不限于包含的数据库，但它起着相同的作用。
对于不符合“遏制”标准的事物，DMV 可以向您显示一系列事物，如果您将它们移动到另一台服务器，这些事物可能会损坏。例如，调用由三部分或四部分组成的名称。
在未来的版本中，还有其他问题将得到解决。例如：
SQL Server 代理是外部依赖项。当您将数据库移动到不同的服务器时，引用该数据库的 SQL 代理作业不会自动随数据库一起移动，您必须确定哪些受到影响并自己编写脚本（这并不像只携带 msdb 那么简单）也）。在 SQL Server 的未来版本中，我设想 (a) 每个数据库都能够拥有自己的代理，或者 (b) 代理将移至操作系统级架构，其中某些转换层会告诉您数据库的位置是，而不必让代理驻留在同一台计算机上。当我们谈论 Azure、异地网络等时，后一个选项可能会变得复杂。
链接服务器也是一种外部依赖项。这可以通过数据库级链接服务器轻松解决 - 特别是因为它们只不过是同义词容器/指针。
还有其他人，但这些都是重量级人物。
They are being developed to make migration of databases between systems easier (both your databases, and databases on SQL Azure that they need to move around to balance resources). Anything that has a dependency outside of the database is considered a risk, because it's extra scaffolding that has to go with the database - easy to forget, easy to get wrong, easy to fall out of sync.
For example, in Denali these issues are addressed:
Today when you move a database to another server, you also have to migrate all the SQL logins at the server level - this can be a pain especially when the SIDs get out of sync. With contained databases, database-level users that don't have a tie to a SQL Server login just come along for the ride when a database is backed up, detached, mirrored, replicated, etc. Nice and easy.
If you have a database with collation that differs from the server collation, you may find that you have collation conflicts when you join or perform other operations with #temp tables, because the #temp tables that get created will inherit the server collation, not the calling database. While you can get around that by specifying a COLLATE clause on every single column reference, with contained databases, #tempdb inherits the collation of the calling database, overriding the server collation.
THROW() can almost fall into this category as well - since you no longer have to use sys.messages to store custom messages. This is not as common as the above two issues, but it certainly does make migrating to a new server work better if there is no requirement to also keep sys.messages in sync. This is not restricted to contained databases, but it plays the same role.
For things that don't meet "containment" criteria, there is a DMV that can show you a list of things that will potentially break if you move them to another server. For example, a call to a three- or four-part name.
In future versions, there are other issues that will be addressed. For example:
SQL Server Agent is an external dependency. When you move a database to a different server, SQL Agent jobs that reference that database do not automatically move with the database, you have to determine which ones are affected and script them out yourself (it is not quite as simple as just bringing along msdb too). In a future version of SQL Server, I envision that either (a) each database will be able to have its own Agent, or (b) Agent will be moved to an OS-level architecture, where some translation layer tells you where the database is, instead of having to have Agent live on the same machine. The latter option can get complicated when we're talking about Azure, geo-disparate networks, etc.
Linked Servers are also an external dependency. This could be easily solved with database-level linked servers - especially since these are little more than synonym containers / pointers.
There are others, but those are the heavy hitters.