Answered by:
Sync data between two databases with existing scope not working

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
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 -
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