Here at work (a multi-billion dollar manufaturing company with a 12 person Windows development team) we are about to go to a single master database for all new applications and will have it broken up with schemas for what we normally would have had databases for before. There will also be a few common schemas with stuff like employee directory and branch directory and so on...
在这里工作（一个拥有12人Windows开发团队的价值数十亿美元的制造公司），我们即将为所有新应用程序转到一个主数据库，并将其分解为我们通常拥有的数据库的模式。之前。还会有一些常见的模式，包括员工目录和分支目录等等......
I'm still not sure how I feel about this move, but we're about to have a meeting on this in a few hours to discuss pros, cons, best practices, pitfalls and so on... so I'm looking for your thoughts on this... Is it good? Is it bad? What problems are we going to run into a year from now?
我仍然不确定我对此举的看法，但我们即将在几个小时内召开会议，讨论利弊，最佳做法，陷阱等......所以我正在寻找你对此的看法......好吗？这不好吗？从现在起一年后我们会遇到什么问题？
Any thoughts, tips, or advice is welcome. Thanks
欢迎任何想法，提示或建议。谢谢
EDIT In response to a comment on this question, we are using SQL Server 2005 and we are actually talking about moving what would have been seperate databases on the same instance into a single database. The driving issue is the complete lack of referential integrity accross databases as the majority of our applications need access to common data such as an employee record, or branch information.
编辑在回答对这个问题的评论时，我们正在使用SQL Server 2005，我们实际上正在谈论将同一个实例上的单独数据库移动到单个数据库中。驱动问题是数据库中完全缺乏参照完整性，因为我们的大多数应用程序需要访问公共数据，例如员工记录或分支信息。
UPDATE Several people requested that I update this question with the results from our meeting so here it is. We debated back and forth the pros and cons of doing this (I even showed them this question using the projector) and by the time we were done we had pretty much covered the pros and cons covered here. About half of us thought we could get it done with the right resources and commitment, and about half thought we couldn't do it (or that it wouldn't work out well). We decided to use some time with Microsoft to get their thoughts and platform specific advice. I will be sure to update this question and my blog after we've talked to them. Thanks for all the help and helpful answers.
更新有几个人要求我用我们会议的结果更新这个问题，所以在这里。我们来回讨论了这样做的利弊（我甚至用投影仪向他们展示了这个问题），当我们完成时，我们几乎涵盖了这里所包含的优点和缺点。我们大约有一半人认为我们可以用正确的资源和承诺来完成它，大约一半人认为我们做不到（或者它不会很好）。我们决定利用微软的时间来获取他们的想法和平台特定的建议。在我们与他们交谈之后，我一定会更新这个问题和我的博客。感谢所有帮助和有用的答案。
12 个解决方案
#1
14
Larger database are harder to maintain due to sheer size: backups take longer, disaster recovery is slower which in turn requires more often backups. You can address these by creating filegroups and using filegroup level backup in your maintenance plans and on crash recovery you can use the 'piecemeal restore' strategy to speed things up.
由于规模庞大，较大的数据库难以维护：备份需要更长时间，灾难恢复速度较慢，这反过来需要更频繁的备份。您可以通过在维护计划中创建文件组并使用文件组级备份来解决这些问题，并且在崩溃恢复时，您可以使用“零碎恢复”策略来加快速度。
Proper use of filegroups will make most of the 'cons' cited by previous replies go away: they can distribute the I/O, they can sanitize your maintenance plans and backup/restore strategy, they offer availability by taking offline only the damaged portion of the the db in case of crash. So I'd say that while those 'cons' are legit concerns, they have can be mitigated by a proper deployment strategy. Its true though that these mitigation actions require a true, experienced, dba at the helm as they will go beyond the comfort zone of a developer turned dba by need.
正确使用文件组将使之前回复中引用的大多数“缺点”消失：他们可以分发I / O，他们可以清理您的维护计划和备份/恢复策略，他们通过仅脱机损坏的部分提供可用性崩溃时的数据库。所以我要说的是，虽然这些“缺点”是合法的问题，但它们可以通过适当的部署策略来缓解。虽然这些缓解行动需要一个真实的，经验丰富的dba掌舵，因为它们将超出开发人员的舒适区域，因此需要，这是真实的。
Some of the pros I can think of quickly:
我能想到的一些专业人士：
- Consistency. You can have a backup-restore so that all data is consistent. Separate dbs don't allow this because you cannot coordinate a consistent set of backups unless you take them all offline, or make them r/o, during the backup.
- 一致性。您可以进行备份还原，以使所有数据保持一致。单独的dbs不允许这样做，因为您无法协调一致的备份集，除非您在备份期间将它们全部脱机或使其成为r / o。
- Dirt cheap high availability: you can deploy database mirroring for disaster recoverability and high availability. Multiple databases have problems because one cannot coordinate a simultaneous failover and apps are faced with the dilemma of seeking each database current location.
- 低廉的高可用性：您可以部署数据库镜像以实现灾难恢复和高可用性。多个数据库存在问题，因为无法协调同时进行故障转移，并且应用程序面临着寻找每个数据库当前位置的困境。
- Security. While most other posts see one database harder to secure, I'd say is easier to secure. Multiple databases seem harder to secure properly simply because what everyone does is they make one login and add it to that database db_owner group. Having one database will make things harder (unless you end up making everyone dbo, very bad) but once you start doing the right thing (granular access) then one db is not harder than multiple dbs, is actually easier because you won't have to copy/maintain some common groups/rights across multiple dbs.
- 安全。虽然大多数其他帖子看到一个数据库更难以保护，但我认为更容易保护。多个数据库似乎更难以正确保护，因为每个人都做的是他们进行一次登录并将其添加到该数据库db_owner组。拥有一个数据库会让事情变得更难（除非你最终让每个人都成为dbo，非常糟糕）但是一旦你开始做正确的事情（细粒度访问），那么一个db并不比多个dbs更难，实际上更容易因为你没有跨多个dbs复制/维护一些共同的组/权限。
- Control. Will be easier to impose certain policies and good practices on a single db rather than multiple ones (no data access to developers, app data access only through execute rights on the schema to enforce procedures access etc).
- 控制。将单个数据库而不是多个数据库强加给某些策略和良好实践会更容易（对开发人员无数据访问权限，仅通过模式上的执行权限来实施应用程序数据以强制执行过程访问等）。
There are also some cons I did not see in other posts:
在其他帖子中我也没有看到一些缺点：
- This will be much harder to pull off that you think right now
- 这对你现在想的很难实现
- Increase coupling between formerly separated applications will impose development restrictions: you can't simply alter your schema, you will have to coordinate it with the rest of the apps (you can argue that this was also the case before, but was brushed under the carpet by having separate dbs, and you're right)
- 增加以前分离的应用程序之间的耦合会产生开发限制：你不能简单地改变你的架构，你必须与其他应用程序协调它（你可以说这也是以前的情况，但是在地毯下刷了通过单独的dbs，你是对的）
- Log writes that are now distributed across multiple db logs will be consolidated into one single log file. If your writes are significant, this may turn out to be a serious bottleneck and force you to buy some expensive fast drives for the new, consolidated, log file. In general this can be addresses by making the log drive a stripped array across as many stripes as needed to make it fast enough (usually raid 10).
- 现在分布在多个db日志中的日志写入将合并到一个日志文件中。如果您的写入很重要，这可能会成为一个严重的瓶颈，并迫使您为新的，整合的日志文件购买一些昂贵的快速驱动器。一般来说，这可以通过使日志驱动器成为一条带有所需数量条带的剥离数组来使地址足够快（通常是raid 10）。
- GAM/SGAM/PFS allocations will also be consolidated, but again this will be alleviated by proper use of file groups.
- GAM / SGAM / PFS分配也将合并，但同样可以通过正确使用文件组来缓解这种情况。
#2
7
Pros:
优点：
- You only need to remember one connection string
- 您只需要记住一个连接字符串
- When users report that access is slow, you know which DB is causing the trouble
- 当用户报告访问速度很慢时，您就知道哪个数据库导致了问题
Cons:
缺点：
- Backups of The One DB will take a long time and will get progressively longer over time.
- One DB的备份将花费很长时间，并且随着时间的推移会逐渐变长。
- Restoring data from a backup will get increasingly difficult.
- 从备份中恢复数据将变得越来越困难。
- Performance Tuning (SQL Profiler, Execution Plan estimation) for a feature for one app will slow down every app.
- 一个应用程序的功能的性能调整（SQL事件探查器，执行计划估计）将减慢每个应用程序的速度。
- Restricting access to a single application's data is cumbersome if at all possible which will likely mean in practice that all devs and DBAs will be given keys to the ENTIRE kingdom.
- 限制访问单个应用程序的数据是很麻烦的，如果可能的话，这可能意味着在实践中所有开发人员和DBA将被赋予整个王国的密钥。
- New developers/DBAs have a much larger learning curve as they need to navigate a large and mostly useless (to them) database structure which means higher costs for training/ramp up.
- 新开发人员/ DBA具有更大的学习曲线，因为他们需要导航大型且无用的（对他们而言）数据库结构，这意味着培训/提升的成本更高。
- When The One database goes down, everyone in your organization plays solitaire until it is restored.
- 当The One数据库出现故障时，组织中的每个人都会玩纸牌游戏直到恢复。
- Creating test instances for app development means copying your entire db
- 为应用程序开发创建测试实例意味着复制整个数据库
#3
3
The only "Pro" I can think of is that all of your systems will be in the one database and therefore a single place to backup, store, etc. However, I would consider this to also be one of the biggest "Cons".
我能想到的唯一“Pro”是你的所有系统都在一个数据库中，因此是一个备份，存储等的地方。但是，我认为这也是最大的“缺点”之一。
Some other general Cons:
其他一般缺点：
- Much harder to move an application to a different location/server in the future.
- 将来将应用程序移动到其他位置/服务器要困难得多。
- Possible locking issues if any applications make use of tempdb.
- 如果任何应用程序使用tempdb，则可能存在锁定问题。
- Possible unrelated performance degredation on one application when another application is being used.
- 使用其他应用程序时，在一个应用程序上可能出现无关的性能降级。
- Much harder to implement an application level security model if all tables are in the same database.
- 如果所有表都在同一个数据库中，那么实现应用程序级安全模型要困难得多。
#4
3
It sounds to me as though your company is transitioning between two completely distinct motives for using database technology. The first is application support. The second is data integration. If I'm right about this, the process will open up a huge can of worms, and many of the issues won't even be addressed by putting all the data in one big database.
听起来好像你的公司在使用数据库技术的两个完全不同的动机之间转换。首先是应用程序支持。第二是数据集成。如果我对此是对的，那么这个过程将会开辟出大量的蠕虫病毒，而且许多问题甚至无法通过将所有数据放在一个大数据库中来解决。
Consider two of the points you made. The first is the complete lack of referential integrity across different databases. The second is the idea that each application will have its own schema. What this permits to happen is complete lack of referential integrity across schemas, putting you back in the quicksand you are in now.
考虑一下你提出的两点。首先是不同数据库之间完全缺乏参照完整性。第二个想法是每个应用程序都有自己的架构。这允许发生的事情是跨模式完全缺乏参照完整性，让你回到现在的流沙中。
Fixing the data so that referential integrity is present, and fixing the schemas so that referential integrity is enforced, and fixing the applications so that the applications agree with the new schemas will turn out to be a monumental task.
修复数据以便存在参照完整性，并修复模式以强制执行参照完整性，并修复应用程序以使应用程序与新模式一致，这将成为一项艰巨的任务。
Here's what your company really needs to do: Have one single CONCEPTUAL database that contains all "enterprise data", and defined in such a way that both referential integrity and entity integrity are enforced. Revise existing schemas so that they conform to the CONCEPTUAL database except for data that is both purely local to that schema and undocumented in the unified conceptual database. Use constraints wherever needed to guarantee that the data covered by these schemas doesn't lose integrity.
以下是贵公司真正需要做的事情：拥有一个包含所有“企业数据”的CONCEPTUAL数据库，并以强制执行完整性和实体完整性的方式定义。修改现有模式，使其符合CONCEPTUAL数据库，但数据纯粹是该模式的本地数据，并且在统一概念数据库中未记录。在需要的地方使用约束来保证这些模式所涵盖的数据不会失去完整性。
Make the decision about whether these schemas belong in one database or many databases based on database administration, fail soft, security, and performance requirements and NOT on the need to integrate data. Whether you use one platform or multiple platforms is a separable decision.
根据数据库管理，软件，安全性和性能要求失败而不是集成数据的需要，决定这些模式是属于一个数据库还是多个数据库。无论您使用一个平台还是多个平台，都是一个可分离的决定。
Where necessary, maintain synchronized copies of the same data in separate databases. Include the overhead of doing this in your performance considerations above.
必要时，在不同的数据库中维护相同数据的同步副本。在上面的性能考虑中包括执行此操作的开销。
Document the conceptual database out the gazoo. Don't just settle for definitions of the FORM of data. Insist on definitions of the semantics of the data as well.
将概念数据库记录在gazoo中。不要仅仅满足于数据形式的定义。坚持对数据语义的定义。
Notice that if you use ID fields instead of natural keys to enforce referential integrity, you will have to generate each ID field in one schema, and let the association between ID and dependent data propagate by means of synonyms, views, and synchronized replication.
请注意，如果使用ID字段而不是自然键来强制引用完整性，则必须在一个模式中生成每个ID字段，并通过同义词，视图和同步复制来传播ID和从属数据之间的关联。
This is not going to be easy.
这并不容易。
#5
0
If DB is getting bigger, making back-up is getting more difficult because of it's size.
如果DB越来越大，由于它的大小，备份变得越来越困难。
#6
0
This could mean a serious scalability problem if you want to add high-traffic applications in the future, since it is much easier to add new database servers which run seperate dbs than it is to parrallelize a single DB. At least in SQL Server.
如果您希望在将来添加高流量应用程序，这可能意味着严重的可伸缩性问题，因为添加运行单独dbs的新数据库服务器要比单个DB并行化更容易。至少在SQL Server中。
#7
0
Pros:
优点：
- The convenience of having everything in one place
- 将所有东西放在一个地方的便利
- Thinking less about good database design
- 少思考良好的数据库设计
Cons:
缺点：
- Even unrelated things are in one place
- 甚至不相关的东西也在一个地方
- Less thinking about good database design leading to poorly normalized data
- 少考虑良好的数据库设计导致数据标准化不佳
To me this just sounds like laziness and a belief that all this "fancy ivory tower database stuff" is worthless.
对我而言，这听起来像是懒惰，并且相信所有这些“花哨的象牙塔数据库的东西”都毫无价值。
#8
0
I can see that being scary, but considering the number of businesses that use Oracle EBS, or SAP, or other systems that are, in essence, this same configuration, I don't see it being a Bad Thing™. It's a big move, and will be tough to get correct, but it can really improve integration across the enterprise in the long run.
我可以看到它是可怕的，但考虑到使用Oracle EBS，SAP或其他系统的企业数量，实质上是相同的配置，我不认为它是一个坏东西™。这是一个很大的举措，并且很难做到正确，但从长远来看，它可以真正改善整个企业的整合。
#9
0
I've never heard of this approach and would like to know how the meeting goes. I see no real benefit in combining multiple applications into a single database when the data doesn't relate to each other.
我从来没有听说过这种方法，想知道会议的进展情况。当数据彼此不相关时，我认为将多个应用程序组合到单个数据库中没有任何实际好处。
I'm thinking you might have issues if you decide that an application requires it's own database server at one point.
如果您决定应用程序在某一时刻需要它自己的数据库服务器，我认为您可能会遇到问题。
#10
0
Ah, the old EggsInOneBasket design pattern. It's not a favourite.
啊，旧的EggsInOneBasket设计模式。这不是最喜欢的。
You're just compounding any problems caused by damage to that database. Spread the risk!
您只是复杂化了对该数据库造成的任何问题。传播风险！
#11
0
For the referential integrity issue, you can make copies of those shared tables in the subsidiary databases. You can't use real replication, but what you do is deny everything but select on these to most users.
对于参照完整性问题，您可以在辅助数据库中创建这些共享表的副本。您不能使用真正的复制，但您所做的是拒绝所有内容，但是对大多数用户选择这些内容。
On the same server, you can either push or pull data from the official repository of the master data and insert any new rows/update any changed rows. You can even do this with a trigger in the master database (I don't recommend it, though).
在同一台服务器上，您可以从主数据的官方存储库中推送或提取数据，并插入任何新行/更新任何已更改的行。您甚至可以使用master数据库中的触发器执行此操作（但我不建议这样做）。
If it's different instances or servers, you can use linked servers or SSIS.
如果是不同的实例或服务器，则可以使用链接服务器或SSIS。
You can put the common data into a "core" schema in each database. Then you can have tools to check that all your core tables in every subsidiary database are consistent. The worse that can happen is that an application is not seeing a new employee because the core isn't updated. And keeping your database separate gives you an ability to decouple and gives you maintenance windows. (You can even decouple and run "standalone" if your master is down for maintenance).
您可以将公共数据放入每个数据库的“核心”模式中。然后，您可以使用工具检查每个子数据库中的所有核心表是否一致。更糟糕的是，应用程序没有看到新员工，因为核心没有更新。保持数据库分离使您能够解耦并为您提供维护窗口。 （如果您的主人因维护而关闭，您甚至可以解除并运行“独立”）。
I expect you'll only be seeing a few dozen of these core entity tables in even a largish enterprise.
我希望你只会在一个大型企业中看到几十个核心实体表。
#12
0
There are many other ways to solve the referential integrity (RI) issue. I am not as familiar with SQL Server as other DB's. In Informix you can use synonyms to point to objects in other DB's and use these for your RI. In Oracle you can make a DB links to one or more DB's to accomplish the same thing. These approaches have the issue that if any of the DB's are down the RI will fail causing issues in the dependent DB's. selects would work, but inserts would fail. Consolidation can be a good idea, depending upon the size of the schema's, and other issues with scalability. SQL Server has serious scalability issues. Other DB platforms allow horizontal scaling with either a share everything approach (Oracle's RAC, latest Informix release) or a partitioned share nothing approach (DB2's DPF, Informix XPS, Netezza, Teradata)
还有许多其他方法可以解决参照完整性（RI）问题。我不像其他数据库那样熟悉SQL Server。在Informix中，您可以使用同义词指向其他数据库中的对象，并将它们用于您的RI。在Oracle中，您可以建立到一个或多个数据库的数据库链接以完成相同的任务。这些方法存在的问题是，如果任何数据库发生故障，RI将失败，从而导致从属数据库中出现问题。选择会起作用，但插入会失败。合并可能是个好主意，具体取决于模式的大小以及可伸缩性的其他问题。 SQL Server存在严重的可伸缩性问题。其他数据库平台允许使用share all方法（Oracle的RAC，最新的Informix版本）或分区的无共享方法（DB2的DPF，Informix XPS，Netezza，Teradata）进行水平扩展。
I am with some of the others here interested to hear the results of your meeting.
我和其他一些有兴趣听取会议结果的人在一起。
#1
14
Larger database are harder to maintain due to sheer size: backups take longer, disaster recovery is slower which in turn requires more often backups. You can address these by creating filegroups and using filegroup level backup in your maintenance plans and on crash recovery you can use the 'piecemeal restore' strategy to speed things up.
由于规模庞大，较大的数据库难以维护：备份需要更长时间，灾难恢复速度较慢，这反过来需要更频繁的备份。您可以通过在维护计划中创建文件组并使用文件组级备份来解决这些问题，并且在崩溃恢复时，您可以使用“零碎恢复”策略来加快速度。
Proper use of filegroups will make most of the 'cons' cited by previous replies go away: they can distribute the I/O, they can sanitize your maintenance plans and backup/restore strategy, they offer availability by taking offline only the damaged portion of the the db in case of crash. So I'd say that while those 'cons' are legit concerns, they have can be mitigated by a proper deployment strategy. Its true though that these mitigation actions require a true, experienced, dba at the helm as they will go beyond the comfort zone of a developer turned dba by need.
正确使用文件组将使之前回复中引用的大多数“缺点”消失：他们可以分发I / O，他们可以清理您的维护计划和备份/恢复策略，他们通过仅脱机损坏的部分提供可用性崩溃时的数据库。所以我要说的是，虽然这些“缺点”是合法的问题，但它们可以通过适当的部署策略来缓解。虽然这些缓解行动需要一个真实的，经验丰富的dba掌舵，因为它们将超出开发人员的舒适区域，因此需要，这是真实的。
Some of the pros I can think of quickly:
我能想到的一些专业人士：
- Consistency. You can have a backup-restore so that all data is consistent. Separate dbs don't allow this because you cannot coordinate a consistent set of backups unless you take them all offline, or make them r/o, during the backup.
- 一致性。您可以进行备份还原，以使所有数据保持一致。单独的dbs不允许这样做，因为您无法协调一致的备份集，除非您在备份期间将它们全部脱机或使其成为r / o。
- Dirt cheap high availability: you can deploy database mirroring for disaster recoverability and high availability. Multiple databases have problems because one cannot coordinate a simultaneous failover and apps are faced with the dilemma of seeking each database current location.
- 低廉的高可用性：您可以部署数据库镜像以实现灾难恢复和高可用性。多个数据库存在问题，因为无法协调同时进行故障转移，并且应用程序面临着寻找每个数据库当前位置的困境。
- Security. While most other posts see one database harder to secure, I'd say is easier to secure. Multiple databases seem harder to secure properly simply because what everyone does is they make one login and add it to that database db_owner group. Having one database will make things harder (unless you end up making everyone dbo, very bad) but once you start doing the right thing (granular access) then one db is not harder than multiple dbs, is actually easier because you won't have to copy/maintain some common groups/rights across multiple dbs.
- 安全。虽然大多数其他帖子看到一个数据库更难以保护，但我认为更容易保护。多个数据库似乎更难以正确保护，因为每个人都做的是他们进行一次登录并将其添加到该数据库db_owner组。拥有一个数据库会让事情变得更难（除非你最终让每个人都成为dbo，非常糟糕）但是一旦你开始做正确的事情（细粒度访问），那么一个db并不比多个dbs更难，实际上更容易因为你没有跨多个dbs复制/维护一些共同的组/权限。
- Control. Will be easier to impose certain policies and good practices on a single db rather than multiple ones (no data access to developers, app data access only through execute rights on the schema to enforce procedures access etc).
- 控制。将单个数据库而不是多个数据库强加给某些策略和良好实践会更容易（对开发人员无数据访问权限，仅通过模式上的执行权限来实施应用程序数据以强制执行过程访问等）。
There are also some cons I did not see in other posts:
在其他帖子中我也没有看到一些缺点：
- This will be much harder to pull off that you think right now
- 这对你现在想的很难实现
- Increase coupling between formerly separated applications will impose development restrictions: you can't simply alter your schema, you will have to coordinate it with the rest of the apps (you can argue that this was also the case before, but was brushed under the carpet by having separate dbs, and you're right)
- 增加以前分离的应用程序之间的耦合会产生开发限制：你不能简单地改变你的架构，你必须与其他应用程序协调它（你可以说这也是以前的情况，但是在地毯下刷了通过单独的dbs，你是对的）
- Log writes that are now distributed across multiple db logs will be consolidated into one single log file. If your writes are significant, this may turn out to be a serious bottleneck and force you to buy some expensive fast drives for the new, consolidated, log file. In general this can be addresses by making the log drive a stripped array across as many stripes as needed to make it fast enough (usually raid 10).
- 现在分布在多个db日志中的日志写入将合并到一个日志文件中。如果您的写入很重要，这可能会成为一个严重的瓶颈，并迫使您为新的，整合的日志文件购买一些昂贵的快速驱动器。一般来说，这可以通过使日志驱动器成为一条带有所需数量条带的剥离数组来使地址足够快（通常是raid 10）。
- GAM/SGAM/PFS allocations will also be consolidated, but again this will be alleviated by proper use of file groups.
- GAM / SGAM / PFS分配也将合并，但同样可以通过正确使用文件组来缓解这种情况。
#2
7
Pros:
优点：
- You only need to remember one connection string
- 您只需要记住一个连接字符串
- When users report that access is slow, you know which DB is causing the trouble
- 当用户报告访问速度很慢时，您就知道哪个数据库导致了问题
Cons:
缺点：
- Backups of The One DB will take a long time and will get progressively longer over time.
- One DB的备份将花费很长时间，并且随着时间的推移会逐渐变长。
- Restoring data from a backup will get increasingly difficult.
- 从备份中恢复数据将变得越来越困难。
- Performance Tuning (SQL Profiler, Execution Plan estimation) for a feature for one app will slow down every app.
- 一个应用程序的功能的性能调整（SQL事件探查器，执行计划估计）将减慢每个应用程序的速度。
- Restricting access to a single application's data is cumbersome if at all possible which will likely mean in practice that all devs and DBAs will be given keys to the ENTIRE kingdom.
- 限制访问单个应用程序的数据是很麻烦的，如果可能的话，这可能意味着在实践中所有开发人员和DBA将被赋予整个王国的密钥。
- New developers/DBAs have a much larger learning curve as they need to navigate a large and mostly useless (to them) database structure which means higher costs for training/ramp up.
- 新开发人员/ DBA具有更大的学习曲线，因为他们需要导航大型且无用的（对他们而言）数据库结构，这意味着培训/提升的成本更高。
- When The One database goes down, everyone in your organization plays solitaire until it is restored.
- 当The One数据库出现故障时，组织中的每个人都会玩纸牌游戏直到恢复。
- Creating test instances for app development means copying your entire db
- 为应用程序开发创建测试实例意味着复制整个数据库
#3
3
The only "Pro" I can think of is that all of your systems will be in the one database and therefore a single place to backup, store, etc. However, I would consider this to also be one of the biggest "Cons".
我能想到的唯一“Pro”是你的所有系统都在一个数据库中，因此是一个备份，存储等的地方。但是，我认为这也是最大的“缺点”之一。
Some other general Cons:
其他一般缺点：
- Much harder to move an application to a different location/server in the future.
- 将来将应用程序移动到其他位置/服务器要困难得多。
- Possible locking issues if any applications make use of tempdb.
- 如果任何应用程序使用tempdb，则可能存在锁定问题。
- Possible unrelated performance degredation on one application when another application is being used.
- 使用其他应用程序时，在一个应用程序上可能出现无关的性能降级。
- Much harder to implement an application level security model if all tables are in the same database.
- 如果所有表都在同一个数据库中，那么实现应用程序级安全模型要困难得多。
#4
3
It sounds to me as though your company is transitioning between two completely distinct motives for using database technology. The first is application support. The second is data integration. If I'm right about this, the process will open up a huge can of worms, and many of the issues won't even be addressed by putting all the data in one big database.
听起来好像你的公司在使用数据库技术的两个完全不同的动机之间转换。首先是应用程序支持。第二是数据集成。如果我对此是对的，那么这个过程将会开辟出大量的蠕虫病毒，而且许多问题甚至无法通过将所有数据放在一个大数据库中来解决。
Consider two of the points you made. The first is the complete lack of referential integrity across different databases. The second is the idea that each application will have its own schema. What this permits to happen is complete lack of referential integrity across schemas, putting you back in the quicksand you are in now.
考虑一下你提出的两点。首先是不同数据库之间完全缺乏参照完整性。第二个想法是每个应用程序都有自己的架构。这允许发生的事情是跨模式完全缺乏参照完整性，让你回到现在的流沙中。
Fixing the data so that referential integrity is present, and fixing the schemas so that referential integrity is enforced, and fixing the applications so that the applications agree with the new schemas will turn out to be a monumental task.
修复数据以便存在参照完整性，并修复模式以强制执行参照完整性，并修复应用程序以使应用程序与新模式一致，这将成为一项艰巨的任务。
Here's what your company really needs to do: Have one single CONCEPTUAL database that contains all "enterprise data", and defined in such a way that both referential integrity and entity integrity are enforced. Revise existing schemas so that they conform to the CONCEPTUAL database except for data that is both purely local to that schema and undocumented in the unified conceptual database. Use constraints wherever needed to guarantee that the data covered by these schemas doesn't lose integrity.
以下是贵公司真正需要做的事情：拥有一个包含所有“企业数据”的CONCEPTUAL数据库，并以强制执行完整性和实体完整性的方式定义。修改现有模式，使其符合CONCEPTUAL数据库，但数据纯粹是该模式的本地数据，并且在统一概念数据库中未记录。在需要的地方使用约束来保证这些模式所涵盖的数据不会失去完整性。
Make the decision about whether these schemas belong in one database or many databases based on database administration, fail soft, security, and performance requirements and NOT on the need to integrate data. Whether you use one platform or multiple platforms is a separable decision.
根据数据库管理，软件，安全性和性能要求失败而不是集成数据的需要，决定这些模式是属于一个数据库还是多个数据库。无论您使用一个平台还是多个平台，都是一个可分离的决定。
Where necessary, maintain synchronized copies of the same data in separate databases. Include the overhead of doing this in your performance considerations above.
必要时，在不同的数据库中维护相同数据的同步副本。在上面的性能考虑中包括执行此操作的开销。
Document the conceptual database out the gazoo. Don't just settle for definitions of the FORM of data. Insist on definitions of the semantics of the data as well.
将概念数据库记录在gazoo中。不要仅仅满足于数据形式的定义。坚持对数据语义的定义。
Notice that if you use ID fields instead of natural keys to enforce referential integrity, you will have to generate each ID field in one schema, and let the association between ID and dependent data propagate by means of synonyms, views, and synchronized replication.
请注意，如果使用ID字段而不是自然键来强制引用完整性，则必须在一个模式中生成每个ID字段，并通过同义词，视图和同步复制来传播ID和从属数据之间的关联。
This is not going to be easy.
这并不容易。
#5
0
If DB is getting bigger, making back-up is getting more difficult because of it's size.
如果DB越来越大，由于它的大小，备份变得越来越困难。
#6
0
This could mean a serious scalability problem if you want to add high-traffic applications in the future, since it is much easier to add new database servers which run seperate dbs than it is to parrallelize a single DB. At least in SQL Server.
如果您希望在将来添加高流量应用程序，这可能意味着严重的可伸缩性问题，因为添加运行单独dbs的新数据库服务器要比单个DB并行化更容易。至少在SQL Server中。
#7
0
Pros:
优点：
- The convenience of having everything in one place
- 将所有东西放在一个地方的便利
- Thinking less about good database design
- 少思考良好的数据库设计
Cons:
缺点：
- Even unrelated things are in one place
- 甚至不相关的东西也在一个地方
- Less thinking about good database design leading to poorly normalized data
- 少考虑良好的数据库设计导致数据标准化不佳
To me this just sounds like laziness and a belief that all this "fancy ivory tower database stuff" is worthless.
对我而言，这听起来像是懒惰，并且相信所有这些“花哨的象牙塔数据库的东西”都毫无价值。
#8
0
I can see that being scary, but considering the number of businesses that use Oracle EBS, or SAP, or other systems that are, in essence, this same configuration, I don't see it being a Bad Thing™. It's a big move, and will be tough to get correct, but it can really improve integration across the enterprise in the long run.
我可以看到它是可怕的，但考虑到使用Oracle EBS，SAP或其他系统的企业数量，实质上是相同的配置，我不认为它是一个坏东西™。这是一个很大的举措，并且很难做到正确，但从长远来看，它可以真正改善整个企业的整合。
#9
0
I've never heard of this approach and would like to know how the meeting goes. I see no real benefit in combining multiple applications into a single database when the data doesn't relate to each other.
我从来没有听说过这种方法，想知道会议的进展情况。当数据彼此不相关时，我认为将多个应用程序组合到单个数据库中没有任何实际好处。
I'm thinking you might have issues if you decide that an application requires it's own database server at one point.
如果您决定应用程序在某一时刻需要它自己的数据库服务器，我认为您可能会遇到问题。
#10
0
Ah, the old EggsInOneBasket design pattern. It's not a favourite.
啊，旧的EggsInOneBasket设计模式。这不是最喜欢的。
You're just compounding any problems caused by damage to that database. Spread the risk!
您只是复杂化了对该数据库造成的任何问题。传播风险！
#11
0
For the referential integrity issue, you can make copies of those shared tables in the subsidiary databases. You can't use real replication, but what you do is deny everything but select on these to most users.
对于参照完整性问题，您可以在辅助数据库中创建这些共享表的副本。您不能使用真正的复制，但您所做的是拒绝所有内容，但是对大多数用户选择这些内容。
On the same server, you can either push or pull data from the official repository of the master data and insert any new rows/update any changed rows. You can even do this with a trigger in the master database (I don't recommend it, though).
在同一台服务器上，您可以从主数据的官方存储库中推送或提取数据，并插入任何新行/更新任何已更改的行。您甚至可以使用master数据库中的触发器执行此操作（但我不建议这样做）。
If it's different instances or servers, you can use linked servers or SSIS.
如果是不同的实例或服务器，则可以使用链接服务器或SSIS。
You can put the common data into a "core" schema in each database. Then you can have tools to check that all your core tables in every subsidiary database are consistent. The worse that can happen is that an application is not seeing a new employee because the core isn't updated. And keeping your database separate gives you an ability to decouple and gives you maintenance windows. (You can even decouple and run "standalone" if your master is down for maintenance).
您可以将公共数据放入每个数据库的“核心”模式中。然后，您可以使用工具检查每个子数据库中的所有核心表是否一致。更糟糕的是，应用程序没有看到新员工，因为核心没有更新。保持数据库分离使您能够解耦并为您提供维护窗口。 （如果您的主人因维护而关闭，您甚至可以解除并运行“独立”）。
I expect you'll only be seeing a few dozen of these core entity tables in even a largish enterprise.
我希望你只会在一个大型企业中看到几十个核心实体表。
#12
0
There are many other ways to solve the referential integrity (RI) issue. I am not as familiar with SQL Server as other DB's. In Informix you can use synonyms to point to objects in other DB's and use these for your RI. In Oracle you can make a DB links to one or more DB's to accomplish the same thing. These approaches have the issue that if any of the DB's are down the RI will fail causing issues in the dependent DB's. selects would work, but inserts would fail. Consolidation can be a good idea, depending upon the size of the schema's, and other issues with scalability. SQL Server has serious scalability issues. Other DB platforms allow horizontal scaling with either a share everything approach (Oracle's RAC, latest Informix release) or a partitioned share nothing approach (DB2's DPF, Informix XPS, Netezza, Teradata)
还有许多其他方法可以解决参照完整性（RI）问题。我不像其他数据库那样熟悉SQL Server。在Informix中，您可以使用同义词指向其他数据库中的对象，并将它们用于您的RI。在Oracle中，您可以建立到一个或多个数据库的数据库链接以完成相同的任务。这些方法存在的问题是，如果任何数据库发生故障，RI将失败，从而导致从属数据库中出现问题。选择会起作用，但插入会失败。合并可能是个好主意，具体取决于模式的大小以及可伸缩性的其他问题。 SQL Server存在严重的可伸缩性问题。其他数据库平台允许使用share all方法（Oracle的RAC，最新的Informix版本）或分区的无共享方法（DB2的DPF，Informix XPS，Netezza，Teradata）进行水平扩展。
I am with some of the others here interested to hear the results of your meeting.
我和其他一些有兴趣听取会议结果的人在一起。