我有几个使用我的 Windows 应用程序的客户端数据库。
我想将此数据发送到在线网站。
客户端数据库和服务器数据库结构不同，因为我们需要将客户端ID列添加到服务器数据库中的某些表中。
我用这种方式来同步数据库；使用另一个应用程序并使用带有事务的 C# 批量复制来同步数据库。
我的服务器数据库sql server太忙，无法运行并行任务。
我正在研究这个解决方案：
我在更新、删除、插入后使用触发器将更改保存在一个表中，并创建 SQL 查询来发送 Web 服务来同步数据。
但我必须先发送所有数据！巨大的数据集（大于16mg）
我认为不能使用复制，因为结构和主键不同。
I have several client databases that use my windows application.
I want to send this data for online web site.
The client databases and server database structure is different because we need to add client ID column to some tables in server data base.
I use this way to sync databases; use another application and use C# bulk copy with transaction to sync databases.
My server database sql server is too busy and parallel task cannot be run.
I work on this solution:
I use triggers after update, delete, insert to save changes in one table and create sql query to send a web service to sync data.
But I must send all data first! Huge data set (bigger than 16mg)
I think can't use replication because the structure and primary keys are different.
发布评论
评论（3）
您是否考虑过使用 SSIS 进行计划数据同步？您可以相当轻松地进行数据转换和批量插入。
Have you considered using SSIS to do scheduled data synchronization? You can do data transformation and bulk inserts fairly easily.
据我了解您想要做什么，您希望允许多个客户端应用程序将其数据同步到服务器，以便服务器拥有来自所有站点的所有数据，但每个记录也有一个客户端标识符，以便您可以保持追溯到源头的可追溯性。
为什么必须先将所有数据发送到服务器才能获得其他信息设置？您应该能够同时构建所有这些东西。此外，您不必一次性上传所有数据。将它们分阶段安排为每天一个（假设您的客户数据库数量较少），这样您就可以轮流专注于每个任务，并确保该过程准确完成。
将所有数据合并到一张表后，您是否会将数据复制回客户端？您的大小信息传达错误，您是说每个数据库都大于 16GB？那么 5 个站点的累积大小为 80GB 可以复制回各个站点吗？
否则，您概述的使用单独的应用程序专门处理数据上传的方法将是最合适的。
更新主数据库后是否要升级各个架构？您可能需要更改表并向每条记录添加一个布尔列，并将它们标记为“已发送”或“未发送”，以跟踪“晚”更新/插入的任何记录。我有一种感觉，您将进行滚动部署/升级，并且您正在尝试找出如何使其保持“同步”而不丢失任何内容。
As I understand what you're trying to do, you want to allow multiple client applications to have their data synchronized to a server in such a way that the server has all the data from all the sites, but that each record also has a client identifier so you can maintain traceability back to the source.
Why must you send all the data to the server before you get the other information setup? You should be able to build all these things concurrently. Also, you don't have to upload all the data at one time. Stage them out to one per day (assuming you have a small number of client databases), that would give you a way to focus on each in turn and make sure the process was completed accurately.
Will you be replicating the data back to the clients after consolidating all the data into one table? Your size information was miscommunicated, were you saying each database was larger than 16GB? So then 5 sites would have a cumulative size of 80GB to be replicated back to the individual sites?
Otherwise, the method you outlined with using a separate application to specifically handle the uploading of data would be the most appropriate.
Are you going to upgrade the individual schemas after you update the master database? You may want to ALTER TABLE and add a bool column to every record and mark them as "sent" or "not sent" to keep track of any records that were updated/inserted "late". I have a feeling you're going to be doing a rolling deployment/upgrade and you're trying to figure out how to keep it all "in sync" without losing anything.
您可以使用 SQL Server 事务复制：如何：在不受信任的域中或通过 Internet 运行 SQL Server 的计算机之间进行复制
You could use SQL Server Transactional Replication: HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted Domains or Across the Internet