<< Change of venue for the Girl geek dinner | Home | Latest version of the OpennetCF is out now! >>

SQLite Merge Replication to SQL Server 2000/5

Just a quick question for anyone using SQLite. Is there any way of merge replicating the SQLite databases over to SQL Server? The reason I ask this odd question is that we already have a large quantity of data on an SQL Server and it is a rather complex database which could not run on SQLite. We have data in SQLite on a Windows CE platform that we wish to transfer to the SQL Server database system to populate multiple SQLite data... How can we do this? I did wonder if there was some clever way of doing this. At the moment it doesn't look as though the .netopennetframework will do it. I just wondered if anyone else knew of an implementation that could help me out.

posted @ Friday, August 05, 2005 3:24 PM

Print

Comments on this entry:

# re: SQLite Merge Replication to SQL Server 2000/5

Left by Peter Foot at 8/7/2005 10:19 AM
Gravatar
I don't believe there will be a direct route since merge replication is specific for SQL Server -> SQL Server or SQL Server -> SQL Server CE.
You can do snapshot (or transactional) replication to any ODBC data source. But this might mean either copying your sqllite databases to the server machine (ouch!) or finding another replication product which can go between SQLite and an ODBC desktop database...
Another solution would be to write your own code either using delimited text or XML or the SqlClient provider for .NETCF to move data between the server and device database, but in this case you'll have to define your rules yourself.
A completely different option would be to switch to SQL CE / SQL Mobile on the device - not only giving you direct merge replication but probably also additional features which SQLite doesn't offer. But perhaps there are other reasons for choosing SQLite as your device database...

Peter

# re: SQLite Merge Replication to SQL Server 2000/5

Left by Sarah at 8/7/2005 5:10 PM
Gravatar
Hi Peter,

Thanks for the comment. For some reason I didn't think this would be a nice simple thing to do. Life is never simple when it comes to multiple database systems.

On the note of your completely different option using SQL CE/ Mobile... We are using Windows CE 4.2 not 5.0 therefore it does not support the new version of SQL Server CE. The old 2.0 version of this used the underlying CEDB engine and crashes when run from a CF Card after 4.5 days, which is not reliable for our products, which is a shame really because that would have been the nice easy way round all this.

Ordinarily I am not an open source bunny. In fact I have never advocated the use of open source software until the problems that I had with the CEDB engine on Windows CE. To be honest with you I wouldn't be suprised if the new version of SQL Mobile also crashed after 4.5 days on a CF card running Windows CE 5.0 because the underlying way the system works has not been changed much in this area.

I will have a play with the database stuff when I get back from Gent, and hopefully I will have thought of something funky. I'm sure someone somewhere has come across the same problem and has found a solution.

# re: SQLite Merge Replication to SQL Server 2000/5

Left by Peter Foot at 8/8/2005 8:19 AM
Gravatar
SQL CE 2.0 doesn't use CEDB - you may be confusing it with Pocket Access which did use CEDB. To be honest I'd look into it further as the 4.5 day crashing seems unusual I know of a lot of "always-on" projects which are using Sql CE without encountering this.

One thing I would check is that the device has the latest version of the .NET Compact Framework running on it - the latest is .NETCF v1.0 Service Pack 3

Since the original release of .NETCF v1.0 there have been numerous fixes and performance increases around System.Data and the rest of the framework - this may cure your problem. There haven't been any updates to the SQL CE 2.0 engine itself.

# re: SQLite Merge Replication to SQL Server 2000/5

Left by Sarah at 8/8/2005 8:33 AM
Gravatar
Are those same projects running the CE OS from a CF card? The problem we have encountered with the 4.5 days has been on a bespoke device running off of a CF card.

We did a check of the latest version of the .net CF 1.0 and that was correct, and we put all the hotfixes on as well and it still faulted. We spent 2 months debugging this to find out which component was the culprit and were very disappointed by Microsoft's lack of support from both the development side and the OEM side. Eventually we had to make the critical decision of moving away from Microsoft supported databases to SQLite as it was the only thing that we could use that we could guaruntee would not fail as we had the source and did complete unit and system testing for it.

We have given up trying to use the in built database components as they don't seem to be giving us the levels of control over our system that we HAVE to maintain in class 2b devices (ISO 9001). They just made the system unpredictable and unstable.

Unfortunately by going in this direction we have to now create a way of syncronising these devices with the central servers... Oh what joy! ;) At least it gives me a fun challenge.

I guess if Microsoft want to learn from this then they should look into their developer support and OEM support links. To be fair I did get a response from Mike Hall (whom I contacted in last minute desperation to try and find out why it was doing this!) 3 weeks after contacting him, but by that time we had decided to go with SQLite.

# re: SQLite Merge Replication to SQL Server 2000/5

Left by Sarah at 8/8/2005 8:37 AM
Gravatar
We did look into SQL Server CE but it was mentionned on the Microsoft newsgroup that other people had been having problems with the database being corrupted when it was stored on a CF card during the shutdown of the systems. We decided implementing this was not worth the risk.

# re: SQLite Merge Replication to SQL Server 2000/5

Left by Peter Foot at 8/8/2005 9:11 AM
Gravatar
What was your pattern of access when using the database, did you maintain an open connection and periodically execute commands etc, or did you open and close the connection for each operation?
How frequently did you compact the database?
What type of device were you targetting? What power management settings did you have (behaviour of devices differ in this regard as some are more aggressive than others at powering down the SD card interface). What was the charging pattern for the device - back to a cradle every night?

Peter

# re: SQLite Merge Replication to SQL Server 2000/5

Left by Sarah at 8/8/2005 10:42 AM
Gravatar
Whenever an event was to be logged into the database the connection to the database was opened, the position of the last entry was read and the new entry then written. After each operation the connection was closed. Events occurred between once every 30 seconds to 15 minutes, average every 2.5 minutes.

We flush the database every 60 seconds with a wait between each cycle of 5 seconds. We don't compact the database, instead when it reaches maximum size we wrap it, so that we remove the oldest events.

The device that we are targetting is a MIPS II processor with 256mb CF Card. This is a headless device so no screen output. 2x ethernet ports, 1 x rs232 & 2X USB2. The platform running on it is Windows CE 4.2.

Power management is a low voltage power supply (LVPS) of 2.5 volts with a back-up battery attached if the voltage is below this.

The charging pattern of the device was that it was on mains when it crashed, and the battery always between 25% -90+%. There shouldn't have been any battery issues, and the batteries were charged in most cases. When we put a scope on it and left it running for the 4.5 days recording the power levels they were consistent and correct up until it failed, when there was just no power to the services board at all... It had literally switched everything off. There were no power surges or spikes and no dips in power either.

The last events in the database (which we use for logging what is happening to it!) showed normal activity and no shutdown events, but re-commenced fine and continued putting events into it. So you see it is an odd thing. The database wasn't even corrupt.

# re: SQLite Merge Replication to SQL Server 2000/5

Left by Sébastien Mouren at 8/11/2005 7:48 AM
Gravatar
In my previous company we developed several projects running with SQL Server CE 2.0. A lot of these projects store the data in NAND flash cards and none has encountered problems with this environment as long as you respect the targeted platform and its constraints (power management, size and place of the temporary database, the flash media limitation). The same is very true for any database products from Oracle Lite, SQL Lite, Sybase & co.

Nonetheless, if you wanna stick with SQL Lite you have different solutions depending on how you wan't to replicate your data:

You could write a synchronization agent (a process or a dll like for SQL Server CE) capable of interacting with the SQL Server agent hosted by IIS; the latter is an ISAPI module that output and input XML-like formatted flow of information. These interaction are not documented but they could be analyzed (be careful of legal implication).

You could write a RAPI module that acts as a proxy for SQL Server to query the snapshot and perform data merging. You would have to write its sibling to report changes in mobile database too.

You could also write a data merging module on the device that uses MS data access technologies (OLEDB or ADO.NET) to access SQL Server through a standard transport protocol. This module would perform the modification

You could do some export using DTS and a simple format (XML, CSV etc..) from your database and a merge on your device using SQL Lite inherent functions.

You could also use third party middleware layers as ViaDB.

These solutions balance differently the load generated by and usage possibilities of the replication. The choice really depends on your usage scenario and the interaction you envision with the server.

Regards,

Sébastien

# re: SQLite Merge Replication to SQL Server 2000/5

Left by Sébastien Mouren at 8/11/2005 7:56 AM
Gravatar
Just few complementary questions:
Is the device a NEC MobilePro?
What API were you using to access the datastore from your applications?

# re: SQLite Merge Replication to SQL Server 2000/5

Left by Sarah at 8/12/2005 11:24 AM
Gravatar
Sebastien,

The device in question is a bespoke device, not a mobile or NEC device at all. At the moment we are just writing the data to the device but we are going to be accessing it and placing it into a SQL Server DB at some point. So at the moment we aren't using any API, what we are trying to determine is the easiest way to do it without impacting on the 400+ devices already out there with out changing the way the basic database works.

We will not be changing back to a MS database on the device at this point. We will stick with SQLite and either write our own driver to SQL Server or as you suggested maybe use the OLEDB tools in C# to access the data and move it... We are also considering using a webservice to take the data from one source to another.

I'm sure we will find some way of doing this.

# re: SQLite Merge Replication to SQL Server 2000/5

Left by Ahmed Abd-ElHaffiez at 11/7/2005 5:53 AM
Gravatar
what about replication between SQLite and Oracle.
is it possible to replicate between those DB? and if so, what are the steps i have to follow to make it work?

# re: SQLite Merge Replication to SQL Server 2000/5

Left by Replication Guide at 3/26/2007 3:02 PM
Gravatar
Another option that no one has really commented on if you are looking to go to SQL Server is a product called UltraLite from (ianwwhere.com). i am pretty sure that it has all the features the SQLLite has and it is free (except for the sync which requires a license).

Your comment:



 (will not be displayed)


 
 
 
 
 

Live Comment Preview:

 
«November»
SunMonTueWedThuFriSat
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345






London Girl Geek Dinners



Locations of visitors to this page




Amazon Wishlist

About
Article Categories
Archives
Post Categories
Girl Geek Stuff...
Mobile Developer Centres
The Boys
The Girls!
Useful Information
Syndication:

RSS ATOM