3

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?

migrated from stackoverflow.com Jun 21 '15 at 19:51

This question came from our site for professional and enthusiast programmers.

  • ProjectId should be GUIDs for a start. I worked on a few projects using Microsoft Sync Framework (msdn.microsoft.com/en-us/library/bb902854(v=sql.110).aspx). But records that required to be synced to central had their Id unique by either GUID, prefixed strings, or composite keys (LocationId+UserId+ProjectId) – Hassan Gulzar Jun 21 '15 at 6:40
  • That is good suggestion but like i said i do not want to change database of the client application because it is already working application and users have already created projects. – Nirav Parmar Jun 21 '15 at 7:10
  • The central DB is also already created and running? – Hassan Gulzar Jun 21 '15 at 7:53
  • No Central DB is not created. The Admiin application is yet to be created which will be connected to this central DB and Admin could view all the projects. – Nirav Parmar Jun 21 '15 at 8:01
  • This is a one-way sync, right? – Enigmativity Jun 21 '15 at 9:02
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.

0

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.

Sample tables

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.