Hi

Is there a way to archive this?

In a table with a column called “mirrored” time stamp

Set mirrored no null if any row in the table is modified. Except the column “mirrored” itself?

This would be the base for a system to mirror all our databases in other server. With a program querying the column mirrored and act according insert/update every minute. With no databases locked in the process.

We do not allow deletes in our system so there is not an issue with deletes.

Thanks for the advice.

Jorge


You could do this with triggers but you'd be better of using the built in replication features.

Roy Lambert

Jorge,

<< In a table with a column called “mirrored” time stamp

Set mirrored no null if any row in the table is modified. Except the column “mirrored” itself?

This would be the base for a system to mirror all our databases in other server. With a program querying the column mirrored and act according insert/update every minute. With no databases locked in the process.

We do not allow deletes in our system so there is not an issue with deletes. >>

You're not going to want to design any system that doesn't use locking and expect it to work properly, especially if you're talking about designing a mirroring/replication system.  As Roy indicated, if you're trying to perform a hot backup, etc., then you're going to want to use the built-in replication.  There are all sorts of issues that the replication takes care of for you that are very hard to replicate, such as using Lamport timestamps to ensure that all updates are replicated in the exact same order as they were executed, even across multiple tables:

https://en.wikipedia.org/wiki/Lamport_timestamps

Tim Young
Elevate Software
www.elevatesoft.com

Hello Tim.

<<You're not going to want to design any system that doesn't use locking and expect it to work properly, especially if you're talking about designing a mirroring/replication system.  As Roy indicated, if you're trying to perform a hot backup, etc., then you're going to want to use the built-in replication>>

All my insistation with this questions, is that some systems can not afford to lock the write access to the database not even 1 minute in a 24x7.

The backup locks the database, and the save updates too (like a smaller backup),

in our case, the databases grow about 1gb a day and is very intense and transactional.

i agree that the elevatedb replication system is robust, and is a very good solution for 99% of the systems, that can afford some time with no write transactions. but in our case, is a little issue, thant can be worked on. because like you say in some of your posts. Nothing is imposible.

Just one little idea:

When you "save updates", maybe a second file could take over, and store the updates that you missed while the saving update is being proccesed and stored. After finish, the second file become primary. i guess that in that way, you dont have to acquire the lock while your are saving the current update and the tables still always ready to write access.

This is just one idea, i admire your work and i know that there is a lot of things regarding the actual replication model that i am not aware of it.

Best regards.

Jorge


Is your system primarily table based or query based?

Roy Lambert

Jorge

I would support other's suggestions on this forum that you review EDB's replication capabilities. These include the creation of update files which can be reviewed and interrogated, and which capture all changes to a DB, and allow it to be perfectly synchronized with another copy of the DB.

This can be done between 1 or more central DB(s) and almost any number of backups or secondary sites. I use this feature widely and it makes for a very robust architecture, as it means there are always multiple copies of data in different locations which are automatically keeping pace with each other.

It is not suitable where data is highly time-critical, or where all people accessing the data want to see _exactly_ the same data all the time. Users have to get used to the idea of some small delays in some data arriving (ie from remote offices/warehouses) but if this is not an issue it is a really good facility.

The only real requirement is a system to generate a genuinely unique primary key for every row of every table.

Roy,

<Is your system primarily table based or query based?>

All our tables has primary keys to optimize row updates.

Hello,

<<It is not suitable where data is highly time-critical>>

This is exactly the issue, tables receiving new rows every second of the day. If you lock the table, even 5 seconds, to publish the update, this particular system, will fail, because there is a lot of little deamons, working across a group of 5 servers against the database, and always having at least 30 users in datasnap sessions against the databases, and 50 users locally against the same databases.

Having the replication database out of sync several minutes, is not the issue.

but the solution for us, is a custom replication system, because ther is only 2 or 3 tables that are specifically critical for the businness and they are suppossed to be in separate severs duplicated all the time.

for full backup, and incremental published updates, we can have an open window between 2am or 3am, to do it. Maybe a full once a month, and the rest published updates.

its only those 2 or 3 critical tables, that contains financials trx, that has to be mirrored basically online.

All the creations and updates in our system in critical tables,will go to the database replica too as a part of the logic program.

This could be work, because all of aour tables has primary keys. and we don not allow deletes in any table.

and i agree with you.. The publish and updates system is very robust, and suitable for most of the cases. i am using it allready.

best regards.

Jorge Ortiz wrote:

>  If you lock the table, even 5 seconds, to publish the update, this particular system, will fail

While I understand your point, this statement would also worry me a lot. Sounds pretty fragile. I'd want each writer to be in a basic retry loop so that it can handle a simple short lock. Servers are going to need a restart occasionally, and that will be longer.

I just think the chaos monkey will strike and if it is that critical, it needs to be more robust.

--

Matthew Jones

Jorge


I'm having difficulty in making a reply without causing offence. I've deleted four before posting so far. I apologise in advance if anything I post below offends. That's not my aim. On this forum we like to help but it can be difficult.

You are asking very specific questions without giving enough information. It comes across as though you've got yourself in a corner with a system design that should have been better thought though to start with. As Matthew says what you have sounds very fragile.

What we don't know so far:

1. transaction volumes
2. database design
3. operational mode (are databases updated via sql or table navigational methods)
4. database size

There'll be more. I know there's a language barrier to overcome but from your posts I'm very uncertain as to your skill level. I have no idea how the system operates apart from the fact that there are positings going in every second and that you never delete anything. So here's another question or so

5. is the system simply used to record input or are reports produced
6. what is the maximum acceptable delay between something being posted and it appearing in the database
7. do the postings have to go into the live database or could there be an intermediate database for data capture
8. do you have any disaster recovery plans
9. you have 5 servers (30 users) and 50 local users - how are they connected


Looking at what you've posted so far I also have to ask if you've considered hardware / cloud based solutions

There are a lot of ways that things can be done to help - using ElevateDB triggers (horrible to write but workable) to write out to a duplicate table, subclassing the TEDBTable component to do the same thing, using the inbuilt replication, paying someone to do a custom replication system for you, disk mirroring, cloud mirror, but without knowing a lot more I wouldn't like to suggest an approach.

Roy Lambert