none
Sync data between two databases with existing scope not working RRS feed

  • Question

  • I'm new to Microsoft Sync Framework 2.1. Appreciate to anyone who read my question. This is my scenario. I want to sync data from database server (sql 2008 r2) to my local sql2008 r2. When first time running the program, the sync framework successfully sync the data (dbo.department) from server to my local db (dbo.department) with successfully create necessary scope and tracking table in both databases. After this, i delete all the data from my local db and try to re-run the program again. However, in this time although the program finish running without giving me any error, but when i check on my local db (dbo.department), it does not contain the any data as what i can see in server database (dbo.department). I try to enable my

    DeProvisionServerClient(scopeName) in my program and re-run it, once again it successfully sync the data between both databases. May I know what wrong with my code ? seem like it does not using my existing scope which created in both server and local previously. many thanks

     

     Below is my program:

    ImportsSystem.Data.SqlClient
    Imports Microsoft.Synchronization
    Imports Microsoft.Synchronization.Data
    Imports Microsoft.Synchronization.Data.SqlServer
    
    Module Module1
      ' server conn string
      Dim MainDataSource As String = "Data Source=server;Initial Catalog=DstClient;User ID=sa;Password=password"
      ' local database conn string
      Dim LocalDataSource As String = "Data Source=localhost\sql2008r2;Initial Catalog=DstClient;User  ID=sa;Password=password"
      Dim strTableName As String = "Department"
     
      Sub Main()
       SyncSqlTable("scope_" & strTableName)
      End Sub
    
      Private Sub SyncSqlTable(ByVal scopeName As String)
       Try
          'DeProvisionServerClient(scopeName)
          ProvisionServer(scopeName)
          ProvisionClient(scopeName)
          ExecuteSyncData(scopeName, "download")
     
       Catch ex As Exception
       End Try
      End Sub
    
    
      Private Sub ProvisionServer(ByVal _scopeName As String)
      ' connect to server database
       Dim serverConn As New SqlConnection(MainDataSource)
       Try
         serverConn.Open()     
         Dim serverProvision As New SqlSyncScopeProvisioning(serverConn)
         ' define a new scope name
         Dim scopeDesc As New DbSyncScopeDescription(_scopeName)
         ' get the description of a table from server database
         Dim tableDesc As DbSyncTableDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable(strTableName, serverConn)
         ' check available scope in main server
         If Not serverProvision.ScopeExists(_scopeName) Then
           ' add the table description to the sync scope definition
           scopeDesc.Tables.Add(tableDesc)
           ' create a server scope provisioning object based on the scope
            serverProvision = New SqlSyncScopeProvisioning(serverConn, scopeDesc)
           ' skipping the creation of table since table already exists on server
           serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip)
           ' start the provisioning process
           serverProvision.Apply()
         End If
       Catch ex As Exception
       End Try
       serverConn.Close()
      End Sub
    
    
      Private Sub ProvisionClient(ByVal _scopeName As String)
       ' create a connection to the local database
       Dim clientConn As New SqlConnection(LocalDataSource)
       ' create a connection to the main server database
       Dim serverConn As New SqlConnection(MainDataSource)
       Try
         clientConn.Open()
         Dim clientProvision As New SqlSyncScopeProvisioning(clientConn)
         ' get the scope description which have been created in server 
         Dim scopeDesc As DbSyncScopeDescription = SqlSyncDescriptionBuilder.GetDescriptionForScope(_scopeName, serverConn)
         ' create server provisioning object based on the scope
         clientProvision = New SqlSyncScopeProvisioning(clientConn, scopeDesc)
         ' check available scope in local database
         If Not clientProvision.ScopeExists(_scopeName) Then
           ' starts the provisioning process
           clientProvision.Apply()
         End If
    
         Catch ex As Exception
    
         End Try
         serverConn.Close()
         clientConn.Close()
      End Sub
    
    
      Private Sub ExecuteSyncData(ByVal _scopeName As String, ByVal _direction As String)
        ' create a connection to the local database
        Dim clientConn As New SqlConnection(LocalDataSource)
        ' create a connection to the main server database
        Dim serverConn As New SqlConnection(MainDataSource)
        Try
          ' create the sync orhcestrator
          Dim syncOrchestrator As New SyncOrchestrator()
          ' set local provider of orchestrator to a sync provider associated with the 
          ' scope in the client database
          syncOrchestrator.LocalProvider = New SqlSyncProvider(_scopeName, clientConn)
          ' set the remote provider of orchestrator to a server sync provider associated with
          ' the scope in the main server database
          syncOrchestrator.RemoteProvider = New SqlSyncProvider(_scopeName, serverConn)
          ' set the direction of sync session
          Select Case _direction
            Case ("download")
              syncOrchestrator.Direction = SyncDirectionOrder.Download
            Case ("upload")
              syncOrchestrator.Direction = SyncDirectionOrder.Upload
          End Select
        ' execute the synchronization process
        Dim syncStats As SyncOperationStatistics = syncOrchestrator.Synchronize()
        Console.WriteLine("Start Time: " + syncStats.SyncStartTime.ToString)
        Console.WriteLine("Total Changes Uploaded: " + syncStats.UploadChangesTotal.ToString)
        Console.WriteLine("Total Changes Downloaded: " + syncStats.DownloadChangesTotal.ToString)
        Console.WriteLine("Complete Time: " + syncStats.SyncEndTime.ToString)
        Console.Read()
        Catch ex As Exception
        End Try
        serverConn.Close()
        serverConn.Dispose()
        clientConn.Close()
        clientConn.Dispose()
      End Sub
    
    
      Private Sub DeProvisionServerClient(ByVal _scopeName As String)
        ' create a connection to the local database
        Dim clientConn As New SqlConnection(LocalDataSource)
        ' create a connection to the main server database
        Dim serverConn As New SqlConnection(MainDataSource)
        Try
           serverConn.Open()
           clientConn.Open()
           Dim serverDeProvision As New SqlSyncScopeDeprovisioning(serverConn)
           Dim clientDeProvision As New SqlSyncScopeDeprovisioning(clientConn)
           ' Remove the scope from server db
           serverDeProvision.DeprovisionScope(_scopeName)
           ' Remove all scopes from the server db
           serverDeProvision.DeprovisionStore()
           ' Remove the scope from client db
           clientDeProvision.DeprovisionScope(_scopeName)
           ' Remove all scopes from the client db
          clientDeProvision.DeprovisionStore()
        Catch ex As Exception
        End Try
        serverConn.Close()
        serverConn.Dispose()
        clientConn.Close()
        clientConn.Dispose()
      End Sub
    End Module
    

     

     

    Friday, April 1, 2011 7:48 AM

Answers

  • Sync Fx will not resend data that you have deleted on your client. As far as your client is concerned, it has received those rows from the server already and if there are no changes on those rows in the server, they will not be selected either.

    If you want the client to download the rows again, you can do a dummy update on the server rows so their timestamps are incremented (e.g., update tablex set col1=col1) and so they can be detected as changed.

    however, in your case, if you do dummy update on the server and initiate a download sync, you will most likely get a conflict between the update in the server and delete on the local copy,  so you have to handle that as well (subscribe to ApplyChangeFailed and set RetryWithForceWrite).

    • Marked as answer by KokYeng Friday, April 1, 2011 8:54 AM
    Friday, April 1, 2011 8:13 AM
    Moderator

All replies

  • Sync Fx will not resend data that you have deleted on your client. As far as your client is concerned, it has received those rows from the server already and if there are no changes on those rows in the server, they will not be selected either.

    If you want the client to download the rows again, you can do a dummy update on the server rows so their timestamps are incremented (e.g., update tablex set col1=col1) and so they can be detected as changed.

    however, in your case, if you do dummy update on the server and initiate a download sync, you will most likely get a conflict between the update in the server and delete on the local copy,  so you have to handle that as well (subscribe to ApplyChangeFailed and set RetryWithForceWrite).

    • Marked as answer by KokYeng Friday, April 1, 2011 8:54 AM
    Friday, April 1, 2011 8:13 AM
    Moderator
  • you are right. If I make changes to my record in server, my client will get new record update from sync. If i add new record to my server, my client will also get the new record. If i delete record in server, my record in client will also get deleted. Only when i delete record in client after synchronization process and re-run the sync process, my client won't get any update. many thanks for your explanation.
    Friday, April 1, 2011 8:58 AM