Tim,
You may already have this implemented but hidden the checksum column from access.

I'd like to see a generated (readonly) checksum column for each row that is based on all the row values. I could then use this column and the primary key, between 2 tables to determine which table rows have different data for the same key, and use this information to sync the tables. This would be a manual operation instead of using replication.

If this hasn't yet been implemented, then maybe a column type of "CRC" that would checksum all of the columns when the table row is saved.

Barry

Barry,

This could get expensive if you have large BLOB/CLOB columns.

Why not just add a "dirty" column or "last changed" timestamp column and
use a trigger to populate those?

How do you plan to deal with deleted rows? Unless you do row by row scan
another table that is populated by delete trigger could store the unique
ids.

Or of course you could use the built-in replication though you indicated
you don't want to.

Raul


On 11/17/2012 1:17 PM, Barry wrote:
> Tim,
> You may already have this implemented but hidden the checksum column from access.
>
> I'd like to see a generated (readonly) checksum column for each row that is based on all the row values. I could then use this column and the primary key, between 2 tables to determine which table rows have different data for the same key, and use this information to sync the tables. This would be a manual operation instead of using replication.
>
> If this hasn't yet been implemented, then maybe a column type of "CRC" that would checksum all of the columns when the table row is saved.
>
> Barry
>

Raul wrote:

>Or of course you could use the built-in replication though you indicated
>you don't want to.

The more I think about it, the more I am inclined to lean towards EDB replication. I haven't used EDB replication yet, so I have one question that is nagging me.

What happens if I have a client with my application on 2+ of their computers with identical table structure for their database, and clients have been entering data into them for several months. You can think of each location as a branch office.

Then the client wants to "upgrade" my software and start replicating the data between them. How does the EDB replication know which rows to transfer to the other computer? (There are some rows that have the same values, like tax codes etc.) The 2+ databases need to be initially sync'd months after the data has been entered. Can EDB do this?

Barry

Barry,

Others who have replication in production might be able to provide
better opinions on this but here's my 2 cents :

Unfortunately this is something that is dependent on the actual and db
design so you would need to resolve this yourself before you start with
replication. Also some of the final design will depend on what you want
to replicate.

It's likely going to be quite a bit more complex that just common data -
how did you assign your unique IDs (primary keys) for example?

If you're using autoinc columns then that is likely a problem since
different sites might have the same tax code (or some other lookup
field) with different ID so their databases are basically different. if
you're using guids for example then IDs are OK but the data context is
still an issue - you obviously need to get the tax code to be the same
ID on both sides i assume so one database needs to be updated.

I would suggest you think of this as merging exercise first - what if
the 2 remote branches had to work from same DB? How would you merge the
databases into a single one.

Then i would review EDB docs and sampels and try out the replication as
you might need to re-design your database structures (IDs etc) and
possibly other processes (like how new tax code changes are entered -
add to one and replicate etc).

Raul


On 11/19/2012 11:04 AM, Barry wrote:
> The more I think about it, the more I am inclined to lean towards EDB replication. I haven't used EDB replication yet, so I have one question that is nagging me.
> What happens if I have a client with my application on 2+ of their computers with identical table structure for their database, and clients have been entering data into them for several months. You can think of each location as a branch office.
> Then the client wants to "upgrade" my software and start replicating the data between them. How does the EDB replication know which rows to transfer to the other computer? (There are some rows that have the same values, like tax codes etc.) The 2+ databases need to be initially sync'd months after the data has been entered. Can EDB do this?

Raul wrote:

>>Others who have replication in production might be able to provide
better opinions on this but here's my 2 cents :<<

>>Unfortunately this is something that is dependent on the actual and db
design so you would need to resolve this yourself before you start with
replication. Also some of the final design will depend on what you want
to replicate.<<

>>It's likely going to be quite a bit more complex that just common data -
how did you assign your unique IDs (primary keys) for example?>>

>>If you're using autoinc columns then that is likely a problem since
different sites might have the same tax code (or some other lookup
field) with different ID so their databases are basically different. if
you're using guids for example then IDs are OK but the data context is
still an issue - you obviously need to get the tax code to be the same
ID on both sides i assume so one database needs to be updated<<

I'm using GUID throughout. I would be supplying the tax codes to all clients so the table data is identical.

>>I would suggest you think of this as merging exercise first - what if
the 2 remote branches had to work from same DB? How would you merge the
databases into a single one.<<

Good point. I have Branch_Id GUID on most table rows.

>>Then i would review EDB docs and sampels and try out the replication as
you might need to re-design your database structures (IDs etc) and
possibly other processes (like how new tax code changes are entered -
add to one and replicate etc).<<

I have a spare machine I'm itching to get EDB Server on and try out replication. I guess once I figure out how replication works, I can worry about the initial syncing the databases later (that's why I wanted a checksum on each row).

Thanks for your input.

Barry

Barry,

<< You may already have this implemented but hidden the checksum column from
access. >>

No, we had something like this in DBISAM but scrapped it in EDB because it
wasn't practical for determining whether two rows (in the same table) were
the same or not w/ regard to BLOBs.  EDB uses an update counter along with a
unique row number to do the same as what DBISAM uses checksums for.

<< I'd like to see a generated (readonly) checksum column for each row that
is based on all the row values. I could then use this column and the primary
key, between 2 tables to determine which table rows have different data for
the same key, and use this information to sync the tables. This would be a
manual operation instead of using replication. >>

I would seriously consider using the replication instead.  It's got all of
the kinks worked out for you.

Tim Young
Elevate Software
www.elevatesoft.com

Barry,

<< Then the client wants to "upgrade" my software and start replicating the
data between them. How does the EDB replication know which rows to transfer
to the other computer? (There are some rows that have the same values, like
tax codes etc.) The 2+ databases need to be initially sync'd months after
the data has been entered. Can EDB do this? >>

You're going to have to do a manual merge first between the two before
starting the replication for both databases.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com