sql-server - 在 SQL 数据库之间共享数据

Question

我试图解决一个问题，这一次，我没有创造。

我在一个有许多 Web 应用程序的环境中工作，这些应用程序由不同服务器上的不同数据库提供支持。

每个数据库在其设计和应用方面都相当独特，但每个数据库中仍然存在我想抽象出来的通用数据。每个数据库，例如有一个供应商表，一个用户表等......

我想将这些公共(public)数据抽象到一个数据库中，但仍然让其他数据库加入这些表，甚至有强制执行约束的键等......我在 MsSql 环境中。



有哪些选择？在我看来，我有以下选择:

链接服务器

只读登录以访问 View

还有什么需要考虑的吗？

Answer 1

有很多方法可以解决这个问题。我强烈推荐解决方案 1、2 或 3，具体取决于您的业务需求:

Transactional Replication :如果公共(public)数据库是帐户记录，并且您希望为单独的应用程序提供只读版本的数据，那么您可以将核心表，甚至可能只是表的核心列，复制到每个单独的服务器。这种方法的一个优点是您可以根据需要复制到任意数量的订阅者数据库。这也意味着您可以根据订阅者的需要自定义哪些表和字段可供订阅者使用。因此，如果一个应用程序需要用户表而不是供应商表，那么您只需订阅用户表。如果另一个只需要供应商表而不需要用户表，那么您只能订阅供应商表。另一个好处是复制保持自身同步，如果出现问题，您可以随时重新初始化订阅。

我使用事务复制从数据仓库推出 100 多个表，以分离需要访问来自多个系统的聚合数据的下游应用程序。由于我们的数据仓库每小时从镜像和日志传送数据源更新一次，因此生产应用程序在每小时 20 到 80 分钟的滑动窗口内拥有来自众多系统的数据。

Peer-to-Peer transactional replication作为发布类型可能更适合您提供的用例。如果您想逐个节点推出架构或复制更改，这可能非常有用。标准事务复制在这方面有一些限制。

快照复制发布类型比事务发布具有更多延迟，但如果延迟程度可以接受，您可能需要考虑它。

尽管您提到您是一家 Microsoft SQL Server 商店，但请记住其他 RDBM 也有类似的技术。由于您专门讨论 MS SQL Server，请注意事务复制也允许您复制到 Oracle 数据库。因此，如果您的组织中有一些这样的解决方案，该解决方案仍然可以工作。

使用事务复制的一个缺点是，如果您的中央服务器出现故障，您可能会开始遇到复制对象下游副本中的数据延迟。如果复制的对象(文章)非常大并且您需要重新初始化表，那么这也可能需要很长时间才能完成。

Mirrors :如果您希望在下游服务器上近乎实时地访问数据库，您最多可以设置两个异步镜像。我以这种方式将数据与 CRM 应用程序集成在一起。所有读取都来自连接到镜像。所有写入都被推送到一个消息队列，然后该队列将更改应用到中央生产服务器。这种方法的缺点是不能创建超过 2 个异步镜像。您不想为此目的使用同步镜像，除非您也计划将镜像用于灾难恢复。

Messaging Systems :如果您希望有许多独立的应用程序需要来自单个中央数据库的数据，那么您可能需要考虑企业消息传递系统，如 IBM Web Sphere、Microsoft BizTalk、Vitria、TIBCO 等。这些应用程序是专门为解决这个问题而构建的.它们的实现和维护往往既昂贵又麻烦，但如果您拥有全局分布式系统或数十个独立应用程序，它们都需要在某种程度上共享数据，则它们可以扩展。

Linked Servers : 听起来你已经想到了这个。您可以通过链接服务器公开数据。我不相信这是一个好的解决方案。如果你真的想走这条路，那么考虑设置一个从中央数据库到另一台服务器的异步镜像，然后设置到镜像的链接服务器连接。这至少会降低来自 Web 应用程序的查询会导致中央生产数据库出现阻塞或性能问题的风险。

IMO，链接服务器往往是共享应用程序数据的危险方法。这种方法仍然将数据视为数据库中的二等公民。这会导致一些非常糟糕的编码习惯，特别是因为您的开发人员可能使用不同的连接方法在不同的服务器上工作。您不知道是否有人会针对您的核心数据编写真正令人讨厌的查询。如果您设置了一个标准，要求将共享数据的完整副本向下推送到非核心服务器，那么您就不必担心开发人员是否编写了错误的代码。至少从他们糟糕的代码不会危及其他编写良好的系统的性能的角度来看。

有很多资源可以解释为什么在这种情况下使用链接服务器会很糟糕。原因的非详尽列表包括: (a) the account used for the linked server must have DBCC SHOW STATISTICS permissions or the queries will not be able to make use of existing statistics , (b) 除非作为 OPENQUERY 提交，否则不能使用查询提示，(c) 与 OPENQUERY 一起使用时不能传递参数，(d) 服务器没有关于链接服务器的足够统计信息，因此，创建非常糟糕的查询计划，(e) 网络连接问题可能导致故障，(f) any one of these five performance issues , 和 (g) the dreaded SSPI context error when trying to authenticate windows active directory credentials in a double hop scenario .链接服务器对于某些特定场景很有用，但不建议围绕此功能构建对中央数据库的访问，尽管技术上可行，但不建议这样做。

批量 ETL 流程:如果 Web 应用程序可以接受高度延迟，那么您可以使用 SSIS (lots of good links in this StackOverflow question) 编写批量 ETL 流程。由 SQL Server 代理作业执行以在服务器之间移动数据。还有其他替代 ETL 工具，如 Informatica、Pentaho 等，因此请使用最适合您的工具。

如果您需要低延迟，这不是一个好的解决方案。我在与 3rd 方托管的 CRM 解决方案同步到可以容忍高延迟的领域时使用了这个解决方案。对于不能容忍高延迟的字段(基本帐户创建数据)，我们依靠在帐户生成点通过 Web 服务调用在 CRM 中创建重复记录。

每晚备份和恢复:如果您的数据可以容忍高度延迟(最多一天)和不可用时间，那么您可以跨环境备份和恢复数据库。对于需要 100% 正常运行时间的 Web 应用程序来说，这不是一个好的解决方案。这个想法是您进行基线备份，将其还原为单独的还原名称，然后在新数据库准备好使用时重命名原始数据库和新数据库。我已经在一些内部网站应用程序中看到过这样做，但我通常不推荐这种方法。这更适合较低的开发环境，而不是生产环境。

Log Shipping Secondaries :您可以在主服务器和任意数量的辅助服务器之间设置日志传送。这类似于夜间备份和还原过程，不同之处在于您可以更频繁地更新数据库。在一个实例中，该解决方案用于通过在两个日志传送接收者之间切换来向下游用户公开来自我们主要核心系统之一的数据。还有另一台服务器指向这两个数据库，并在新数据库可用时在它们之间切换。我真的很讨厌这个解决方案，但有一次我看到这个实现时，它确实满足了业务的需求。