I have been working with Advantage Database for over 12 years and last year I moved over to ElevateDB and now with the new release including a migration from ADS to EDB, I tried a test. One of the features we use a lot in ADS is the replication. I looked at the EDB and I'm confused about the replication.
Here is how it works in ADS:
1) You create a Publication were you select the tables you wish to replicate. You can uncheck fields that you don't which to replicate, like field with auto generated numbers.

2) Then you create a Subscription or many of them if you which to replicate the Publication to many servers. In the Subscription you select the IP address, port, login, password and path to the database files.

That is it. Every time there is any change to any of the tables the server replicates them instantly, you have an option to pause or disable the Subscription.

Until now I found that I can Publish the Database and it does not look like I can select which fields in the database is published.

I search the forum but what I found was that I had to create a Store and then manually execute the replication, hope I'm wrong.

Can anyone explain how the replication works?

Thanks,
Kim

KimHJ wrote:

> 2) Then you create a Subscription or many of them if you which to replicate the Publication to many servers. In the Subscription you select the IP address, port, login, password and path to the database files.
> Can anyone explain how the replication works?

Not me, but I presume you have seen http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Replication and the other linked articles.

Thing is, the way you describe it working in Advantage doesn't sound at all safe in this modern world. Having the database server having an open connection for anyone to get into is a risk. Obviously if it is all internal, then that's fine, but replication is often to "disconnected" locations. Thus having a way to save and then push the changes should be more secure. At the least, it is a different way of doing things, but should be just as robust. And by having separate files you should be able to keep them and roll back to various places for repeats etc.

Anyway, I expect someone who uses this will be along soon to explain the realities.

--

Matthew Jones

"Matthew Jones" wrote:

>>Not me, but I presume you have seen http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Replication and the other linked articles.

Thanks, I didn't look in the SQL manual only the database manual. I can see that this is not an automatic process. It looks like I have to create a program in each end that can run the Update and the Copy.

>>Thing is, the way you describe it working in Advantage doesn't sound at all safe in this modern world. Having the database server having an open connection for anyone to get into is a risk. Obviously if it is all internal, then that's fine, but replication is often to "disconnected" locations. Thus having a way to save and then push the changes should be more secure. At the least, it is a different way of doing things, but should be just as robust. And by having separate files you should be able to keep them and roll back to various places for repeats etc.

It worked fine with us for over 10 years with no problems. You would have to know the port, password and username to get access. We have clients that have many stores, but local database server. We replicate into the main office and then you can set it up to automatic re-replicate. So if a customer comes into one store and get entered into database, the customer will within 2 minutes show in all stores database.

Kim

KimHJ

Advantage sounds a bit easier to implement than Elevate. However the Elevate system is more open and programmable to allow more diverse replication, for example replication with multiple "layers". Perhaps Advantage also does this.

What I can say is that EDB does work reliably, and I use its replication features with my customers.

Anyway if you want to work with EDB you will have to get to grips with the more complex process.

A while back I wrote a "test bed" system which does the minimum necessary to ensure replication:

http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_binaries&msg=81&page=1

--

I think it is worth reviewing this.

--

To summarize the EDB Process:

Once an Elevate database is Published EDB starts to create "PBL" files in the database folder. These contain Update-data. You / the Admin has to convert these PBL files into "Update files" (by calling SAVE UPDATES FOR DATABASE "<Your DB>" TO STORE <UpdatesStore> )

You then have to manually write code to move these files, to "User Stores", so every user has copies of every update.

On the "User" side you have to copy the files to a local store then call "LOAD UPDATE <UpdateFileName>" for EVERY file. All UPD files must be applied in date order, and none should be applied more than once ...

So there is quite a lot of "clockwork" in the system, which you have to build. However it does allow things like split database schema, where one set of users share access to some tables, while other users only access others.

Elevate DOES NOT support partial Publishing of tables (i.e. only publishing some fields) Publishing is managed at TABLE level. You can change settings in the Update process to stop triggers from re-running to avoid certain types of update.

Elevate REQUIRES a unique Key field in every table of a Published Database. This can be a GUID field, or some Integer that is guaranteed to be unique.

I think you will have to spend a bit of time getting to know how the system works! It is fairly SQL intensive, but quite robust.

Adam Brett wrote:

>>Advantage sounds a bit easier to implement than Elevate. However the Elevate system is more open and programmable to allow more diverse replication, for example replication with multiple "layers". Perhaps Advantage also does this.

>>I think you will have to spend a bit of time getting to know how the system works! It is fairly SQL intensive, but quite robust.

Thanks Adam.

I did try a test yesterday between two computers and I can see I need to create some programs or service that automatic execute the SQL.

My next test is to see what happens when I update a table with a AutoInc field, will it AutoInc according to the setting of the table or will it use the value from the Update table?

Kim

KimHJ wrote:
<<
I did try a test yesterday between two computers and I can see I need to create some programs or service that automatic execute the SQL.
>>

You can but you don't have to - you can create EDB jobs that execute the SQL on regular interval for example so this all can be fully contained in EDB

Raul

Kim,

<< Until now I found that I can Publish the Database and it does not look like I can select which fields in the database is published. >>

No, tables are published on a whole-table basis.

<< I search the forum but what I found was that I had to create a Store and then manually execute the replication, hope I'm wrong. >>

Please see this technical article:

http://www.elevatesoft.com/articles?action=view&category=edb&article=building_sales_quote_replication_system_elevatedb

It explains how to set up the stores/jobs needed to automate the transfer of the update files for replication.

Tim Young
Elevate Software
www.elevatesoft.com

Kim,

<< My next test is to see what happens when I update a table with a AutoInc field, will it AutoInc according to the setting of the table or will it use the value from the Update table? >>

It depends upon how the table column is defined in the target table:

GENERATED BY DEFAULT AS IDENTITY...

vs.

GENERATED ALWAYS AS IDENTITY...

In general, you don't want to use IDENTITY columns with replication without some additional planning up-front because they have no concept of a global identify space.  The technical article on replication that I linked to in my other response covers the topic of ranged identify columns for replication.

Tim Young
Elevate Software
www.elevatesoft.com

Kim,

<< My next test is to see what happens when I update a table with a AutoInc field, will it AutoInc according to the setting of the table or will it use the value from the Update table? >>

AutoInc fields are complex in Replicated situations. Remember each DB is atomic, so it can't see the others. If any AutoInc is re-used you will get clashes.

In my system I have a small separate database on each instance of the database with a source table for AutoInc numbers, and I put a different starting "seed" integer into it for each user. In this way you are confident that every user uses unique AutoInc values.

However, these values will no longer follow "clean" ranges, i.e. there will be gaps between different machines.

I am sure this issue must also exist on Advantage-database systems?

Adam Brett wrote:

>>I am sure this issue must also exist on Advantage-database systems?

No, in Advantage you can exclude columns. I do not replicate AutoInc columns I let the receiving table assign an Integer to the column.

I can see that it will be a problem for users of Advantage Database that use replication to convert to EDB, since the replication in Advantage Database is very easy and all automatic.

Kim