用不同的模式同步2个数据库 [英] Syncrhonizing 2 database with different schemas

问题描述

我们有一个使用通用表设计的规范化SQL Server 2008数据库。因此，我们没有通用的表（实体，实例，关系，属性等），而不是为每个实体（例如产品，订单，订单项等）都有单独的表。

We have a normalized SQL Server 2008 database designed using generic tables. So, instead of having a separate table for each entity (e.g. Products, Orders, OrderItems, etc), we have generic tables (Entities, Instances, Relationships, Attributes, etc).

我们决定使用一个单独的非规范化数据库来快速检索数据。假设它们具有不同的架构，您能否建议我使用各种技术来同步这两个数据库？

We have decided to have a separate denormalized database for quick retrieval of data. Could you please advise me of various technologies out there to synchronize these 2 databases, assuming they have different schemas?

干杯，
Mosh

Cheers, Mosh

推荐答案

当有两个数据库时拥有截然不同的架构，您应该研究数据迁移或复制的技术，而不是同步技术。 SQL Server为此提供了两种技术，SSIS和复制，或者您可以编写自己的脚本来执行此操作。

When two databases have so radically different schemas you should be looking at techniques for data migration or replication, not synchronization. SQL Server provides two technologies for this, SSIS and Replication, or you can write your own script to do this.

复制将从源数据库中获取新数据或修改过的数据，并将其复制到目标数据库。它提供了用于计划，打包和分发变更的机制，并且可以处理实时以及批量更新。为了工作，它需要在两个数据库中添加足够的信息以跟踪修改和匹配的行。在您的情况下，很难识别出哪些产品已更改，因为您将不得不识别4个或更多不同表中的所有相关修改行。可以做到，但需要一些努力。无论如何，由于复制不允许对源数据进行任何转换，因此必须创建与目标模式匹配的视图。

Replication will take new or modified data from a source database and copy it to a target database. It provides mechanisms for scheduling, packaging and distributing changes and can handle both real-time as well as batch updates. To work it needs to add enough info in both databases to track modifications and matching rows. In your case it would be hard to identify which "Products" have changed as you would have to identify all relevant modified rows in 4 or more different tables. It can be done but it will require some effort. In any case, you would have to create views that match the target schema, as replication doesn't allow any transformation of the source data.

SSIS将从一个数据中提取数据源，对其进行转换并将其推向目标。它没有用于跟踪更改的内置机制，因此您将不得不向表中添加字段以跟踪更改。严格来说，这是一个可以按计划运行的批处理过程。主要好处是，您可以执行多种转换，而复制几乎不允许执行任何转换（除了从视图中绘制数据外）。您可以创建一个数据流，以在与产品相关的属性记录更改时仅修改相关的产品字段，或者仅重新构成整个产品记录并覆盖目标记录。

SSIS will pull data from one source, transform it and push it to a target. It has no built-in mechanisms for tracking changes so you will have to add fields to your tables to track changes. It is strictly a batch process that can run according to a schedule. The main benefit is that you can perform a wide variety of transformations while replication allows almost none (apart from drawing the data from a view). You could create dataflows that modify only the relevant Product field when a Product related Attribute record changes, or simply reconstitute an entire Product record and overwrite the target record.

最后，您可以创建自己的触发器或存储过程，这些触发器或存储过程将在数据更改时运行并将其从一个数据库复制到另一个数据库。

Finally, you can create your own triggers or stored procedures that will run when the data changes and copy it from one database to the other.

我还应该指出，您可能已经-规范化您的数据库。在这三种情况下，当您联接所有表以重新构成实体时，都会有一些性能损失，从而导致必需的大量锁定和索引的低效使用。为了方便更改，您要牺牲性能和可伸缩性。

I should also point out that you have probably over-normalized your database. In all three cases you will have some performance penalty when you join all tables to reconstitute an entity, resulting in a larger amount of locking that is necessary and inefficient use of indexes. You are sacrificing performance and scalability for the sake of ease of change.

也许您应该看一下SQL Server 2008的稀疏列功能，以便在保持性能和可伸缩性的同时支持灵活的架构。

Perhaps you should take a look at the Sparse Column feature of SQL Server 2008 for a way to support flexible schemas while maintaining performance and scalability.

这篇关于用不同的模式同步2个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！