Database Replication

[karldmoore]

I'm just doing some investigation into database replication. The application is currently uses HSQLDB or MySQL (it could work with any other database if required). The application is starting to be deployed as an appliance, where more appliances can be dropped in at a later date to provide load balancing. As current each appliance co-locates the database, this won't work. As such two options have been presented, making one database shared between all appliances or replicating the data between the appliances. I'd personally prefer the former as the changes would be very minor. I'm afraid however the latter wil be required.

Has anyone had any experience with problems such as this? Anyone got any ideas of technologies to solve it? I've had a look at a few technologies with the link below looking like something that might be useful.
http://sequoia.continuent.org/HomePage

[jose.cervera]

Hi,

I guess you've already looked at the built-in possibilities of MySql, haven't you?

http://dev.mysql.com/doc/refman/5.1/en/replication.html

If I understand correctly, though, replicated statements are processed _after_ acknowledging the operation, in a special dedicated thread. So you may not be sure about the synchronization state of the nodes.

Then, of course, if you have some money to spend, you have Oracle

Regards.

[karldmoore]

I have had a read of the MySQL reference manual and had a play with it's replication. The problem here is with databases that don't support it out the box. I've been tasked with having a look into the alternatives. As for Oracle, I think the price of the database would outweigh the price of the project. I did have a look at Oracle Berkeley (I think it was), this said it supported replication, but the Java version didn't seem to have any information on this.

[al0]

Originally Posted by karldmoore

I have had a read of the MySQL reference manual and had a play with it's replication. The problem here is with databases that don't support it out the box. I've been tasked with having a look into the alternatives. As for Oracle, I think the price of the database would outweigh the price of the project. I did have a look at Oracle Berkeley (I think it was), this said it supported replication, but the Java version didn't seem to have any information on this.

Oracle in "Standard Edition" and, especially, "Standard One Edition" is not so really expensive and is sufficient for almost any purpose. As far as I can remember latter costs around USD 5000 for unlimited users license.

And one other thing "it could work with any other database if required". I do not buy such statements - different databases have huge differences not only in syntax, but also in semantic, the same SQL-statement can give different results on different databases (and I mean the situation when statement is syntactically correct on both databases). Moreover, even simplest arithmetical expression in the SELECT list give sometimes different results due to differnt stratigies to handle precision.

[karldmoore]

Originally Posted by al0

Oracle in "Standard Edition" and, especially, "Standard One Edition" is not so really expensive and is sufficient for almost any purpose. As far as I can remember latter costs around USD 5000 for unlimited users license.

Interesting. As a software vendor we'd have to ship the database with the application, not sure that would quite be the same price . I'll have a look into that.

Originally Posted by al0

And one other thing "it could work with any other database if required". I do not buy such statements - different databases have huge differences not only in syntax, but also in semantic, the same SQL-statement can give different results on different databases (and I mean the situation when statement is syntactically correct on both databases). Moreover, even simplest arithmetical expression in the SELECT list give sometimes different results due to differnt stratigies to handle precision.

I appreciate what you are saying. Sadly, it's one of those I'm the customer what I want goes kind of things. If they run oracle then we need to work with it, likewise SQLServer, DB2, etc..... It's not really that much of an issue as we do run custom SQL for each database type (if required).

I was basically looking for a general way to do replication if there was one. If not fair enough. Statement replication looked interesting, doing it in the database would also be good. I really don't want to have to go down the route of writing this myself, I'm not a fan of re-inventing wheels.

[al0]

Yes, it would not be the same price. If your company would register as Oracle Partner (it is relatevely cheap and easy) you can ship Oracle database with your product at quite discounted price as soon as your customer are entitled to use that database to run your application only(so known application-specific license). Better to contact Oracle for details as I do not remember them all. Anyway, it is not architecture question and hardly pertinent to discuss in this forum any further. But we can continue discussion in a private e-mail (as far as I remember this site supports them).

Concerning the customers - I'm at the same position. last system that I have developed runs (and is tested)against 3 different DBs -Oracle, DB2 for iSeries (AKA AS/400) and Derby. And I shall say that even for latest 2 there are differences in spite of that Derby was to great extend modelled after DB2.

It is not easy, but possible to develop application that support more then one database (as soon as application is not very demanding), it just not possible to say "application supports any and all relational databases".

The worst is that it is not alway clear when you need custom SQL for another database, as sometimes SQL is still legal and sems to work - but with subtle (or not so subtle ) differences that you may overlook while developing. Another hard to catch problem is quite different handling of concurrency, you never see any problem in single-user mode (and 95% percent of development are usually done in it), but it may have very adverse effects in production multi-user environment. These problems may affect both performance (in a very critical way) and even data integrity. I would strongly suggest books of Tom Kyte to anyone who plan such kind of development. They are mostly Oracle-oriented, but it always put enough attention to differences betweeen Oracle and other databases.

BTW, for databases, like SQL Server 2005, that support both traditional "pure locking" and "multiversioning" as concurrency strategies application behavior and dataintegrity may be compromised by switching of database settings!

One more remark - for "pure locking" and "multiversioning" databses quite different DB designs (and in some cases, applications designs) may be (and are) needed to optimise application performance.

Regards,

Oleksandr

Originally Posted by karldmoore

Interesting. As a software vendor we'd have to ship the database with the application, not sure that would quite be the same price . I'll have a look into that.



I appreciate what you are saying. Sadly, it's one of those I'm the customer what I want goes kind of things. If they run oracle then we need to work with it, likewise SQLServer, DB2, etc..... It's not really that much of an issue as we do run custom SQL for each database type (if required).

I was basically looking for a general way to do replication if there was one. If not fair enough. Statement replication looked interesting, doing it in the database would also be good. I really don't want to have to go down the route of writing this myself, I'm not a fan of re-inventing wheels.

[karldmoore]

Thanks again for the advice, I'll another read of this when I'm back in the office and I'll have a word with my boss.

[al0]

For that matter one more edition of Oracle exists - Oracle XE. It is completely free for any purpose. Definitely, it has some limitations, the most serios - database size (max. 4Gb) and lack of Java engine in the DB (server-side, client side JDBC is supported). Feature matrix with comparision of all editions (from XE to Enterprise) is located at http://www.oracle.com/technology/pro...uct_family.pdf

Regards,
Oleksandr

[karldmoore]

Thanks again Oleksandr. I did download OracleXE last week to have a play with. As usual, I got side tracked with other problems so I haven't had time to see what it does and doesn't support. I'll check this out first think next week, unless I get lumped with some other problems .

[al0]

Originally Posted by karldmoore

Thanks again Oleksandr. I did download OracleXE last week to have a play with. As usual, I got side tracked with other problems so I haven't had time to see what it does and doesn't support. I'll check this out first think next week, unless I get lumped with some other problems .

Hello,

I have not yet asked my favorite question - are you sure that you really need database replication and single DB for all appliances will not do (as you have stated)?

How many concurrent users are you anticipating? What is estimated DB size? How complicated are DB operations? How high is update/select ratio (here "update" is any operation that changes data)? What is requirment for avg/max response times?

Regards,
Oleksandr

Regards,