?
Solved

Very slow merge replication between SQL Server 2008 R2 and SQLCE 3.5 PDA clients

Posted on 2012-09-02
8
Medium Priority
?
1,797 Views
Last Modified: 2013-12-27
Hello,

I have a Merge Replication infrastructure consisting of two servers and various Windows Mobile clients all running SQLCE 3.5 SP2:  

1. an application server running IIS and SQL Server Compact Server Tools (64bit), and
2. a database server running Microsoft SQL Server 2008 R2 Standard Edition

Both servers run Windows 2008 R2 Standard Edition (64bit).

The database that I need to replicate to the Windows Mobile clients is quite small, aroung 21 mb, however it consistently takes around 1.5 hours to complete a full data synchronization between a Windows Mobile client and the app server running IIS 7.5.

My previous environment (SQL 2000 on Windows 2003 and SQLCE 2.0) only took around 5-6 minutes to fully synchronize the database, so I am at a loss as to what is going on...

The only thing different from my previous environment is that I had ONE server running both IIS and SQL Server, whereas now I have two servers, one application server running IIS 7.5 and the SQL Compact Server Tools (64bit) and another database server running only Microsoft SQL Server 2008 R2 Standard Edition.

Can you please help find out what is going on?
Thank you
0
Comment
Question by:devshed
  • 5
  • 2
8 Comments
 
LVL 10

Expert Comment

by:Ramesh Babu Vavilla
ID: 38360107
sp_updatestats
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38375317
Is the initial snapshot slow as well, or do snapshots replicate in a reasonable amount of time? If they're quick but ongoing replication is slow, it may be something related to the indexes or data layout, as the other expert suggests. If the initial replication snapshot is slow to apply, I'd look more at the connection you have - is the internet access slow? How quickly can you get another file from that server to your device over the cell/wifi network?
0
 

Author Comment

by:devshed
ID: 38380453
sqlservr: do I have to execute that on a recurring basis on my sql2k8 box, or it is a one time fix?

Please note that I never had such issues with the exact same database on my sql2k box, where I never executed this command.

I shall try your suggestion on Monday and get back to you.

ryanmccauley: no, the initial snapshot on the server side gets created quite fast; it is a small db, anyway. However, any kind of full replication op between server and WM PDA clients gets always 1,5 hour to complete.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:devshed
ID: 38395067
Hello,

The sp_updatestats operation did not help. It still takes 1,5 hour to fully synchronize a WM client running SQLCE 3.5 with a SQL 2008 R2 backend.

Also, I tested the download speed of a file about 7mb to my PDA and it takes about 2 minutes.

Can you help?
0
 

Author Comment

by:devshed
ID: 38395196
Please find below the contents of my merge replication log file. You will notice that the time it takes for the 64kb to get downloaded to the WM client increases with every download. So, initially it takes about 7 seconds to download the first few 64kb batches and then the time increases until it needs about 2 minutes to download the same 64kb.

Can you help ??

2012/09/13 16:59:39 == SQLCESA Version ==> 3.5.8080.0
2012/09/13 16:59:39 Hr=80070585 ERR:Failure reading Transport version header 0
2012/09/13 16:59:39 Hr=80070585 ERR:REQUEST NOT QUEUED for ulRSCBId =  -1
2012/09/13 17:09:31 Hr=00000000 Compression Level set to  3
2012/09/13 17:09:31 Hr=00000000 Count of active RSCBs =  0
2012/09/13 17:09:31 Thread=96C RSCB=1 Command=OPWC Hr=00000000 Total Compressed bytes in =  206
2012/09/13 17:09:31 Thread=96C RSCB=1 Command=OPWC Hr=00000000 Total Uncompressed bytes in =  427
2012/09/13 17:09:31 Thread=96C RSCB=1 Command=OPWC Hr=00000000 Responding to OpenWrite, total bytes =  206
2012/09/13 17:09:31 Thread=96C RSCB=1 Command=OPWC Hr=00000000 E:\KMobile\KMobileSQLCEMR\30.13D7F0BDD522_D4C3B1FC-9267-A694-1580-B8FA9C1E08E0 0
2012/09/13 17:09:31 Thread=704 RSCB=1 Command=SYNC Hr=00000000 Synchronize prepped 0
2012/09/13 17:09:31 Thread=704 RSCB=1 Command=SYNC Hr=00000000 10.0 server found. Trying 10.0 error components... 0
2012/09/13 17:09:31 Thread=704 RSCB=1 Command=SYNC Hr=00000000 Could instantiate 10.0 error components 0
2012/09/13 17:09:31 Thread=704 RSCB=1 Command=SYNC Hr=00000000 10.0 server found. Trying 10.0 reconciler components... 0
2012/09/13 17:09:31 Thread=704 RSCB=1 Command=SYNC Hr=00000000 Could instantiate 10.0 replication components 0
<PARAMS RSCB="1" HostName="" Publisher="K-DB" PublisherNetwork="" PublisherAddress="" PublisherSecurityMode="1" PublisherLogin="" PublisherDatabase="KMobile" Publication="KMobileCE" ProfileName="DEFAULT" SubscriberServer="216 - 221242b6-b1d8-4c6d-94a8-4ff7a5d34257 - 13d7f0bdd522" SubscriberDatabasePath="SD Card\KMobile\DATA\KMOBILE.sdf" Distributor="K-DB" DistributorNetwork="" DistributorAddress="" DistributorSecurityMode="1" DistributorLogin="" ExchangeType="3" ValidationType="0" QueryTimeout="300" LoginTimeout="15" SnapshotTransferType="0" DistributorSessionId="45"/>
2012/09/13 17:09:35 Thread=704 RSCB=1 Command=SCHK Hr=00000000 SyncCheck responding 0
2012/09/13 17:09:35 Thread=FAC RSCB=1 Command=OPNR Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:09:41 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65522
2012/09/13 17:09:46 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:09:52 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:09:58 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:10:04 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:10:10 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:10:22 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:10:37 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:10:53 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:11:05 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:11:36 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:11:54 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:12:19 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65522
2012/09/13 17:12:41 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:13:08 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:13:36 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:14:16 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
<STATS Period_Start="2012/09/13 16:59:44" Period_Duration="900" Syncs="1" SubmitSQLs="0" RDAPushes="0" RDAPulls="0" AVG_IN_File_Size="427" AVG_OUT_File_Size="0" Completed_Operations="0" Incomplete_Operations="0" Total_Sync_Thread_Time="3" Total_Transfer_Thread_Time_IN="0" Total_Transfer_Thread_Time_OUT="0" Total_Sync_Queue_Time="0" Total_Transfer_Queue_Time_IN="0" Total_Transfer_Queue_Time_OUT="0" />
2012/09/13 17:14:52 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:15:31 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:16:00 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:16:29 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:16:58 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:17:26 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:18:09 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:18:51 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:19:33 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:20:15 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65522
2012/09/13 17:21:13 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65522
2012/09/13 17:22:02 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:22:49 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65522
2012/09/13 17:23:43 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65522
2012/09/13 17:24:45 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:25:30 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:25:44 Hr=80070585 ERR:Failure reading Transport version header 0
2012/09/13 17:25:44 Hr=80070585 ERR:REQUEST NOT QUEUED for ulRSCBId =  -1
2012/09/13 17:26:51 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:28:06 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:28:56 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:30:06 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:31:20 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65522
2012/09/13 17:32:56 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:34:20 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
2012/09/13 17:35:51 Thread=FAC RSCB=1 Command=FTCH Hr=00000000 Responding to Fetch, bytes =  65520
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38396147
That's obviously the problem, but I've honestly never seen anything like that before. Some questions:

1. What's the network connection with the PDAs look like? Are they cell-based and remote, wifi-based in the office, or do they sync when docked in their cradle (using the host PC network)? I'm wondering if the problem is related to their network connection.

2. Can you set up the same type of replication to a desktop client on your physical network - the speed at which that happens would confirm that it's not network- or server-configuration related, and would isolate the problem to something that happens just on the PDAs. Ideally, you'd use SQLCE on your desktop so that you're replicating the PDA exactly.

3. What do ping times look like against the devices while they're synchronizing? Is there any indication that their bandwidth is being saturated or that they're unresponsive?

I'm grasping at straws here, as there's nothing obvious, but hopefully it sheds some light on what's going on here.
0
 

Accepted Solution

by:
devshed earned 0 total points
ID: 38398625
EUREKA !!!

http://blogs.msdn.com/b/sqlblog/archive/2009/06/24/performance-tips-for-faster-download-of-a-large-sql-compact-3-5-database-via-replication.aspx

There seems to be an issue with any SQLCE version after SQLCE 2.0 in which during a full data sync, the pda client progressively requires more and more time to download the same size data chunk sent by the server.

I solved my problem by following the advice of the article and created an application that creates the SQLCE database (& compresses it) on the server once every night.

Then, I changed the logic in my PDA application so that for full data sync scenarios, I download the compressed file via FTP, decompress it and apply a dummy/empty data sync to the main server to update the metadata held in the downloaded copy of the SDF file.

That's all folks!

PS. It is very strange that such process was quite fast in SQLCE 2.0 but so horribly slow on subsequent versions of the platform.
0
 

Author Closing Comment

by:devshed
ID: 38412885
I found the solution to my problem myself.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Once again I push the limits of my phone.  An introduction to the Android Google Now Launcher.
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month14 days, 7 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question