Remove From My Forums

Sync error if the initial database is almost 2G.

Question
0

Sign in to vote

Hi,

We are implementing Sync Framework 2.1. During development we are able to sync the data smothly however when we tried to sync migrated data almost 2G, it keeps on having timeout error.

Please give us guidance for the correct approach(e.g. sync by batch, or initially create provisioned DB and do tweaking in the tracking tables) to resolve our problem.

Let me know if you need more details...

Thanks and Regards,

Arnel

Thursday, May 26, 2011 10:40 AM

Answers

0

Sign in to vote
assuming you're using SqlSyncProvider, you can try to set the CommandTimeoutProperty.

another approach is to break down your sync into multiple scopes, assuming you can partition the data.
- Marked as answer by Yunwen Bai Wednesday, June 1, 2011 3:09 PM
Thursday, May 26, 2011 10:49 AM

All replies

0

Sign in to vote
assuming you're using SqlSyncProvider, you can try to set the CommandTimeoutProperty.

another approach is to break down your sync into multiple scopes, assuming you can partition the data.
- Marked as answer by Yunwen Bai Wednesday, June 1, 2011 3:09 PM
Thursday, May 26, 2011 10:49 AM
0

Sign in to vote

JuneT has the right suggestions. can you also share out the stack so we can identify where the timeout was thrown? e.g. during select change or during apply changes at the destination?

thanks

yunwen
This posting is provided "AS IS" with no warranties, and confers no rights.

Thursday, May 26, 2011 5:21 PM
0

Sign in to vote

Hi Guys,

Thanks for the Immediate reply.

To answer your questions. Yes we are using SQLSyncProvider. And Here's the stack trace "<error errorId="648b5754-edcf-44d2-aa94-aa73d247334b" host="Windows_2008" type="System.ServiceModel.FaultException`1[[System.ServiceModel.ExceptionDetail, System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]" message="Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." source="mscorlib" detail="System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Fault Detail is equal to An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
 at RDOIT.RDO.RDMCGAS.Interfaces.RDOSyncService.RDORegisterSync(String serverName, String connString) in C:\WorkSpace\Development\Synchronization\RDO.Interfaces\Interfaces\RDOSyncService.cs:line 195
 at SyncInvokeRDORegisterSync(Object , Object[] , Object[] )
 at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
 at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
 at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
 at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc& rpc)
 at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage3(MessageRpc& rpc)
 at System.Serv...)." user="RDOMNL\aleliarda" time="2011-06-03T09:03:08.5209619Z">".

By the way what do you mean by multiple scopes and can you give a code snippet on how to do it? And also with regards to partitioning is this somewhat like selecting a records let's say top 100? or is there other way to partition the data?

Thanks and Regards,

Arnel

Friday, June 3, 2011 11:09 AM
0

Sign in to vote

it seems you have a timeout over the mid tier, you can consider to increase the timeout value in the webconfig to avoid it.

regarding with filter data and mutiple scopes with the providers, you can refer to bol at http://207.46.16.248/en-us/library/ff928701(SQL.110).aspx for the details and sample.

thanks

Yunwen
This posting is provided "AS IS" with no warranties, and confers no rights.

Friday, June 3, 2011 2:12 PM
0

Sign in to vote

Hi Yunwen,

Is it possible to redefine existing scope? Just as for example I've already created the scope with parameter based template and later decided to remove it with the new scope without the parameter based template. If yes, how? and what is the impact (e. g. re-provisioning) to the client db that previously sync.?

Thanks and Regards,

Arnel

Wednesday, June 8, 2011 7:27 AM
0

Sign in to vote

Hi Yunwen,

I have additional question. What will happened if I have Server1 -> Sync with Client1 and Server2 -> Sync with Client2 and eventually I decided to sync Server1 with Client2? Will sync. would still work for both Server1, Client2 and Client1.

Thanks and Regards,

Arnel

Wednesday, June 8, 2011 7:37 AM
0

Sign in to vote

to answer your questions:

1. you cant modify a scope definition. you can define an additional scope in your scenario without the filter. however, this new scope will no "knowledge" of what was already sync. if you go the route of deprovision/reprovision, you also lose all context of what was already synched. assuming you have bidirectional sync, that means you're client will try to upload its rows to the server and download rows from the server which would result to conflicts being raised since the rows are already existing.

2. assuming you have the same scope definition for all servers and clients, you should be able to sync them since the provider you're using supports peer to peer sync.

Wednesday, June 8, 2011 9:53 AM
0

Sign in to vote

Hi,

Thanks for the reply.

I have another question. Given that I have a performance concern during initial sync specially over the network, would there be no problem if I'm going to sync Server with Client in the same machine and later on backup this client db and restore it in a different machine and do the synching again?

Regards,

Arnel

Thursday, June 23, 2011 6:59 AM
0

Sign in to vote

if you use a backup to initialize a client, make sure you do a PerformPostRestoreFixup on it after you restore and before the first sync. just look up the docs on how to do this.

Sync framework has some kind of unique identifier for each replica, and if you just back up and restore the db, you will have clients having the same id and this may lead to problems with the clients not able to upload or download changes.

Thursday, June 23, 2011 7:09 AM
0

Sign in to vote

Hi,

Thanks again. With your help we were able to apply the filter data and mutiple scopes however as when the synchronization is running for around 6 hours the sql memory usage will reach the maximum usage and it will cause timeout. Is there a way to reinitialize the sql memory usage or limit not to reach maximum memory usage?

Thanks and Regards,

Arnel

Friday, June 24, 2011 3:35 AM
0

Sign in to vote

how big are the scopes you're synching? is this happening on an initial sync from a backup you restored? or is it from an initial sync of a completely newly provisioned client?

Sync framework cant throttle Sql Server settings but you can control how much changes you're sending to Sql Server.

for example, if you have a scope with a sales table that has 10 million rows and its rows has for example a State column, try to partition that table into multiple scopes based on State. so if you have 10 states, you have 10 scopes, 10 syncs.

assuming 1m rows per scope, you're only synching 1m rows at a time. if the 1m rows in a state gets updated, you only send 1m to sql server to update. whereas if you have 10m row scope and 10m rows were updated, you're sending 10m rows to sql server in a single transaction.

Friday, June 24, 2011 3:59 AM
0

Sign in to vote

It's actually from initial sync of a completely newly provisioned client. In one scope it's about 18k rows of 4m rows.

Friday, June 24, 2011 6:51 AM
0

Sign in to vote

if the server and client are both sql server, just provision the server, back up, restore to client, PerformPostRestoreFixup and sync

Friday, June 24, 2011 7:48 AM
0

Sign in to vote

Hi,

Thanks again. Yes both our server and client are sql. We tried this approach but seems nothing happened. As per my observation nothing changed in the tracking tables after the sync.

Here's the code snippet we used:
SqlConnection.ClearPool(serverConn);
serverConn = new SqlConnection(ConnStr_SqlSync_Server);
SqlSyncStoreRestore databaseRestore = new SqlSyncStoreRestore(serverConn);
databaseRestore.PerformPostRestoreFixup();

Please advise.

Thanks and Regards,

Arnel

Wednesday, June 29, 2011 10:08 PM
0

Sign in to vote

are you doing the performpostrestorefixup on the database that you restored?

Wednesday, June 29, 2011 11:31 PM
0

Sign in to vote

Yes.

Thursday, June 30, 2011 1:39 PM
0

Sign in to vote

Hi,

how can we verify the database if PerformPostRestoreFixup is executed successfully

Thanks and Regards,

Arnel

Thursday, June 30, 2011 8:21 PM
0

Sign in to vote

let's back track a bit, can you confirm this is the step you followed:

1. provision scope in server database

2. back up server database

3. restore backup in client

4. run performpostrestorefixup in client database

5. sync with server

can you enable tracing as well: http://msdn.microsoft.com/en-us/library/cc807160.aspx

Friday, July 1, 2011 3:17 PM
0

Sign in to vote

Yes we did step 1 to 5 and below are the code snippet.

1. provision scope in server database
   ---proxy.provisionScope();
if (!string.IsNullOrEmpty(template) && !string.IsNullOrEmpty(scope))
                {
                    sourceProvider = _helper.ConfigureSqlSyncProviderWithTemplate(WebConfig.SqlHostProvider, template, scope, true);
                    clientProvider = _helper.ConfigureSqlSyncProviderWithTemplate(provider, template, scope, true);
                }
                else
                {
                    sourceProvider = _helper.ConfigureSqlSyncProviderWithoutTemplate(WebConfig.SqlHostProvider, true);
                    clientProvider = _helper.ConfigureSqlSyncProviderWithoutTemplate(provider, true);
                }
                if (WebConfig.LogInformation)
                {
                    if (string.IsNullOrEmpty(template) && string.IsNullOrEmpty(scope))
                    {
                        _helper.eLog(_appName, string.Format("[{0}][{1}]: Provisioning successful.", provider, "Default Scope"), EventLogEntryType.Information);
                    }
                    else
                    {
                        _helper.eLog(_appName, string.Format("[{0}][{1}_scope_{2}]: Provisioning successful.", provider, template, scope), EventLogEntryType.Information);
                    }
                }
2. back up server database
   ---Backup database(Manually)
3. restore backup in client
   ---Restore database(Manually)
4. run performpostrestorefixup in client database
    ---proxy.PerformPostRestoreFixup();
               SqlConnection serverConn = ConfigureConnection(remoteProvider, false);
                SqlSyncStoreRestore databaseRestore = new SqlSyncStoreRestore(serverConn);
                databaseRestore.CommandTimeout = _cmdTimeout;
                databaseRestore.PerformPostRestoreFixup();

5. sync with server
    --proxy.SynchronizeProvider();
            localProvider.CommandTimeout = _cmdTimeout;
            remoteProvider.CommandTimeout = _cmdTimeout;

            SyncOrchestrator orchestrator = new SyncOrchestrator();
            orchestrator.LocalProvider = localProvider;
            orchestrator.RemoteProvider = remoteProvider;
            orchestrator.Direction = SyncDirectionOrder.UploadAndDownload;

            SyncOperationStatistics stats = orchestrator.Synchronize();

Monday, July 4, 2011 8:18 PM
0

Sign in to vote

By the way aside from this concern above I have this scenario(I wanted to partation the synchronization by Division related to Employee and Attendance table). Lets say I have the following tables Employee, Attendance and Division, I wanted to sync. Employee and Attendance table related to Division table having Division Name from "Div 1" to "Div 4" only. The relationship of the tables are Division table has the foreign key in Employee table and Employee table has the foreign key in Attendance table. Is this possible with parameter template? and how?

Thanks and Regards,

Arnel

Monday, July 4, 2011 9:07 PM
0

Sign in to vote

is your remoteprovider your client? looking at your code, the fixup is done on serverconn/remoteprovider.

to answer your other question, that should be possible.

check out the approach here: http://social.msdn.microsoft.com/Forums/en-US/syncdevdiscussions/thread/82235748-fac2-435f-8035-8d8809aeb82d

Tuesday, July 5, 2011 1:06 AM
0

Sign in to vote

Thanks for the reply.

Yes the remoteprovider is our client.

Tuesday, July 5, 2011 1:48 PM
0

Sign in to vote

Hi,

Base on the code snippet above for this scenario "The server and client are both sql server, provision the server, back up, restore to client, PerformPostRestoreFixup and sync", can you check if there's something missing? Or can you send me a working prototype or point me to where I can verify our code implementation?

Thanks and Regards,

Arnel

Wednesday, July 6, 2011 10:22 PM
0

Sign in to vote

can you verify that the scope_id in the scope_info table has changed?

Thursday, July 7, 2011 1:21 AM
0

Sign in to vote

Yes it changed.

Thursday, July 7, 2011 8:45 PM
0

Sign in to vote

PostRestoreFixup must have work then. you might want to enable tracing to get a detailed log of what's going on. when you get the log, zip it and send it to me at: junet at live dot com dot au

Friday, July 8, 2011 12:45 AM

Answered by:

Sync error if the initial database is almost 2G.

Question

Answers

All replies