Very slow merge replication between SQL Server 2008 R2 and SQLCE 3.5 PDA clients
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
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
Ramesh Babu Vavilla
sp_updatestats
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?
ASKER
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.
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.
ASKER
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?
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?
ASKER
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\
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
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"
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
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\
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
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"
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
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I found the solution to my problem myself.