Sync many client database to one central database

Question

We have client application which can be installed on local machine where users can work offline and create projects in it. This application is already developed.

Now we need to create Admin application where Admin can view all the Projects created by the client application. So basically we need to sync all the client database with one central database.

I considered using Microsoft Sync Framework but the challenge is.

1. Suppose I have table MasterProject in which ProjectId is Primary key and Identity.
2. user1 creates two project so the Project ids will be 1 & 2.
3. user2 creates 2 project its Project ids will be 1 & 2.

Now I need to sync both these data to Central database it won't allow since project id is Primary key at central database. If I remove primary key at central database the Sync Framework does not support. I have also tried to create composite primary key on ProjectId and UserId and tried to trick the sync framework by matching the schema of tables but it does not work.

I don't want to change the database of client application. Is there any way to achieve this?

Answer 1

It seems to me that you're falling into an XY Problem. You want to see the data from lots of different databases, and have the mindset that you should only connect to a single database. That line of thinking logically lead you to the conclusion that you need to bring all the information into one central database.

IMO your real problem is needing to see the data in multiple databases in one application.

One way to accomplish this is to merge the separate databases back into a central database like you suggested. Since you aren't looking to modify the existing clients to use the master database this seems like a complicated and error prone plan that will increase your maintenance costs.

1. You have to figure out how to merge separate databases without conflicts.
2. You have to worry about performance tuning the master database separately from the clients since it is going to naturally have much more data than any single source database.
3. You are artificially increasing the storage/hardware/licensing costs by creating a master database that is only going to be used by a small number of users.
4. While the space/size constraints of SQL Express might work for the client databases, once all the databases are pulled into the master database the combined total might overwhelm SQL Express's constraints.

   ---

I would recommend you consider taking a different approach. Instead of concentrating the databases why not concentrate the connection strings? Following this route you would only have to write a screen for the admins to select the project and then perform a lookup for the correct connection string for the project's database. Then you would be able to essentially use the client but dynamically swap out the connection string depending on what database is being targeted.

Answer 2

Reading your latest comment, you can begin with a composite key for uniqueness. There can be a key for the Master table itself + key(s) coming from user's project.

You may or may not have more/less keys per individual project tables. However, combining with the set of keys per project table, you'll have a set that'll be unique and still be able to represent original project table's data.