locked
Strategies for retrofiting existing databases with sync RRS feed

  • Question

  • Hello,

    I am looking for ideas on how to handle situations like this. Our company has an application that is installed at several thousand locations. Each location has it's own SQL Server Express 2008 db. We would like to be able to utilize the sync framework to keep a copy of each clients data on our side of things. We have a few constraints which are making a solution unclear:

    1) The client databases cannot be changed, although the SF tracking triggers and various infrastructure are ok.
    2) the client databases unfortunately use identity columns are primary keys for the tables and there is no "client id" table or field in the current schema to help differentiate one clients data from another clients data

    So basically we have dbs that were created without thought to a syncronization scenario, and we'd like to gather their data now.

    My initial thoughts would be to have an aggregate database that did use guids and then have our clients to an upload-only sync with that db, somehow mapping from an identity key to the guid key on the aggregator side.
    What this would mean is that we would want to sync heterogeneous schemas - something like this:

    Client TableA
    id - identity pk
    otherfields...

    Aggregator TableA
    clientId - guid pk
    id - int pk
    otherfields...

    I am not sure if such a thing is possible using SF, and if it is, which part I should focus on for customization. Right now I am looking at the collaborative scenario in an n-tier setup.

    One thought is to have each client sync with a local sql ce store and then grab the dataset generated in the changesApplied event, and handle that on the aggregator side, inserting the appropriate clientId values as we process the datasets.

    But it seems like there should be a better way - is there?

    Thanks,
    Dan Griffin

    Wednesday, March 9, 2011 6:27 PM

Answers

  • here's the code snippet for adding the extra column for the ClientId:

    static void ClientChangesSelected(object sender, DbChangesSelectedEventArgs e)
        {
          //let's check if we're synching the table we're interested
          if (e.Context.DataSet.Tables.Contains("testtable"))
          {
            var dataTable = e.Context.DataSet.Tables["testtable"];
            
            //let's add the new column
            dataTable.Columns.Add(new DataColumn("ClientId"));
    
            //let's assign the client guid
            var clientId = "89198182-24C9-4CD7-8427-F6790270E62C";
    
            for (int j = 0; j < dataTable.Rows.Count; j++)
            {
              DataRow row = dataTable.Rows[j];
    
              // if it's an update or an insert, just assign the clientId
              if (row.RowState == DataRowState.Modified || row.RowState == DataRowState.Added)
              {
                row["ClientId"] = clientId;
              }
              // if it's a delete, undo the delete status, 
              // assign the guid, accept the changes for the quid, 
              // then revert back the row as deleted
              else if (row.RowState == DataRowState.Deleted)
              {
                row.RejectChanges();
                row["ClientId"] = clientId;
                row.AcceptChanges();
                row.Delete();
              }
              
            }
          }
        }
    
    Thursday, March 10, 2011 9:38 AM

All replies

  • Hi Dan,

    I did a quick test with one server DB and two client DBs using a similar approach to what you have in mind and it appears to work.

    Basically, you'll provision the server as it is with the extra ClientId column, then you'll provision the clients without the ClientId column.

    On the client ChangesSelected event, you would loop thru the Dataset and add the ClientId column to the DataTable. Then loop thru each row in the DataTable to assign the ClientId value for that client.

    You can handle the inserted and updated rows similarly. However, you will have to handle the deletes separately as you cannot access the row and assign a value to any of its columns when it's flagged as deleted. So for deleted, you would call RejectChanges on the row, assign the ClientId, AcceptChanges, then Delete the row to restore the deleted status.

    You can also do this on the server side before applying the changes.

    I'll see if i can post the code later on.

     

    Thursday, March 10, 2011 7:37 AM
  • here's the code snippet for adding the extra column for the ClientId:

    static void ClientChangesSelected(object sender, DbChangesSelectedEventArgs e)
        {
          //let's check if we're synching the table we're interested
          if (e.Context.DataSet.Tables.Contains("testtable"))
          {
            var dataTable = e.Context.DataSet.Tables["testtable"];
            
            //let's add the new column
            dataTable.Columns.Add(new DataColumn("ClientId"));
    
            //let's assign the client guid
            var clientId = "89198182-24C9-4CD7-8427-F6790270E62C";
    
            for (int j = 0; j < dataTable.Rows.Count; j++)
            {
              DataRow row = dataTable.Rows[j];
    
              // if it's an update or an insert, just assign the clientId
              if (row.RowState == DataRowState.Modified || row.RowState == DataRowState.Added)
              {
                row["ClientId"] = clientId;
              }
              // if it's a delete, undo the delete status, 
              // assign the guid, accept the changes for the quid, 
              // then revert back the row as deleted
              else if (row.RowState == DataRowState.Deleted)
              {
                row.RejectChanges();
                row["ClientId"] = clientId;
                row.AcceptChanges();
                row.Delete();
              }
              
            }
          }
        }
    
    Thursday, March 10, 2011 9:38 AM
  • Thank you June! I will try this out today and post back with my results. I thought that something like this might be possible, but I was concerned that the mismatch in knowledge between the server and clients would cause a problem. The fact that the PKs are different is what really made me question the approach..
    Thursday, March 10, 2011 1:43 PM
  • you're right about the mismatch and this will not work in a bidirectional sync. i think the reason why it works is that the sync knowledge on the server is based on the ClientId+Id columns, and the dataset containst both columns so it is able to look up if it has received the change or not.

    Thursday, March 10, 2011 2:33 PM
  • That worked! I will be putting it through alot more testing to make sure that there aren't any quirks, but so far so good...thanks again!

    I wonder if this could work bidirectionally by doing the same type of thing, except to remove rows that didn't have the target ClientId...that's not what I'm tasked with now, but you never know..Something would probably have to be done while selecting changes on the server side because the knowledge coming from the client would only contain the Id. Maybe something in the SelectingChanges event could alter this but I'm not sure...any idea?

    Thursday, March 10, 2011 4:15 PM
  • if you want it to work bidirectional and is not concerned with rows from other clients, try putting a filter on the server side scope to filter on ClientId. then, when the changes has been selected, take out the ClientId column from the datatable and let the dataset flow thru.

    Thursday, March 10, 2011 11:14 PM