SQL Server：单独的架构或单独的数据库
我们有两种在客户现场实施的产品，其中一种需要另一种的存在。我在与附加组件所需的数据库对象的主要产品相同的数据库中实现了单独的模式。因为从理论上讲，该附加组件也可以成为未来产品的附加组件（尽管目前还没有计划），所以我正在重新考虑该决定。我们目前使用 2005，但计划在一年左右迁移到 2008 R2 或 Denali。
其中一个因素是，在单独的 VS 2010 数据库项目中维护单独的架构很困难，因为在将项目架构与包含另一架构的数据库进行比较时，无法将 VS 项目的视图限制为一个架构。
假设两个架构始终位于同一个 SQL Server 实例中，是否有任何理由避免将它们拆分为单独的数据库？
备份由在实例中的所有数据库上运行的脚本处理，因此不必担心。我们希望将来能够在托管（SaaS）的基础上提供产品，因此对多租户的影响是一个因素。我们可能会在一个实例中托管多个客户。
We have two products that get implemented at customer sites, one of which requires the presence of the other. I implemented a separate schema within the same database as the main product for the database objects needed by the add-on. Because the add-on could theoretically become an add-on to future products as well (although none are currently planned), I am reconsidering that decision. We currently use 2005, but plan to migrate to 2008 R2 or Denali in a year or so.
One factor is that maintaining the separate schemas in separate VS 2010 database projects is difficult because of the inability to restrict the view of a VS project to one schema when comparing the project schema to a database that contains another schema.
Are there any reasons to avoid splitting the two schemas into separate databases, assuming they will always be in the same SQL Server instance?
Backups are handled by scripts that operate on all databases in the instance, so that is not a concern. We are hoping to offer the products on a hosted (SaaS) basis in the future, so the impact on multi-tenancy is a factor. We would likely host multiple customers within an instance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
如果它们位于两个单独的数据库中，您将无法获得事务一致性。根据您的 HA/DR 机制，数据库可能会不同步。例如，使用数据库镜像，一个数据库在应用的事务日志方面可以远远领先于另一个数据库。镜像上的一个数据库可能在上午 10 点之前处于最新状态，但另一个数据库可能在上午 9:55 之前处于最新状态。如果发生故障转移，两个数据库将不同步。
You don't get transactional consistency if they're in two separate databases. Depending on your HA/DR mechanism, the databases might get out of sync. Using database mirroring, for example, one database can be far ahead of the other in terms of transaction logs applied. One database might be current up to 10AM on the mirror, but the other database might only be current up to 9:55AM. In the event of a failover, boom, your two databases aren't in sync.
请记住，在同一个数据库中，您可以强制执行外键约束，但如果不编写触发器，则不能在单独的数据库中强制执行外键约束。
Remember that in the same database you can enforce foreign key constraints while you cannot in separate databases without writing triggers.
我认为将数据分割到数据库而不是方案中的决定高度依赖于这些产品是否总是一起使用而不是其他方式，我的意思是，将此类产品视为一个产品是否有意义，或者它们是否两种完全不同的产品一起使用。
既然您说将来您计划也将该附加组件用于其他产品，我认为最好的解决方案是将数据拆分到不同的数据库中，因为稍后您将至少面临以下场景之一：
每一个都使得维护变得困难......所以我建议为你提到的场景分离数据库。
I think that the decision of splitting the data in databases instead of schemes is highly dependable on if such products will always be used together and no other way, I mean, if it makes sense to think of such of products as one, or if they're two completely different products being used together.
Since you say that in the future you plan to use the add-on for other products as well I think the best solution would be to split the data across separate databases, 'cause later you're gonna face at least one of these scenarios:
Each of these make it hard to maintain... So I recommend to separate databases for the scenario you mention.
您应该（严格地！）针对两个模型（单独的模式和单独的数据库）测试应用程序的应用程序性能。如果其中一个被证明不可接受，则与另一个一起去。显而易见的是，使用一种形式而不是其他形式的唯一真正令人信服的理由与您描述的“附加”功能有关。如果这可以是多个不同系统的“附加组件”，如果您希望所有已安装和支持的“基本”应用程序使用单个附加实例，那么您非常希望将该功能封装在其中它自己的数据库实例，以便它可以被任何/所有此类实例共享/访问。
正如思想练习一样，如果您希望（或必须）仅将附加功能存储在“第一个”应用程序实例中，并且所有后续安装的实例都引用该代码，那么支持它的解决方案可以基于同义词（在 SQL 2005 中引入）。安装“基础”应用程序后，必须进行检查以确定是否以及在何处找到“附加”代码，如果找到，则将引用其托管数据库构建必要的同义词。安装附加组件时需要类似的安装例程，以识别和更新所有支持的数据库。 （这是一个有趣的想法，但就长期维护和管理而言，单独的数据库将是更好的解决方案。）
You should (severely!) test application performance for your application against both models (separate schema and separate database). If one proves unacceptable, go with the other. With the obvious said, the only truly compelling reason to use one form over the other ties in to the "add-on" functionality you describe. If this can be an "add-on" to multiple different systems, if you want a single add-on instance to be used by all installed and supported "base" applications, then you'd pretty much want to have that functionality encapsulated within its own database instance, so that it could be shared/accessed by any/all such instances.
Just as a thought-exercise, if you wanted (or had) to have the add-on functionality be stored only in the "first" application instance, with that code referenced by all subsequently installed instances, a solution supporting it could be based on synonyms (introduced in SQL 2005). Upon installation of a "base" app, a check would have to be made determining if and where the "add-on" code was found, and if it is found the necessary synonyms would be built referencing its hosting database. A similar installation routine would be required for when the add-on is installed, to identify and update all supported databases. (Its an intriguing idea, but separate database would be a better solution as regards to long-term maintenance and management.)