Sync data between two databases with existing scope not working

• Question

• 

  

  0

  Sign in to vote

  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