Re: About Design Requirements for Replication across different DBMS's...
From: Sky (forums_at_xact-solutions.removethis.com)
Date: 04/02/04
- Next message: Paul Ibison: "Re: Pull Merge replication between two SQL Server machines in different, but trusted domains"
- Previous message: Winston: "Re: C# Replication Between Microsoft SQL server 2000 and Microsoft Access"
- In reply to: Hilary Cotter: "Re: About Design Requirements for Replication across different DBMS's..."
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 2 Apr 2004 20:59:04 +0200
Hi Hilary: addendum...
Just came across some info at:
http://www.xpinneret.com/cwu09-01.html
which was about the Palm pilot, but it did clear up some issues.
I still would need an identity column, a DateLastModified, but I have to add
a byte column.
The palm pilots was simply 4 bytes:
dirty (ie: modified)
secret
locked
deleted
In other words, one simply modifies the SQL statements to "SELECT * from
table where id=... AND deleted=false (I havn't yet figured out how to write
the bit checking part in cross platform sql, but you get the point).
And instead of using a traditional DELETE statement, making it an UPDATE
statement that modifies the bit. Quite easy for that step.
Then we are back to synchronization (the article didn't cover that ;-( ) but
I think I am starting to get a less hazy view of what we're talking about...
"Hilary Cotter" <hilaryk@att.net> wrote in message
news:OI%236$fLGEHA.2980@TK2MSFTNGP09.phx.gbl...
> 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!
> >
> >
> >
>
>
- Next message: Paul Ibison: "Re: Pull Merge replication between two SQL Server machines in different, but trusted domains"
- Previous message: Winston: "Re: C# Replication Between Microsoft SQL server 2000 and Microsoft Access"
- In reply to: Hilary Cotter: "Re: About Design Requirements for Replication across different DBMS's..."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
