复制以保持数据完整性
我们有两种情况：
我们有一个包含职位表的数据库服务器。这些职位需要由另一台服务器上的数据库访问。我们已经建立了链接服务器连接，一切正常。问题：因为表物理存储在另一台服务器上，所以不可能存在外键引用完整性。此外，每当第一台服务器因维护而停机时，都会破坏第二台服务器上的应用程序，因为它们依赖于它来获取链接服务器连接和数据。
在另一台数据库服务器上，我们有一个用于保存公共数据元素的数据库。例如，有一个美国州和地区的表、一个邮政编码表以及我们的应用程序中使用的各种代码表。问题：就像上面一样，没有参照完整性能力。此外，维护安全性并确保有权访问应用程序数据库的用户具有对这个“公共”数据库的必要访问权限是乏味且耗时的。
我的问题是：由于这些数据对于消费应用程序来说是只读的，我们可以使用复制来解决这个问题吗？我们是否可以将一张职位表从源复制到目标服务器/数据库，并且我们是否可以对“通用”数据库中的表执行相同的操作（将它们复制到任何需要它们的应用程序数据库）？我认为这可以消除上述问题，但这是明智的做法还是会造成比解决的问题更多的问题？
We have two situations:
We have a database server that contains tables of job titles. Those job titles need to be accessed by a database on another server. We have established a linked server connection and everything works well. Problems: There can be no foreign key referential integrity because the tables are physically stored on another server. Also, whenever the first server is taken down for maintenance, it breaks the applications on the second server because they are dependent on it for the linked server connection and data.
On another database server, we have a database that is used to hold common data elements. For example, there is a table of US states and territories, a table of zip codes, and various code tables used throughout our applications. Problem: Just like above, there is no referential integrity abilities. In addition, maintaining the security and ensuring that the users who have access to the application database have the necessary access to this "common" database is tedious and time consuming.
My question is: Since this data is read-only for the consuming applications, could we use replication to solve this problem? Could we replicate the one table of job titles from the source to the destination server / database, and could we do the same for the tables in the "common" database (replicate them to any application database that needs them)? I think this would eliminate the above problems, but would it be a wise course of action or would it cause more issues than it solves?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
听起来您正在尝试使用大量技术解决一个简单的问题。如果您想在通过链接服务器连接的表之间提供某种引用约束，您还可以创建触发器。我提到这一点的唯一原因是因为您的场景仅指定了几个表。
希望这有帮助
Sounds like you are trying to solve a simple problem with alot of technology. If you want to provide some sort of referential constraint between tables connected though a linked server, you could also create triggers. The only reason I mention this is because your scenario only specifies a few tables.
Hope this helps