locked
console application to sync sql express to sql azure system.outofmemoryexception RRS feed

  • Question

  • A couple of months ago I was able to sync a local sql express table of about 200,000 rows (200mb) to sql azure just fine using a console application that uses microsoft sync framework 2.1.

    I let that development machine sit for the last couple of months and then I deleted the rows from the sql azure table and tried to run the sync console application again. After 8 hours it finally threw a system.outofmemoryexception. I tried again and 8 hours later it threw the same error again.

    I don't know why this worked a couple months ago and doesn't now. Could it be something going wrong with indexes? I put the data up a couple months ago on azure and then I created indexes. Now that I deleted the rows in the azure table, it will not sync. Also, a couple months ago it would sync the 200,000 rows very quickly, like 40 minutes. Now it goes for 8+hours and throws the memory exception.

    my code:

    Imports System
    Imports System.Collections.Generic
    Imports System.Linq
    Imports System.Text
    Imports System.Data.SqlClient
    Imports Microsoft.Synchronization.Data.SqlServer
    Imports Microsoft.Synchronization.Data
    Imports Microsoft.Synchronization
    Module sync_to_azure
    'code below from: http://blogs.msdn.com/b/sync/archive/2010/09/24/how-to-sync-large-sql-server-databases-to-sql-azure.aspx
    '
    'instructions on downloading sql azure SDK 2.1 to get dlls used. (note, you can only install this on vista or above)
    '---http://social.technet.microsoft.com/wiki/contents/articles/sql-server-to-sql-azure-synchronization.aspx
    'Microsoft.Synchronization.dll
    'Found in: C:\Program Files\Microsoft Sync Framework\2.1\Runtime\x86 and the Synchronization Framework providers:
    'Microsoft.Synchronization.Data.dll
    'Microsoft.Synchronization.Data.Server.dll
    'Microsoft.Synchronization.Data.SqlServer.dll
    'Found in: C:\Program Files\Microsoft Sync Framework\2.1\Runtime\ADO.NET\V3.1\x86
    Public Function Setup()
    Try
    Dim scopeName As String = "AllTablesSyncGroup"
    Dim sqlServerConn As New SqlConnection(My.Settings.localSQL_Con_string)
    Dim sqlAzureConn As New SqlConnection(My.Settings.AzureDB)
    Dim RemoteProvider As New SqlSyncProvider(scopeName, sqlAzureConn)
    Dim LocalProvider As New SqlSyncProvider(scopeName, sqlServerConn)
    Dim myScope As New DbSyncScopeDescription(scopeName)
    Dim mytablename As DbSyncTableDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable("mytablename", sqlServerConn)
    myScope.Tables.Add(mytablename)
    ' Setup SQL Server for sync
    Dim sqlServerProv As New SqlSyncScopeProvisioning(sqlServerConn, myScope)
    sqlServerProv.CommandTimeout = 60 * 30
    If Not sqlServerProv.ScopeExists(scopeName) Then
    ' Apply the scope provisioning.
    Console.WriteLine("Provisioning SQL Server for sync " + DateTime.Now)
    sqlServerProv.Apply()
    Console.WriteLine("Done Provisioning SQL Server for sync " + DateTime.Now)
    Else
    Console.WriteLine("SQL Server Database server already provisioned for sync " + DateTime.Now)
    End If
    ' Setup SQL Azure for sync
    Dim sqlAzureProv As New SqlSyncScopeProvisioning(sqlAzureConn, myScope)
    If Not sqlAzureProv.ScopeExists(scopeName) Then
    ' Apply the scope provisioning.
    Console.WriteLine("Provisioning SQL Azure for sync " + DateTime.Now)
    sqlAzureProv.Apply()
    Console.WriteLine("Done Provisioning SQL Azure for sync " + DateTime.Now)
    Else
    Console.WriteLine("SQL Azure Database server already provisioned for sync " + DateTime.Now)
    End If
    sqlAzureConn.Close()
    sqlServerConn.Close()

    Catch ex As Exception
    End Try
    Return ""
    End Function

    Public Function Sync()
    Try
    Dim scopeName As String = "AllTablesSyncGroup"
    Dim BatchSize As UInteger = 50000
    Dim MemorySize As UInteger = 100000
    Dim sqlServerConn As New SqlConnection(My.Settings.localSQL_Con_string)
    Dim sqlAzureConn As New SqlConnection(My.Settings.AzureDB)
    Dim RemoteProvider As New SqlSyncProvider(scopeName, sqlAzureConn)
    Dim LocalProvider As New SqlSyncProvider(scopeName, sqlServerConn)
    Dim myScope As New DbSyncScopeDescription(scopeName)
    Dim mytablename As DbSyncTableDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable("mytablename", sqlServerConn)
    myScope.Tables.Add(mytablename)
    'Set memory allocation to the database providers
    RemoteProvider.MemoryDataCacheSize = MemorySize
    LocalProvider.MemoryDataCacheSize = MemorySize
    'Set application transaction size on destination provider.
    RemoteProvider.ApplicationTransactionSize = BatchSize
    Dim orch As New SyncOrchestrator()
    orch.RemoteProvider = RemoteProvider
    orch.LocalProvider = LocalProvider
    orch.Direction = SyncDirectionOrder.UploadAndDownload
    Console.WriteLine("ScopeName={0} ", scopeName.ToUpper())
    Console.WriteLine("Starting Sync " + DateTime.Now)
    ShowStatistics(orch.Synchronize())
    sqlAzureConn.Close()
    sqlServerConn.Close()
    Catch ex As Exception
    Console.WriteLine(ex)
    Stop
    End Try
    Return ""
    Console.WriteLine(DateTime.Now)
    Stop
    End Function


    Public Sub ShowStatistics(ByVal syncStats As SyncOperationStatistics)
    Dim message As String
    message = vbTab & "Sync Start Time :" + syncStats.SyncStartTime.ToString()
    Console.WriteLine(message)
    message = vbTab & "Sync End Time :" + syncStats.SyncEndTime.ToString()
    Console.WriteLine(message)
    message = vbTab & "Upload Changes Applied :" + syncStats.UploadChangesApplied.ToString()
    Console.WriteLine(message)
    message = vbTab & "Upload Changes Failed :" + syncStats.UploadChangesFailed.ToString()
    Console.WriteLine(message)
    message = vbTab & "Upload Changes Total :" + syncStats.UploadChangesTotal.ToString()
    Console.WriteLine(message)
    message = vbTab & "Download Changes Applied :" + syncStats.DownloadChangesApplied.ToString()
    Console.WriteLine(message)
    message = vbTab & "Download Changes Failed :" + syncStats.DownloadChangesFailed.ToString()
    Console.WriteLine(message)
    message = vbTab & "Download Changes Total :" + syncStats.DownloadChangesTotal.ToString()
    Console.WriteLine(message)
    End Sub
    End Module

    Saturday, December 11, 2010 8:33 AM

All replies

  • have you checked if its firing up conflicts/errors on the ApplyChangesFailed event?
    Monday, December 13, 2010 3:29 PM