SQL Server 复制（跨数据库查询和约束）
我们希望将数据从一个数据库复制到其他几个数据库（在另一台服务器上）。将这些表复制到另一台服务器上的共享数据库并让我们的跨数据库查询引用共享数据库是否有意义……或者复制到另一台服务器上的每个单独数据库更有意义吗？跨数据库连接会对性能造成影响吗？跨数据库约束会按预期工作吗？
复制一次到共享数据库将有助于复制性能...我正在尝试评估由于跨数据库查询或约束而导致的任何性能影响是否值得。
编辑：看起来跨数据库约束在sql server中是不可能的？如果这是真的，那么我们将不得不复制到每个数据库
We want to replicate data from one database to several others (on another server). Would it make sense to replicate these tables to a shared database on the other server and have our cross-database queries reference the shared database... or would it make more sense to replicate out to each individual database on the other server? Would cross database joins pose a performance hit? Would cross-database constraints work as expected?
Replicating once to a shared database would help replication performance... I'm trying to evaluate whether or not any performance hit as a result of cross-database queries or constraints would be worth it.
Edit: It looks like cross database constraints are not possible in sql server? If this is true then we would have to replicate to each database
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
跨数据库查询比同一数据库内查询要慢一些。外键仅在同一数据库内工作。通常的方法是在每个数据库中创建一个单独的模式（如 ETL），然后将这些表复制到该模式。这种方法实际上在数据集市之间复制维度表时经常使用。
当使用跨数据库方法时，使用触发器来实现约束——可能会很慢而且很复杂。
根据您的应用程序，您可以将外键实现为“仅逻辑”，并定期运行“查找孤儿”查询来处理引用完整性。
Cross database queries are somewhat slower that within the same DB. Foreign keys work within the same DB only. Usual approach is to create a separate schema in each DB (like ETL) and then replicate those tables to that schema. This approach is actually frequently used when replicating dimension tables between data marts.
When using cross-db approach, use triggers to implement constraints -- may be slow and complicated.
Depending on your application, you may implement foreign keys as "logical only" and run periodic "look for orphans" queries to deal with referential integrity.