August 19, 2008 at 10:31 am
Hello all,
I am in charge of upgrading a .NET CE application from running on SQL Server 2000 to SQL Server 2005. The code was originally started in VS .NET 2003 with Compact Framework 1.1, but I have upgraded all references to CF 2.0 and SQL CE 3.0.
I have had to recreate the mobile publication on SQL Server, I did so using the wizard, ensuring that it was for SQL Server 2005 Mobile, and have included all of the necessary tables. 2 tables are filtered using the SUSER_SNAME() function, and these then filter down further to other tables, joined via unique IDs. Aside from the type of publication, I believe that I have reproduced our desktop publication accurately.
Now, I use the following code inside a thread in .NET on a Pocket PC device in order to create a subscription and a database on the device:
Dim repl As SqlCeReplication = Nothing
repl = New SqlCeReplication
repl.InternetUrl = SyncSet.InternetURL
repl.InternetLogin = SyncSet.InternetID
repl.InternetPassword = Registry.Decrypt(SyncSet.InternetPassword)
repl.PublisherNetwork = Data.SqlServerCe.NetworkType.DefaultNetwork
repl.Publisher = SyncSet.Publisher
repl.PublisherDatabase = SyncSet.Database
repl.Publication = SyncSet.Publication
repl.PublisherSecurityMode = Data.SqlServerCe.SecurityType.DBAuthentication
repl.PublisherLogin = SyncSet.UserID
repl.PublisherPassword = Registry.Decrypt(SyncSet.Password)
strDataSource = MyConnection.DataSource
repl.SubscriberConnectionString = "Data Source=" & strDataSource & ";Password=password"
repl.Subscriber = SyncSet.Subscription
repl.HostName = repl.Subscriber
repl.AddSubscription(AddOption.CreateDatabase)
repl.Synchronize()
The SyncSet and Registry variables refer to internal structures holding various properties such as our database name, etc. I have highlighted the ones that I believe to be relevant in bold.
This only seems to access the server for about 1-2 mins, at most. The device is then taking over 5 hours to transfer approximately 36000 rows into approximately 75 tables on the database held in the Pocket PC device, some containing more data than others, but none more than 2-3K rows each. Before the upgrade, this same code would take approximately 30-40 minutes to transfer the same amount of data. I am at my wits' end trying to work out what has changed so drastically.
After various paths of investigation, it would seem that SQL Server 2005 Mobile now applies foreign keys, where it didn't seem to do so in the SQL Server 2000 mobile edition. Also, looking at the SQL Server 2005 snapshot files, there are now a whole lot of files with a PRC extension that didn't exist in the previous 2K version.
I have tried amending the article options, these have been set for all tables as follows (items in bold are ghosted, and cannot be edited). As many have been set to False as possible, any more seems to cause replication to fail after only a minute or two:
Copy primary key constraint: True
Copy foreign key constraints: False
Copy check constraints: False
Copy clustered index: True
Copy nonclustered indexes: True
Copy default value specifications: False
Copy user triggers: False
Copy extended properties: False
Copy collation: False
Copy unique key constraints: False
Destination object owner: dbo
Action if name is in use: Drop existing object and create a new one
Synchonization direction: Bidirectional
Partition options: Overlapping
Create schemas at Subscriber: True
Convert XML to NTEXT: False
Convert MAX data types to NTEXT and IMAGE: False
Tracking level: Row-level tracking
Verify INSERT permission: False
Verify UPDATE permission: False
Verify DELETE permission: False
Multicolumn UPDATE: True
Subscription options are as follows (items in bold are ghosted, and cannot be edited):
Report conflicts centrally: True
Allow anonymous subscriptions: True
Attachable subscription database: False
Allow pull subscriptions: True
Allow Parameterized filters: True
Validate Subscribers: SUSER_SNAME()
Precompute partitions: True
Optimize synchronization: False
Limit concurrent processes: False
Maximum concurrent processes: 0
Replicate schema changes: False
Has anybody had any similar experiences to these problems? I would dearly love to know if this is a documented bug (have not found any evidence so far), or of any workarounds that will improve the performance.
Kind regards,
Gary
October 27, 2008 at 5:33 am
Hello,
I have the same problem. The difference is that I can select some of the "bold" items, for example "optimize...", but it doesn't help.
I see you question is a while ago, did you already found a solution?
Thanks in advance,
Nino
October 27, 2008 at 8:35 am
Hi,
While I didn't find a solution to the problem, what I did manage to do was improve on the speed in a way. I found that by forcing replication to download the tables with the most data first, by using the @processing_order parameter of the sp_addmergearticle SP.
I can only assume that there is a mobile replication issue with memory when processing table data, as I noticed that the tables that took the longest as part of the complete replication process, would take mere seconds if I amended the replication to only process that one troublesome table.
Or, perhaps the "not for replication" flag is ignored by replication, but some kind of basic foreign-key checking is still taking place on each insert.
I also managed to work out that the full load of data was transferred from the server to the device in under 5 seconds, yet it would then spend literally hours inserting those rows into the tables. Very, very bizarre.
If anybody else can give an explanation, or even share their views, I would really appreciate it. We haven't gone live yet, but this really hasn't filled me with confidence.
August 20, 2009 at 2:12 am
Morning
I am experiencing the same issue , have you manage to find the problem ?..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply