将数据库数据从多个数据库复制到一个。数据复制（某种程度）
这涉及数据复制，有点：
我们有许多安装了 SQL Express 的站点，每个站点上都有一个“审核”数据库，该数据库有一个第一范式的表（为了让生活变得简单:)
现在我需要从每个站点，然后复制内容（例如，日期时间值 > 1/1/200 00:00，但这会明显改变）并将其复制到 sql server 中的一个大“超级表”中，该表也有主键作为站点名称（需要注入）和 SQL Express 表中的当前主键）
例如，许多 SQL Express 数据库具有以下表列
ID、定义名称、定义类型、日期时间、Success、NvarChar1、NvarChar2 等等等，
而大型超级表需要具有：
站点名称、ID、定义名称、定义类型、日期时间、成功、NvarChar1、NvarChar2 等等，
其中粗体项目是主键
是否有微软（或我认为是非微软）应用程序/工具/东西来管理复制所有这些数据，或者我们需要编写自己的应用程序/工具/东西？
非常感谢。
This involves data replication, kind of:
We have many sites with SQL Express installed, there is an 'audit' database on each site that has one table in 1st normal form (to make life simple :)
Now I need to get this table from each site, and copy the contents (say, with a Date Time Value > 1/1/200 00:00, but this will change obviously) and copy it to a big 'super table' in sql server proper, that also has the primary key as the Site Name (That needs injecting in) and the current primary key from the SQL Express table)
e.g. Many SQL Express DBs with the following table columns
ID, Definition Name, Definition Type, DateTime, Success, NvarChar1, NvarChar2 etc etc etc
And the big super table needs to have:
SiteName, ID, Definition Name, Definition Type, DateTime, Success, NvarChar1, NvarChar2 etc etc etc
Where items in bold are the primary key(s)
Is there a Microsoft (or non MS I suppose) app/tool/thing to manager copying all this data accross already, or do we need to write our own?
Many thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以使用SSIS（SQL Server附带的）来填充，它可以设置变量来更改到各种数据库的连接字符串。我有一个循环遍历整个列表并使用来自三个不同供应商的三个不同文件执行相同的过程。您可以使用类似的方法来循环不同的站点数据库。将要从中复制审核数据的数据库的整个列表放入表中，并循环遍历它，每次更改连接字符串。
然而，为什么每个站点都需要一个大型审计表呢？如果数据库中的每个表在发生更改时都填充审计表，那么审计表最终会成为性能的一个巨大问题。每次插入、更新和删除都必须命中该表，然后您建议在其上添加导出。在我看来，这似乎是锁定、死锁和各种肮脏问题的有保证的结构。帮自己一个忙，将每个审计表限制为它正在审计的表。
You can use SSIS (which comes with SQL Server) to populate, it can be set up with variables to change the connection string to the various databases. I have one that loops through the whole list and does the same process using three differnt files from three differnt vendors. You could so something simliar to loop through the different site databases. Put the whole list of database you want to copy the audit data from in a table and loop through it changing the connection string each time.
However, why on earth would you want one mega audit table per site? If every table in the database populates the audit table as changes happen, then the audit table eventually becomes a huge problem for performance. Every insert, update and delete has to hit this table and then you are proposing to add an export on top of that. This seems to me to be a guaranteed structure for locking and deadlocks and all sorts of nastiness. Do yourself a favor and limit each audit table to the table it is auditing.
需要考虑的事项：
链接服务器和 sp_msforeachdb 作为 DIY 解决方案的一部分。
SQL Server 复制（由 Microsoft 提供）（我相信它可以从 SQL Server Express 中提取数据）
SQL Server Integration Services 可以从 SQL Server Express 实例中提取数据。
就我个人而言，我会首先研究集成服务。
祝你好运。
Things to consider:
Linked servers and sp_msforeachdb as part of a do-it-yourself solution.
SQL Server Replication (by Microsoft) (which I believe can pull data from SQL Server Express)
SQL Server Integration Services which can pull data from SQL Server Express instances.
Personally, I would investigate Integration Services first.
Good luck.
您可以使用 SymmetricDS 来完成此操作。 SymmetricDS 是开源、支持网络、独立于数据库的数据同步/复制软件。它使用网络和数据库技术在关系数据库之间近乎实时地复制表。该软件旨在扩展大量数据库、跨低带宽连接工作并承受网络中断。
然而，截至目前，您需要实现自定义 IDataLoaderFilter 扩展点（在 Java 中）来添加额外的列。不过，元数据将可用，因为您的 SiteName 将是 external_id。
You could do this with SymmetricDS. SymmetricDS is open source, web-enabled, database independent, data synchronization/replication software. It uses web and database technologies to replicate tables between relational databases in near real time. The software was designed to scale for a large number of databases, work across low-bandwidth connections, and withstand periods of network outage.
As of right now, however, you would need to implement a custom IDataLoaderFilter extension point (in Java) to add the extra column. The metadata would be available though because your SiteName would be the external_id.