none
Syncronize SQL Server databases RRS feed

  • Question

  • Hi,

    I have an application that has users and groups (users can belong to multiple groups).

    I need to syncronize the users and groups to another database but the tables are different (the data on one table is spread on multiple tables on the other database) and the groups' schema is also different (groups can be mapped, according to a flag value, to roles, groups or domains on the other database).

    Is sync framework the best tool to use? Can I use local cache on the first database to determine the changes made and then use a program to send those changes to the other database and process them or is there a better and easier way to do this?

    Thanks,

    Pedro

    Monday, March 19, 2012 4:04 PM

Answers

  • if you mean you want to access the actual changes that was applied during sync, you can subscribe to the ChangesApplied event. the event parameter has a Context property that contains the changes applied.
    • Marked as answer by PiMané Friday, March 23, 2012 4:40 PM
    Wednesday, March 21, 2012 1:34 AM
    Moderator
  • Think I solved it...

    I don't remove the column from the destination scope description and it works fine... hope..

    :)

    Pedro

    • Marked as answer by PiMané Friday, March 23, 2012 4:40 PM
    Friday, March 23, 2012 4:39 PM

All replies

  • synchronizing one table against multiple tables is tricky.

    I would suggests using SSIS instead.

    Tuesday, March 20, 2012 1:42 AM
    Moderator
  • Hi,

    I used sync framework example that syncs SQL Server with a SQL Server Compact database.

    The synchronization is made by calling:

                NotificationsServiceCacheSyncAgent syncAgent = new NotificationsServiceCacheSyncAgent();
                Microsoft.Synchronization.Data.SyncStatistics syncStats = syncAgent.Synchronize();

    Is there a way of "catching" the data synchronized so I can process it and send it to another database?

    Thanks,

    Pedro

    Tuesday, March 20, 2012 3:20 PM
  • if you mean you want to access the actual changes that was applied during sync, you can subscribe to the ChangesApplied event. the event parameter has a Context property that contains the changes applied.
    • Marked as answer by PiMané Friday, March 23, 2012 4:40 PM
    Wednesday, March 21, 2012 1:34 AM
    Moderator
  • Hi,

    Made some changes to the project...

    I'm using  SqlSyncScopeProvisioning to set the server and client for sync.

    The client and server tables are different (not the same column's number) and so in the DbSyncScopeDescription for both I remove the columns that don't exist on both sides and on the ChangesSelected event I map the columns from on database to the other...

    But every time one column is updated, even if its not on the sync scope, the rows is synchronized... Is there a way to trigger only when certain columns are updated? The columns on the Sync Scope?

    Thanks,

    Pedro

    Wednesday, March 21, 2012 2:03 PM
  • No, change tracking is at the row level. If your up to it, you can modify the trigger to update the tracking table only if certain columns are updated.
    Wednesday, March 21, 2012 2:31 PM
    Moderator
  • Hi,

    Already managed to change the trigger to "monitor" only required columns.

    I "attached" to the remoteProvider.ChangesSelected a function to "change" the data (for example I add 10 days to a datetime column).

    My source has 3 columns and destination has 5 columns. I remove from the destination DbSyncScopeDescription the 2 columns that don't exist on the source.

    But their value is equal to 2 other columns that exist.

    On the ChangesSelected event I have the following code:

                if (e.Context.DataSet.Tables.Contains("MessagesSync"))
                {
                    var dataTable = e.Context.DataSet.Tables["MessagesSync"];
                    //rename the columns to match the destination table’s column names
                    dataTable.Columns["ProcessedDate"].ColumnName = "SentDate";
                    dataTable.Columns.Add("NewDate", typeof(DateTime));
                    foreach (DataRow row in dataTable.Rows)
                    {
                        row["NewDate"] = row["SentDate"];
                        if (!string.IsNullOrEmpty(row["SentDate"].ToString()))
                        {
                            row["SentDate"] = ((DateTime)row["SentDate"]).AddDays(10);
                        }
                    }
                }

    But I get an invalid cast error... 

    How do I "duplicate" a column value to another column value that isn't on the Schema description?

    Thanks,

    Pedro


    • Edited by PiMané Friday, March 23, 2012 3:23 PM
    Friday, March 23, 2012 3:22 PM
  • Think I solved it...

    I don't remove the column from the destination scope description and it works fine... hope..

    :)

    Pedro

    • Marked as answer by PiMané Friday, March 23, 2012 4:40 PM
    Friday, March 23, 2012 4:39 PM
  • Hi,

    I saw your post http://jtabadero.wordpress.com/2011/03/07/manipulating-the-change-dataset-in-sync-fx/ and I don't want to delete the rows in the destination. I have a column there "Deleted" that should change to 1 when a row is deleted.

    I can check that with 

    if (row.RowState == DataRowState.Deleted)

                            dataTable.Rows.Remove(row);

    And avoid deleting the row on the destination table.

    But how can I update the column "Deleted" value? 

    I can get the row Id with row["Id", DataRowVersion.Original] and execute a sql command to update the deleted column, but is there a way of adding a row to the dataTable with that information? The only column that has values is the Id, the other columns are empty...

    Thanks,

    Pedro

    Friday, March 23, 2012 5:07 PM
  • note that for deletes, only the PK of the row that was deleted is kept in the tracking table. however, the update stored procedure expects all the columns defined in the scope.

    you can grab the target row, include it in the change dataset and update the deleted column flag during the ChangesSelected event.

    however, it will be more optimal to simply issue an update directly to the table and remove the deleted row from the change dataset in the ChangesSelected event. or if you're not deleting rows at all from the destination, you can create an instead of trigger to simply set the deleted flag.

    Sunday, March 25, 2012 11:00 AM
    Moderator
  • I managed to get things "going", kind of...

    In my scenario I have a users table on one database (Name, Login, Password), and another on another database (Name, Description, Login, Password).

    Every time a user is inserted on server A I want to send it to server B, by mapping the columns, I keep the Description column on the scope so it has the Name value on the insert.

    When a certain column is updated on server A (for example Name or Password) the user must be updated on server B, but I don't want to update the Description column on server B. How do I "tell" to have Description = Name on the insert but to ignore Description or Description = Description on the update?

    I also came across another issue, managed to solve it (don't know if the correct way)... When a change is made on Server B the changes on Server A are not synced to Server B until Server B sends them to Server A. But I don't want this to happen. Server A is always the "master". What I did was to sync data from B to A and remove the rows from the datatable...

    Thanks,

    Pedro


    Monday, March 26, 2012 2:51 PM
  • Hi,

    I want to synchronize my databases from two different server. Does anyone know how to do it using SQL server 2012?? or any other way. Please help me with the step by step in doing this. I'd greatly appreciate it if you could help.

    Thanks,

    Fratz

    Thursday, June 14, 2012 1:48 AM
  • there is nothing special or different synchronizing SQL Server 2012. you can use the existing SqlSyncProvider, so any existing tutorial in the docs should work...

    see:

    Thursday, June 14, 2012 2:40 AM
    Moderator