none
Synching client database is not picking up server database new rows, why? RRS feed

  • Question

  • I am doing a DB sync Clients are SQL Server 2008 Express and server is SQL Server 2008.  DB sync has been working fine untill about a week ago.

    When i run a sync everything runs smooth but the last few days changes are not picked up from the server database - this is download only (to clients)

    I run test with tracing on and everything runs fine except that no changes are picked up.  It says Inserts: 0 Deletes: 0  Updates: 0  Changes Enumerated: 0.  I know ethere are changes because I can see them when I compare client and server database.

    What can be causing this and where would one look to find the cause.

    Thanks for any help given


    Hassan

    Wednesday, February 8, 2012 8:15 PM

Answers

  • as i have previously mentioned, these steps should work:

    1. provision server

    2. provision  client 1

    3. sync client 1

    4. back up client 1

    5. restore client 1 to client x

    6. performpostrestorefixup on client x

    7. sync client x

    • Marked as answer by HakkaBuster Friday, February 10, 2012 11:37 AM
    Friday, February 10, 2012 9:01 AM
    Moderator

All replies

  • when you make an insert/update/delete, can you check if the corresponding entry in the tracking table is inserted/updated?

    better, run SQL Profiler to capture of a trace of the selectchanges and see if it returns any row.

    Thursday, February 9, 2012 1:04 AM
    Moderator
  • Hi June,

    Doing the tests but I believe that will result in o rows as the trace has show.

    Just in case here is my bared down code showing providers

    SqlConnection clientConn = new SqlConnection(ConfigurationUtility.ClientConnectionString);
    SqlConnection serverConn = new SqlConnection(ConfigurationUtility.ServerConnectionString);
    SyncOrchestrator syncOrchestrator = new SyncOrchestrator();
    syncOrchestrator.LocalProvider = new SqlSyncProvider("AllTablesScope", clientConn);
    syncOrchestrator.RemoteProvider = new SqlSyncProvider("AllTablesScope", serverConn);
    syncOrchestrator.Direction = SyncDirectionOrder.Download;
    SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();


    Hassan

    Thursday, February 9, 2012 8:55 AM
  • the check on the tracking table is to make sure the changes are tracked.

    you can then isolate if its the selectchanges that is failing because the where clause is filtering out the changed rows.

    the selectchanges may show zero but that doesnt mean the changes where not tracked, it only show they were not selected.

    Thursday, February 9, 2012 10:06 AM
    Moderator
  • The tables are tracked.  Just inserted a row into my documents table and a corresponding row is appeared in the document_tracking table.  This is all on the server.  I don't do any filtering at all.  My server provisioning code is below

                    using (SqlConnection serverConn = new SqlConnection(ConfigurationUtility.ServerConnectionString))
                    {
                        // define a new scope named AllTablesScope 
                        DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(ConfigurationUtility.ScopeName);
    
                        // get the description of the tables from the server database 
                        DbSyncTableDescription tableDesc1 = SqlSyncDescriptionBuilder.GetDescriptionForTable("xref_ActiveIngredient", serverConn);
                        DbSyncTableDescription tableDesc2 = SqlSyncDescriptionBuilder.GetDescriptionForTable("dbo.xref_Author", serverConn);
                        DbSyncTableDescription tableDesc3 = SqlSyncDescriptionBuilder.GetDescriptionForTable("xref_Category", serverConn);
                        DbSyncTableDescription tableDesc4 = SqlSyncDescriptionBuilder.GetDescriptionForTable("xref_Crop", serverConn);
                        DbSyncTableDescription tableDesc5 = SqlSyncDescriptionBuilder.GetDescriptionForTable("xref_FileFormat", serverConn);
                        DbSyncTableDescription tableDesc6 = SqlSyncDescriptionBuilder.GetDescriptionForTable("xref_Manufacturer", serverConn);
                        DbSyncTableDescription tableDesc7 = SqlSyncDescriptionBuilder.GetDescriptionForTable("xref_ProductType", serverConn);
                        DbSyncTableDescription tableDesc8 = SqlSyncDescriptionBuilder.GetDescriptionForTable("xref_Sector", serverConn);
                        DbSyncTableDescription tableDesc9 = SqlSyncDescriptionBuilder.GetDescriptionForTable("dbo.Document", serverConn);
                        DbSyncTableDescription tableDesc10 = SqlSyncDescriptionBuilder.GetDescriptionForTable("Document_crop", serverConn);
                        DbSyncTableDescription tableDesc11 = SqlSyncDescriptionBuilder.GetDescriptionForTable("Document_sector", serverConn);
                        DbSyncTableDescription tableDesc12 = SqlSyncDescriptionBuilder.GetDescriptionForTable("Document_activeIngredient", serverConn);
                        DbSyncTableDescription tableDesc13 = SqlSyncDescriptionBuilder.GetDescriptionForTable("Document_category", serverConn);
                        DbSyncTableDescription tableDesc14 = SqlSyncDescriptionBuilder.GetDescriptionForTable("Document_ProductType", serverConn);
    
                        // add the table description to the sync scope definition 
                        scopeDesc.Tables.Add(tableDesc1);
                        scopeDesc.Tables.Add(tableDesc2);
                        scopeDesc.Tables.Add(tableDesc3);
                        scopeDesc.Tables.Add(tableDesc4);
                        scopeDesc.Tables.Add(tableDesc5);
                        scopeDesc.Tables.Add(tableDesc6);
                        scopeDesc.Tables.Add(tableDesc7);
                        scopeDesc.Tables.Add(tableDesc8);
                        scopeDesc.Tables.Add(tableDesc9);
                        scopeDesc.Tables.Add(tableDesc10);
                        scopeDesc.Tables.Add(tableDesc11);
                        scopeDesc.Tables.Add(tableDesc12);
                        scopeDesc.Tables.Add(tableDesc13);
                        scopeDesc.Tables.Add(tableDesc14);
    
                        // create a server scope provisioning object based on the AllTablesScope 
                        SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
    
                        if (!serverProvision.ScopeExists(ConfigurationUtility.ScopeName))
                        {
                            // skipping the creation of table since table already exists on server 
                            serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
                            serverProvision.SetUseBulkProceduresDefault(true);
                            serverProvision.CommandTimeout = 180;
    
                            // start the provisioning process 
                            serverProvision.Apply();
                        }
                    }


    Hassan

    Thursday, February 9, 2012 10:33 AM
  • ok. try running profiler to capture the selectchanges sp and check the value being passed for the timestamp.

    out of curiousity, have you check if either the server database or client database was restored from backup?

    Thursday, February 9, 2012 10:48 AM
    Moderator
  • The client DB was restored from backup.  The server is not.  Are you thinking that we might need to run portrestorefix - I don't think we need that, though i am not sure.

    Captured select changes from profiler for the table I am ionterested in and here it is

    exec [dbo].[Document_selectchanges] @sync_min_timestamp=9223372036854775807,@sync_scope_local_id=1,@sync_scope_restore_count=0,@sync_update_peer_key=0

    How does this help me?

    Thanks June


    Hassan

    Thursday, February 9, 2012 10:57 AM
  • if you look inside the selectchanges sp, you will find that part of the condition is

    [side].[local_update_peer_timestamp] > @sync_min_timestamp

    so try to do a select on the tracking table using the above in the where clause and substituting the @sync_min_timestamp value that you have.

    Thursday, February 9, 2012 11:05 AM
    Moderator
  • Cheers June,

    Just struggling to pass that value in the where clause.  SQL complaining.  Will get back when I have a result  


    Hassan

    Thursday, February 9, 2012 11:16 AM
  • June,

    Can you figure this out?  The local_update_peer_timestamp is a timestamp data type but the selectchanges stored procedure takes in @sync_min_timestamp which is a Bigint!  How are they compared?  My query analyzer is not having it

    Cheers


    Hassan

    Thursday, February 9, 2012 11:58 AM
  • try this:

      declare @sync_min_timestamp BigInt
      set @sync_min_timestamp = 9223372036854775807
      select * FROM [yourtablehere_tracking]
      where local_update_peer_timestamp > @sync_min_timestamp

    btw, what's the sync direction for the scope you're having problem? is it the download only scenario we were talking about before?

    Thursday, February 9, 2012 12:13 PM
    Moderator
  • That worked June thanks.  It returns no rows.

    Yes direction is download only

    What would you look next?


    Hassan

    Thursday, February 9, 2012 12:19 PM
  • Hi June,

    Just noticed that all new rows inserted into the tracking table have a value of 1 for column sync_row_is_ tombstone.  As far as I am concerned that indicates a delete row, right? Those new rows in the tracking table also have a date for column last_change_datetime.

    Does that tell you anything?

    Cheers 


    Hassan

    Thursday, February 9, 2012 1:58 PM
  • yes, a value of 1 in the sync_row_is_tombstone indicates its deleted.

    have you checked the insert trigger? are you sure nothing has changed in your server?

    also, whats the value of a newly inserted row in the tracking table for local_create_peer_timestamp?

    is it less than the @sync_min_timestamp ?

    where was the client backup that was restored originally from? a backup of the db of the same client or somewhere else?

    if the client sync knowledge is already messed up, theres not much you can do about it than to reinitialize. the above investigations is just to understand what caused it and how you can prevent it from happening again.

    Thursday, February 9, 2012 2:10 PM
    Moderator
  • Not sure what I am looking for in the trigger?

    @sync_min_timestamp = 9223372036854775807
    ocal_create_peer_timestamp (for last record inserted is) = 1696777

    For client backup, I have synched server database to a client (provisioning server then  provisioning client then allowing sync to create client database tables) then I distributed this databse to be installed on all clients.  I hope that is not a problem.

    I am interested in knowing what messed this up and if it is messed up

    Really appreciate your assistance June.


    Hassan

    Thursday, February 9, 2012 2:46 PM
  • the insert trigger shouldnt be setting the row as tombstoned. so am not sure why your inserts are marked as such in the tracking table.

    secondly, the value of sync min timestamps is just too high. for newly inserted row, the value in the local create peer timestamp is actually the same as local_update_peer_timestamp

    so in your case, the selectchanges WHERE clause is actually 1696777 > 9223372036854775807

    which results in no rows being selected.

    if it was workign before, can you recall any database related activities done?

    Thursday, February 9, 2012 2:59 PM
    Moderator
  • I had SQL tracking enabled for the database and tables (I initially thought that Sync Framework uses automatic SQL tracking onkly to find that it created its own tracking tables).  Around the time the new rows are not being sent to client I disabled SQL change tracking.

    I'll see if I can remember any other activities ...

    For a new row I see
    local create peer timestamp = 1696777
    local_update_peer_timestamp = 0x000000000019E40D (1696781)

    Not sure what that means though

    I can't even think if the problem is server database or client database.

     


    Hassan

    Thursday, February 9, 2012 3:21 PM
  • That was my test server (I talked about above). 

    My live server has similar situation where local create peer timestamp is less than local_update_peer_timestamp by 2 or 3 and sync_row_is_tombstone = 0 for all rows

     


    Hassan

    Thursday, February 9, 2012 3:43 PM
  • Hi June,

    What would you do if you were in this situation? Is there anything that you are aware of that details the internal working of the sync framework? A book/documentation (not the samples that cover only easy and simple scenarios but detailed complex use of the framework)

    Do you know whether or not creating one client database via sync framework and restoring that to other clients will cause issues.  I did it and it seemed to work except that now it is all broken.  The biggest question is does the framework distinguish between clients? i.e does it compare anything from client other than @sync_min_timestamp?  I am try to think of any issues restoring one database to all clients will create.

    Is there anyone in Microsoft who has expertese in DB sync? Jesse hasn't participated this so maybe it is not his field.

    I am just blabbing and wishing I never used the Framework because I seem to run into problem after another but you and Jesse have both been awesome, thank you

     


    Hassan

    Thursday, February 9, 2012 5:35 PM
  • i just noticed your sync min value is actually the maximum value for a BigInt.

    can you compare the values in the scope_id column of the scope_info table for both local and server, are they the same?

    as i have mentioned in my other forum replies, each replica in the sync community has to have a unique id that distinguishes it from other replicas. that's why when restoring a backup of one client on another requires PerformPostRestoreFixup. in your scenario where it's download only, i'm thinking that shouldnt be an issue.

    are you sure you're not synching restored copies of the same database from the same backup?

    Friday, February 10, 2012 12:51 AM
    Moderator
  • Hi June,

    Yes, that is a big number which worries me!

    scope_id column of client and server are the same.

    Yes, I am synching restored copies.  This is what I did

    • In my dev machine - run code to do a first sync of my dev server.
    • Test that client by adding a new row to the database and synching - it worked
    • Backup sev server database and restore it on live DB server
    • Backup my dev machine database and restore it to all clients
    • Sync clients.  This seemed to have been working until last week - strange enough it doesn't even work on my dev server <-> dev client setup so it is not something client did but something inherintly wrong either in how I did it or the Framework 

    Hope you can get back to me.  There is huge some time difference - You are down under right? :-)

    Cheers


    Hassan

    Friday, February 10, 2012 8:21 AM
  • the scope Ids should have never been the same.

    if the scope Ids are the same, that means what you have on the client or the server is a restored copy from backup of the other.

    a PerformPostRestoreFixup should have changed this Ids

    its fine to sync download only for clients having the same id with a server with different id, but not for synching replicas having the same id.

    Friday, February 10, 2012 8:33 AM
    Moderator
  • Thanks for quick response June.

    I have never run a PerformPostRestoreFixup.  I will try that on my dev environment.  PerformPostRestoreFixup doesn't make clients that already synched to some stage have to synch the whole database all over again? 

    The restore DB could be my issue.  How do I best get initial database to all client without having to make them sync all data.  I want to sync one client and then install that database on every client then each client takes on from there to do its own sync.  I appreciate any advice you give me

    Cheers


    Hassan

    Friday, February 10, 2012 8:41 AM
  • as i have previously mentioned, these steps should work:

    1. provision server

    2. provision  client 1

    3. sync client 1

    4. back up client 1

    5. restore client 1 to client x

    6. performpostrestorefixup on client x

    7. sync client x

    • Marked as answer by HakkaBuster Friday, February 10, 2012 11:37 AM
    Friday, February 10, 2012 9:01 AM
    Moderator
  • Thank you June.

    I am only missing the performpostrestorefixup which I am adding right now.


    Hassan

    Friday, February 10, 2012 9:03 AM
  • note that you only need to do the performpostrestorefixup once after you restore and before the first sync.
    Friday, February 10, 2012 9:49 AM
    Moderator
  • Yep, got you June.

    Just to resolve the current issue rather than try to find out what has caused it, I run performpostrestorefixup on my test machine.  Tried to sycn it but it didn't get the new rows (I can see scope_ids are different now between client and server).  I then run the performpostrestorefixup on my dev server.  I can see several columns that were NULL in the tracking table on server now have value.  Tried to sycn again but it didn't get the new rows!

    Can you think of anything that I can try June.  I'd hate to have to reprovision server.

    Ta


    Hassan

    Friday, February 10, 2012 10:35 AM
  • can you try updating a row and see if it get synched.

    Friday, February 10, 2012 10:49 AM
    Moderator
  • Added a new row and it did indeed sync :-)

    So looks like I have to create a new client database from the current server database (following previous your steps) and release that to clients.  What is your opinion?


    Hassan

    Friday, February 10, 2012 11:08 AM
  • thats the best way to make sure your clients got the most recent copy from the server.

    Friday, February 10, 2012 11:17 AM
    Moderator
  • Thanks June.

    I'll do that.  I really appreciate your assistance


    Hassan

    Friday, February 10, 2012 11:35 AM
  • Hi June,

    I have performed the steps we discussed above, however, instead of using the server directly (we can't as it is hosted outside our environment), I copied the server's database onto our SQL server database of the same name (could not backup restore as client server is SQL 2008 R2 and ours is SQL Server 2008).  I then reprovisioned our server and synched one client.  I restored that client DB onto another client and run PostrestoreFixup then synched.  The sync (even though the DB has the same data as the server is synching the full server DB and is performing client inserts) I tried to run PostrestoreFixup on the client server database but it made no difference.

    Does this make sense and can you think of what I am doing wrong?  I am expecting the sync to finish is seconds as there is no data difference in the client and server

    Cheers
    H


    Hassan

    Tuesday, February 14, 2012 12:09 PM
  • can you clarify which server are you synching your client?

    you have hosted server db

    you copy hosted server db to on premise db

    you provision on premise db

    sync one client to on premise db

    back up client

    restore client to another client - client 2

    run perform post restore fixup on client 2

    sync client 2 to on premise db? or sync client 2 to hosted server db?

    is the hosted db provisioned before?


    Tuesday, February 14, 2012 12:22 PM
    Moderator
  • Hi June

    I synched client 2 first on promise DB and it worked fine as expected

    I then synched client 2 to hosted server db and this is my issue now.  It is getting all rows down to client although the data is already there

    Thanks


    Hassan

    Tuesday, February 14, 2012 12:27 PM
  • I am now thinking on restoring the on premise DB to the hosted server.  The client hosts their own DB and provided me with a laptop I can use to test app.  I could have reprovisoned (I have access to the client hosted DB server) but synching it from my location first time to create client DB will take many hours.  I tried before and gave up after 2 hours


    Hassan

    Tuesday, February 14, 2012 12:30 PM
  • you reprovisioned the copy you made on your on-premise db, this wiped out the sync knowledge that came with it when you copied from hosted db.

    your client knowledge is now based on synching with on-premise db and both on-premise db and your clients have no idea of the rows that are already in the hosted db.

    so when you sync, the hosted db and client has no idea of what each of them got. so they're sending each others records.

    yes, you can restore the on-premise db to the hosted one but that's provided they havent made changes since the last time you made a copy.

    when you made a copy of the hosted db to on premise db, you could have treated it similar to a restored copy and simply ran perform post restore fix up.

    Tuesday, February 14, 2012 12:36 PM
    Moderator
  • Thanks for claryifying this June.

    I tried to treat copied db as a restored copy but I didn't manage to copy the tracking tables only data. tracking tables were complaining and I decided then reprovision.

    I will now restore on premise db onto the hosted server.  No changes were made as I told them not to.  Thanks  again.


    Hassan

    Tuesday, February 14, 2012 2:04 PM