Re: About Design Requirements for Replication across different DBMS's...

From: Hilary Cotter (hilaryk_at_att.net)
Date: 04/02/04 

Date: Fri, 2 Apr 2004 08:34:48 -0500

you can do snapshot and transactional replication to mysql through an odbc
datasource.

SQLCE only supports merge replication.

Are you trying to replicate from SQLCE to MySQL using merge? This isn't
supported by Microsoft.

Not only do you have connection problems but other problems with database
consistency. You replicate to SQLCE using an isapi extension. Your going to
have to write something in Visual C++ to connect the two database.

Addressing your sync statement.

You can use identity columns instead of GUIDs, MySQL does support identity.

There are a lot of problems with you approach. The main problem is how do
you handle conflicts? You are only replicating new records, but what about
updqated records or inserted records?

Suppose you delete a record on the CE device, then you read records on
MySQL, the deleted record is still on MySQL so its sent back to the SQL CE
device.

I think you are trying to roll your own merge replication. Good luck.

"Sky" <forums@xact-solutions.removethis.com> wrote in message
news:ehtSzCKGEHA.2876@TK2MSFTNGP09.phx.gbl...
> Hello:
>
> I've been musing about the requirements of making a CE port of an
> application I wrote so that the data (a classic tasks/project set of
tables)
> can be carried on a CE.
>
> My questions are... I know that CE and MSDE have a replication feature,
but
> what about replicating to other database systems (MySQL in this case).
>
> I've been trying to think of what needs to be modified in the schema
design
> to allow this to work correctly.
>
> My first obversations are that
> a) Autoincrement ID columns are probably a dead-end as they will get out
of
> sequence if they are generated on both the CE and the desktop. Therefore a
> GUID Primary Key column seems to be more in order.
> b) The tables all need some form of LastEdited datetime column
> c) I believe that in NET you can connect to two different providers and
the
> following would work (real rough proxycode -- I never can write INNER JOIN
> statements on the fly without trial and error, but here goes):
>
> Step 1: Get New records on Server:
> SELECT ServerConnection.MyTable.GUID
> FROM ServerConn.MyTable,
> ServerConn.MyTable.GUID
> INNER JOIN
> ON CEConn.MyTable.GUID
> WHERE CEConn.MyTable.GUID=NULL
>
> These we write to the CE client.
>
> Step 2: Reverse Step 1 to write any NEW records on CE to Server
>
> Step 3: Then get any records on server that were edited later than the
CE's:
>
> SELECT ServerConnection.MyTable.GUID
> FROM ServerConn.MyTable, CEConn.MyTable
> WHERE ServerConn.MyTable.GUID=CEConn.MyTable.GUID
> AND (ServerConn.MyTable.LastEdited > CEConn.MyTable.LastEdited)
>
> Step 4: Reverse the process to write any new records from the CE to the
> server.
>
>
> I think that about covers it...Any changes made on the CE done at a
earlier
> date than changes also done on the server are crushed by the Server who
has
> higher priority...
>
>
>
> What I would like to know is whether anybody has dealt with this kind of
> mess before and can give pointers on how to do it.
> For example -- does anybody have a fair idea of how Palm Pilot sychronizes
> their records?
>
> Is there an ODBC schema field that exists per row, cross-platform, that is
> the equivalent of DateLastModified so that I don't have to add it to each
> table?
>
> As you can see -- I'm at the preliminary stages of this, and any advice,
> tips, articles, code samples that you can point me to would be greatly
> appreciated.
>
> Thank you!
>
>
>

Relevant Pages

  • Re: About Design Requirements for Replication across different DBMSs...
    ... I still would need an identity column, a DateLastModified, but I have to add ... one simply modifies the SQL statements to "SELECT * from ... > SQLCE only supports merge replication. ... > Are you trying to replicate from SQLCE to MySQL using merge? ...
    (microsoft.public.sqlserver.replication)
  • Re: MySQL Clustering
    ... The following solves replication breaking over the same auto int race ... Prior to inserting anything into this database, ... mysql> select * from users; ... the user has a random chance of hitting any of your database servers ...
    (freebsd-isp)
  • Re: MySql Load balancing Solutions?
    ... I am part of a team running MySQL 4.1.X on 5 machines in a replication ... A positive advantage is that the 5 machines allows us the opportunity to ... slave to a "W"rite server until the original "W"rite server can be recovered. ... When it comes to management I won't lie, 4.0.XX's handling of Replication ...
    (freebsd-questions)
  • Re: HD Mirroring
    ... Maybe IŽll try to replicate the database on vinum volumes to get the best service performance from ... mysql, with the "failsafetyness" of vinum mirrors. ... > Is MySQL's replication not fast enough? ...
    (freebsd-hackers)
  • Re: SBS 2003 and Replication Errors with Remote DC
    ... I just promoted the remote DC last week, so I still have time to solve the replication issues. ... Domain Controller Diagnosis ... Connecting to directory service on server alpha. ... Performing upstream analysis. ...
    (microsoft.public.windows.server.sbs)