CODEWITHSUNDEEP
c#sql-serverdatabasedata-synchronization
Michael Jordan
Michael Jordan

Reputation: 432

How to sync master SQL Server database with multiple customer data to individual customer db's

I have a central SQL Server database in which we store multiple customers' data. The tables that need to be synchronized are similar but not identical. For example:

In the customer's db (which is SQL Server CE wrapped with EF) is a table (obviously a contrived example) with rows:

public class Person
{
    public string FirstName;
    public string LastName;
}

Then, on the SQL Server, the corresponding table rows would be more like:

public class Person
{
    public int CustomerID;
    public string FirstName;
    public string LastName;
}

The purpose of the extra CustomerID column is, of course, because there are multiple rows stored from many different customers, and we want to be able to only select one customer's information at any particular time.

I am looking at MS Sync technology as well as Merge Replication, but initially, these are my impressions:

Sync does not appear to be suitable as it appears to be one-way. It would seem to be more attuned to having some central store of information that you would like to have synchronized out to one or more customers who are in effect slaves of the master. The synchronization that I need is definitely a two-way process.

Merge Replication appears that it may be able to accomplish this, but I am not sure if it will deal with this particular situation, where all of the customer information is stored in a single table and it would be necessary to filter out the rows to be involved in the particular synchronization before performing it.

Our current implementation is not performant. It involves the SQL Server, on top of which we load EF, on top of which we load reflection, on top of which we load dynamic. With this stack it is taking anywhere from half a second to a full second for every row that gets added to any table.

Would anyone have any advice on a better way to handle this situation?

Upvotes: 0

Views: 232

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88852

I would just optimize your custom solution. It doesn't sound like it's a complicated-enough scenario to benefit from a full Sync/Repl framework. Plus you should be planning to move from CE to SqlLite, or otherwise move off of CE.

Upvotes: 1

Related Questions