What is the best way to sync multiple SqlServers to one SQL Server 2005?

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 a开发者_如何学Goll data first! Huge data set (bigger than 16mg)

I think can't use replication because the structure and primary keys are different.

---

Have you considered using SSIS to do scheduled data synchronization? You can do data transformation and bulk inserts fairly easily.

---

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.

---

You could use SQL Server Transactional Replication: HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted Domains or Across the Internet